package com.sidi.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
public class MyDbutil {
private String dri = "com.mysql.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/myout?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";
private String username = "root";
private String password = "";
private volatile static MyDbutil faction = null;
private static ObjectPool connectionPool = null;
private MyDbutil() {
}
//打开
public static MyDbutil Open() {
while (faction == null) {
synchronized(MyDbutil.class){
if (faction == null) {
faction = new MyDbutil();
}
}
}
return faction;
}
//关闭连接
public void Close(Connection conn) {
DbUtils.closeQuietly(conn);
//ShutdownPool();
}
/**
* 连接池启动
* @throws Exception
*/
public void StartPool() {
try {
Class.forName(dri);
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if (connectionPool != null) {
ShutdownPool();
}
try {
connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url,username,password);
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, "SELECT 1 as a", false, true);
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.registerPool("dbpool2", connectionPool);
System.out.println("装配连接池OK");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 释放连接池
*/
public static void ShutdownPool() {
try {
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.closePool("dbpool2");
} catch (SQLException e) {
e.printStackTrace();
}
}
// 获得数据库连接
public synchronized Connection getConnection() {
Connection conn = null;
try {
if(connectionPool == null)
StartPool();
conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:dbpool2");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//执行查询方法(使用map)
public List query(String sql) {
List results = null;
Connection conn = null;
try {
conn = getConnection();
QueryRunner qr = new QueryRunner();
results = (List) qr.query(conn, sql, new MapListHandler());
} catch (SQLException e) {
e.printStackTrace();
} finally {
Close(conn);
}
return results;
}
//执行查询方法(使用map)
public List query(String sql, Object param) {
List results = null;
Connection conn = null;
try {
conn = getConnection();
QueryRunner qr = new QueryRunner();
results = (List) qr.query(conn, sql, param, new MapListHandler());
} catch (SQLException e) {
e.printStackTrace();
} finally {
Close(conn);
}
return results;
}
//执行查询方法(使用)
public List query(String sql, Class module) {
List results = null;
Connection conn = null;
try {
conn = getConnection();
QueryRunner qr = new QueryRunner();
results = (List) qr.query(conn, sql, new BeanListHandler(module));
} catch (SQLException e) {
e.printStackTrace();
} finally {
Close(conn);
}
return results;
}
//查询单个数据
public Object get_one(String sql, Class module) {
Object results = null;
Connection conn = null;
try {
conn = getConnection();
QueryRunner qr = new QueryRunner();
results = (Object) qr.query(conn, sql, new BeanHandler(module));
} catch (SQLException e) {
e.printStackTrace();
} finally {
Close(conn);
}
return results;
}
//增删改方法
public int execute(String sql) throws Exception {
Connection conn = getConnection();
int rows = 0;
try {
QueryRunner qr = new QueryRunner();
rows = qr.update(conn, sql);
} finally {
Close(conn);
}
return rows;
}
//增删改方法
public int execute(String sql, Object[] params) throws Exception {
Connection conn = getConnection();
int rows = 0;
try {
QueryRunner qr = new QueryRunner();
rows = qr.update(conn, sql, params);
} finally {
Close(conn);
}
return rows;
}
}
单例,使用了连接池,用到的jar是commons-dbcp-1.2.2.jar commons-pool-1.3.jar commons-dbutils-1.1.jar