为什么要有数据库连接池？
数据库的连接对象创建工作，比较消耗性能。
什么是数据库连接池？
一开始在内存中开辟一块空间（集合），一开始先往池子里面放置多个连接对象。后面需要连接的话，直接从池子里面去。不要去自己创建连接了。使用完毕，要记得归还连接。
编写自己的数据库连接池
- 代码实现（要实现DataSource接口）：
package com.itheima.jdbc.util;
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;
public class MyDataSource implements DataSource{
List<Connection> list = new ArrayList<Connection>();
public MyDataSource() {
System.err.println("对象创建成功了...");
for(int i=0;i<10;i++) {
Connection conn = JDBCUtil.getConn();
list.add(conn);
}
}
@Override
public Connection getConnection() throws SQLException {
//来拿如果连接池是空的
if(list.size()==0) {
for(int i=0;i<10;i++) {
Connection conn = JDBCUtil.getConn();
list.add(conn);
}
}
//拿走了,就获取第一个，并移除第一个。
Connection con = list.remove(0);
System.err.println("拿到了...");
return con;
}
public void addBack(Connection con) {
System.err.println("归还了...");
list.add(con);
}
/*------------------下面的没用到------------------*/
@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 stub
}
@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;
}
}
- 测试代码
package com.itheima.jdbc.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class dataBases {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
PreparedStatement ps = null;
MyDataSource mydatasource = new MyDataSource();
try {
conn = mydatasource.getConnection();
String sql = "insert into bank values(null,'wangwu',1000)";
ps = (PreparedStatement) conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
try {
ps.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
mydatasource.addBack(conn);
}
}
}
开源连接池
DBCP
database connection pool
不使用配置文件
- 首先导入以下两个包
package com.java1995;
import com.itheima.jdbc.util.JDBCUtil;
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DBCPDemo {
public static void main(String[] args){
Connection conn = null;
PreparedStatement ps = null;
try {
/*1、构建数据源对象*/
BasicDataSource dataSource = new BasicDataSource();
//配置数据库信息
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost/soft03");
dataSource.setUsername("root");
dataSource.setPassword("w19980315");
/*2、得到连接对象*/
conn = dataSource.getConnection();
String sql = "insert into bank values(null,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "zhaoliu");
ps.setInt(2, 1000);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
}
使用配置文件
-
还是导入那两个包，另外在配置文件里写上数据库信息:
-
代码如下:
package com.java1995;
import com.itheima.jdbc.util.JDBCUtil;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class DBCPDemo02 {
public static void main(String [] args) {
Connection conn = null;
PreparedStatement ps = null;
BasicDataSourceFactory factory = new BasicDataSourceFactory();
Properties properties = new Properties();
try {
InputStream is = new FileInputStream("src/dbcpconfig.properties");
properties.load(is);
DataSource dataSource = factory.createDataSource(properties);
conn = dataSource.getConnection();
String sql = "insert into bank values(null,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "aobama");
ps.setInt(2, 400);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
}
C3P0
不使用配置文件
- 首先导入C3P0的jar包
package com.java1995;
import com.itheima.jdbc.util.JDBCUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class C3P0Demo {
public static void main(String[] args){
Connection con = null;
PreparedStatement ps = null;
try {
/*1、创建datasource*/
ComboPooledDataSource dataSource = new ComboPooledDataSource();
/*2、设置数据库连接信息*/
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost/soft03");
dataSource.setUser("root");
dataSource.setPassword("w19980315");
/*3、得到连接对象*/
con = dataSource.getConnection();
String sql = "insert into bank values(null , ? ,?)";
ps = con.prepareStatement(sql);
ps.setString(1, "luhan");
ps.setInt(2, 100);
ps.executeUpdate();
} catch (PropertyVetoException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(con, ps);
}
}
}
使用配置文件
package com.java1995;
import com.itheima.jdbc.util.JDBCUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class C3P0Demo {
public static void main(String[] args){
Connection con = null;
PreparedStatement ps = null;
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
con = dataSource.getConnection();
String sql = "insert into bank values(null , ? ,?)";
ps = con.prepareStatement(sql);
ps.setString(1, "wangbo");
ps.setInt(2, 1001111);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(con, ps);
}
}
}
DBUtils
增删改
//dbutils 只是帮我们简化了CRUD 的代码， 但是连接的创建以及获取工作。 不在他的考虑范围
QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
//增加
//queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);
//删除
//queryRunner.update("delete from account where id = ?", 5);
//更新
//queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);
查询
-
直接new接口的匿名实现类
QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource()); Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){ @Override public Account handle(ResultSet rs) throws SQLException { Account account = new Account(); while(rs.next()){ String name = rs.getString("name"); int money = rs.getInt("money"); account.setName(name); account.setMoney(money); } return account; } }, 6); System.out.println(account.toString()); -
直接使用框架已经写好的实现类
- 查询单个对象
//查询单个对象
Account account = queryRunner.query("select * from account where id = ?",
new BeanHandler<Account>(Account.class), 8);```
- 查询多个对象
```QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
List<Account> list = queryRunner.query("select * from account ",
new BeanListHandler<Account>(Account.class));
ResultSetHandler 常用的实现类
以下两个是使用频率最高的
BeanHandler, 查询到的单个数据封装成一个对象
BeanListHandler, 查询到的多个数据封装 成一个List<对象>
ArrayHandler, 查询到的单个数据封装成一个数组
ArrayListHandler, 查询到的多个数据封装成一个集合 ，集合里面的元素是数组。
MapHandler, 查询到的单个数据封装成一个map
MapListHandler,查询到的多个数据封装成一个集合 ，集合里面的元素是map。
3058
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
