apache的dbUtils的使用

1.引入相关的jar:

2.MapListHandler的使用，将结果集转换为List<Map<String,Object>>

@Beforepublic  void  init() throws SQLException, ClassNotFoundException{String driverClass="oracle.jdbc.driver.OracleDriver";String url="jdbc:oracle:thin:@192.168.233.134:1521:orcl";String username="cnmef";String password="cnmef";Class.forName(driverClass); connection=DriverManager.getConnection(url,username,password);}/** * 测试apache中dbUtils的mapListHandler的使用 * @throws SQLException */@Testpublic  void  MapListHandlerTest() throws SQLException{String sql="select " +"t1.id  as \"id\","+"  t1.nofficeid  as nofficeid, "+"  t1.ncurrencyid  as \"ncurrencyid\", "+"  t1.transaction_num as \"transactionNum\", "+"  t1.interest_start as  \"interestStart\", "+" t1.interest_end  as \"interestEnd\","+"   t1.margin_amount as \"marginAmount\", "+" t1.abstract as  \"summary\","+" t1.ncontractid as \"ncontractId\","+" t1.in_account as \"inAccount\","+"  t1.out_account     as \"outAccount\" ,"+"   t1.inputuserid as \"inputuserid\","+"  t1.checkuserid as \"checkuserid\","+"  t1.checkdate as \"checkdate\","+"  (select t2.id  from   sett_subaccount    t2  where  t2.naccountid=t1.in_account  and   rownum=1  ) as  \"subInAccountId\","+"  (select t3.id  from   sett_subaccount    t3  where  t3.naccountid=t1.out_account  and   rownum=1  ) as  \"subOutAccountId\","+" t1.margin_amount   as \"marginAmount\" "+" from sett_depositbatchreview t1   " ;QueryRunner queryRunner=new QueryRunner();List<Map<String,Object>> map=queryRunner.query(connection, sql, new MapListHandler());for(Map<String,Object> temp:map){System.out.println(temp);}}    @After      public  void close(){        try{            if(connection!=null){                connection.close();            }        }catch(Exception e){            e.printStackTrace();        }    } 

3.BeanListHandler的使用,返回List<Bean>:

@Beforepublic  void  init() throws SQLException, ClassNotFoundException{String driverClass="oracle.jdbc.driver.OracleDriver";String url="jdbc:oracle:thin:@192.168.233.134:1521:orcl";String username="cnmef";String password="cnmef";Class.forName(driverClass); connection=DriverManager.getConnection(url,username,password);}/**     * 测试apache中dbUtils的mapListHandler的使用     * @throws SQLException     */    @Test    public  void  BeanListHandlerTest() throws SQLException{        String sql="select " +                "t1.id  as \"id\","+                "  t1.nofficeid  as nofficeid, "+                "  t1.ncurrencyid  as \"ncurrencyid\", "+                "  t1.transaction_num as \"transactionNum\", "+                "  t1.interest_start as  \"interestStart\", "+                " t1.interest_end  as \"interestEnd\","+                "   t1.margin_amount as \"marginAmount\", "+                " t1.abstract as  \"summary\","+                " t1.ncontractid as \"ncontractId\","+                " t1.in_account as \"inAccount\","+                "  t1.out_account     as \"outAccount\" ,"+                "   t1.inputuserid as \"inputuserid\","+                "  t1.checkuserid as \"checkuserid\","+                "  t1.checkdate as \"checkdate\","+                "  (select t2.id  from   sett_subaccount    t2  where  t2.naccountid=t1.in_account  and   rownum=1  ) as  \"subInAccountId\","+                "  (select t3.id  from   sett_subaccount    t3  where  t3.naccountid=t1.out_account  and   rownum=1  ) as  \"subOutAccountId\","+                " t1.margin_amount   as \"marginAmount\" "+                " from sett_depositbatchreview t1   ";                QueryRunner queryRunner=new QueryRunner();                List<MarginAccountVo> map=queryRunner.query(connection, sql, new BeanListHandler<MarginAccountVo>(MarginAccountVo.class));                for(MarginAccountVo temp:map){                    System.out.println(temp);                }    }    @After      public  void close(){        try{            if(connection!=null){                connection.close();            }        }catch(Exception e){            e.printStackTrace();        }    }

