JDBC（二）（3）

@Test
  public void testGetConnection() throws SQLException{
    //创建了DBCP的数据库连接池
    BasicDataSource source = new BasicDataSource();
    
    //设置基本信息
    source.setDriverClassName("com.mysql.jdbc.Driver");
    source.setUrl("jdbc:mysql:///myDB");
    source.setUsername("root");
    source.setPassword("root");
    
    //还可以设置其他涉及数据库连接池管理的相关属性：
    source.setInitialSize(10);
    source.setMaxActive(10);
    //。。。
    
    Connection conn = source.getConnection();
    System.out.println(conn);
  }

@Test
  public void testGetConnection1() throws Exception{
    Properties pros = new Properties();
    
    //方式1：
//    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
    //方式2：
    FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
    
    
    pros.load(is);
    DataSource source = BasicDataSourceFactory.createDataSource(pros);
    
    Connection conn = source.getConnection();
    System.out.println(conn);
  }

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=abc123
initialSize=10
#...

package com.atguigu4.connection;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import org.junit.Test;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidTest {
  
  @Test
  public void getConnection() throws Exception{
    Properties pros = new Properties();
    
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
    
    pros.load(is);
    
    DataSource source = DruidDataSourceFactory.createDataSource(pros);
    Connection conn = source.getConnection();
    System.out.println(conn);
    
  }
}

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall

// 测试添加
@Test
public void testInsert() throws Exception {
  QueryRunner runner = new QueryRunner();
  Connection conn = JDBCUtils.getConnection3();
    
  String sql = "insert into customers(name,email,birth)values(?,?,?)";
  int count = runner.update(conn, sql, "何成飞", "he@qq.com", "1992-09-08");
  System.out.println("添加了" + count + "条记录");
    
  JDBCUtils.closeResource(conn, null);
}

// 测试删除
@Test
public void testDelete() throws Exception {
  QueryRunner runner = new QueryRunner();
  Connection conn = JDBCUtils.getConnection3();
  String sql = "delete from customers where id < ?";
  int count = runner.update(conn, sql,3);
  System.out.println("删除了" + count + "条记录");
    
  JDBCUtils.closeResource(conn, null);
}

/*
 * 测试查询:查询一条记录
 * 
 * 使用ResultSetHandler的实现类：BeanHandler
 */
@Test
public void testQueryInstance() throws Exception{
  QueryRunner runner = new QueryRunner();
  Connection conn = JDBCUtils.getConnection3();
    
  String sql = "select id,name,email,birth from customers where id = ?";
    
  //
  BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
  Customer customer = runner.query(conn, sql, handler, 23);
  System.out.println(customer); 
  JDBCUtils.closeResource(conn, null);
}

/*
 * 测试查询:查询多条记录构成的集合
 * 
 * 使用ResultSetHandler的实现类：BeanListHandler
 */
@Test
public void testQueryList() throws Exception{
  QueryRunner runner = new QueryRunner();
  Connection conn = JDBCUtils.getConnection3();
    
  String sql = "select id,name,email,birth from customers where id < ?";
    
  //
  BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
  List<Customer> list = runner.query(conn, sql, handler, 23);
  list.forEach(System.out::println);
    
  JDBCUtils.closeResource(conn, null);
}

/*
 * 自定义ResultSetHandler的实现类
 */
@Test
public void testQueryInstance1() throws Exception{
  QueryRunner runner = new QueryRunner();
  Connection 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 rs) throws SQLException {
      System.out.println("handle");
//      return new Customer(1,"Tom","tom@126.com",new Date(123323432L));
        
      if(rs.next()){
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        Date birth = rs.getDate("birth");
          
        return new Customer(id, name, email, birth);
      }
      return null;
        
    }
  };
    
  Customer customer = runner.query(conn, sql, handler, 23);
    
  System.out.println(customer);
    
  JDBCUtils.closeResource(conn, null);
}

/*
 * 如何查询类似于最大的，最小的，平均的，总和，个数相关的数据，
 * 使用ScalarHandler
 * 
 */
@Test
public void testQueryValue() throws Exception{
  QueryRunner runner = new QueryRunner();
  Connection conn = JDBCUtils.getConnection3();
    
  //测试一：
//  String sql = "select count(*) from customers where id < ?";
//  ScalarHandler handler = new ScalarHandler();
//  long count = (long) runner.query(conn, sql, handler, 20);
//  System.out.println(count);
    
  //测试二：
  String sql = "select max(birth) from customers";
  ScalarHandler handler = new ScalarHandler();
  Date birth = (Date) runner.query(conn, sql, handler);
  System.out.println(birth);
    
  JDBCUtils.closeResource(conn, null);
}