构建spring+dbcp连接池+sqlite

我这两周在开发公司自己的搜索引擎，主要是用lucene，配置数据我设计是用sqlite，所有的配置通过spring进行串联

1.准备jar包

spring.jar

commons-pool.jar

commons-logging.jar

commons-dbcp.jar

2.导入jar包到工程里

3.创建数据库操作类

package com.search.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import org.apache.commons.dbcp.BasicDataSource;


/**
* 连接和使用数据库资源的工具类
*
* @author yifangyou
* @version search 2012-03-12
*/
public class DatabaseSqliteUtil {

/**
* 数据源
*/
private BasicDataSource dataSource;

/**
* 数据库连接
*/
public Connection conn;

/**
* 获取数据源
* @return 数据源
*/
public BasicDataSource getDataSource() {
return dataSource;
}

/**
* 设置数据源
* @param dataSource 数据源
*/
public void setDataSource(BasicDataSource dataSource) {
this.dataSource = dataSource;
}


/**
* 获取数据库连接
* @return conn
*/
public Connection getConnection() {
try {
conn = dataSource.getConnection();
} catch (Exception e) {
e.printStackTrace();
return null;
}
return conn;
}

/**
* 关闭数据库连接
* @param conn
*/
public static void closeConnection(Connection conn) {
if (null != conn) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/**
* 获取执行SQL的工具
* @param conn 数据库连接
* @return stmt
*/
public static int getFoundRows(Connection conn) {
Statement stmt=null;
ResultSet rs=null;
try {
stmt=getStatement(conn);
rs=stmt.executeQuery("SELECT FOUND_ROWS()");
if(rs.next()){
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeStatement(stmt);
closeResultSet(rs);
}
return 0;
}

/**
* 获取执行SQL的工具
* @param conn 数据库连接
* @return stmt
*/
public static Statement getStatement(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}

return stmt;
}

/**
* 获取执行SQL的工具
* @param conn 数据库连接
* @param sql SQL语句
* @return prepStmt
*/
public static PreparedStatement getPrepStatement(Connection conn, String sql) {
PreparedStatement prepStmt = null;
try {
prepStmt = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return prepStmt;
}

/**
* 关闭数据库资源
* @param stmt
*/
public static void closeStatement(Statement stmt) {
if (null != stmt) {
try {
stmt.close();
stmt = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/**
* 关闭数据库资源
* @param prepStmt
*/
public static void closePrepStatement(PreparedStatement prepStmt) {
if (null != prepStmt) {
try {
prepStmt.close();
prepStmt = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/**
* 获取结果集
* @param stmt 执行SQL的工具
* @param sql SQL语句
* @return 结果集
*/
public static ResultSet getResultSet(Statement stmt, String sql) {
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}

/**
* 关闭数据库资源
* @param rs
*/
public static void closeResultSet(ResultSet rs) {
if (null != rs) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static Boolean setAutoCommit(Connection conn,boolean commitStatus){
if(conn==null){
return true;
}
try {
boolean commit = conn.getAutoCommit();
conn.setAutoCommit(commitStatus);
return commit;
} catch (SQLException e1) {
e1.printStackTrace();
return true;
}
}
public static boolean rollback(Connection conn,boolean oldCommitStatus){
if(conn==null){
return true;
}
try {
conn.rollback(); // 事物回滚
conn.setAutoCommit(oldCommitStatus);
return true;
} catch (SQLException e1) {
e1.printStackTrace();
return false;
}
}
public static boolean commit(Connection conn,boolean oldCommitStatus){
if(conn==null){
return true;
}
try {
conn.commit(); // 事物回滚
conn.setAutoCommit(oldCommitStatus);
return true;
} catch (SQLException e1) {
e1.printStackTrace();
return false;
}
}
public static int getLastId(PreparedStatement ps){
ResultSet rs=null;
try {
rs = ps.getGeneratedKeys();
if (rs != null&&rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();

}finally{
closeResultSet(rs);
}
return -1;
}
/**
* 判断是否是管理员
* @param conn mysql连接
* @param ip 请求ip
* @param password 管理员密码
* @author yaofuyuan
* @since 2011-08-02 12:58:00
* @return void 0：不是,1:是,-1:数据库出错
*/
public int isSuperAdmin(Connection conn,String ip,String password){
if(conn==null){
return -1;
}
PreparedStatement ps =getPrepStatement(
conn,
"select count(*) as count from auth_admin_server where ip=? and password=?");
ResultSet rs = null;
try {
// 查询帐号，用户名和密码
ps.setString(1, ip);
ps.setString(2, password);
rs=ps.executeQuery();
if (rs.next()) {
if(rs.getInt("count")==0){
//用户名密码错误
return 0;
}else{
return 1;
}
}
return -1;
}
catch(Exception e){
e.printStackTrace();
return -1;
}finally{
closeResultSet(rs);
closePrepStatement(ps);
}

}


public int test(Connection conn){

PreparedStatement pst =getPrepStatement(conn, "select 123");
// 获取结果集
ResultSet rs = null;
try {
rs = pst.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库资源
closeResultSet(rs);
closePrepStatement(pst);
}

return -1;
}


}

2.创建applicationContent.xml

<?xml version="1.0" encoding="UTF-8"?>

<!--
  - Application context definition for JPetStore's business layer.
  - Contains bean references to the transaction manager and to the DAOs in
  - dataAccessContext-local/jta.xml (see web.xml's "contextConfigLocation").
  -->
<beans xmlns=""
        xmlns:xsi=""
        xmlns:aop=""
        xmlns:tx=""
        xsi:schemaLocation="
             /spring-beans-2.5.xsd
             /spring-aop-2.5.xsd
             /spring-tx-2.5.xsd">

      <!-- =================================== 配置Spring数据源 ========================================= -->
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
            destroy-method="close">
        <property name="driverClassName" value="org.sqlite.JDBC" />
        <property name="url" value="jdbc:sqlite:search.db" />
        <property name="maxIdle" value="10"/>
        <property name="maxActive" value="100"/>
        <property name="maxWait" value="10000"/>
        <property name="validationQuery" value="select 1"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="1200000"/>
        <property name="minEvictableIdleTimeMillis" value="1800000"/>
        <property name="numTestsPerEvictionRun" value="5"/>
        <property name="defaultAutoCommit" value="true"/>
    </bean>
    <!--
        BasicDataSource提供了close()方法关闭数据源，所以必须设定destroy-method=”close”属性，
        以便Spring容器关闭时，数据源能够正常关闭。除以上必须的数据源属性外，
         还有一些常用的属性：
        defaultAutoCommit：设置从数据源中返回的连接是否采用自动提交机制，默认值为 true；
        defaultReadOnly：设置数据源是否仅能执行只读操作， 默认值为 false；
        maxActive：最大连接数据库连接数，设置为0时，表示没有限制；
        maxIdle：最大等待连接中的数量，设置为0时，表示没有限制；
        maxWait：最大等待秒数，单位为毫秒， 超过时间会报出错误信息；
        validationQuery：用于验证连接是否成功的查询SQL语句，SQL语句必须至少要返回一行数据，
                          如你可以简单地设置为：“select count(*) from user”；
        removeAbandoned：是否自我中断，默认是 false ；
        removeAbandonedTimeout：几秒后数据连接会自动断开，在removeAbandoned为true，提供该值；
        logAbandoned：是否记录中断事件， 默认为 false；
     -->
    <bean id="databaseUtil" class="com.search.util.DatabaseSqliteUtil">
        <property name="dataSource" ref="dataSource" />
    </bean>


        <bean id="Main" class="com.search.main.Main" scope="prototype">
            <property name="databaseUtil" ref="databaseUtil" />
        </bean>
</beans>

4.使用

package com.search.main;

import java.sql.Connection;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.search.tool.Spider;
import com.search.util.DatabaseSqliteUtil;


public class Main {

    public static void main(String[] args) {

        ApplicationContext beanFactory=new FileSystemXmlApplicationContext("D:/workspace/lucene-3.5.0/cn_applicationContext.xml");
        DatabaseSqliteUtil databaseUtil=(DatabaseSqliteUtil)beanFactory.getBean("databaseUtil");
        Connection conn=databaseUtil.getConnection();
        System.out.println(databaseUtil.test(conn));
        DatabaseSqliteUtil.closeConnection(conn);

//        Spider ph = new Spider("http://www.test.dev");
//        ph.openWriteIndexDB("D:/workspace/lucene-3.5.0/search-db");
//        try {
//            //ph.processHtml();
//            //Thread search = new Thread(ph);
//            //search.start();// 启动线程
//            ph.crawl(ph.getLinklist());
//        } catch (Exception ex) {
//
//        }
//        ph.closeWriteIndexDB();
        //search();
    }
}

执行结果

end