4.测试MapHandler:返回Map<String,Object>

@Beforepublic  void  init() throws SQLException, ClassNotFoundException{String driverClass="oracle.jdbc.driver.OracleDriver";String url="jdbc:oracle:thin:@192.168.233.134:1521:orcl";String username="cnmef";String password="cnmef";Class.forName(driverClass); connection=DriverManager.getConnection(url,username,password);}/**     * 测试apache中dbUtils的mapListHandler的使用     * @throws SQLException     */    @Test    public  void  MapHandlerTest() throws SQLException{        String sql="select " +                "t1.id  as \"id\","+                "  t1.nofficeid  as nofficeid, "+                "  t1.ncurrencyid  as \"ncurrencyid\", "+                "  t1.transaction_num as \"transactionNum\", "+                "  t1.interest_start as  \"interestStart\", "+                " t1.interest_end  as \"interestEnd\","+                "   t1.margin_amount as \"marginAmount\", "+                " t1.abstract as  \"summary\","+                " t1.ncontractid as \"ncontractId\","+                " t1.in_account as \"inAccount\","+                "  t1.out_account     as \"outAccount\" ,"+                "   t1.inputuserid as \"inputuserid\","+                "  t1.checkuserid as \"checkuserid\","+                "  t1.checkdate as \"checkdate\","+                "  (select t2.id  from   sett_subaccount    t2  where  t2.naccountid=t1.in_account  and   rownum=1  ) as  \"subInAccountId\","+                "  (select t3.id  from   sett_subaccount    t3  where  t3.naccountid=t1.out_account  and   rownum=1  ) as  \"subOutAccountId\","+                " t1.margin_amount   as \"marginAmount\" "+                " from sett_depositbatchreview t1  where  t1.id=?   ";                QueryRunner queryRunner=new QueryRunner();                /*                 * queryRunner.query(connection,sql,ResultSetHandler<T>,params);                 */                Map<String,Object> map=queryRunner.query(connection, sql, new MapHandler(),1);                System.out.println(map);    }    @After      public  void close(){        try{            if(connection!=null){                connection.close();            }        }catch(Exception e){            e.printStackTrace();        }    }

5.测试BeanHandler:返回javaBean

@Beforepublic  void  init() throws SQLException, ClassNotFoundException{String driverClass="oracle.jdbc.driver.OracleDriver";String url="jdbc:oracle:thin:@192.168.233.134:1521:orcl";String username="cnmef";String password="cnmef";Class.forName(driverClass); connection=DriverManager.getConnection(url,username,password);}/**     * 测试apache中dbUtils的mapListHandler的使用     * @throws SQLException     */    @Test    public  void  BeanHandlerTest() throws SQLException{        String sql="select " +                "t1.id  as \"id\","+                "  t1.nofficeid  as nofficeid, "+                "  t1.ncurrencyid  as \"ncurrencyid\", "+                "  t1.transaction_num as \"transactionNum\", "+                "  t1.interest_start as  \"interestStart\", "+                " t1.interest_end  as \"interestEnd\","+                "   t1.margin_amount as \"marginAmount\", "+                " t1.abstract as  \"summary\","+                " t1.ncontractid as \"ncontractId\","+                " t1.in_account as \"inAccount\","+                "  t1.out_account     as \"outAccount\" ,"+                "   t1.inputuserid as \"inputuserid\","+                "  t1.checkuserid as \"checkuserid\","+                "  t1.checkdate as \"checkdate\","+                "  (select t2.id  from   sett_subaccount    t2  where  t2.naccountid=t1.in_account  and   rownum=1  ) as  \"subInAccountId\","+                "  (select t3.id  from   sett_subaccount    t3  where  t3.naccountid=t1.out_account  and   rownum=1  ) as  \"subOutAccountId\","+                " t1.margin_amount   as \"marginAmount\" "+                " from sett_depositbatchreview t1  where  t1.id=1   ";                QueryRunner queryRunner=new QueryRunner();                MarginAccountVo bean=queryRunner.query(connection, sql, new BeanHandler<MarginAccountVo>(MarginAccountVo.class));                System.out.println(bean);    }    @After      public  void close(){        try{            if(connection!=null){                connection.close();            }        }catch(Exception e){            e.printStackTrace();        }    }