JDBC_调用函数drivermanagerconnection()后,catch-CSDN博客

本文链接：https://blog.csdn.net/u014011993/article/details/53649789

 
 DriverManager获取数据库连接 

 
 Class.forName(driver); 

 
 DriverManager.getConnection(driverUrl, user, password); 

  @Test 

  public void testDriver() throws SQLException{ 

  //1. 创建一个Driver实现类的对象 

 
 Driver driver = new com.mysql.jdbc.Driver();  

  //2. 准备连接数据库的基本信息：url, user, password 

  String url = "jdbc:mysql://localhost:3306/test"; 

 
 Properties info = new Properties(); 

 
 info.put("user", "root"); 

 
 info.put("password", "root");  

  //3. 调用Driver接口的connect(url, info) 获取数据库连接 

 
 Connection connection = (Connection) driver.connect(url, info); 

  System.out.println(connection); 

}

  @Test 

  public void testDriverManager() throws Exception{ 

  //1. 准备连接数据库的4个字符窜 

 
 String driverClass = "com.mysql.jdbc.Driver"; 

  String driverClass2 = "oracle.jdbc.driver.OracleDriver"; 

 
 String jdbcUrl = "jdbc:mysql:///test"; //省略的简单写法 

  String jdbcUrl2 = "jdbc:oracle:thin:@localhost:1521:sid"; 

 
 String user = "root"; 

  String user2 = "root"; 

 
 String password = "root"; 

  String password2 = "root"; 

  //读取类路径下的jdbc.properties文件 

 
 InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");//项目中src里面有properties的话，加载就这样用 

  Properties properties = new Properties(); 

  properties.load(in); 

  driverClass = 
 properties.getProperty("driver"); 

  jdbcUrl = 
 properties.getProperty("jdbcUrl"); 

  user = 
  properties.getProperty("user"); 

  password = 
 properties.getProperty("password");  

  //2. 加载数据库驱动程序（对应的Driver实现类中有注册驱动的静态代码块） 

  DriverManager.registerDriver((Driver) Class.forName(driverClass).newInstance()); 这样写也可以 

  Class.forName(driverClass); 

  Class.forName(driverClass2); 

  //3. 通过DriverManager的getConnection()方法获取数据库连接 

  //通过修改jdbcUrl2, user2, password2，可以获取到不同的数据库连接 

 
 Connection connection = (Connection) DriverManager.getConnection(jdbcUrl, user, password);  

  System.out.println(connection); 

}

