DBUtil的使用


一、DBUtil介绍

DBUtil是什么及作用

DBUtil是Apache提供的一个开源的JDBC工具类库,是java编程中数据库操作实用工具,小巧简单。

DBUtil封装了对JDBC的操作,简化了JDBC操作,可以让我们少些代码。

1.对数据表的读操作

可以将结果转换成List、Array、Set等集合。

2.对数据表的写操作

只需要写SQL语句

3.可以使用数据源,使用JNDI,数据库连接池等技术来优化程序性能。

二、DBUtil的三个核心对象

QueryRunner类

QueryRunner中提供对sql语句的操作,主要有三个方法:

1.query()用于执行select操作

2.update()用于执行更新操作(insert、update、delete)

3.batch()批处理

ResultSetHandler接口

定义了执行select操作后如何封装结果集

DBUtils类

是一个工具类,定义了关闭资源和事务处理的方法

1.QueryRunner类

构造函数:

QueryRunner有四个构造函数,这里主要讲两个

1)QueryRunner qr = new QueryRunner();

这个构造函数没有参数,可以用于需要手动控制事务,在使用该对象调用方法时(query,update,batch),需要传入Connection对象

2) QueryRunner qr = new QueryRunner(DataSource ds);

这个构造函数可以传入一个数据源参数,它的事务是自动控制的(一个sql一个事务),在使用该对象调用方法时,不用再传入Connection对象。

方法:query(),update(),batch()

下面写几个例子:

1)建数据表(这里我就用自己原有的数据表了)

2)创建项目导入jar包

3)代码示例

1 public class TestQueryRunner {
 2     
 3 //    insert操作
 4     @Test
 5     public void testInsert() throws SQLException {
 6 //        创建QueryRunner对象
 7         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
 8         qr.update("insert into user(username,password,email,birthday) values(?,?,?,?)",
 9                 "xiaoming","123456","xiaoming@163.com",new Date());
10     }
11     
12 //    update操作
13     @Test
14     public void testUpdate() throws SQLException {
15         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
16         qr.update("update user set password = ? where username = ?","123123","xiaoming");
17         
18     }
19     
20 //    delete操作
21     @Test
22     public void testDelete() throws SQLException {
23         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
24         qr.update("delete from user where id = ?",9);
25     }
26     
27 //    select操作
28     @Test
29     public void testSelect() throws SQLException {
30         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
31         User user = qr.query("select * from user where username = ?", 
32                 new BeanHandler<User>(User.class),"xiaoming");
33         System.out.println(user);
34     }
35     
36 //    batch操作(执行相同的sql语句)
37     @Test
38     public void testBatch() throws SQLException {
39         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
40 //        传入一个二维数组,高维是需要执行的sql语句的次数,低维为需要给sql语句中?赋的值
41         Object[][] params = new Object[10][];
42         for (int i = 0; i < params.length; i++) {
43             params[i] = new Object[] {"xiaoming","123456","xiaoming@163.com",new Date()};
44         }
45         qr.batch("insert into user(username,password,email,birthday) values(?,?,?,?)",params );
46     }
47 }

2.ResultSetHandler接口下的9个结果处理

1.ArrayHandler:取一条记录,将该记录中的每列值封装到object[]数组中

1 public class TestResultSetHandler {
 2 //    ArrayHandler:取1条记录,把该条记录的每列值封装到一个Object[]数组中(如果没有指定取哪一条,默认取表中第一条)
 3     @Test
 4     public void testArrayHandler() throws SQLException {
 5         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
 6         Object[] objects = qr.query("select * from user where username = ?", 
 7                 new ArrayHandler(),"xiaoming");
 8         for (Object object : objects) {
 9             System.out.println(object);
10         }
11     }
12 }

输出结果:

10
xiaoming
123123
xiaoming@163.com
2018-11-22 15:14:25

2.ArrayListHandler:取多条记录,把每条记录的每列值封装到一个Object[]数组中,再将object[]放入List中

1 //    ArrayListHandler:取多条记录,把每条记录的每列值封装到一个Object[]数组中,再将object[]放入List中
 2     @Test
 3     public void testArrayListHandler() throws SQLException {
 4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
 5         List<Object[]> list = qr.query("select * from user", new ArrayListHandler());
 6         for (Object[] objects : list) {
 7             for (Object object : objects) {
 8                 System.out.println(object);
 9             }
10             System.out.println("------------------------");
11         }
12     }

输出结果:

2
jeery
123456
xm@163.com
2018-10-07 13:56:37
------------------------
3
tom
123456
xm@163.com
2018-10-07
------------------------
4
??OHUA
123
xm@163.com
2018-12-01

3.columnListHandler:取某一列的值,封装到List中

1 //    columnListHandler:取某一列的值,封装到List中
2     @Test
3     public void testColumnListHandler() throws SQLException {
4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
5         List<Object> list = qr.query("select * from user", new ColumnListHandler(2));//2,指sql语句中查询出来的列中的第二列
6         for (Object object : list) {
7             System.out.println(object);
8         }
9     }

