当前位置:Gxlcms > 数据库问题 > 数据库连接池

数据库连接池

时间:2021-07-01 10:21:17 帮助过:11人阅读

为什么要有数据库连接池?

数据库的连接对象创建工作,比较消耗性能。

什么是数据库连接池?

一开始在内存中开辟一块空间(集合),一开始先往池子里面放置多个连接对象。后面需要连接的话,直接从池子里面去。不要去自己创建连接了。使用完毕,要记得归还连接。

编写自己的数据库连接池

  • 代码实现(要实现DataSource接口):
  1. <code>package com.itheima.jdbc.util;
  2. import java.io.PrintWriter;
  3. import java.sql.Connection;
  4. import java.sql.SQLException;
  5. import java.sql.SQLFeatureNotSupportedException;
  6. import java.util.ArrayList;
  7. import java.util.List;
  8. import java.util.logging.Logger;
  9. import javax.sql.DataSource;
  10. public class MyDataSource implements DataSource{
  11. List<Connection> list = new ArrayList<Connection>();
  12. public MyDataSource() {
  13. System.err.println("对象创建成功了...");
  14. for(int i=0;i<10;i++) {
  15. Connection conn = JDBCUtil.getConn();
  16. list.add(conn);
  17. }
  18. }
  19. @Override
  20. public Connection getConnection() throws SQLException {
  21. //来拿如果连接池是空的
  22. if(list.size()==0) {
  23. for(int i=0;i<10;i++) {
  24. Connection conn = JDBCUtil.getConn();
  25. list.add(conn);
  26. }
  27. }
  28. //拿走了,就获取第一个,并移除第一个。
  29. Connection con = list.remove(0);
  30. System.err.println("拿到了...");
  31. return con;
  32. }
  33. public void addBack(Connection con) {
  34. System.err.println("归还了...");
  35. list.add(con);
  36. }
  37. /*------------------下面的没用到------------------*/
  38. @Override
  39. public PrintWriter getLogWriter() throws SQLException {
  40. // TODO Auto-generated method stub
  41. return null;
  42. }
  43. @Override
  44. public void setLogWriter(PrintWriter out) throws SQLException {
  45. // TODO Auto-generated method stub
  46. }
  47. @Override
  48. public void setLoginTimeout(int seconds) throws SQLException {
  49. // TODO Auto-generated method stub
  50. }
  51. @Override
  52. public int getLoginTimeout() throws SQLException {
  53. // TODO Auto-generated method stub
  54. return 0;
  55. }
  56. @Override
  57. public Logger getParentLogger() throws SQLFeatureNotSupportedException {
  58. // TODO Auto-generated method stub
  59. return null;
  60. }
  61. @Override
  62. public <T> T unwrap(Class<T> iface) throws SQLException {
  63. // TODO Auto-generated method stub
  64. return null;
  65. }
  66. @Override
  67. public boolean isWrapperFor(Class<?> iface) throws SQLException {
  68. // TODO Auto-generated method stub
  69. return false;
  70. }
  71. @Override
  72. public Connection getConnection(String username, String password) throws SQLException {
  73. // TODO Auto-generated method stub
  74. return null;
  75. }
  76. }</code>
  • 测试代码
  1. <code>package com.itheima.jdbc.util;
  2. import java.sql.Connection;
  3. import java.sql.PreparedStatement;
  4. import java.sql.SQLException;
  5. public class dataBases {
  6. public static void main(String[] args) {
  7. // TODO Auto-generated method stub
  8. Connection conn = null;
  9. PreparedStatement ps = null;
  10. MyDataSource mydatasource = new MyDataSource();
  11. try {
  12. conn = mydatasource.getConnection();
  13. String sql = "insert into bank values(null,'wangwu',1000)";
  14. ps = (PreparedStatement) conn.prepareStatement(sql);
  15. ps.executeUpdate();
  16. } catch (SQLException e) {
  17. try {
  18. ps.close();
  19. } catch (SQLException e1) {
  20. // TODO Auto-generated catch block
  21. e1.printStackTrace();
  22. }
  23. // TODO Auto-generated catch block
  24. e.printStackTrace();
  25. }finally {
  26. mydatasource.addBack(conn);
  27. }
  28. }
  29. }</code>

开源连接池

DBCP

database connection pool

不使用配置文件

  • 首先导入以下两个包
    技术分享图片
  1. <code>package com.java1995;
  2. import com.itheima.jdbc.util.JDBCUtil;
  3. import org.apache.commons.dbcp.BasicDataSource;
  4. import java.sql.Connection;
  5. import java.sql.PreparedStatement;
  6. import java.sql.SQLException;
  7. public class DBCPDemo {
  8. public static void main(String[] args){
  9. Connection conn = null;
  10. PreparedStatement ps = null;
  11. try {
  12. /*1、构建数据源对象*/
  13. BasicDataSource dataSource = new BasicDataSource();
  14. //配置数据库信息
  15. dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  16. dataSource.setUrl("jdbc:mysql://localhost/soft03");
  17. dataSource.setUsername("root");
  18. dataSource.setPassword("w19980315");
  19. /*2、得到连接对象*/
  20. conn = dataSource.getConnection();
  21. String sql = "insert into bank values(null,?,?)";
  22. ps = conn.prepareStatement(sql);
  23. ps.setString(1, "zhaoliu");
  24. ps.setInt(2, 1000);
  25. ps.executeUpdate();
  26. } catch (SQLException e) {
  27. e.printStackTrace();
  28. }finally {
  29. JDBCUtil.release(conn, ps);
  30. }
  31. }
  32. }</code>