/*

  * ResultSet:结果集，封装了使用JDBC进行查询的结果 

  * 1. 调用Statement对象的executeQuery(sql)可以得到结果集 

  * 2. ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一行的前面 

  * 可以调用next()方法检测下一行是否有效，该方法如果有效，则返回true，且指针下移 

  * 相当于Iterator对象的hasNext()和next()方法的结合体 

  * 3. 当指针对位到一行时，可以通过调用getXxx(index)或getXxx(columnName) 

  * 获取每一列的值。例如：getInt(1), getString("name") 

  * 4. ResultSet当然也需要进行关闭 

*

*

  * */ 

  @Test 

  public void testResultSet(){ 

  //获取id=4的customers数据表的记录，并打印 

 
 Connection conn = null; 

 
 Statement statement = null; 

 
 ResultSet rs = null; 

  try { 

  //1. 获取Connection 

 
 conn = (Connection) JDBCTools.getConnection(); 

  //2. 获取Statement 

 
 statement = conn.createStatement(); 

  //3. 准备SQL 

 
 String sql = "SELECT ID, NAME, EMAIL, BIRTH FROM customers WHERE id = 4"; 

  // String sql = "SELECT ID, NAME, EMAIL, BIRTH FROM customers"; //输出多条 

  //4. 执行查询，得到ResultSet 

 
 rs = statement.executeQuery(sql); 

  //5. 处理ResultSet 

 
 if(rs.next()){ //输出单条用if 

  int id = 
 rs.getInt(1); 

  String name = 
 rs.getString("NAME"); 

  String email = 
  rs.getString(3); 

  Date birth = 
  rs.getDate(4); 

  System.out.println(id); 

  System.out.println(name); 

  System.out.println(email); 

  System.out.println(birth); 

}

  // 
 while(rs.next()){ //输出多条用while 

  // int id = 
  rs.getInt(1); 

  // String name = 
 rs.getString("NAME"); 

  // String email = 
  rs.getString(3); 

  // Date birth = 
  rs.getDate(4); 

//

  // System.out.println(id); 

  // System.out.println(name); 

  // System.out.println(email); 

  // System.out.println(birth); 

  // } 

  //6. 关闭数据库资源 

  } catch (Exception e) { 

  e.printStackTrace(); 

  }finally{ 

 
 JDBCTools.release(rs, statement, conn); 

}

}

 
 PreparedStatement 

  @Test 

  public void testPreparedStatements(){ 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  try { 

  connection = JDBCTools.getConnection(); 

 
 String sql = "INSERT INTO customers (name, email, birth)"+ "VALUES(?,?,?)"; 

 
 preparedStatement = (PreparedStatement) connection. 
 prepareStatement 
 (sql); 

 
 preparedStatement.setString(1, "atguigu"); 

 
 preparedStatement.setString(2, "simpleit@163.com"); 

 
 preparedStatement.setDate(3,  
 new Date(new java.util.Date().getTime()) 
 ); 

 
 preparedStatement.executeUpdate(); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  }finally{ 

  JDBCTools.releaseDB(null, preparedStatement, connection); 

}

}

 
 利用反射和JDBC元数据编写通用的查询方法 

/*

  * 获取通用的查询方法：可以根据传入的SQL、Class对象，返回SQL对应的记录的对象 

  * clazz：描述对象的类型 

  * sql: SQL语句，可能带占位符 

  * args: 填充占位符的可变参数 

*

  * */ 

 
 public <T> T get(Class<T> clazz, String sql, Object...args){ 

 
 T entity = null;  

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  ResultSet resultSet = null; 

  try { 

  //1. 得到ResultSet对象 

  connection = JDBCTools.getConnection(); 

 
 preparedStatement = (PreparedStatement) connection.prepareStatement(sql); 

  for(int i = 0; i<args.length ; i++){ 

  preparedStatement.setObject(i+1, args[i]); 

}

 
 resultSet = preparedStatement.executeQuery(); 

 
 //2. 得到ResultSetMetaData对象 

 
 ResultSetMetaData rsmd = resultSet.getMetaData(); 

  //3. 创建一个Map<String, Object>对象，键：SQL查询的列的别名，值：列的值 

 
 Map<String, Object> values= new HashMap<String, Object>(); 

  //4. 处理结果集，利用ResultSetMetaData填充3对应的Map对象 

  if(resultSet.next()){ 

  for(int i=0; i<rsmd.getColumnCount(); i++){ 

  String columnLable = 
 rsmd.getColumnLabel(i +1); //获取每一列的别名 

  Object columnValue = resultSet.getObject(i + 1); 

 
 values.put(columnLable, columnValue); 

}

}

  //5. 若Map不为空集，利用反射，创建clazz对应的对象 

 
 if(values.size() >0){ 

 
 entity = (T) clazz.newInstance();  

  //6. 遍历Map对象 ,利用反射为Class对象的对应属性赋值 

  for(Map.Entry<String, Object> entry: 
 values.entrySet()){ 

 
 String fieldName = entry.getKey(); 

 
 Object value = entry.getValue();  

 
  
 ReflectionUtils.setFieldValue(entity, fieldName, value); 

}

}

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(resultSet, preparedStatement, connection); 

}

 
 return entity; 

}

  public void testResultSetMetaData(){ 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  ResultSet resultSet = null; 

  try { 

  String sql = "SELECT flow_id flowId, type, id_card idCard, exam_card examCard, student_name studentName, "+"location, grade FROM examstudent WHERE flow_id = 
 ?"; 

  connection = JDBCTools.getConnection(); 

 
 preparedStatement = (PreparedStatement) connection.prepareStatement(sql); 

 
 preparedStatement.setInt(1, 5);  

 
 resultSet = preparedStatement.executeQuery();  

  Map<String, Object> values = new HashMap<String, Object>(); 

  //1. 得到ResultSetMetaData对象 

 
 ResultSetMetaData rsmd = resultSet.getMetaData(); // 
 ResultSetMetaData是JDBC元数据 

  while(resultSet.next()){ 

  //2. 打印每一列的列名 

  for(int i=0; i<rsmd.getColumnCount(); i++){ 

  String columnLabel = rsmd.getColumnLabel(i+1); 

  Object columValue = resultSet.getObject(columnLabel); 

  values.put(columnLabel, columValue); 

}

}

  System.out.println(values); 

  Class clazz = Student.class; 

  Object object = clazz.newInstance(); 

  for(Map.Entry<String, Object> entry: 
 values.entrySet()){ 

  String fieldName = entry.getKey(); 

  Object fieldValue = entry.getValue(); 

  // System.out.println(fieldName + ":" +fieldValue); 

 
 ReflectionUtils.setFieldValue(object, fieldName, fieldValue);  

}

  System.out.println(object); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(resultSet, preparedStatement, connection); 

}

}

 
 使用beanUtils工具类操作JavaBean 

  /* * Connection 代表应用程序和数据库的链接 * * */ 

  @Test 

  public void testGetConnection() throws Exception{ 

  //1. 获取连接的4个字符窜: user, password, jdbcUrl, driverClass 

  String 
 user = "root"; 

  String 
 password = "root"; 

  String 
 jdbcUrl = "jdbc:mysql:///atguigu"; 

  String 
 driverClass = "com.mysql.jdbc.Driver"; 

  //2. 加载驱动:Class.forName(driverClass); 

 
 Class.forName(driverClass); 

  //3. 调用DriverManager.getConnection(jdbcUrl, user, password); 

 
 Connection connection = (Connection) DriverManager.getConnection(jdbcUrl, user, password); 

  System.out.println(connection); 

}

 
 使用JavaBean需要同时加入：commons-beanutils-1.8.0.jar 和 commons-logging.jar 

  @Test 

  public void testProperty() throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{ 

 
 Object object = new Student(); 

  System.out.println(object); 

 
 BeanUtils.setProperty(object, "idCard", "2115658895466"); //设置原有的属性的值 

 
 System.out.println(object);-------------------------------------------------------------输出的话，就是和Student类中的 toString() 方法有关的  

 
 Object obj = BeanUtils.getProperty(object, " 
 idCard 
 "); //获得原有的属性的值 

  System.out.println(obj); 

}

  @Test 

  public void testSetProperty() throws IllegalAccessException, InvocationTargetException { 

  Object object = new Student(); 

  System.out.println(object); 

 
 BeanUtils.setProperty(object, " 
 idCard2 
 ", "2115658895466"); //创建新的属性 

  System.out.println(object); 

}

  for(int i=0; i<rsmd. 
 getColumnCount; i++){ 

  String columnLabel = 
 rsmd.getColumnLabel(i); 

}

  //返回某条记录的某一个字段的值，或一个统计的值（一共有都少条记录）等 

 
 public <E> E getForValue(String sql, Object...args) throws Exception{ 

  //1. 得到结果集：该结果集应该只有一行，且只有一列 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  ResultSet resultSet = null; 

  try { 

 
 //1. 得到结果集 

  connection = JDBCTools.getConnection(); 

  preparedStatement = connection.prepareStatement(sql); 

 
 for(int i=0; i<args.length; i++){ 

 
 preparedStatement.setObject(i+1, args[i]); 

}

  resultSet = preparedStatement.executeQuery(); 

 
 //2. 取得结果 

 
 if 
 (resultSet.next()){

 
 return (E) resultSet.getObject(1); 

}

  } catch(Exception ex){ 

  ex.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(resultSet, preparedStatement, connection); 

}

  return null; 

}

}

 
 JDBC获取插入记录的主键值 

/*

  * 取得数据库自动生成的主键 

  * */ 

  @Test 

  public void testGetKeyValue() { 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  try { 

  connection = JDBCTools.getConnection(); 

  String sql = "INSERT INTO customers(name, email, birth) " + "VALUES(?, ?, ?)"; 

  //使用重载的preparedStatement(sql, flag) 来生成PreparedStatement对象 

 
 preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  

  preparedStatement.setString(1, "ABCDE"); 

  preparedStatement.setString(2, "abc@atguigu.com"); 

  preparedStatement.setDate(3, new Date(new java.util.Date().getTime())); 

  preparedStatement.executeUpdate(); 

  //通过 getGeneratedKeys() 获取包含了新生成的主键的ResultSet对象 

 
 //在ResultSet中只有一列 GENERATED_KEYS，用于存放新生成的主键值 

  ResultSet rs = preparedStatement.getGeneratedKeys(); 

  if(rs.next()){ 

  System.out.println(rs.getObject(1)); 

}

  ResultSetMetaData rsmd = rs.getMetaData(); 

  for(int i=0; i<rsmd.getColumnCount(); i++){ 

  System.out.println(rsmd.getColumnName(i+1)); 

}

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, preparedStatement, connection); 

}

}

}

 
 JDBC 处理Blob(大型文件对象) 

