DBUtils学习（二）

1、新建测试类

package tan.com;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.ResultSetHandler;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import org.junit.Test;public class DBUtilsTest {/** *ScalerHandler:把结果集转为一个数值并返回，如果有两列则返回第一列的值 *ScalarHandler: 可以返回指定列的一个值或返回一个统计函数的值.  */@Testpublic void testScalerHandler(){Connection connection=null;try {connection=JDBCTools.getConnection();    String sql="SELECT name " +"FROM user where id=?";//获取指定id的值//String sql="SELECT count(id) " +"FROM user ";//返回有几条记录@SuppressWarnings("unchecked")Object u6=queryRunner.query(connection, sql, new ScalarHandler(),5);//Object u6=queryRunner.query(connection, sql, new ScalarHandler());System.out.println(u6);} catch (Exception e) {e.printStackTrace();}finally{JDBCTools.release(null, null, connection);}}/** * MapListHandler:将结果集转为一个Map的List * Map对应查询的一条记录： 键：SQL查询的列名（注意不是列的别名）值：对应列的值 * 而MapListHandler:返回的是多条记录对应的Map集合 *  */@Testpublic void testMapListHandler(){Connection connection=null;try {connection=JDBCTools.getConnection();String sql="SELECT id, name, age, school " +"FROM user ";List<Map<String, Object>>u5=queryRunner.query(connection, sql, new MapListHandler());System.out.println(u5);} catch (Exception e) {e.printStackTrace();}finally{JDBCTools.release(null, null, connection);}}/** * MapHandler:返回SQL对应的第一条记录对应的Map对象。 * 键：SQL查询的列名（注意不是列的别名） * 值：对应列的值 */@Testpublic void testMapHandler(){Connection connection=null;try {connection=JDBCTools.getConnection();String sql="SELECT id, name, age, school " +"FROM user ";Map<String, Object>u4=queryRunner.query(connection, sql, new MapHandler());System.out.println(u4);} catch (Exception e) {e.printStackTrace();}finally{JDBCTools.release(null, null, connection);}}/** *  BeanListHandler: 把结果集转为一个 Bean 的 List, 并返回. Bean 的类型在 * 创建 BeanListHanlder 对象时以 Class 对象的方式传入. 可以适应列的别名来映射 JavaBean 的属性名:  * String sql = "SELECT id, name age, school " +"FROM user WHERE id = ?"; *  * BeanListHandler(Class<T> type) *  * BeanListHandler:把结果集转为一个List，该List不为null，但是可能为空集合（size（）方法返回0） * 若sql语句的确可以查询到记录，则list中存放创建的BeanListHandler传入的Class对象对应的对象。 */@Testpublic void testBeanListHandler(){Connection connection=null;try {connection=JDBCTools.getConnection();String sql="SELECT id, name, age, school " +"FROM user ";List<user>u3=queryRunner.query(connection, sql, new BeanListHandler<>(user.class));System.out.println(u3);} catch (Exception e) {e.printStackTrace();}finally{JDBCTools.release(null, null, connection);}}/** * BeanHandler: 把结果集转为一个 Bean, 并返回. Bean 的类型在创建 BeanHandler对象时以 Class 对象的方式传入 * BeanHandler(Class<T> type)  */@Testpublic void tesstBeanHandler(){Connection connection=null;try {connection=JDBCTools.getConnection();String sql="SELECT id, name, age, school " +"FROM user where id>=?";@SuppressWarnings("unchecked")user u2=queryRunner.query(connection, sql, new BeanHandler(user.class), 5);System.out.println(u2);} catch (Exception e) {e.printStackTrace();}finally{JDBCTools.release(null, null, connection);}}//测试 QueryRunner 的 query 方法，往下看---->//可以放在外面，因为这个线程是安全的QueryRunner queryRunner=new QueryRunner();@SuppressWarnings("rawtypes")class MyResultHandler implements ResultSetHandler{@Overridepublic Object handle(ResultSet resultSet) throws SQLException {List<user>users=new ArrayList<user>();while(resultSet.next()){//取出所有的结果集int id=resultSet.getInt(1);String name=resultSet.getString(2);int age=resultSet.getInt(3);String school=resultSet.getString(4);//添加到集合中user u1=new user(id, name, age, school);users.add(u1);}return users;}}/** * 测试 QueryRunner 的 query 方法 */@Testpublic void testQuery(){Connection connection=null;try {connection=JDBCTools.getConnection();//查询String sql="SELECT id, name, age, school " +"FROM user";/** * 2. 调用 query 方法: * ResultSetHandler 参数的作用: query 方法的返回值直接取决于  * ResultSetHandler 的 hanlde(ResultSet rs) 是如何实现的. 实际上, 在 * QueryRunner 类的 query 方法中也是调用了 ResultSetHandler 的 handle()方法作为返回值的。 */@SuppressWarnings("unchecked")Object obj=queryRunner.query(connection, sql, new MyResultHandler());System.out.println(obj);} catch (Exception e) {e.printStackTrace();}finally{JDBCTools.release(null, null, connection);}}/** * 测试QueryRunner类的update方法 * @throws Exception  */@Testpublic void testQueryRunnerUpdate() throws Exception {//1.创建QueryRunner的实现类QueryRunner queryRunner=new QueryRunner();//2.使用update方法删除id为2和3的用户//String sql = "DELETE FROM user " +"WHERE id IN (?,?)";String sql="update user set name=? "+"where id=?";Connection connection=null;try {connection=JDBCTools.getConnection();//获取数据库连接queryRunner.update(connection, sql, "小强",5);//System.out.println("更新操作成功！");} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JDBCTools.release(null, null, connection);}}}

2、创建可用于控制台输出的Bean

package tan.com;public class user {private Integer id;private String name;private Integer age;private String school;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public String getSchool() {return school;}public void setSchool(String school) {this.school = school;}public user(Integer id, String name, Integer age, String school) {super();this.id = id;this.name = name;this.age = age;this.school = school;}public user() {// TODO Auto-generated constructor stub}/* (non-Javadoc) * @see java.lang.Object#toString() */@Overridepublic String toString() {return "user [id=" + id + ", name=" + name + ", age=" + age+ ", school=" + school + "]";}}

3、工具类别忘了

package tan.com;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class JDBCTools {//数据库连接池应该只被初始化一次（多个项目共享一个连接池就够了）private static DataSource dataSource=null;static{dataSource=new ComboPooledDataSource("helloc3p0");}//获取数据库连接的方法public static Connection getConnection() throws Exception{return dataSource.getConnection();}    /**     * 2、关闭资源     * 关闭资源关闭 Statement 和 Connection和ResultSet     * @param rs     * @param statement     * @param conn     */public static void release(ResultSet rs, Statement statement, Connection conn) {if(rs != null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (Exception e2) {e2.printStackTrace();}}if (conn != null) {try {conn.close();} catch (Exception e2) {e2.printStackTrace();}}}/** * 关闭 Statement 和 Connection * @param statement * @param conn */public static void release(Statement statement, Connection conn) {if (statement != null) {try {statement.close();} catch (Exception e2) {e2.printStackTrace();}}if (conn != null) {try {conn.close();} catch (Exception e2) {e2.printStackTrace();}}}}

4、还是采用c3p0连接池

<?xml version="1.0" encoding="UTF-8"?><c3p0-config><named-config name="helloc3p0"><!-- 指定连接数据源的基本属性 --><property name="user">root</property><property name="password">1234</property><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql:///tan</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>