第十七天dbutils的使用------CommonsDbUtils(Apache)第三方的:只

所实现的功能差不多。这是第三方开发的，要导入相应的jar包(就一个jar包)，比自己写的强大。强大之处在于结果集的处理。主要涉及的类是 org.apache.commons.dbutils Class QueryRunner API如下： Constructor Summary QueryRunner () Constructor for QueryR

所实现的功能差不多。这是第三方开发的，要导入相应的jar包(就一个jar包)，比自己写的强大。强大之处在于结果集的处理。主要涉及的类是

org.apache.commons.dbutils
Class QueryRunner

API如下：

Constructor Summary

QueryRunner()
Constructor for QueryRunner.

QueryRunner(boolean pmdKnownBroken)
Constructor for QueryRunner, allows workaround for Oracle drivers

QueryRunner(DataSource ds)
Constructor for QueryRunner which takes a DataSource.

QueryRunner(DataSource ds, boolean pmdKnownBroken)
Constructor for QueryRunner, allows workaround for Oracle drivers.

Method Summary
int[]	batch(Connection conn, String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
int[]	batch(String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
T	query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) Deprecated. Use query(Connection,String,ResultSetHandler,Object...) instead
T	query(Connection conn, String sql, Object param, ResultSetHandler rsh) Deprecated. Use query(Connection, String, ResultSetHandler, Object...)
T	query(Connection conn, String sql, ResultSetHandler rsh) Execute an SQL SELECT query without any replacement parameters.
T	query(Connection conn, String sql, ResultSetHandler rsh, Object... params) Execute an SQL SELECT query with replacement parameters.
T	query(String sql, Object[] params, ResultSetHandler rsh) Deprecated. Use query(String, ResultSetHandler, Object...)
T	query(String sql, Object param, ResultSetHandler rsh) Deprecated. Use query(String, ResultSetHandler, Object...)
T	query(String sql, ResultSetHandler rsh) Executes the given SELECT SQL without any replacement parameters.
T	query(String sql, ResultSetHandler rsh, Object... params) Executes the given SELECT SQL query and returns a result object.
int	update(Connection conn, String sql) Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
int	update(Connection conn, String sql, Object... params) Execute an SQL INSERT, UPDATE, or DELETE query.
int	update(Connection conn, String sql, Object param) Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter.
int	update(String sql) Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters.
int	update(String sql, Object... params) Executes the given INSERT, UPDATE, or DELETE SQL statement.
int	update(String sql, Object param) Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter.

一、框架编写准备：数据库元数据的获取
1、元数据：数据库、表、列的定义信息
二、编写自己的框架简化JDBC开发

CUD：语句不同和参数不同。

三、ORM：
O：Object
R：Relation
M：Mapping
对象关系映射

JavaBean 关系数据库表结构 ：对应的映射关系

Hibernate：ORM映射框架 -----》规范化：JPA（Java Persistent API)
IBatis(Apache):ORM映射框架----------->2010(google) MyBatis
Commons DbUtils(Apache):只是对JDBC编码进行了简单的封装。
Spring JDBC Template:只是对JDBC编码进行了简单的封装。

例子：不用自己关闭资源，底层源代码已经关了，而且是结合dbcp使用的，关闭不是真的关闭，而是加入到连接池中，以后就放心使用，一句话解决crud操作。

//账户维护
public class DBUtilDemo {
	private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
	@Test
	public void testAdd() throws Exception{
		qr.update("insert into account(name,money) values(?,?)", "fff",1000);
	}
	@Test
	public void testFindOne() throws Exception{
		Account a = qr.query("select * from account where id=?", new BeanHandler<Account>(Account.class),1);
		System.out.println(a);
	}
	@Test
	public void testFindAll() throws Exception{
		List<Account> list = qr.query("select * from account", new BeanListHandler<Account>(Account.class));
		for(Account a:list)
			System.out.println(a);
	}
	//插入大文本行不行：知道clob对应的类型是什么
	/*
	 * create table t1(id int,content text);
	 */
	@Test
	public void testText() throws Exception{
		File file = new File("src/jpm.txt");
		Reader reader = new FileReader(file);//流并没有对应的数据库类型
		char ch[] = new char[(int)file.length()];
		reader.read(ch);//不好，开发中不用
		reader.close();
		
		Clob clob = new SerialClob(ch);
		qr.update("insert into t1(id,content) values(?,?)", 1,clob);
	}
	//插入大二进制行不行：知道blob对应的类型是什么
	/*
	 * create table t2(id int,content longblob);
	 */
	@Test
	public void testBlob() throws Exception{
		InputStream in = new FileInputStream("src/1.jpg");
		byte b[] = new byte[in.available()];
		in.read(b);
		in.close();
		
		Blob blob = new SerialBlob(b);
		qr.update("insert into t2(id,content) values(?,?)", 1,blob);
	}
	//批处理
	/*
	 * create table t3(id int,name varchar(200));
	 */
	@Test
	public void testBatch()throws Exception{
		Object params[][] = new Object[10][];//第1维，插入的条数。第2维，每条需要的参数
		for(int i=0;i<params.length;i++){
			params[i] = new Object[]{i+1,"aaa"+(i+1)};
		}
		
		
		qr.batch("insert into t3 values(?,?)", params);
	}
}

