1 package com.mozq.jdbc.test;
2
3 import java.sql.SQLException;
4 import java.util.List;
5 import java.util.Map;
6 import java.util.Map.Entry;
7
8 import org.apache.commons.dbutils.QueryRunner;
9 import org.apache.commons.dbutils.handlers.BeanHandler;
10 import org.apache.commons.dbutils.handlers.BeanListHandler;
11 import org.apache.commons.dbutils.handlers.BeanMapHandler;
12 import org.apache.commons.dbutils.handlers.ColumnListHandler;
13 import org.apache.commons.dbutils.handlers.MapHandler;
14 import org.apache.commons.dbutils.handlers.MapListHandler;
15 import org.apache.commons.dbutils.handlers.ScalarHandler;
16 import org.junit.Test;
17
18 import com.mozq.domain.User;
19 import com.mozq.jdbc.C3P0Utils;
20
21 public class DBUtilsTest_R {
22 /**
23 * 测试查询用户数量,ScalarHandler处理器，new ScalarHandler<Long>(),注意类型参数只能写Long，不能写Integer
24 */
25 @Test
26 public void findAll_ScalarHandler() {
27 try {
28 //1.创建核心执行对象
29 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
30 //2.写sql
31 String sql = "select count(*) from t_user";
32 //3.准备参数
33 //4.执行sql，进行结果处理
34 Long row = queryRunner.query(sql, new ScalarHandler<Long>());
35 System.out.println("操作行数：" + row);
36 } catch (SQLException e) {
37 e.printStackTrace();
38 }
39 }
40 /**
41 * 测试根查询所有用户,MapListHandler处理器，new MapListHandler()
42 */
43 @Test
44 public void findAll_MapListHandler() {
45 try {
46 //1.创建核心执行对象
47 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
48 //2.写sql
49 String sql = "select * from t_user";
50 //3.准备参数
51 //4.执行sql，进行结果处理
52 List<Map<String, Object>> users = queryRunner.query(sql, new MapListHandler());
53 for(Map<String, Object> user : users) {
54 for(Entry<String, Object> entry : user.entrySet()) {
55 System.out.print(entry.getKey()+":"+entry.getValue()+";");
56 }
57 System.out.println();
58 }
59 } catch (SQLException e) {
60 e.printStackTrace();
61 }
62 }
63 /**
64 * 测试根据id查询单个用户,MapHander处理器，new MapHandler()
65 */
66 @Test
67 public void findById_MapHandler() {
68 try {
69 //1.创建核心执行对象
70 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
71 //2.写sql
72 String sql = "select * from t_user where id=?";
73 //3.准备参数
74 Object[] params = {9};
75 //4.执行sql，进行结果处理
76 Map<String, Object> user = queryRunner.query(sql, new MapHandler(), params);
77 for (Entry<String, Object> entry : user.entrySet()) {
78 System.out.println(entry.getKey()+ ":" + entry.getValue());
79 }
80 /*
81 id:9
82 name:刘备
83 password:liu456
84 */
85 } catch (SQLException e) {
86 e.printStackTrace();
87 }
88 }
89 /**
90 * 测试查询全部用户名,ColumnList处理器，按列索引处理(需要在查询语句中明确列顺序)和按列名处理
91 */
92 @Test
93 public void findAllName_ColumnListHandler() {
94 try {
95 //1.创建核心执行对象
96 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
97 //2.写sql
98 String sql = "select password, name, id from t_user";
99 //3.准备参数
100 //4.执行sql，进行结果处理
101 List<String> userNames = queryRunner.query(sql, new ColumnListHandler<String>());
102 for (String userName : userNames) {
103 System.out.println(userName);
104 }
105 List<String> userNames2 = queryRunner.query(sql, new ColumnListHandler<String>("name"));
106 for (String userName : userNames2) {
107 System.out.println(userName);
108 }
109
110 } catch (SQLException e) {
111 e.printStackTrace();
112 }
113 }
114 /**
115 * 测试查询全部,BeanMap处理器，new BeanMapHandler<Integer, User>(User.class, "id")
116 */
117 @Test
118 public void findAll_BeanMapHandler() {
119 try {
120 //1.创建核心执行对象
121 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
122 //2.写sql
123 String sql = "select id, name, password from t_user";
124 //3.准备参数
125 //4.执行sql，进行结果处理
126 /*用主键列来当键，完全没有问题*/
127 System.out.println("new BeanMapHandler<Integer, User>(User.class, \"id\")");
128 Map<Integer, User> users = queryRunner.query(sql, new BeanMapHandler<Integer, User>(User.class, "id"));
129 for (Entry<Integer, User> it : users.entrySet()) {
130 System.out.println(it.getValue());
131 }
132
133 /*用可重复的列来当键，会发生记录覆盖*/
134 System.out.println("new BeanMapHandler<String, User>(User.class, \"name\")----------");
135 Map<String, User> users2 = queryRunner.query(sql, new BeanMapHandler<String, User>(User.class, "name"));
136 for (Entry<String, User> it : users2.entrySet()) {
137 System.out.println(it.getValue());
138 }
139
140 } catch (SQLException e) {
141 e.printStackTrace();
142 //java.sql.SQLException: Cannot determine value type from string 'liu123' Query: select * from t_user Parameters: []
143 }
144 }
145 /**
146 * 测试查询全部,BeanList处理器，new BeanListHandler<User>(User.class)
147 */
148 @Test
149 public void findAll_BeanListHandler() {
150 try {
151 //1.创建核心执行对象
152 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
153 //2.写sql
154 String sql = "select * from t_user";
155 //3.准备参数
156 //4.执行sql，进行结果处理
157 List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class));
158 for (User user : users) {
159 System.out.println(user);
160 }
161 } catch (SQLException e) {
162 e.printStackTrace();
163 }
164 }
165 /**
166 * 测试根据id查询单个,Bean处理器，new BeanHandler<User>(User.class)
167 */
168 @Test
169 public void findById_BeanHandler() {
170 try {
171 //1.创建核心执行对象
172 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
173 //2.写sql
174 String sql = "select * from t_user where id=?";
175 //3.准备参数
176 Object[] params = {9};
177 //4.执行sql，进行结果处理
178 User user = queryRunner.query(sql, new BeanHandler<User>(User.class), params);
179 System.out.println(user);//User [id=9, name=刘备, password=liu123]
180 } catch (SQLException e) {
181 e.printStackTrace();
182 }
183 }
184 }
1 package com.mozq.jdbc.test;
2
3 import java.sql.SQLException;
4
5 import org.apache.commons.dbutils.QueryRunner;
6 import org.apache.commons.dbutils.handlers.BeanHandler;
7 import org.junit.Test;
8
9 import com.mozq.domain.User;
10 import com.mozq.jdbc.C3P0Utils;
11
12 /**
13 * 测试DBUtils工具类的增删改操作
14 * @author jie
15 *
16 */
17 public class DBUtilsTest_CUD {
18 /**
19 * 根据id删除用户
20 */
21 @Test
22 public void DeleteUserById() {
23 try {
24 // 1.创建核心类
25 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
26 // 2.写sql
27 String sql = "delete from t_user where id=?";
28 // 3.准备参数
29 Object[] params = { 8 };
30 // 4.执行sql，进行结果处理
31 int row = queryRunner.update(sql, params);
32 if (row > 0) {
33 System.out.println("删除成功");
34 } else {
35 System.out.println("删除失败");
36 }
37 } catch (SQLException e) {
38 e.printStackTrace();
39 }
40 }
41
42 /**
43 * 根据id修改用户
44 */
45 @Test
46 public void UpdateUserById() {
47 try {
48 // 1.创建核心类
49 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
50 // 2.写sql
51 String sql = "update t_user set password=? where id=?";
52 // 3.准备参数
53 Object[] params = { "jingdong", 8 };
54 int row = queryRunner.update(sql, params);
55 if (row > 0) {
56 System.out.println("修改成功");
57 } else {
58 System.out.println("修改失败");
59 }
60 } catch (SQLException e) {
61 e.printStackTrace();
62 }
63 }
64
65 /**
66 * 插入用户方法
67 */
68 @Test
69 public void addUser() {
70 try {
71 // 1.创建核心类
72 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
73 // 2.写sql
74 String sql = "insert into t_user(name, password) values(?,?)";
75 // 3.准备参数
76 Object[] params = { "京东0", "刘强东0" };
77 int row = queryRunner.update(sql, params);
78 if (row > 0) {
79 System.out.println("插入成功");
80 } else {
81 System.out.println("插入失败");
82 }
83 } catch (SQLException e) {
84 e.printStackTrace();
85 }
86 }
87
88 /**
89 * 测试insert方法，查看返回对象
90 */
91 @Test
92 public void insert2() {
93 try {
94 QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
95 String sql = "insert into t_user(id, name, password) values(?, ?, ?)";
96 User userObj = queryRunner.insert(sql, new BeanHandler<User>(User.class), 31, "京东5", "刘强东");
97 System.out.println(userObj);
98 } catch (SQLException e) {
99 e.printStackTrace();
100 }
101 }
102 }
浙公网安备 33010602011771号