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

//账户维护
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;
    }

} 

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);
	}
}