部分源代码如下：说明已经关闭资源了。

  public int update(String sql, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, params);
    }

    /**
     * Calls update after checking the parameters to ensure nothing is null.
     * @param conn The connection to use for the update call.
     * @param closeConn True if the connection should be closed, false otherwise.
     * @param sql The SQL statement to execute.
     * @param params An array of update replacement parameters.  Each row in
     * this array is one set of update replacement values.
     * @return The number of rows updated.
     * @throws SQLException If there are database or parameter errors.
     */
    private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        PreparedStatement stmt = null;
        int rows = 0;

        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rows = stmt.executeUpdate();

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return rows;
    }

} 

DBUtils框架提供的结果处理器例子:

public class ResultSetHandlerDemo {
	private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
	
	//ArrayHandler：把结果集中的第一行数据转成对象数组。
	@Test
	public void test1() throws SQLException{
		//数组中的元素就是记录的每列的值
		Object[] objs = qr.query("select * from account where id=?", new ArrayHandler(), 1);
		for(Object obj:objs)
			System.out.println(obj);
	}
	//ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
	@Test
	public void test2() throws SQLException{
		//数组中的元素就是记录的每列的值
		List<Object[]> list = qr.query("select * from account", new ArrayListHandler());
		for(Object[] objs:list){
			System.out.println("------------------");
			for(Object obj:objs){
				System.out.println(obj);
			}
		}
	}
	//ColumnListHandler：将结果集中某一列的数据存放到List中。 投影
	@Test
	public void test3() throws SQLException{
		//数组中的元素就是记录的每列的值
		List<Object> list = qr.query("select * from account", new ColumnListHandler("name"));
		for(Object objs:list){
			System.out.println(objs);
		}
	}
	//KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map<列名,列值>里，再把这些map再存到一个map里，其key为指定的key。
	@Test
	public void test4() throws SQLException{
		Map<Object,Map<String,Object>> bmap= qr.query("select * from account", new KeyedHandler("id"));
		for(Map.Entry<Object,Map<String,Object>> bme:bmap.entrySet()){
			System.out.println("-----------------");
			for(Map.Entry<String,Object> lme:bme.getValue().entrySet()){
				System.out.println(lme.getKey()+"="+lme.getValue());
			}
		}
	}
	//MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值
	@Test
	public void test5() throws SQLException{
		Map<String,Object> map = qr.query("select * from account where id=?", new MapHandler(),1);
		for(Map.Entry<String,Object> lme:map.entrySet()){
			System.out.println(lme.getKey()+"="+lme.getValue());
		}
	}
	//MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
	@Test
	public void test6() throws SQLException{
		List<Map<String,Object>> list= qr.query("select * from account", new MapListHandler());
		for(Map<String,Object> map:list){
			System.out.println("-----------------");
			for(Map.Entry<String,Object> lme:map.entrySet()){
				System.out.println(lme.getKey()+"="+lme.getValue());
			}
		}
	}
	//ScalarHandler :只有一条记录的投影查询 select count(*) from account;
	@Test
	public void test7() throws SQLException{
//		Object obj = qr.query("select * from account where id=?", new ScalarHandler("name"),1);
//		System.out.println(obj);
		Object obj = qr.query("select count(*) from account", new ScalarHandler(1));
		int num = ((Long)obj).intValue();
		System.out.println(num);
	}
}