连接池技术的思想:
连接复用(高效、安全),避免数据库频繁建立、关闭的开销
--------------------极客学院(参考lulei)
1.配置文件
<proxool>
<!-- 连接池别名 -->
<alias>localmysqldb</alias>
<!-- 连接数据库的驱动URL -->
<driver-url><![CDATA[jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8]]></driver-url>
<!-- 连接数据库的驱动类 -->
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-properties>
<property name="user" value="root"/>
<property name="password" value="ROOT"/>
</driver-properties>
<!-- 处于睡眠的最大时间 -->
<house-keeping-sleep-time>900000</house-keeping-sleep-time>
<!-- 连接的最大活动时间 -->
<maximum-active-time>500000</maximum-active-time>
<!-- 最大的连接数量 -->
<maximum-connection-count>10</maximum-connection-count>
<!-- 最小的连接数量 -->
<minimum-connection-count>4</minimum-connection-count>
<!-- 检测连接是否处于空闲状态,执行这条语句来测试 -->
<house-keeping-test-sql>select 1</house-keeping-test-sql>
<prop key="hibernate.connection.release_mode">after_transaction</prop>
</proxool>
</proxool-config>
2。工具类
public class ClassUtil {
/**
* @param c
* @return
* @Author:
* @Description: 返回class文件所在的目录
*/
public static String getClassPath(Class<?> c) {
return c.getResource("").getPath().replaceAll("%20", " ");
}
/**
* @param c
* @return
* @Author:
* @Description: 返回class文件所在项目的根目录
*/
public static String getClassRootPath(Class<?> c) {
return c.getResource("/").getPath().replaceAll("%20", " ");
}
/**
* @param c
* @param hasName 是否包括class名
* @return
* @Author:
* @Description: 返回class文件所在的目录
*/
public static String getClassPath(Class<?> c, boolean hasName) {
String name = c.getSimpleName() + ".class";
String path = c.getResource(name).getPath().replaceAll("%20", " ");
if (hasName) {
return path;
} else {
return path.substring(0, path.length() - name.length());
}
}
}
3.数据库连接池配置加载类
public class DBPool {
private String poolPath;//数据库连接池的配置文件路径
private DBPool() {
}
/**
* @return
* @Author:
* @Description: 返回DBPool对象
*/
public static DBPool getDBPool() {
return DBPoolDao.dbPool;
}
/**
*@Description: 静态内部类实现单例模式
*@Author:
*@Version:1.1.0
*/
private static class DBPoolDao{
private static DBPool dbPool = new DBPool();
}
public String getPoolPath() {
if (poolPath == null) {
//如果poolPath为空,赋值为默认值
poolPath = ClassUtil.getClassRootPath(DBPool.class) + "proxool.xml";
}
return poolPath;
}
/**
* @param poolPath
* @Author:
* @Description: 设置数据库连接池的配置文件路径
*/
public void setPoolPath(String poolPath) {
this.poolPath = poolPath;
}
}
4.数据库连接池管理类
public class DBManager {
private DBManager(){
try {
//数据库连接池配置文件
JAXPConfigurator.configure(DBPool.getDBPool().getPoolPath(), false);
//数据库加载驱动类
Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param poolName
* @return
* @throws SQLException
* @Author:
* @Description: 获取数据库连接
*/
public Connection getConnection(String poolName) throws SQLException {
return DriverManager.getConnection(poolName);
}
/**
*@Description: 内部静态类实现单例模式
*@Author:
*@Version:1.1.0
*/
private static class DBManagerDao {
private static DBManager dbManager = new DBManager();
}
/**
* @return
* @Author:
* @Description: 返回数据库连接池管理类
*/
public static DBManager getDBManager() {
return DBManagerDao.dbManager;
}
}
5. sql语句参数转化
public class DBOperation {
private String poolName;//数据库连接池别名
private Connection con = null;//数据库连接
public DBOperation(String poolName) {
this.poolName = poolName;
}
/**
* @Author:
* @Description:关闭数据库连接
*/
public void close() {
try {
if (this.con != null) {
this.con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @throws SQLException
* @Author:lulei
* @Description: 打开数据库连接
*/
private void open() throws SQLException {
//先关闭后打开,防止数据库连接溢出
close();
this.con = DBManager.getDBManager().getConnection(this.poolName);
}
/**
* @param sql
* @param params
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @Author:
* @Description: sql语句参数转化
*/
private PreparedStatement setPres(String sql, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException{
if (null == params || params.size() < 1) {
return null;
}
PreparedStatement pres = this.con.prepareStatement(sql);
for (int i = 1; i <= params.size(); i++) {
if (params.get(i) == null) {
pres.setString(i, "");
} else if (params.get(i).getClass() == Class.forName("java.lang.String")) {
pres.setString(i, params.get(i).toString());
} else if (params.get(i).getClass() == Class.forName("java.lang.Integer")) {
pres.setInt(i, (Integer) params.get(i));
} else if (params.get(i).getClass() == Class.forName("java.lang.Long")) {
pres.setLong(i, (Long) params.get(i));
} else if (params.get(i).getClass() == Class.forName("java.lang.Double")) {
pres.setDouble(i, (Double) params.get(i));
} else if (params.get(i).getClass() == Class.forName("java.lang.Flaot")) {
pres.setFloat(i, (Float) params.get(i));
} else if (params.get(i).getClass() == Class.forName("java.lang.Boolean")) {
pres.setBoolean(i, (Boolean) params.get(i));
} else if (params.get(i).getClass() == Class.forName("java.sql.Date")) {
pres.setDate(i, java.sql.Date.valueOf(params.get(i).toString()));
} else {
return null;
}
}
return pres;
}
/**
* @param sql
* @return
* @throws SQLException
* @Author:
* @Description: 执行SQL语句,返回影响行数
*/
public int executeUpdate(String sql) throws SQLException {
this.open();
Statement state = this.con.createStatement();
return state.executeUpdate(sql);
}
/**
* @param sql
* @param params
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @Author:
* @Description: 执行sql语句,返回影响行数
*/
public int executeUpdate(String sql, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException {
this.open();
PreparedStatement pres = setPres(sql, params);
if (null == pres) {
return 0;
}
return pres.executeUpdate();
}
/**
* @param sql
* @return
* @throws SQLException
* @Author:
* @Description: 执行sql语句,返回结果集
*/
public ResultSet executeQuery(String sql) throws SQLException {
this.open();
Statement state = this.con.createStatement();
return state.executeQuery(sql);
}
/**
* @param sql
* @param params
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @Author:
* @Description:执行sql语句,返回结果集
*/
public ResultSet executeQuery(String sql, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException {
this.open();
PreparedStatement pres = setPres(sql, params);
if (null == pres) {
return null;
}
return pres.executeQuery();
}
}
6.数据基本操作
public class DBServer {
private DBOperation dbOperation;
public DBServer(String poolName) {
dbOperation = new DBOperation(poolName);
}
/**
* @Author:
* @Description: 关闭数据库连接
*/
public void close() {
dbOperation.close();
}
/**
* @param sql
* @return
* @throws SQLException
* @Author:
* @Description: 数据库新增操作
*/
public int insert(String sql) throws SQLException {
return dbOperation.executeUpdate(sql);
}
/**
* @param tableName
* @param columns
* @param params
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @Author:
* @Description: 数据库新增操作
*/
public int insert(String tableName, String columns, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException {
String sql = insertSql(tableName, columns);
return dbOperation.executeUpdate(sql, params);
}
/**
* @param sql
* @return
* @throws SQLException
* @Author:
* @Description: 数据库删除操作
*/
public int delete(String sql) throws SQLException {
return dbOperation.executeUpdate(sql);
}
/**
* @param tableName
* @param condition
* @return
* @throws SQLException
* @Author:lulei
* @Description: 数据库删除操作
*/
public int delete(String tableName, String condition) throws SQLException {
if (null == tableName) {
return 0;
}
String sql = "delete from " + tableName + " " + condition;
return dbOperation.executeUpdate(sql);
}
/**
* @param sql
* @return
* @throws SQLException
* @Author:
* @Description: 数据库更新操作
*/
public int update(String sql) throws SQLException {
return dbOperation.executeUpdate(sql);
}
/**
* @param tableName
* @param columns
* @param condition
* @param params
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @Author:
* @Description: 数据库更新操作
*/
public int update(String tableName, String columns, String condition, HashMap<Integer, Object> params) throws SQLException, ClassNotFoundException {
String sql = updateSql(tableName, columns, condition);
return dbOperation.executeUpdate(sql, params);
}
/**
* @param sql
* @return
* @throws SQLException
* @Author:
* @Description: 数据库查询操作
*/
public ResultSet select(String sql) throws SQLException {
return dbOperation.executeQuery(sql);
}
/**
* @param tableName
* @param columns
* @param condition
* @return
* @throws SQLException
* @Author:lulei
* @Description: 数据库查询操作
*/
public ResultSet select(String tableName, String columns, String condition) throws SQLException {
String sql = "select " + columns + " from " + tableName + " " + condition;
return dbOperation.executeQuery(sql);
}
/**
* @param tableName
* @param columns
* @param condition
* @return
* @Author:
* @Description: 组装 update sql eg: update tableName set column1=?,column2=? condition
*/
private String updateSql(String tableName, String columns, String condition) {
if (tableName == null || columns == null) {
return "";
}
String[] column = columns.split(",");
StringBuilder sb = new StringBuilder();
sb.append("update ");
sb.append(tableName);
sb.append(" set ");
sb.append(column[0]);
sb.append("=?");
for (int i = 1; i < column.length; i++) {
sb.append(", ");
sb.append(column[i]);
sb.append("=?");
}
sb.append(" ");
sb.append(condition);
return sb.toString();
}
/**
* @param tableName
* @param columns
* @return
* @Author:
* @Description: 组装 insert sql eg: insert into tableName (column1, column2) values (?,?)
*/
private String insertSql(String tableName, String columns) {
if (tableName == null || columns == null) {
return "";
}
int n = columns.split(",").length;
StringBuilder sb = new StringBuilder("");
sb.append("insert into ");
sb.append(tableName);
sb.append(" (");
sb.append(columns);
sb.append(") values (?");
for (int i = 1; i < n; i++) {
sb.append(",?");
}
sb.append(")");
return sb.toString();
}
}