使用配置文件

  • 还是导入那两个包,另外在配置文件里写上数据库信息:
    技术分享图片

  • 代码如下:
  1. <code>package com.java1995;
  2. import com.itheima.jdbc.util.JDBCUtil;
  3. import org.apache.commons.dbcp.BasicDataSourceFactory;
  4. import javax.sql.DataSource;
  5. import java.io.FileInputStream;
  6. import java.io.InputStream;
  7. import java.sql.Connection;
  8. import java.sql.PreparedStatement;
  9. import java.util.Properties;
  10. public class DBCPDemo02 {
  11. public static void main(String [] args) {
  12. Connection conn = null;
  13. PreparedStatement ps = null;
  14. BasicDataSourceFactory factory = new BasicDataSourceFactory();
  15. Properties properties = new Properties();
  16. try {
  17. InputStream is = new FileInputStream("src/dbcpconfig.properties");
  18. properties.load(is);
  19. DataSource dataSource = factory.createDataSource(properties);
  20. conn = dataSource.getConnection();
  21. String sql = "insert into bank values(null,?,?)";
  22. ps = conn.prepareStatement(sql);
  23. ps.setString(1, "aobama");
  24. ps.setInt(2, 400);
  25. ps.executeUpdate();
  26. } catch (Exception e) {
  27. e.printStackTrace();
  28. }finally {
  29. JDBCUtil.release(conn, ps);
  30. }
  31. }
  32. }</code>

C3P0

不使用配置文件

  • 首先导入C3P0的jar包

技术分享图片

  1. <code>package com.java1995;
  2. import com.itheima.jdbc.util.JDBCUtil;
  3. import com.mchange.v2.c3p0.ComboPooledDataSource;
  4. import java.beans.PropertyVetoException;
  5. import java.sql.Connection;
  6. import java.sql.PreparedStatement;
  7. import java.sql.SQLException;
  8. public class C3P0Demo {
  9. public static void main(String[] args){
  10. Connection con = null;
  11. PreparedStatement ps = null;
  12. try {
  13. /*1、创建datasource*/
  14. ComboPooledDataSource dataSource = new ComboPooledDataSource();
  15. /*2、设置数据库连接信息*/
  16. dataSource.setDriverClass("com.mysql.jdbc.Driver");
  17. dataSource.setJdbcUrl("jdbc:mysql://localhost/soft03");
  18. dataSource.setUser("root");
  19. dataSource.setPassword("w19980315");
  20. /*3、得到连接对象*/
  21. con = dataSource.getConnection();
  22. String sql = "insert into bank values(null , ? ,?)";
  23. ps = con.prepareStatement(sql);
  24. ps.setString(1, "luhan");
  25. ps.setInt(2, 100);
  26. ps.executeUpdate();
  27. } catch (PropertyVetoException e) {
  28. e.printStackTrace();
  29. } catch (SQLException e) {
  30. e.printStackTrace();
  31. }finally {
  32. JDBCUtil.release(con, ps);
  33. }
  34. }
  35. }</code>

使用配置文件

  • C3P0说明文档

技术分享图片

  1. <code>package com.java1995;
  2. import com.itheima.jdbc.util.JDBCUtil;
  3. import com.mchange.v2.c3p0.ComboPooledDataSource;
  4. import java.sql.Connection;
  5. import java.sql.PreparedStatement;
  6. import java.sql.SQLException;
  7. public class C3P0Demo {
  8. public static void main(String[] args){
  9. Connection con = null;
  10. PreparedStatement ps = null;
  11. try {
  12. ComboPooledDataSource dataSource = new ComboPooledDataSource();
  13. con = dataSource.getConnection();
  14. String sql = "insert into bank values(null , ? ,?)";
  15. ps = con.prepareStatement(sql);
  16. ps.setString(1, "wangbo");
  17. ps.setInt(2, 1001111);
  18. ps.executeUpdate();
  19. } catch (SQLException e) {
  20. e.printStackTrace();
  21. }finally {
  22. JDBCUtil.release(con, ps);
  23. }
  24. }
  25. }</code>

DBUtils

增删改

  1. <code> //dbutils 只是帮我们简化了CRUD 的代码, 但是连接的创建以及获取工作。 不在他的考虑范围
  2. QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
  3. //增加
  4. //queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);
  5. //删除
  6. //queryRunner.update("delete from account where id = ?", 5);
  7. //更新
  8. //queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);</code>

查询

  1. 直接new接口的匿名实现类

    1. <code> QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
    2. Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){
    3. @Override
    4. public Account handle(ResultSet rs) throws SQLException {
    5. Account account = new Account();
    6. while(rs.next()){
    7. String name = rs.getString("name");
    8. int money = rs.getInt("money");
    9. account.setName(name);
    10. account.setMoney(money);
    11. }
    12. return account;
    13. }
    14. }, 6);
    15. System.out.println(account.toString());</code>
  2. 直接使用框架已经写好的实现类

  • 查询单个对象
    QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource()); //查询单个对象 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<对象>


  1. <code>ArrayHandler, 查询到的单个数据封装成一个数组
  2. ArrayListHandler, 查询到的多个数据封装成一个集合 ,集合里面的元素是数组。
  3. MapHandler, 查询到的单个数据封装成一个map
  4. MapListHandler,查询到的多个数据封装成一个集合 ,集合里面的元素是map。</code>

数据库连接池

标签:rest   connect   money   actor   nts   rac   where   except   tps   

人气教程排行