亲宝软件园·资讯

展开

Apache—DBUtils框架

huwenfei 人气:1

Apache—DBUtils框架

【原文链接】https://www.cnblogs.com/niuchuangfeng/p/9182542.html

 

一、什么是dbutils

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。

 

二、dbutils核心API

 

1、QueryRunner类

 

使用QueryRunner实现增删改查

 1 package com.dbutils;
 2 
 3 import java.sql.SQLException;
 4 import java.util.Date;
 5 import java.util.List;
 6 
 7 import org.apache.commons.dbutils.QueryRunner;
 8 import org.apache.commons.dbutils.handlers.BeanHandler;
 9 import org.apache.commons.dbutils.handlers.BeanListHandler;
10 import org.junit.Test;
11 
12 import com.domain.User;
13 import com.utils.JdbcUtils;
14 
15 /**
16  * 
17  * 使用dbutils完成数据库的增删改查
18  * 
19  */
20 public class Demo1 {
21     // 插入
22     @Test
23     public void insert() throws SQLException {
24         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
25         String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
26         Object params[] = { 1, "aa", "123", "aaa@sina.com", new Date() };
27         runner.update(sql, params);
28     }
29 
30     // 删除
31     @Test
32     public void delete() throws SQLException {
33         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
34         String sql = "delete from users where id=?";
35         runner.update(sql, 2);
36     }
37 
38     // 修改
39     @Test
40     public void update() throws SQLException {
41         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
42         String sql = "update users set email=? where id=?";
43         Object params[] = { "ffd@sina.com", 2 };
44         runner.update(sql, params);
45     }
46 
47     // 查找单条记录
48     @Test
49     public void find() throws SQLException {
50         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
51         String sql = "select * from users where id=?";
52         User user = (User) runner.query(sql, 1, new BeanHandler(User.class));
53         System.out.println(user.getEmail());
54     }
55 
56     // 查找所有记录
57     @Test
58     public void getAll() throws SQLException {
59         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
60         String sql = "select * from users";
61         List<User> list = (List) runner.query(sql, new BeanListHandler(
62                 User.class));
63         for (User user : list) {
64             System.out.println(user.getEmail());
65         }
66     }
67     
68     // 批处理
69         @Test
70         public void batch() throws SQLException {
71             QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
72             String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
73             
74             //三条sql,五个参数
75             Object params[][] = new Object[3][5];
76             
77             for(int i=0;i<params.length;i++){
78                 params[i] = new Object[]{i+1,"aaa"+i,"123","sina.com",new Date()};
79             }
80             
81             runner.batch(sql, params);
82         }
83             
84 }

 

 

 1 public class JdbcUtils {
 2     private static DataSource ds;
 3     static{
 4         try {
 5             Properties prop = new Properties();
 6             InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
 7             prop.load(in);
 8             
 9             //创建数据源
10             BasicDataSourceFactory factory = new BasicDataSourceFactory();
11             ds = factory.createDataSource(prop);
12         } catch (Exception e) {
13             throw new ExceptionInInitializerError(e);
14         }
15     }
16     
17     //提供数据源
18     public static DataSource getDataSource(){
19         return ds;
20     }
21     
22     //获取连接
23     public static Connection  getConnection() throws SQLException{
24         return ds.getConnection();
25     }
26 }

 

 

 

 

 

2、ResultSetHandler接口的实现类

ArrayHandler:把结果集中的第一行数据转成对象数组。

ArrayListHandler:把结果集中的每一行数据都转成一个数组,再存放到List中。

BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。

BeanListHandler:将结果集中的每一行数据都封装到一个对应的JavaBean实例中,存放到List里。

ColumnListHandler:将结果集中某一列的数据存放到List中。

KeyedHandler(name):将结果集中的每一行数据都封装到一个Map里,再把这些map再存到一个map里,其key为指定的key。

MapHandler:将结果集中的第一行数据封装到一个Map里,key是列名,value就是对应的值。

MapListHandler:将结果集中的每一行数据都封装到一个Map里,然后再存放到List

  1 package com.dbutils;
  2 import java.sql.SQLException;
  3 import java.util.Arrays;
  4 import java.util.List;
  5 import java.util.Map;
  6 
  7 import javax.swing.JButton;
  8 import org.apache.commons.dbutils.QueryRunner;
  9 import org.apache.commons.dbutils.handlers.ArrayHandler;
 10 import org.apache.commons.dbutils.handlers.ArrayListHandler;
 11 import org.apache.commons.dbutils.handlers.ColumnListHandler;
 12 import org.apache.commons.dbutils.handlers.KeyedHandler;
 13 import org.apache.commons.dbutils.handlers.ScalarHandler;
 14 import org.junit.Test;
 15 
 16 import com.utils.JdbcUtils;
 17 
 18 /**
 19  * 
 20  * dbutils结果集
 21  *
 22  */
 23 public class Demo2 {
 24     @Test
 25     //把结果的第一行数据封装到数组中
 26     public void test1() throws SQLException{
 27         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
 28         String sql = "select * from users";
 29         //强转的时候要加[]
 30         Object result[] = (Object[]) runner.query(sql, new ArrayHandler());
 31         System.out.println(result[0]);
 32         System.out.println(result[1]);
 33     }
 34     
 35     @Test
 36     //把结果的每行数据封装到数组中,在把数据存放在list集合中
 37     public void test2() throws SQLException{
 38         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
 39         String sql = "select * from users";
 40         List<Object[]> list = (List) runner.query(sql, new ArrayListHandler());
 41         System.out.println(list);
 42         for(Object[] objects : list){
 43             System.out.println(Arrays.toString(objects));
 44         }
 45     }
 46     
 47     @Test
 48     //把结果集的某一列封装到一个list集合中
 49     public void test3() throws SQLException{
 50         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
 51         String sql = "select * from users";
 52         
 53         List list1 = (List) runner.query(sql, new ColumnListHandler());
 54         for(Object object : list1){
 55             System.out.println(object);
 56         }
 57         
 58         List list2 = (List) runner.query(sql,new ColumnListHandler("name"));
 59         for(Object object : list2){
 60             System.out.println(object);
 61         }
 62         
 63         List list3 = (List) runner.query(sql,new ColumnListHandler(4));
 64         for(Object object : list3){
 65             System.out.println(object);
 66         }
 67     }
 68     
 69     //KeyedHandler(name):将结果集中的每一行数据都封装到一个Map里,再把这些map再存到一个map里,其key为指定的key
 70     @Test
 71     public void test4() throws SQLException{
 72         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
 73         String sql = "select * from users";
 74         Map<Integer,Map<String,Object>> map = (Map) runner.query(sql, new KeyedHandler("id"));
 75         for(Map.Entry<Integer, Map<String, Object>> me : map.entrySet()){
 76             int key = me.getKey();
 77             
 78             //拿到封装每条记录的map
 79             for(Map.Entry<String, Object> entry:me.getValue().entrySet()){
 80                 System.out.println(entry.getKey()+"="+entry.getValue());
 81             }
 82         }
 83     }
 84     
 85     
 86     @Test
 87     //获取记录总数
 88     public void test5() throws SQLException{
 89         QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
 90         String sql = "select count(*) from users";
 91         //Object result[] = (Object[]) runner.query(sql, new ArrayHandler());
 92         /*long totalrecord = (Long)result[0];
 93         int num = (int)totalrecord;
 94         System.out.println(num);
 95         int totalrecord = ((Long)result[0]).intValue();
 96         */
 97         int totalrecord = ((Long)runner.query(sql, new ScalarHandler(1))).intValue();
 98         System.out.println(totalrecord);
 99     }
100     
101     
102 }

加载全部内容

相关教程
猜你喜欢
用户评论