关于数据库连接池:
1 数据库的连接对象创建工作,比较消耗性能。
2 一开始在内存中开辟一块空间,往池子里放置多个连接对象,需要连接的时候从连接池里面调用,
使用完毕归还连接,确保连接对象能够循环利用。解决jdbc中连接部分的工作
自定义数据库连接池:
/** * * 这是一个数据库连接池 * 1 一开始先往池子里放10个连接 * 2 通过getConnection获取连接 * 3 用完之后用addback归还连接 * 4 扩容 * * 问题: * 1 sun公司针对数据库连接池定义的一套规范 * 需要额外记住addBack的方法 * 2 需要做成单例 * 3 无法面向接口编程,因为接口里面没有定义addBack方法 */ public class MyDataSource implements DataSource{ List<Connection> list = new ArrayList<>(); public MyDataSource(){ 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 conn = list.remove(0); return conn; } //归还 public void addBack(Connection conn){ list.add(conn); } @Override
解决自定义数据库连接池出现的问题
修改接口的close方法,Connection调用close,归还对象,而非关闭连接
如何扩展?
1. 修改源码
2. 继承,必须得知道这个接口的具体实现是谁
3. 使用装饰者模式
4. 动态代理
开源连接池:DBCP,C3P0
DBCP:Apache开发
DBCP开发的两种方式:
public class DBCPDemo { static String driverClassName = "com.mysql.jdbc.Driver"; //jdbc主协议,mysql子协议,所以类名倒着写driverClassName为com.mysql,url为jdbc协议下mysql //主协议:子协议://本地:数据库 static String url = "jdbc:mysql://localhost/bank"; static String username = "root"; static String password = "fungsumyuet"; @Test //手动设置的方式 public void testDBCP01(){ Connection conn = null; PreparedStatement ps = null; try { //构建数据源对象 BasicDataSource dataSource = new BasicDataSource(); //数据库连接参数 dataSource.setDriverClassName(driverClassName); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); conn = dataSource.getConnection(); String sql = "insert into account values(null,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, "bushi"); ps.setInt(2, 1000); ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtil.release(conn, ps); } } @Test //配置文件的方式,因为是Java工程而非web工程,所以可以用IO流FileInputStream加载文件,web工程用类加载形式 public void testDBCP02(){ Connection conn = null; PreparedStatement ps = null; try { BasicDataSourceFactory factory = new BasicDataSourceFactory(); Properties properties = new Properties(); InputStream is = new FileInputStream("src//dbcpconfig.properties"); properties.load(is);; DataSource dataSource = factory.createDataSource(properties); conn = dataSource.getConnection(); String sql = "insert into account values(null,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, "liangchaowei"); ps.setInt(2, 1000); ps.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtil.release(conn, ps); } } }
C3P0(重点掌握):
public class C3P0Demo01 { @Test //c3p0代码方式 public void testC3P0(){ //1 .创建dataSource ComboPooledDataSource dataSource = new ComboPooledDataSource(); Connection conn = null; PreparedStatement ps = null; try { //设置参数 dataSource.setDriverClass("com.mysql.jdbc.Driver"); dataSource.setJdbcUrl("jdbc:mysql://localhost/bank"); dataSource.setUser("root"); dataSource.setPassword("fungsumyuet"); conn = dataSource.getConnection(); String sql = "insert into account values(null,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, "zhourunfa"); ps.setInt(2,1000); ps.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtil.release(conn, ps); } } @Test //C3P0配置文件方式 public void testC3P02(){ Connection conn = null; PreparedStatement ps = null; try { //1 .创建dataSource,源码里面类加载形式加载配置文件 ComboPooledDataSource dataSource = new ComboPooledDataSource(); //设置参数 conn = dataSource.getConnection(); String sql = "insert into account values(null,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, "wuqilong"); ps.setInt(2,1000); ps.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtil.release(conn, ps); } } }
DBUtils:联合连接池,简化CRUD操作
1.导包,引入配置文件
2.创建连接池对象,dataSource
3.将连接池对象传进QueryRunner()
4.处理业务实现功能
public class TestDBUtils { @Test public void demo01() throws SQLException{ //C3P0连接池 ComboPooledDataSource dataSource = new ComboPooledDataSource(); //dbUtils简化CRUD代码 QueryRunner queryRunner = new QueryRunner(dataSource); //增加 // queryRunner.update("insert into account values(null,?,?)", "panfeng",1000); //删除 // queryRunner.update("delete from account where id = ?", 12); //更新 // queryRunner.update("update account set money = ? where id = ? ", 999,9); //查询,查询到的数据还是在ResultSet里面,然后调用下面的handle方法,由用户手动去封装 /* 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()){ int id = rs.getInt("id"); String name = rs.getString("name"); int money = rs.getInt("money"); account.setId(id); account.setName(name); account.setMoney(money); } return account; } }, 9); System.out.println(account.toString());*/ //查询单个对象,通过类的字节码得到该类的实例: A1 a = new A1.class.newInstance(); /*Account account = queryRunner.query("select * from account where id = ?", new BeanHandler<Account>(Account.class),8); System.out.println(account.toString());*/ //查询多个对象 List<Account> list = queryRunner.query("select * from account", new BeanListHandler<Account>(Account.class)); for(Account account : list){ System.out.println(account.toString()); } } }
DBUtil通用模版:
元数据:描述数据的数据。比如String sql , 描述sql的数据叫做元数据
public class CommonCRUDUtil { @Test //sql 可变参数 public void testUpdate(){ // update("insert into account values(null,?,?)","aa" , 10); // update("delete from account where id = ?", 12); //按照问号个数 // update02("update account set money = ? where id = ?", 800 , 7); } @Test public void testQuery(){ Account account = query("select * from account where id = ?", new ResultSetHandler<Account>(){ @Override public Account handle(ResultSet rs) { Account account = new Account(); try { if(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); int money = rs.getInt("money"); account.setId(id); account.setName(name); account.setMoney(money); return account; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; }}, 9 ); System.out.println(account.toString()); } //通用的增删改查功能 /** * @param sql * @param args 可变参数,有几个占位符,就写几个参数进来 */ public void update(String sql , Object ... args){ Connection conn = null; PreparedStatement ps = null; try { conn = JDBCUtil02.getConn(); ps = conn.prepareStatement(sql); //因为不知道是什么类型的数组,所以都用Object来对待 for(int i = 0 ; i<args.length ; i++){ ps.setObject(i+1, args[i]); } ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(conn, ps); } } public void update02(String sql , Object ... args){ Connection conn = null; PreparedStatement ps = null; try { conn = JDBCUtil02.getConn(); ps = conn.prepareStatement(sql); //元数据,获取到有几个问号,占位符 ParameterMetaData metaData = ps.getParameterMetaData(); int count = metaData.getParameterCount(); for(int i = 0; i<count ; i++){ ps.setObject(i+1, args[i]); } ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(conn, ps); } } //通用查询 public <T> T query(String sql ,ResultSetHandler<T> handler, Object ... args){ Connection conn = null; PreparedStatement ps = null; try { conn = JDBCUtil02.getConn(); ps = conn.prepareStatement(sql); //元数据,获取到有几个问号,占位符 ParameterMetaData metaData = ps.getParameterMetaData(); int count = metaData.getParameterCount(); for(int i = 0; i<count ; i++){ ps.setObject(i+1, args[i]); } //问题1:数据获取封装成什么对象返回? ResultSet rs = ps.executeQuery(); T t = (T) handler.handle(rs); return t; } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(conn, ps); } return null; } }