- 在JDBC中使用BoneCP
在JDBC中使用BoneCP相当简单,设置一些关于数据库连接池的参数信息,比如连接池的最大、最小连接数等。下面是一个简单的例子:
- package com.netskycn;
- import java.sql.Connection;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import com.jolbox.bonecp.BoneCP;
- import com.jolbox.bonecp.BoneCPConfig;
- /** 测试bonecp数据库连接池的工具
- * @author zhoufoxcn 周公
- * @version 0.1
- * 说明:这是一个在JDBC中使用BoneCP的例子
- * 2010-11-23
- */
- public class MainClass {
- public static void main(String[] args) {
- BoneCP connectionPool = null;
- Connection connection = null;
- try {
- //加载JDBC驱动
- Class.forName("com.mysql.jdbc.Driver");
- } catch (Exception e) {
- e.printStackTrace();
- return;
- }
- try {
- //设置连接池配置信息
- BoneCPConfig config = new BoneCPConfig();
- //数据库的JDBC URL
- config.setJdbcUrl("jdbc:mysql:///jobeet");
- //数据库用户名
- config.setUsername("root");
- //数据库用户密码
- config.setPassword("jeri");
- //数据库连接池的最小连接数
- config.setMinConnectionsPerPartition(5);
- //数据库连接池的最大连接数
- config.setMaxConnectionsPerPartition(10);
- //
- config.setPartitionCount(1);
- //设置数据库连接池
- connectionPool = new BoneCP(config);
- //从数据库连接池获取一个数据库连接
- connection = connectionPool.getConnection(); // fetch a connection
- if (connection != null){
- System.out.println("Connection successful!");
- Statement stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery("SELECT * FROM customer");
- while(rs.next()){
- System.out.println(rs.getInt(1)+":"+rs.getString("firstname")+","+rs.getString("lastname"));
- }
- }
- //关闭数据库连接池
- connectionPool.shutdown();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- if (connection != null) {
- try {
- connection.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
- }
====================一个池的例子========DatabaseConnectionPool.java=========================
package org.dave.common.database;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ResourceBundle;
//import org.slf4j.Logger;
//import org.slf4j.LoggerFactory;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
/**
* 数据库连接池
* @author David Day
*/
public final class DatabaseConnectionPool {
public static void main(String[] args) {
// test for "conf/sysconfig.ini"
// startup();
//
Connection conn = DatabaseConnectionPool.getConnection();
if(null != conn){
System.err.println("conn is not null");
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
}else{
System.err.println("conn is null=====");
}
}
static{
startup();
}
private static ResourceBundle BUNDLE = ResourceBundle.getBundle("conf/sysconfig");//从sysconfig.ini中获取
private static final String DRIVER = "driver";
private static final String URL = "default.url";
private static final String USERNAME = "default.user";
private static final String PASSWORD = "default.password";
private static final String MAX_CONNECTION = "db.maxConn";
private static BoneCP pool;
/**
* 开启连接池
*/
public static void startup() {
System.err.println("begin to startup...");
try {
// for test
// System.err.println("DRIVER " + BUNDLE.getString(DRIVER));
// System.err.println("URL " + BUNDLE.getString(URL));
// System.err.println("USERNAME " + BUNDLE.getString(USERNAME));
// System.err.println("PASSWORD " + BUNDLE.getString(PASSWORD));
// System.err.println("MAX_CONNECTION " + BUNDLE.getString(MAX_CONNECTION));
BUNDLE = ResourceBundle.getBundle("conf/sysconfig");
Class.forName(BUNDLE.getString(DRIVER));
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl(BUNDLE.getString(URL));
config.setUsername(BUNDLE.getString(USERNAME));
config.setPassword(BUNDLE.getString(PASSWORD));
config.setMaxConnectionsPerPartition(Integer.parseInt(BUNDLE.getString(MAX_CONNECTION)));
config.setPartitionCount(3);
pool = new BoneCP(config);
System.out.println("已经创建的数据库连接(startup)"+pool.getTotalCreatedConnections());
System.out.println("已创建未使用(startup)"+pool.getTotalFree());
System.out.println("正在使用(startup)"+pool.getTotalLeased());
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 关闭连接池
*/
public static void shutdown() {
pool.shutdown();
}
/**
* @return 数据库连接
*/
public static Connection getConnection() {
try {
return pool.getConnection();
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
// throw new DatabaseException(e);
return null;
}
}
public static void closeConnection(Connection conn) {
if(null != conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != pool){
System.out.println("已创建["+pool.getTotalCreatedConnections()+"]未使用["+pool.getTotalFree()+"]在使用["+pool.getTotalLeased()+"]");
}
}
}
========================对应的数据库存储类===========================================
package org.dave.common.database.access;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.dave.common.database.convert.ResultConverter;
/**
* 数据存取类
* @author David Day
*/
public abstract class DataAccess {
/**
* 日志工具
*/
// private static final Logger LOG = LoggerFactory.getLogger(DataAccess.class);
/**
* 数据库连接
*/
private Connection conn;
/**
* @param conn 数据库连接
*/
protected DataAccess(Connection conn) {
this.conn = conn;
}
/**
* 插入数据
* @param sql
* @param generatedKeysConverter 主键映射
* @param params
* @return 主键
* @throws DataAccessException
*/
protected <T> T insert(String sql, ResultConverter<T> generatedKeysConverter, Object... params) throws DataAccessException {
try {
PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
setParameters(pstmt, params);
executeUpdate(pstmt);
ResultSet rs = pstmt.getGeneratedKeys();
nextResult(rs);
return convertResult(rs, generatedKeysConverter);
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
throw new DataAccessException(e);
}
}
/**
* 更新数据
* @param sql
* @param params
* @return 影响行数
* @throws DataAccessException
*/
protected int update(String sql, Object... params) throws DataAccessException {
return executeUpdate(getPreparedStatement(sql, params));
}
/**
* 查询单个结果
* @param <T>
* @param sql
* @param converter
* @param params
* @return
*/
protected <T> T queryForObject(String sql, ResultConverter<T> converter, Object... params) {
ResultSet rs = executeQuery(sql, params);
if (nextResult(rs)) {
return convertResult(rs, converter);
} else {
return null;
}
}
/**
* 查询结果列表
* @param <T>
* @param sql
* @param converter
* @param params
* @return
*/
protected <T> List<T> queryForList(String sql, ResultConverter<T> converter, Object... params) {
ResultSet rs = executeQuery(sql, params);
List<T> list = new ArrayList<T>();
while (nextResult(rs)) {
list.add(convertResult(rs, converter));
}
return list;
}
/**
* @param sql SQL语句
* @return 预编译声明
*/
private PreparedStatement getPreparedStatement(String sql, Object... params) throws DataAccessException {
PreparedStatement pstmt = getPreparedStatement(sql);
setParameters(pstmt, params);
return pstmt;
}
/**
* @param sql SQL语句
* @return 预编译声明
*/
private PreparedStatement getPreparedStatement(String sql) throws DataAccessException {
try {
return conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
throw new DataAccessException(e);
}
}
/**
* 为预编译声明传入参数
* @param pstmt 预编译声明
* @param params 参数
* @throws DataAccessException
*/
private void setParameters(PreparedStatement pstmt, Object... params) throws DataAccessException {
try {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
throw new DataAccessException(e);
}
}
/**
* 执行更新操作
* @param pstmt
* @return 影响行数
* @throws DataAccessException
*/
private int executeUpdate(PreparedStatement pstmt) throws DataAccessException {
try {
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
throw new DataAccessException(e);
}
}
/**
* 执行查询操作
* @param pstmt 预编译声明
* @return 结果集
* @throws DataAccessException
*/
private ResultSet executeQuery(PreparedStatement pstmt) throws DataAccessException {
try {
return pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
throw new DataAccessException(e);
}
}
/**
* 执行查询操作
* @param sql SQL语句
* @param params 参数
* @return 结果集
* @throws DataAccessException
*/
private ResultSet executeQuery(String sql, Object... params) throws DataAccessException {
return executeQuery(getPreparedStatement(sql, params));
}
/**
* 移动到下一行记录
* @param rs 结果集
* @return 是否有下一行记录
* @throws DataAccessException
*/
private boolean nextResult(ResultSet rs) throws DataAccessException {
try {
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
throw new DataAccessException(e);
}
}
/**
* 映射
* @param rs 结果集
* @return 映射结果
* @throws DataAccessException
*/
private <T> T convertResult(ResultSet rs, ResultConverter<T> converter) throws DataAccessException {
try {
return converter.convert(rs);
} catch (SQLException e) {
e.printStackTrace();
// LOG.error(e.getMessage(), e);
throw new DataAccessException(e);
}
}
}
======================对应转化后的数据库处理类==================================
package com.qh.server;
import java.sql.Connection;
import org.dave.common.database.access.DataAccess;
import org.dave.common.database.convert.IntegerConverter;
import org.dave.common.database.convert.LongConverter;
import org.dave.common.database.convert.StringConverter;
public class HandleDAO extends DataAccess{
protected HandleDAO(Connection conn) {
super(conn);
// TODO Auto-generated constructor stub
}
/**
* 个数查询
* @param sql
* @return
*/
public int findIntV(String sql) {
Integer i = super.queryForObject(sql,new IntegerConverter());
return null == i ? 0 : i.intValue();
// return super.queryForObject("SELECT id,name,pass,email FROM demo WHERE id=?",
// new DemoConverter(), id);
}
/**
* 字符串查询
* @param sql
* @return
*/
public String findStringV(String sql) {
String s = super.queryForObject(sql,new StringConverter());
return null == s ? "" : s;
}
public Long findLongV(String sql) {
Long s = super.queryForObject(sql,new LongConverter());
return null == s ? Long.parseLong("0") : s;
}
/**
* 更新
* @param sql
*/
public void update(String sql) {
super.update(sql);
}
}
======================调用例子代码===================
conn = DatabaseConnectionPool.getConnection();
dao = new HandleDAO(conn);
int a = dao.findIntV(sql);