输出结果:

jeery
tom
??OHUA

4.keyedHandler:取多条记录,每一条记录封装到一个map中,再将这个map封装到另一个map中,key为指定的字段值。

1 //    4.keyedHandler:取多条记录,每一条记录封装到一个map中,再将这个map封装到另一个map中,key为指定的字段值。
 2     @Test
 3     public void testKeyedHandler() throws SQLException {
 4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
 5         Map<Object, Map<String, Object>> map = qr.query("select * from user", new KeyedHandler(1));//以第一列id的值为大map的key值
 6         for (Map.Entry<Object, Map<String, Object>> bm : map.entrySet()) {
 7             for (Map.Entry<String, Object> mm : bm.getValue().entrySet()) {
 8                 System.out.println(mm.getKey()+"        "+mm.getValue());
 9             }
10             System.out.println("---------------------------");
11         }
12     }

输出结果:

birthday 2018-10-07 13:56:37
password 123456
id 2
email xm@163.com
username jeery
---------------------------
birthday 2018-10-07
password 123456
id 3
email xm@163.com
username tom
---------------------------
birthday 2018-12-01
password 123
id 4
email xm@163.com
username ??OHUA
---------------------------

5.mapHandler:取一条记录,将记录的列名和列值放到一个map中

1 //    mapHandler:取一条记录,将记录的列名和列值放到一个map中
 2     @Test
 3     public void testMapHandler() throws SQLException {
 4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
 5         Map<String, Object> map = qr.query("select * from user", new MapHandler());
 6         for (Map.Entry<String, Object> m : map.entrySet()) {
 7             System.out.println(m.getKey()+"\t"+m.getValue());
 8         }
 9         
10     }

输出结果:

birthday 2018-10-07 13:56:37
password 123456
id 2
email xm@163.com
username jeery

6.MapListHandler:取多条记录,把每条记录封装到map中,再将map封装到List中

1 //    MapListHandler:取多条记录,把每条记录封装到map中,再将map封装到List中
 2     @Test
 3     public void testMapListHandler() throws SQLException {
 4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
 5         List<Map<String,Object>> list = qr.query("select * from user", new MapListHandler());
 6         for (Map<String, Object> map : list) {
 7             for (Map.Entry<String, Object> m : map.entrySet()) {
 8                 System.out.println(m.getKey()+"\t"+m.getValue());
 9             }
10             System.out.println("------------------------------");
11         }
12     }

输出结果:

birthday 2018-10-07 13:56:37
password 123456
id 2
email xm@163.com
username jeery
------------------------------
birthday 2018-10-07
password 123456
id 3
email xm@163.com
username tom
------------------------------
birthday 2018-12-01
password 123
id 4
email xm@163.com
username ??OHUA
------------------------------

7.ScalarHandler:取单行单列数据(一个单元格)

1 //    ScalarHandler:取单行单列数据(一个单元格)
2     @Test
3     public void testScalarHandler() throws SQLException {
4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
5         Object count = qr.query("select count(*) from user", new ScalarHandler(1));
6         System.out.println(count);
7     }

输出结果:8

8.BeanHandler:取一条数据

1 //    BeanHandler:取一条数据
2     @Test
3     public void testBeanHandler() throws SQLException {
4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
5         User user = qr.query("select * from user", new BeanHandler<User>(User.class));
6         System.out.println(user);
7     }

输出结果:

User [id=2, username=jeery, password=123456, email=xm@163.com, birthday=2018-10-07 13:56:37]

9.BeanListHandler:取多条数据

1 //    BeanHandler:取一条数据
2     @Test
3     public void testBeanListHandler() throws SQLException {
4         QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
5         List<User> list = qr.query("select * from user", new BeanListHandler<User>(User.class));
6         for (User user : list) {
7             System.out.println(user);
8         }
9     }

输出结果:

User [id=2, username=jeery, password=123456, email=xm@163.com, birthday=2018-10-07 13:56:37]
User [id=3, username=tom, password=123456, email=xm@163.com, birthday=2018-10-07]
User [id=4, username=??OHUA, password=123, email=xm@163.com, birthday=2018-12-01]
User [id=5, username=??, password=123456, email=xm@163.com, birthday=2018-10-07 16:18:50]
User [id=6, username=??, password=123456, email=xm@163.com, birthday=2018-10-07 16:19:29]
User [id=7, username=125, password=12345, email=xm@163.com, birthday=2018-12-01]
User [id=8, username=142, password=321, email=xm@163.com, birthday=2018-12-01]
User [id=10, username=xiaoming, password=123123, email=xiaoming@163.com, birthday=2018-11-22 15:14:25]


原文链接:https://www.cnblogs.com/98dkk/p/10150204.html