数据库连接池和DBUtiles
1.使用装饰者模式创建数据库连接池
1.新建一个装饰者类ConnnectionWrap,实现java.sql.Connection接口,并实现其中的close()和prepareStatement()方法;
public class ConnectionWrap implements Connection{
Connection conn;
List<Connection> list;
public ConnectionWrap(Connection conn,List<Connection> list) {
this.conn = conn;
this.list = list;
}
@Override
public void close() throws SQLException {
//conn.close();
list.add(conn);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql);
}
...
}
2.新建一个MyDataSource类,实现javax.sql.DataSource(数据库连接池)接口;
package com.nikehu;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.DataSource;
/**
* 这是一个数据库连接池,一开始先往池子里面放10个连接
* 1.开始创建10个连接
* 2.来的程序通过getConnection获取连接
* 3.用完之后,使用addBack归还连接。
* 4.扩容
*
*/
public class MyDataSource implements DataSource{
List<Connection> list = new ArrayList<Connection>();
public MyDataSource() {
for (int i = 0; i < 10; i++) {
Connection conn = new DBConnection().getConnection();
list.add(conn);
}
}
/**
* 该连接池对外公布的获取连接的方法
*/
@Override
public Connection getConnection() throws SQLException {
//获取连接时,先查看池中是否还有空闲连接
if(list.size()==0){
for (int i = 0; i < 5; i++) {
Connection conn1 = new DBConnection().getConnection();
list.add(conn1);
}
}
Connection connection = new ConnectionWrap(list.remove(0), list);
return connection;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
// TODO Auto-generated method stu
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public Connection getConnection(String username, String password)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
3.获取连接的时候,只要创建MyDataSource对象,通过getConnetion()方法便可以获取数据库连接;
4.使用数据库连接后,只要调用Connection对象的close()方法便可归还数据库链接到数据库连接池中。
2.数据库连接池DBCP
使用别人已经实现了的数据库连接池,站在巨人的肩膀上前进;
开源的数据库连接池:DBCP,C3P0,
DPCP(DataBase Connection Pool):数据库连接池,是Java数据库连接池的一种,有apache开发,通过数据库连接池,可以让程序自动管理数据库连接的释放和断开。
C3P0:一个开源的JDBC连接池,它实现了数据源和JNDI绑定,支持JDBC3规范和JDBC2的标准扩展,目前使用它的开源项目有Hibernate,Spring等。
使用DBCP
1.导入DBCP的jar包:commons-dbcp-1.4.jar和commons-pool-1.5.6.jar
@Test
public void testDBCP01(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//构建数据源对象
BasicDataSource dataSource = new BasicDataSource();
//连接数据库
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost/bank");
dataSource.setUsername("root");
dataSource.setPassword("971102");
//得到连接对象
connection = dataSource.getConnection();
String sql = "select * from account";
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String name = rs.getString("name");
int money = rs.getInt("money");
System.out.println(name+","+money);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
配置文件
dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bank
username=root
password=971102
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
/**
* 使用配置文件连接数据库
*/
@Test
public void testDBCP02(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
BasicDataSourceFactory factory = new BasicDataSourceFactory();
Properties properties = new Properties();
InputStream is = null;
try {
is = new FileInputStream("src//dbcpconfig.properties");
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
try {
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
DataSource dataSource = factory.createDataSource(properties);
connection = dataSource.getConnection();
String sql = "select * from account";
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String name = rs.getString("name");
int money = rs.getInt("money");
System.out.println(name+","+money);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
使用C3P0
1.导入c3p0架包
/**
* 代码实现
*/
@Test
public void testC3P0(){
//创建DataSourse对象
ComboPooledDataSource comboPool = new ComboPooledDataSource();
try {
comboPool.setDriverClass("com.mysql.jdbc.Driver");
} catch (PropertyVetoException e1) {
e1.printStackTrace();
}
comboPool.setJdbcUrl("jdbc:mysql://localhost/bank");
comboPool.setUser("root");
comboPool.setPassword("971102");
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = comboPool.getConnection();
String sql = "select * from account";
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String name = rs.getString("name");
int money = rs.getInt("money");
System.out.println(name+","+money);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
配置文件,c3p0支持properties,xml两种格式的配置文件。
c3p0-config.xml(名字固定,不可更改)
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- default-config 默认的配置, -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost/bank</property>
<property name="user">root</property>
<property name="password">971102</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
</c3p0-config>
/**
* 配置文件实现
* c3p0-config.xml
*/
@Test
public void testC3P01(){
//创建DataSourse对象
ComboPooledDataSource comboPool = new ComboPooledDataSource();
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = comboPool.getConnection();
String sql = "select * from account";
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String name = rs.getString("name");
int money = rs.getInt("money");
System.out.println(name+","+money);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
3.DBUtils
Commons DbUtils是apache组织提供的一个对JDBC进行简单封装的开源工具库类,使用它能够简化JDBC应用程序的开发,同时也不会影响程序的性能。
DbUtils增删该
1.导入dbutils架包:
/**
* 增删改
*/
@Test
public void insert(){
//dbutils 只是帮我们简化了CRUD的代码,但是数据库连接的创建和获取还得我们自己来。
ComboPooledDataSource dataSource = new ComboPooledDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);
try {
queryRunner.update("insert into account values (null,?,?)","王五",10000);
queryRunner.update("delete from account where id=?",1);
queryRunner.update("update account set money=money-? where id = ?",300,2);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void query(){
ComboPooledDataSource dataSource = new ComboPooledDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);
//执行查询,再使用handle()方法对数据进行封装
try {
Account account = queryRunner.query("select * from account where id = ?", 2, new ResultSetHandler<Account>() {
@Override
public Account handle(ResultSet rs) throws SQLException {
Account account = new Account();
while(rs.next()){
account.setName(rs.getString("name"));
account.setMoney(rs.getInt("money"));
}
return account;
}
});
System.out.println(account.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void query01(){
ComboPooledDataSource dataSource = new ComboPooledDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);
//执行查询,再使用handle()方法对数据进行封装
try {
//单条数据查询
BeanHandler<Account> handler = new BeanHandler<Account>(Account.class);
Account account = queryRunner.query("select * from account where id = ?", 2,handler);
System.out.println(account.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void query02(){
ComboPooledDataSource dataSource = new ComboPooledDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);
//执行查询,再使用handle()方法对数据进行封装
try {
//多条数据查询
//通过类的字节码获取类的实例
List<Account> query = queryRunner.query("select * from account", new BeanListHandler<Account>(Account.class));
System.out.println(query.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
ResultSetHandler常用的已知实现类:
BeanHandler:查询到的数据封装成一个对象;
BeanListHandler:查询到的数据封装成一个List<对象>;
ArrayHandler:查询到的单个数据封装成一个数组;
ArrayListHandler:查询到的数据封装成的一个集合;集合里面的元素是数组;
ColumnListHandler:查具体某一个列
KeyedHandler:查具体某一个值的
MapHandler:查询到的单个数据封装成一个map
MapListHandler:查询到的数据封装成的一个集合;集合里面的元素是map;
ScalaHandler: