Apache——DBUtils

悠悠 2022-05-16 12:44 164阅读 0赞

需要导入的Jar包：commons-dbutils-1.2.jar

### 简单的CRUD+总记录数： ###

package com.kexin.demo;
    
    import java.sql.SQLException;
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    import org.junit.Test;
    
    import com.kexin.domain.Account;
    import com.kexin.utils.JdbcUtils;
    
    public class Demo1 {
        
        @Test
        public void insert() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "insert into account(id,name,money) values(?,?,?)";
            Object params[] = {
       1,"vvv",1000};
            qrunner.update(sql, params);
        }
        @Test
        public void update() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "update account set money =? where id =?";
            Object params[] ={
       900,1};
            qrunner.update(sql,params);
        }
        @Test
        public void delete() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "delete from account where id = ?";
            Object params[] = {
       1};
            qrunner.update(sql, params);
        }
        @SuppressWarnings("deprecation")
        @Test
        public void find() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql =  "select * from account where id = ?";
            Object params[] = {
       2};
            Account acc = (Account) qrunner.query(sql, params, new BeanHandler(Account.class));
            System.out.println(acc.getName()+"\t"+acc.getMoney());
        }
        @Test
        public void getAll() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "select * from account";
            @SuppressWarnings("unchecked")
            List<Account> list = (List<Account>) qrunner.query(sql, new BeanListHandler(Account.class));
            for(int i =0;i<list.size();i++){
                System.out.println(((Account)list.get(i)).getName()+"\t"+((Account)list.get(i)).getMoney());
            }
        }
        @Test
        public void batch() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "insert into account(name,money) values(?,?)";
            Object params[][] = {
       {
       "aaa",123},{
       "bbb",321},{
       "ccc",444}};
            qrunner.batch(sql, params);
        }
        @Test
        public void getRecords() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "select count(*) from account";
            Object rs[] = (Object[]) qrunner.query(sql, new ArrayHandler());
            int totalRecords = ((Long)rs[0]).intValue();
            System.out.println(totalRecords);
        }
        @Test
        public void getRecords1() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "select count(*) from account";
            int totalRecords = ((Long)qrunner.query(sql, new ScalarHandler())).intValue();
            System.out.println(totalRecords);
        }
    }

### ResultSetHandle接口的实现类： ###

*  ArrayHandle：把结果集中的第一行数据转成对象数组
 *  ArrayListHandle：把结果集中的每一行数据都转成一个数组，再存放到List中
 *  BeanHandle：将结果集中的每一行数据都封装到一个对应的JavaBean实例中
 *  BeanListHandle：将结果集中的每一行数据都封装到一个对应的JavaBean对象中，存放到List中
 *  ColumnListHandle：将结果集中的某一列数据存放到List中
 *  KeyedHandle(name)：将结果集中的每一行数据都封装到一个Map中，再把map存到一个map中，其key为指定的key
 *  MapHandle：将结果集中的第一行数据封装到一个Map里，key是列名，value是对应的值
 *  MapListHandle：将结果集中的每一行数据都放到map里，再存放到List中

package com.kexin.demo;
    
    import java.sql.SQLException;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.ArrayHandler;
    import org.apache.commons.dbutils.handlers.ArrayListHandler;
    import org.apache.commons.dbutils.handlers.ColumnListHandler;
    import org.apache.commons.dbutils.handlers.KeyedHandler;
    import org.apache.commons.dbutils.handlers.MapHandler;
    import org.apache.commons.dbutils.handlers.MapListHandler;
    import org.junit.Test;
    
    import com.kexin.utils.JdbcUtils;
    
    public class Demo2 {
        @Test
        public void TestArrayHandler() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql =  "select * from account where id = ?";
            Object params[] = {
       2};
            Object rs[] = (Object[]) qrunner.query(sql, params, new ArrayHandler());
            for(int i=0;i<rs.length;i++){
        
                System.out.print(rs[i]+"\t");
            }
        }
        @Test
        public void TestArrayListHandle() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql =  "select * from account";
            List list =  (List) qrunner.query(sql,new ArrayListHandler());
            for(int i=0;i<list.size();i++){
        
                System.out.print(((Object[])list.get(i))[0]+"\t");
            }
        }
        @Test
        public void TestColumnListHandle() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql =  "select * from account";
            List list =  (List) qrunner.query(sql,new ColumnListHandler("name"));
            for(int i=0;i<list.size();i++){
        
                System.out.print((list.get(i))+"\t");
            }
        }
        @Test
        public void TestKeyedHandle() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql =  "select * from account";
            Map map =  (Map) qrunner.query(sql,new KeyedHandler("id"));
            for(int i=0;i<map.size();i++){
        
                System.out.print(((Map)map.get(i+1)).get("name")+"\t");
            }
        }
        @Test
        public void TestMapHandle() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql =  "select * from account";
            Map map =  (Map) qrunner.query(sql,new MapHandler());
            System.out.print((map.get("name"))+"\t");
        }
        @Test
        public void TestMapListHandle() throws SQLException{
            QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
            String sql =  "select * from account";
            List list =  (List) qrunner.query(sql,new MapListHandler());
            for(int i=0;i<list.size();i++){
        
                System.out.print(((Map)list.get(i)).get("name")+"\t");
            }
        }
    }

Apache——DBUtils

发表评论取消回复

还没有评论，来说两句吧...

相关阅读