/*

  * 
  插入BLOB类型的数据必须使用PreparedStatement。因为BLOB类型的数据是无法使用字符拼写的 

*

  * 
 调用setBlob(int index, InputStream inputStream) 

  * */ 

  @Test 

  public void testInsertBlob(){ 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  try { 

  connection = JDBCTools.getConnection(); 

  String sql = "INSERT INTO customers(name, email, birth, picture) " + "VALUES(?, ?, ?, ?)"; 

  preparedStatement = connection.prepareStatement(sql); 

  preparedStatement.setString(1, "ABCDE"); 

  preparedStatement.setString(2, "abc@atguigu.com"); 

  preparedStatement.setDate(3, new Date(new java.util.Date().getTime())); 

 
 InputStream inputStream = new FileInputStream("01.jpg"); 

 
 preparedStatement.setBlob(4, inputStream);  

  preparedStatement.executeUpdate(); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, preparedStatement, connection); 

}

}

/*

  * 读取blob数据： 

  * 1. 
 使用getBlob方法读取到Blob对象 

  * 2. 
 调用Blob的getBinaryStream()对象，得到输入流，再使用IO操作即可 

  * */ 

  @Test 

  public void readBlob(){ 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  ResultSet resultSet = null; 

  try { 

  connection = JDBCTools.getConnection(); 

  String sql = "SELECT id, name customerName, email, birth, picture " +"FROM customers " + "WHERE id=9"; 

  preparedStatement = connection.prepareStatement(sql); 

  resultSet = preparedStatement.executeQuery(); 

  if(resultSet.next()){ 

  int id = resultSet.getInt(1); 

  String name = resultSet.getString(2); 

  String email = resultSet.getString(3); 

  System.out.println(id + "," + name + "," + email); 

 
 Blob picture = (Blob) resultSet.getBlob(5);  

 
 InputStream in = picture.getBinaryStream(); 

  OutputStream out = new FileOutputStream("Beauty.jpg"); 

  byte []buffer = new byte[1024]; 

  int len = 0; 

  while((len = in.read(buffer))!=-1){ 

  out.write(buffer, 0, len); 

}

  out.close(); 

  in.close(); 

}

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(resultSet, preparedStatement, connection); 

}

}

 
 JDBC处理事务 

/*

  * Tom给Jerry汇款500元 

*

  * 关于事务： 

  * 1、如果多个操作，每个操作使用的是自己的单独的链接，则无法保证事务 

  * 2、具体步骤： 

  * 1） 
 事务操作开始钱，开始事务：取消Connection的默认提交行为 

 
 * connection.setAutoCommit(false); 

  * 2）如果事务的操作都成功，则提交事务 

  * 3）回滚事务：若出现异常，则在catch块中回滚事务 

*

  * */ 

  @Test 

  public void testTransaction() { 

  Connection connection = null; 

  try { 

  connection = JDBCTools.getConnection(); 

 
 //开始事务：取消默认提交 

 
 connection.setAutoCommit(false);  

  String sql = "UPDATE users SET balance = " + "balance-500 WHERE id=1"; 

  update(connection, sql); 

  int i = 10/0; 

  System.out.println(i); 

  sql = "UPDATE users SET balance = " + "balance+500 WHERE id=2"; 

  update(connection, sql); 

 
 //提交事务 

 
 connection.commit();  

  } catch (Exception e) { 

  e.printStackTrace(); 

  //回滚事务 

  try { 

  connection.rollback(); 

  } catch (SQLException e1) { 

  // TODO Auto-generated catch block 

  e1.printStackTrace(); 

}

  } finally{ 

  JDBCTools.releaseDB(null, null, connection); 

}

)

 
 事务的隔离级别 

 
 事务的并发问题有： 

 
 脏读：T1 和 T2两个事物，T1读取了已经被T2更新但 
 没有提交 
 的字段。然后T2回滚，T1读取的内容就是无效的了。 

 
 重复读：T1和T2两个事物，T1，T2从一个表中读取一个字段，然后T2 
 更新 
 了这个字段，T1再次读取，值就不同了。 

 
 幻读：T1和T2两个事物，T1，T2从一个表中读取一个字段，然后T2在表中插入了些新的行，T1再次读取，就会多出新的几行。 

 
 事务的隔离级别有： 

 
 READ UNCOMMITED: 脏读、重复读、幻读 都会有 

 
 READ COMMITED: 脏读可以避免，重复读、但幻读会有 

 
 REPEATABLE READ: 脏读、重复读可以避免，但幻读会有 

 
 SERIALIZABLE：都可以避免，但效率十分低下 

  //返回某条记录的某一个字段的值，或一个统计的值（一共有都少条记录）等 

  public <E> E getForValue(String sql, Object...args) throws Exception{ 

  //1. 得到结果集：该结果集应该只有一行，且只有一列 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  ResultSet resultSet = null; 

  try { 

  //1. 得到结果集 

  connection = JDBCTools.getConnection(); 

  System.out.println(connection.getTransactionIsolation()); 

  // connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); 

 
 connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//把隔离级别设置为：READ COMMITED 

  preparedStatement = connection.prepareStatement(sql); 

  for(int i=0; i<args.length; i++){ 

  preparedStatement.setObject(i+1, args[i]); 

}

  resultSet = preparedStatement.executeQuery(); 

  //2. 取得结果 

  if(resultSet.next()){ 

  return (E) resultSet.getObject(1); 

}

  } catch(Exception ex){ 

  ex.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(resultSet, preparedStatement, connection); 

}

  return null; 

}

 
 JDBC 批量处理语句 

/**

  * 向 Oracle 的 customers (mysql的customors)数据表中插入 10 万条记录 

  * 测试如何插入, 用时最短. 

 
 * 1. 使用 Statement. 

*/

  @Test 

  public void testBatchWithStatement(){ 

  Connection connection = null; 

  Statement statement = null; 

  String sql = null; 

  try { 

  connection = JDBCTools.getConnection(); 

 
 JDBCTools.beginTx(connection);  

  statement = connection.createStatement(); 

 
 long begin = System.currentTimeMillis(); 

  for(int i = 0; i < 100000; i++){ 

  // sql = "INSERT INTO customors VALUES(" + (i + 1) + ", 'name_" + i + "', '29-6月 -13')"; 

 
 sql = "INSERT INTO customors VALUES(" + (i + 1) + ", 'name_" + i + "', '2016-11-17 13:17:05')"; 

 
 statement.addBatch(sql); 

}

 
 long end = System.currentTimeMillis();  

  System.out.println("Time: " + (end - begin)); //39567 

 
 JDBCTools.commit(connection); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  JDBCTools.rollback(connection); 

  } finally{ 

  JDBCTools.releaseDB(null, statement, connection); 

}

}

}

/*

 
 *用PreparedStatement 

*/

  @Test 

  public void testBatchWithPreparedStatement(){ 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  String sql = null; 

  try { 

  connection = JDBCTools.getConnection(); 

 
 JDBCTools.beginTx(connection); 

 
 sql = "INSERT INTO customors VALUES(?,?,?)"; 

 
 preparedStatement = connection.prepareStatement(sql); 

  Date date = new Date(new java.util.Date().getTime()); 

  long begin = System.currentTimeMillis(); 

 
 for(int i = 0; i < 100000; i++){ 

 
 preparedStatement.setInt(1, i + 1); 

 
 preparedStatement.setString(2, "name_" + i); 

 
 preparedStatement.setDate(3, date);  

 
 preparedStatement.executeUpdate(); 

}

  long end = System.currentTimeMillis(); 

  System.out.println("Time: " + (end - begin)); //9819 

 
 JDBCTools.commit(connection); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  JDBCTools.rollback(connection); 

  } finally{ 

  JDBCTools.releaseDB(null, preparedStatement, connection); 

}

}

  @Test 

  public void testBatch(){ 

  Connection connection = null; 

  PreparedStatement preparedStatement = null; 

  String sql = null; 

  try { 

  connection = JDBCTools.getConnection(); 

 
 JDBCTools.beginTx(connection); 

 
 sql = "INSERT INTO customers VALUES(?,?,?)"; 

 
 preparedStatement = connection.prepareStatement(sql); 

  Date date = new Date(new java.util.Date().getTime()); 

  long begin = System.currentTimeMillis(); 

 
 for(int i = 0; i < 100000; i++){ 

 
 preparedStatement.setInt(1, i + 1); 

 
 preparedStatement.setString(2, "name_" + i); 

 
 preparedStatement.setDate(3, date);  

 
  
 //"积攒" SQL  

 
 preparedStatement.addBatch();  
  

 
 //当 "积攒" 到一定程度, 就统一的执行一次. 并且清空先前 "积攒" 的 SQL 

 
 if((i + 1) % 300 == 0){ 

 
 preparedStatement.executeBatch(); 

 
 preparedStatement.clearBatch(); 

}

}

 
 //若总条数不是批量数值的整数倍, 则还需要再额外的执行一次.  

 
 if(100000 % 300 != 0){ 

 
 preparedStatement.executeBatch(); 

 
 preparedStatement.clearBatch(); 

}

  long end = System.currentTimeMillis(); 

  System.out.println("Time: " + (end - begin)); //569 

 
 JDBCTools.commit(connection); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  JDBCTools.rollback(connection); 

  } finally{ 

  JDBCTools.releaseDB(null, preparedStatement, connection); 

}

}

 
 C3P0数据库连接池（ 
 真正在企业开发的时候，都是要通过“数据库连接池”去连接数据库的） 

 
 需要导入jar包：c3p0-0.9.1.2.jar 

 
 使用数据库连接池的利处： 

 
 数据库连接池是负责管理、分配和释放数据库连接的 

 
 它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个 

 
 c3p0数据库连接池： 

 
 （1）需要创建一个xml文件c3p0-config.xml 

  <?xml version="1.0" encoding="UTF-8"?> 

 
 <c3p0-config> 

 
 <named-config name="helloc3p0">  

 
 <!-- 指定连接数据源的基本属性 --> 

  <property name="user">root</property> 

  <property name="password">1230</property> 

  <property name="driverClass">com.mysql.jdbc.Driver</property> 

  <property name="jdbcUrl">jdbc:mysql:///atguigu</property> 

 
 <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 --> 

  <property name="acquireIncrement">5</property> 

 
 <!-- 初始化数据库连接池时连接的数量 --> //初始化数据库连接池的链接数量要和最小数据库连接数量相同 

  <property name="initialPoolSize">5</property> 

 
 <!-- 数据库连接池中的最小的数据库连接数 --> 

  <property name="minPoolSize">5</property> 

 
 <!-- 数据库连接池中的最大的数据库连接数 --> 

  <property name="maxPoolSize">10</property> 

 
 <!-- C3P0 数据库连接池可以维护的 Statement 的个数 --> 

  <property name="maxStatements">20</property> 

 
 <!-- 每个连接同时可以使用的 Statement 对象的个数 --> 

  <property name="maxStatementsPerConnection">5</property> 

  </named-config> 

 
 </c3p0-config> 

(2)

/**

  * 1. 创建 c3p0-config.xml 文件, 

  * 参考帮助文档中 Appendix B: Configuation Files 的内容 

  * 2. 创建 ComboPooledDataSource 实例； 

  * DataSource dataSource = 

  * new ComboPooledDataSource("helloc3p0"); 

  * 3. 从 DataSource 实例中获取数据库连接. 

*/

  @Test 

  public void testC3poWithConfigFile() throws Exception{ 

 
 DataSource dataSource = new ComboPooledDataSource("helloc3p0");  

  System.out.println( 
 dataSource.getConnection()); 

 
 ComboPooledDataSource comboPooledDataSource = (ComboPooledDataSource) dataSource; 

  System.out.println( 
 comboPooledDataSource.getMaxStatements()); 

}

 
 dbcp数据库连接池 

 
 需要导入数据库连接池的jar包 commons-dbcp-1.4.jar 和 commons-pool-1.5.5.jar  

 
 前者依赖于后者，所以这两个jar包都是需要导入的 

  （1）需要创建dbcp.properties文件 

  username=root 

  password=root 

  driverClassName=com.mysql.jdbc.Driver 

  url=jdbc:mysql:///atguigu 

 
 initialSize=10 //初始化链接数量 

 
 maxActive=50 //最大链接数量 

 
 minIdle=5 //最小空闲链接 

 
 maxWait=5000 //超时等待时间 

  （2） 

/**

  * 1. 加载 dbcp 的 properties 配置文件: 配置文件中的键需要来自 BasicDataSource 

  * 的属性. 

 
 * 2. 调用 BasicDataSourceFactory 的 createDataSource 方法创建 DataSource 

  * 实例 

  * 3. 从 DataSource 实例中获取数据库连接. 

*/

  @Test 

  public void testDBCPWithDataSourceFactory() throws Exception{ 

  Properties properties = new Properties(); 

 
 InputStream inStream = JDBCTest.class.getClassLoader().getResourceAsStream("dbcp.properties"); 

 
 properties.load(inStream);  

 
 DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);  

  System.out.println(dataSource.getConnection()); 

 
 BasicDataSource basicDataSource = (BasicDataSource) dataSource; 

  System.out.println(basicDataSource.getMaxWait()); 

}

 
 用DBUtils查询（select） 

 
 需要导入：commons-dbutils-1.3.jar 

  /** * 测试 
 QueryRunner 类的 update 方法 

  * 该方法可用于 INSERT, UPDATE 和 DELETE 

*/

  @Test 

  public void testQueryRunnerUpdate() { 

  //1. 创建 QueryRunner 的实现类 

 
 QueryRunner queryRunner = new QueryRunner();  

 
 String sql = "DELETE FROM customers " +"WHERE id IN (?,?)";  

  Connection connection = null; 

  try { 

  connection = JDBCTools.getConnection(); 

  //2. 使用其 update 方法 

 
 queryRunner.update(connection, sql, 
   
 12, 13 
 ); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, null, connection); 

}

}

}

 
 /** 1. ResultSetHandler 的作用: QueryRunner 的 query 方法的返回值最终取决于 

 
 * query 方法的 ResultHandler 参数的 hanlde 方法的返回值.  

* /

  /** * 测试 QueryRunner 的 query 方法 */ 

  @SuppressWarnings({ "unchecked", "rawtypes" }) 

  @Test 

  public void testResultSetHandler(){ 

 
 String sql = "SELECT id, name, email, birth " + "FROM customers";  

  //1. 创建 QueryRunner 对象 

 
 QueryRunner queryRunner = new QueryRunner();  

  Connection conn = null; 

  try { 

  conn = JDBCTools.getConnection(); 

  /**2. 调用 query 方法: 

  * 
 ResultSetHandler 参数的作用: query 方法的返回值直接取决于 

  * ResultSetHandler 的 handle(ResultSet rs) 是如何实现的. 实际上, 在 

  * QueryRunner 类的 query 方法中也是调用了 ResultSetHandler 的 handle() 

  * 方法作为返回值的。 */ 

 
 Object object = queryRunner.query(conn, sql,  

 
 new ResultSetHandler(){ 

 
 @Override 

 
 public Object handle(ResultSet rs) throws SQLException { 

 
 List<Customer> customers = new ArrayList<>(); 

  while( 
 rs.next()){ 

  int id = 
 rs.getInt(1); 

  String name = 
 rs.getString(2); 

  String email = 
 rs.getString(3); 

  Date birth = 
 rs.getDate(4);  

 
 Customer customer = new Customer(id, name, email, birth); 

  customers.add(customer); 

}

  return customers; 

}

}

);

  System.out.println(object); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, null, conn); 

}

}

 
 /* 3. BeanHandler: 把结果集转为一个 Bean, 并返回. Bean 的类型在创建 BeanHandler 

 
 * 对象时以 Class 对象的方式传入 

 
 * BeanHandler(Class<T> type)  

* /

  /** 
 BeanHandler: 把结果集的一条记录转为创建BeanHandler对象传入的Class参数对应的对象 * */ 

  @Test 

  public void testBeanHandler(){ 

  Connection connection = null; 

  try { 

  connection = JDBCTools.getConnection(); 

 
 String sql = "SELECT id, name CustomerName, email, birth " + "FROM customers WHERE id >= ?";  
  

 
 QueryRunner queryRunner = new QueryRunner(); 

 
 Customer customer = queryRunner.query(connection, sql, new BeanHandler(Customer.class), 5); 

  System.out.println(customer); 

  } catch (Exception e) { 

  // TODO Auto-generated catch block 

  e.printStackTrace(); 

  } finally { 

  JDBCTools.releaseDB(null, null, connection); 

}

}

 
 /* 2. BeanListHandler: 把结果集转为一个 Bean 的 List, 并返回. Bean 的类型在 

 
 * 创建 BeanListHanlder 对象时以 Class 对象的方式传入. 可以适应列的别名来映射  

 
 * JavaBean 的属性名:  

 
 * String sql = "SELECT id, name customerName, email, birth " + 

 
 * "FROM customers WHERE id = ?"; 

*

 
 * BeanListHandler(Class<T> type)*/ 

  /**测试 
 ResultSetHandler 的  
 BeanListHandler  
 实现类 

  * BeanListHandler: 把结果集转为一个 Bean 的 List. 该 Bean 

  * 的类型在创建 BeanListHandler 对象时传入: 

*

  * 
 new BeanListHandler<>(Customer.class) 

*

*/

  @Test 

  public void testBeanListHandler(){ 

 
 String sql = "SELECT id, name customerName, email, birth " + "FROM customers";  

  //1. 创建 QueryRunner 对象 

 
 QueryRunner queryRunner = new QueryRunner();  

  Connection conn = null; 

  try { 

  conn = JDBCTools.getConnection(); 

  Object object = 
  queryRunner.query(conn, sql, new BeanListHandler<>(Customer.class));  

  System.out.println(object); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, null, conn); 

}

}

 
 / * 4. MapHandler: 把结果集转为一个 Map 对象, 并返回. 若结果集中有多条记录, 仅返回 

 
 * 第一条记录对应的 Map 对象. Map 的键: 列名(而非列的别名), 值: 列的值 

* /

  @Test 

  public void testMapHandler(){ 

  Connection connection = null; 

 
 QueryRunner queryRunner = new QueryRunner();  

 
 String sql = "SELECT id, name customerName, email, birth " + "FROM customers WHERE id = ?";  

  try { 

  connection = JDBCTools.getConnection(); 

 
 Map<String, Object> map = queryRunner.query(connection, sql, new MapHandler(), 4);  

  System.out.println( 
 map); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, null, connection); 

}

}

 
 /* 5. MapListHandler: 把结果集转为一个 Map 对象的集合, 并返回.  

 
 * Map 的键: 列名(而非列的别名), 值: 列的值*/ 

  @Test 

  public void testMapListHandler(){ 

  Connection connection = null; 

 
 QueryRunner queryRunner = new QueryRunner();  

 
 String sql = "SELECT id, name, email, birth " + "FROM customers";  

  try { 

  connection = JDBCTools.getConnection(); 

 
 List<Map<String, Object>> mapList = queryRunner.query(connection, sql, new MapListHandler());  

  System.out.println( 
 mapList); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, null, connection); 

}

}

 
 / * 6. ScalarHandler: 可以返回指定列的一个值或返回一个统计函数的值.  

*/

  @Test 

  public void testScalarHandler(){ 

  Connection connection = null; 

 
 QueryRunner queryRunner = new QueryRunner();  

 
 String sql = "SELECT name FROM customers " +"WHERE id = ?";  

  try { 

  connection = JDBCTools.getConnection(); 

 
 Object count = queryRunner.query(connection, sql, new ScalarHandler(), 6);  

  System.out.println(count); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, null, connection); 

}

}

 
 /**7. QueryLoader: 可以用来加载存放着 SQL 语句的资源文件. 

 
 * 使用该类可以把 SQL 语句外置化到一个资源文件中. 以提供更好的解耦 

  * @throws IOException */ 

 
 (1)需要创建一个sql.properties文件 

 
 UPDATE_CUSTOMER=UPDATE customers SET name = wangww WHERE id = 9 

 
 （2） 

  @Test 

  public void testQueryLoader() throws IOException{ 

  // / 代表类路径的根目录. 

 
 Map<String, String> sqls = QueryLoader.instance().load("/sql.properties");  

  String updateSql = 
  sqls.get("UPDATE_CUSTOMER"); 

  System.out.println(updateSql); 

}

 
 用DBUtils编写通用的DAO 

 
 day_21 

 
 DAO.java 

/**

  * 访问数据的 DAO 接口. 

  * 里边定义好访问数据表的各种方法 

  * @param T: DAO 处理的实体类的类型. 

*/

  public interface DAO<T> { 

/**

  * 
 批量处理的方法 

  * @param args: 填充占位符的 Object [] 类型的可变参数. */ 

 
 void  
 batch 
 (Connection connection,  

 
 String sql, Object [] ... args) throws SQLException; 

/**

  * 
 返回具体的一个值, 例如总人数, 平均工资, 某一个人的 email 等. */ 

 
 <E> E  
 getForValue 
 (Connection connection, 

 
 String sql, Object ... args) throws SQLException; 

/**

  * 
 返回 T 的一个集合 */ 

 
 List<T>  
 getForList 
 (Connection connection, 

 
 String sql, Object ... args) throws SQLException 

/**

  * 返回一个 T 的对象 */ 

 
 T  
 get 
 (Connection connection, String sql,  

 
 Object ... args) throws SQLException; 

/**

  * INSERT, UPDATE, DELETE 

  * @param connection: 数据库连接 

  * @param sql: SQL 语句 

  * @param args: 填充占位符的可变参数. 

  * @throws SQLException 

*/

 
 void  
 update 
 (Connection connection, String sql,  

 
 Object ... args) throws SQLException; 

}

 
 JdbcDaoImpl.java 

