01 Druid 连接池

1.手动设置参数的方法
@Test
// 手动设置参数的方法:
public void demo1(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql:///day04");
    dataSource.setUsername("root");
    dataSource.setPassword("123");
    try{
        // 获得连接:
        conn = dataSource.getConnection();
        // 编写SQL：
        String sql = "select * from account";
        pstmt = conn.prepareStatement(sql);
        // 执行sql:
        rs = pstmt.executeQuery();
        while(rs.next()){
            System.out.println(rs.getInt("id")+"   "+rs.getString("name")+"   "+rs.getDouble("money"));
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release(rs, pstmt, conn);
    }
    
}
2.使用配置文件 .properties 文件使用Properties 类来处理 
@Test 
public void demo2(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try{
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        // 获得连接:
        conn = dataSource.getConnection();
        // 编写SQL：
        String sql = "select * from account";
        pstmt = conn.prepareStatement(sql);
        // 执行sql:
        rs = pstmt.executeQuery();
        while(rs.next()){
            System.out.println(rs.getInt("id")+"   "+rs.getString("name")+"   "+rs.getDouble("money"));
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release(rs, pstmt, conn);
    }
}

02 C3P0连接池

1.手动设置链接代码  
@Test
// 手动设置参数的方法:
public void demo1(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try{
        // 核心类:
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        // 手动设置参数:
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql:///day04");
        dataSource.setUser("root");
        dataSource.setPassword("123");
        // 获得连接:
        conn = dataSource.getConnection();
        // 编写SQL：
        String sql = "select * from account";
        pstmt = conn.prepareStatement(sql);
        // 执行sql:
        rs = pstmt.executeQuery();
        while(rs.next()){
            System.out.println(rs.getInt("id")+"   "+rs.getString("name")+"   "+rs.getDouble("money"));
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release(rs, pstmt, conn);
    }
    
}

2.使用配置文件添加
@Test
// 配置文件的方式
public void demo2(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try{
        // 核心类:
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        // 获得连接:
        conn = dataSource.getConnection();
        // 编写SQL：
        String sql = "select * from account";
        pstmt = conn.prepareStatement(sql);
        // 执行sql:
        rs = pstmt.executeQuery();
        while(rs.next()){
            System.out.println(rs.getInt("id")+"   "+rs.getString("name")+"   "+rs.getDouble("money"));
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release(rs, pstmt, conn);
    }
    
}

03 DBUtils 使用

查看核心API：
    QueryRunner     ：核心执行类
    ResultSetHandler    ：提供对查询结果封装
    DbUtils         ：工具类

    @Test
 简单使用DBUils
    public void demo1() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
        String sql = "insert into account values (null,?,?)";
        queryRunner.update(sql, "刘如花",10000);
    
    }

1.添加
@Test
// 保存操作
public void demo1() throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql ="insert into account values (null,?,?)";
    queryRunner.update(sql, "aaa",10000);
}
2.修改
@Test
// 修改操作
public void demo2() throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql ="update account set name=?,money=? where id =?";
    queryRunner.update(sql, "bbb",20000,4);
}
3.删除
@Test
// 删除操作
public void demo3() throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql ="delete from account where id =?";
    queryRunner.update(sql, 4);
}
4.查询
ArrayHandler：
@Test
// ArrayHandler:将查询到的一条记录封装到数组当中
public void demo1() throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql  = "select * from account where id = ?";
    Object[] objs = queryRunner.query(sql, new ArrayHandler(), 1);
    System.out.println(Arrays.toString(objs));
}
ArrayListHandler：
将多条记录进行封装，一条记录封装成一个数组，多条记录封装成一个装有数组的集合
@Test
// ArrayListHandler:   将多条记录进行封装，一条记录封装成一个数组，多条记录封装成一个装有数组的集合
public void demo2() throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select * from account";
    List<Object[]> list = queryRunner.query(sql, new ArrayListHandler());
    for (Object[] objects : list) {
        System.out.println(Arrays.toString(objects));
    }
}
BeanHandler
将一条记录封装到一个JavaBean中
JavaBean就是满足了一定格式的Java类 属性私有化
提供无参数的构造
对私有属性提供public的get和set方法
@Test
// BeanHandler:将一条记录封装到一个javaBean中的
public void demo3()throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select * from account where id = ?";
    Account account = queryRunner.query(sql, new BeanHandler<>(Account.class), 1);
    System.out.println(account);
}   
BeanListHandler
将多条记录封装到一个装有JavaBean的List集合中
@Test
// BeanListHandler:将多条记录封装到一个装有JavaBean的List集合中
public void demo4()throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select * from account";
    List<Account> list = queryRunner.query(sql, new BeanListHandler<>(Account.class));
    for (Account account : list) {
        System.out.println(account);
    }
}
MapHandler
将一条记录封装成一个Map集合，Map的key是字段名称，Map的value是字段的值。
@Test
// MapHandler:封装一条记录到Map中
public void demo5()throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select * from account where id = ?";
    Map<String,Object> map = queryRunner.query(sql, new MapHandler() ,2);
    System.out.println(map);
}
MapListHandler
将多条记录进行封装，一条记录封装成一个Map，多条记录封装成一个装有Map的List集合中
@Test
// MapListHandler:
public void demo6()throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select * from account";
    List<Map<String,Object>> list = queryRunner.query(sql, new MapListHandler());
    for (Map<String, Object> map : list) {
        System.out.println(map);
    }
}
ColumnListHandler
@Test
// ColumnListHandler
public void demo7()throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select name from account";
    List<Object> list  = queryRunner.query(sql, new ColumnListHandler());
    for (Object object : list) {
        System.out.println(object);
    }
}
ScalarHandler
@Test
//ScalarHandler：单值查询
public void demo8()throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select count(*) from account";
    Long count = (Long) queryRunner.query(sql, new ScalarHandler());
    System.out.println(count);
}
KeyedHandler
@Test
// KeyedHandler:
public void demo9()throws SQLException{
    QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
    String sql = "select * from account";
    Map<Object,Map<String,Object>> map= queryRunner.query(sql, new KeyedHandler("name"));
    for (Object key : map.keySet()) {
        System.out.println(key + "   "+map.get(key));
    }
}

04 JDBC的事务管理

在Connection对象中有对事务管理操作的一组API：
setAutoCommit(boolean flag);
commit();
rollback();
事务管理：
@Test
// 基本转账环境
public void demo1(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    try{
        // 获得连接:
        conn = JDBCUtils.getConnection();

        ###！！// 开启事务:（***重点***）
        conn.setAutoCommit(false);

        // 编写SQL:
        String sql = "update account set money = money + ? where name = ?";
        // 预编译SQL:
        pstmt = conn.prepareStatement(sql);
        // 设置参数：
        // 扣除刘立 1000 
        pstmt.setDouble(1, -1000);
        pstmt.setString(2, "刘立");
        pstmt.executeUpdate(); // 扣钱
        
        int d = 1 / 0;
        
        // 给凤姐加 1000
        pstmt.setDouble(1, 1000);
        pstmt.setString(2, "凤姐");
        pstmt.executeUpdate(); // 加钱
        
        ###！！// 提交事务:（***重点***）
        conn.commit();
    }catch(Exception e){
        ###！！// 回滚事务:（***重点***）
        try {
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
    }finally{
        // 释放资源:
        JDBCUtils.release(pstmt, conn);
    }
}