数据连接池的使用(2)

 学习了连接池之后，JdbcUtil工具类中的getConnection方法就可以应用，如下：

package com.atguigu.jdbc; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; import com.mchange.v2.c3p0.DataSources; /** * 预备工作 : * 1) 把要访问的数据库相关的驱动程序复制到项目中, 就是jar包 * 2) 配置项目属性, 把jar包导入到本项目的buildpath中 * @author Administrator * */ public class JdbcUtil { private static DataSource dataSource; // 声明静态属性对象引用. static { dataSource = new ComboPooledDataSource("mysql-config"); // 连接池对象只需要创建一次就可以了 } public static Connection getConnection() throws SQLException { return dataSource.getConnection(); // 要想获取连接, 只需要从连接池中获取,用完以后, 再归还回来 } public static Connection getConnectionOld() throws IOException, ClassNotFoundException, SQLException { // 1) 读取配置文件 Properties properties = new Properties(); properties.load(new FileInputStream("jdbc.properties")); // 2) 获取配置文件中的必要的信息 String driverClass = properties.getProperty("driverClass"); String url = properties.getProperty("url"); String user = properties.getProperty("user"); String password = properties.getProperty("password"); // 3) 注册驱动 , 加载驱动类 Class.forName(driverClass); // 4) 通过驱动管理器获取连接(需要url,用户,密码) return DriverManager.getConnection(url, user, password);// 暗含 new Socket(host,port), 认证,其他各种初始化操作 } //关闭连接 public static void close(Connection connection) { close(connection, null); } public static void close(Connection connection, Statement statement) { close(connection, statement, null); } public static void close(Connection connection, Statement statement, ResultSet resultSet) { if (resultSet != null) { try { resultSet.close(); } catch (Exception e) { e.printStackTrace(); } } if (statement != null) { try { statement.close(); } catch (Exception e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (Exception e) { e.printStackTrace(); } } } //销毁连接池 public static void destroy() { try { DataSources.destroy(dataSource); } catch (SQLException e) { e.printStackTrace(); } } } DBUtils工具类

将常用的操作数据库的JDBC的类和方法集合在一起，就是DBUtils.JDBC。提供供我们使用的工具类QueryRunner来执行操作。
在使用之前我们仍然需要将commons-dbutils-1.3.jar添加到当前工程下，并添加到path路径。

package com.atguigu.jdbc; import java.sql.Connection; import java.sql.SQLException; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.junit.Test; public class QueryRunnerTest { // 使用我们自定义工具实现表的创建 @Test public void test1() throws SQLException { QueryRunner qr = new QueryRunner(); Connection connection = JdbcUtil.getConnection(); qr.update(connection, "create table test2(aa int, bb varchar(10))"); JdbcUtil.close(connection); } // 使用我们自定义工具向表中插入一条记录 @Test public void test2() throws SQLException { QueryRunner qr = new QueryRunner(); Connection connection = JdbcUtil.getConnection(); int rows = qr.update(connection, "insert into test2(aa, bb) values(?,?)", 10, "xxx"); System.out.println(rows + " rows"); JdbcUtil.close(connection); } // 使用DBUtils.JDBC接口中提供的方法对departments表进行查询，把结果集中的所有记录转换为department对象集合并存入List集合中，然后遍历输出对象 @Test public void test3() throws SQLException { //query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) String sql = "select * from departments where department_id > ?"; QueryRunner qr = new QueryRunner(); Connection connection = JdbcUtil.getConnection(); BeanListHandler<Department> rsh = new BeanListHandler<Department>(Department.class); // 把结果集中的所有记录转换为对象集合 List<Department> list = qr.query(connection, sql, rsh, 20); for (Department department : list) { System.out.println(department); } } // 使用DBUtils.JDBC接口中提供的方法对departments表进行查询，把结果集中的一条记录转换为department实体对象，然后输出对象 @Test public void test4() throws SQLException { String sql = "select * from departments where department_id = ?"; QueryRunner qr = new QueryRunner(); Connection connection = JdbcUtil.getConnection(); BeanHandler<Department> rsh = new BeanHandler<Department>(Department.class); // 把结果集中的一条记录转换为实体对象 Department objDepartment = qr.query(connection, sql, rsh, 20); System.out.println(objDepartment); } // 使用DBUtils.JDBC接口中提供的方法对departments表进行查询，将每一条记录存入集合中，然后遍历输出每一个数据 @Test public void test5() throws SQLException { String sql = "select * from employees"; QueryRunner qr = new QueryRunner(); Connection connection = JdbcUtil.getConnection(); ArrayListHandler rsh = new ArrayListHandler(); List<Object[]> list = qr.query(connection, sql, rsh); for (Object[] objects : list) { for (int i = 0; i < objects.length; i++) { System.out.print(objects[i] + "\t"); } System.out.println(); } } // 使用DBUtils.JDBC接口中提供的方法对departments表进行查询，将查询到的一个数据输出 @Test public void test6 () throws SQLException { String sql = "select count(*) from world.country"; QueryRunner qr = new QueryRunner(); Connection connection = JdbcUtil.getConnection(); ScalarHandler rsh = new ScalarHandler(); Object singleValue = qr.query(connection, sql, rsh); System.out.println(singleValue); } @Test public void test7() throws Exception { QueryRunner qr = new QueryRunner(); List<Object> list = qr.query(JdbcUtil.getConnection(), "select * from student", new ColumnListHandler(1)); for (Object object : list) { System.out.println(object); } } //MapHandler把第一行数据封装到Map集合中, 列名作为键, 对应值作为值 @Test public void test8() throws Exception { QueryRunner qr = new QueryRunner(); Map<String, Object> map = qr.query(JdbcUtil.getConnection(), "select * from student", new MapHandler()); Set<String> keys = map.keySet(); for (String key : keys) { Object value = map.get(key); System.out.println(key + " -------- " + value); } } //MapListHandler把一行数据封装到Map集合中, 并把所有行生成的Map再放入一个List集合 @Test public void test9() throws Exception { QueryRunner qr = new QueryRunner(); List<Map<String, Object>> list = qr.query(JdbcUtil.getConnection(), "select * from student", new MapListHandler()); for (Map<String, Object> map2 : list) { Set<String> keys = map2.keySet(); for (String key : keys) { Object value = map2.get(key); System.out.println(key + " -------- " + value); } System.out.println(); } } } 到这里就可以统一整理一下自己定义的JdbcUtil工具类、CommonUtil工具类，使自定义的工具类能达到JDButi.JDBC相同的功能，如下：