/**

  * 使用 QueryRunner 提供其具体的实现 

  * @ 
 param <T>: 子类需传入的泛型类型.  

*/

  public class JdbcDaoImpl<T> 
   
 implements  
 DAO<T> { 

  private QueryRunner queryRunner = null; 

 
 private Class<T> type;  

  public JdbcDaoImpl() { 

  queryRunner = new QueryRunner(); 

  type = 
 ReflectionUtils.getSuperGenericType(getClass()); 

}

  @Override 

 
 public void  
 batch 
 (Connection connection, String sql, Object[]... args) throws SQLException { 

 
 queryRunner.batch(connection, sql, args); 

}

  @Override 

 
 public <E> E  
 getForValue 
 (Connection connection, String sql, Object... args) throws SQLException { 

  return (E) queryRunner.query(connection, sql, new ScalarHandler(), args); 

}

  @Override 

 
 public List<T>  
 getForList 
 (Connection connection, String sql, Object... args)  

  throws SQLException { 

  return queryRunner.query(connection, sql, 

  new BeanListHandler<>(type), args); 

}

  @Override 

 
 public T  
 get 
 (Connection connection, String sql, Object... args) throws SQLException { 

  return queryRunner.query(connection, sql, 

  new BeanHandler<>(type), args); 

}

  @Override 

 
 public void  
 update 
 (Connection connection, String sql, Object... args) throws SQLException { 

  queryRunner.update(connection, sql, args); 

}

}

 
 Customer.java 

 
 CustomerDao.java 

  public class CustomerDao 

 
 extends  
 JdbcDaoImpl<Customer>{ 

}

 
 CustomerDaoTest.java 

  @Test 

  public void testGet() { 

  Connection connection = null; 

  try { 

  connection = JDBCTools.getConnection(); 

 
 String sql = "SELECT id, name customerName, " + "email, birth FROM customers " + "WHERE id = ?"; 

  Customer customer = 
  customerDao.get(connection, sql, 5); 

  System.out.println(customer); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally{ 

  JDBCTools.releaseDB(null, null, connection); 

}

}

 
 JDBCTools 

  /** JDBC 的工具类 

  * 其中包含: 获取数据库连接, 关闭数据库资源等方法 */ 

  public class JDBCTools { 

  //处理数据库事务的 

 
 //提交事务 

  public 
 static void 
  commit(Connection connection){ 

  if(connection != null){ 

  try { 

 
 connection.commit(); 

  } catch (SQLException e) { 

  e.printStackTrace(); 

}

}

}

 
 //回滚事务 

  public 
 static void 
  rollback(Connection connection){ 

  if(connection != null){ 

  try { 

 
 connection.rollback(); 

  } catch (SQLException e) { 

  e.printStackTrace(); 

}

}

}

 
 //开始事务 

  public 
 static void 
  beginTx(Connection connection){ 

  if(connection != null){ 

  try { 

 
 connection.setAutoCommit(false); 

  } catch (SQLException e) { 

  e.printStackTrace(); 

}

}

}

  private 
 static DataSource dataSource = null; 

 
 //数据库连接池应只被初始化一次.  

 
 static{ 

 
 dataSource = new ComboPooledDataSource("c3p0"); 

}

  public 
 static Connection getConnection() throws Exception { 

 
 return dataSource.getConnection(); 

}

  public 
 static void releaseDB(ResultSet resultSet, Statement statement, 

  Connection connection) { 

  if (resultSet != null) { 

  try { 

  resultSet.close(); 
 //先关resultSet 

  } catch (SQLException e) { 

  e.printStackTrace(); 

}

}

  if (statement != null) { 

  try { 

  statement.close(); 
 //再关statement 

  } catch (SQLException e) { 

  e.printStackTrace(); 

}

}

  if (connection != null) { 

  try { 

 
 //数据库连接池的 Connection 对象进行 close 时  
 //最后关connection 

 
 // 
 并不是真的进行关闭 
 , 而是把该数据库连接会归还到数据库连接池中.  

 
 connection.close(); 

  } catch (SQLException e) { 

  e.printStackTrace(); 

}

}

}

}

 
 JDBC调用函数和存储过程 

 
 如果是函数的话： 

 
 查看文档 

 
 { 
 ?= 
  call <procedure-name>[<arg1>,<arg2>,<arg3>...]} ---调用的 
 “函数” 

 
 而 {call <procedure-name>[<arg1>,<arg2>,<arg3>...]} ---这种是调用的 
 “存储过程” 

  /** 如何使用 JDBC 调用存储在数据库中的函数或存储过程*/ 

  @Test 

  public void testCallableStatment() { 

  Connection connection = null; 

 
 CallableStatement callableStatement = null; 

  try { 

  connection = JDBCTools.getConnection(); 

  // 1. 通过 Connection 对象的 prepareCall() 

  // 方法创建一个 CallableStatement 对象的实例. 

  // 在使用 Connection 对象的 preparedCall() 方法时, 

  // 需要传入一个 String 类型的字符串, 该字符串用于指明如何调用存储过程. 

 
 String sql = "{?= call sum_salary(?, ?)}"; 

 
 callableStatement = connection.prepareCall(sql); 

  // 2. 通过 CallableStatement 对象的 

 
 //reisterOutParameter() 方法注册 OUT 参数. 

 
 callableStatement.registerOutParameter(1, Types.NUMERIC); 

 
 callableStatement.registerOutParameter(3, Types.NUMERIC);  

  // 3. 通过 CallableStatement 对象的 setXxx() 方法设定 IN 或 IN OUT 参数. 若想将参数默认值设为 

  // null, 可以使用 setNull() 方法. 

 
 callableStatement.setInt(2, 80);  

  // 4. 通过 CallableStatement 对象的 
 execute() 方法执行存储过程 

 
 callableStatement.execute();  

 
 // 5. 如果所调用的是带返回参数的存储过程,  

 
 //还需要通过 CallableStatement 对象的 getXxx() 方法获取其返回值. 

  double sumSalary = 
 callableStatement.getDouble(1); 

  long empCount = 
 callableStatement.getLong(3); 

  System.out.println(sumSalary); 

  System.out.println(empCount); 

  } catch (Exception e) { 

  e.printStackTrace(); 

  } finally { 

  JDBCTools.releaseDB(null, callableStatement, connection); 

}

}