数据库连接池

一、三种实现方式

private static ComboPooledDataSource cpds=new ComboPooledDataSource("helloc3p0");
    public  static Connection getConnection1() throws Exception {

        Connection conn=cpds.getConnection();
        return conn;
    }
    private static DataSource source;
    static{
        try{
            Properties pro=new Properties();
            FileInputStream is=new FileInputStream(new File("src/dbcp.properties"));
            pro.load(is);
            DataSource source= BasicDataSourceFactory.createDataSource(pro);
        }catch (Exception e){
            e.printStackTrace();
        }

    }
    public Connection getConnection2() throws Exception {

        Connection conn=source.getConnection();
        return conn;
    }
    private static DataSource source1 ;
    static{
        try{
            Properties pro=new Properties();
            InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("src/druid.properties");
            pro.load(is);
            source1= DruidDataSourceFactory.createDataSource(pro) ;
        }catch (Exception e){
            e.printStackTrace();
        }

    }
    public static Connection getConnection3() throws Exception {


            Connection conn=source1.getConnection();
            return  conn;

    }

二、使用QueryRunner进行增删改查操作，特殊值查找，以及自定义实现类

package dbutils;

import jdbc_3_bean.Customer;
import jdbc_8_util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class QueryRunnerTest {
    public static void main(String[] args) {
        testQuery08();
    }
    //测试插入操作--update
    public static void testQuery01()  {
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
           conn= JDBCUtils.getConnection3();
            String sql="insert into customers(name,email,birth)value(?,?,?)";
            int count=runner.update(conn,sql,"蔡徐坤","canxukun@126.com","1997-10-26");
            System.out.println("添加了"+count+"条数据");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
    //查询一条记录--BeanHandler
    public static void testQuery02()  {
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
            System.out.println(runner.query(conn,sql,handler,23));
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
    //查询多条记录--BeanListHandler
    public static void testQuery03(){
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers ";
            BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);
            List<Customer> list=runner.query(conn,sql,handler);
            list.forEach(System.out::println);

        }catch(Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //查询一条记录的map形式--MapHandler
    public static void testQuery04()  {
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            MapHandler handler=new MapHandler();
            Map<String,Object> map=runner.query(conn,sql,handler,23);
            System.out.println(map);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
    //查询多条记录的map形式--MapListHandler
    public static void testQuery05()  {
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers ";
            MapListHandler handler=new MapListHandler();
            List<Map<String,Object>> list=runner.query(conn,sql,handler);
            list.forEach(System.out::println);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }



    }
    //查询行数--ScalarHandler
    public static void testQuery06()  {
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils.getConnection3();
            String sql="select count(*) from customers";
            ScalarHandler handler=new ScalarHandler();
            Long count=(long)runner.query(conn,sql,handler);
            System.out.println("有"+count+"行数据");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //查询最大生日--ScalarHandler
    public static void testQuery07() {
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils.getConnection3();
            String sql="select max(birth) from customers";
            ScalarHandler handler=new ScalarHandler();
            Date maxBirth=(Date)runner.query(conn,sql,handler);
            System.out.println("最大生日为"+maxBirth);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
    //自定义ResultSetHandler的实现类
    public static void testQuery08() {
        Connection conn=null;
        try{
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    if(resultSet.next()){
                        int id=resultSet.getInt("id");
                        String name=resultSet.getString("name");
                        String email=resultSet.getString("email");
                        Date birth=resultSet.getDate("birth");
                        Customer customer=new Customer(id,name,email,birth);
                        return  customer;
                    }
                    return null;
                }
            };
            Customer customer=runner.query(conn,sql,handler,23);
            System.out.println(customer);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
}

三、使用DbUtils类关闭资源

    public static void closeResouse03(Connection conn, Statement ps, ResultSet rs){
        try {
            DbUtils.close(conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            DbUtils.close(ps);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            DbUtils.close(rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void closeResouse04(Connection conn, Statement ps, ResultSet rs){
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }