MySQL数据库连接池的简单应用

/**
 * 使用连接池访问MySQL数据库
 */

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class DataSource_test {
	
	private DataSource initDatabase(String url, String driverClassName,
			String userName, String password) {
		PoolProperties p = new PoolProperties();
		p.setUrl(url);
		p.setDriverClassName(driverClassName);
		p.setUsername(userName);
		p.setPassword(password);
		p.setJmxEnabled(true);
		p.setTestWhileIdle(false);
		p.setTestOnBorrow(true);
		p.setValidationQuery("SELECT 1");
		p.setTestOnReturn(false);
		p.setValidationInterval(30000);
		p.setTimeBetweenEvictionRunsMillis(30000);
		p.setMaxActive(100);
		p.setInitialSize(10);
		p.setMaxWait(10000);
		p.setRemoveAbandonedTimeout(60);
		p.setMinEvictableIdleTimeMillis(30000);
		p.setMinIdle(10);
		p.setLogAbandoned(true);
		p.setRemoveAbandoned(true);
		p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
				+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
		DataSource dataSource = new DataSource();
		dataSource.setPoolProperties(p);
		return dataSource;
	}
	
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import org.apache.tomcat.jdbc.pool.DataSource;

public class ProcessThread implements Runnable {
	
	private final static String SQL = "SELECT * FROM persons WHERE age>? AND time>? AND home=?";
	
	private DataSource dataSource;
	private int i = 0;
	private String[] citys = {"Beijing, Shanghai, Nanjing, Shenzhen, Xian"};
	
	public ProcessThread(DataSource dataSource, int i){
		this.dataSource = dataSource;
	}

	@Override
	public void run() {
		// TODO Auto-generated method stub
		ResultSet result = null;
		try {
			Connection conn = dataSource.getConnection();
			PreparedStatement preState = conn.prepareStatement(SQL);
			preState.setInt(1, 20);
			preState.setTimestamp(2, new Timestamp(new Date().getTime()-24*60*60*1000));
			preState.setString(3, citys[i]);
			result = preState.executeQuery();
			while(result.next()){
				System.out.println(result.getString(1));
			}
			result.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

注：

    1，用多线程连接数据库进行操作的时候可以使用 dataSource.getConnection();获取多个连接，当操作结束后会自动回收连接。

    2，用PreparedStatement预编译的 SQL 语句的对象可以避免SQL语句注入。

    3，用PreparedStatement添加SQL语句的参数不能为数据库表名。

所需jar包：

mysql-connector-java-5.1.18