数据库连接池

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

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

什么是数据库连接池？

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

编写自己的数据库连接池

• 代码实现（要实现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);
        }
    }
}

使用配置文件

• C3P0说明文档

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);

查询

1. 直接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());

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 <对象>

ArrayHandler,  查询到的单个数据封装成一个数组
ArrayListHandler,  查询到的多个数据封装成一个集合 ，集合里面的元素是数组。 
MapHandler,  查询到的单个数据封装成一个map
MapListHandler,查询到的多个数据封装成一个集合 ，集合里面的元素是map。