Druid C3P0连接池的使用和DBUtils的使用以及

作者: 葡小萄家的猫 | 来源:发表于2017-07-27 21:20 被阅读0次

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

Druid C3P0连接池的使用和DBUtils的使用以及

01 Druid 连接池

02 C3P0连接池

03 DBUtils 使用

04 JDBC的事务管理

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Druid C3P0连接池的使用 和DBUtils的使用 以及

01 Druid 连接池

02 C3P0连接池

03 DBUtils 使用

04 JDBC的事务管理

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Druid C3P0连接池的使用和DBUtils的使用以及