DBUtils学习一增删该查

  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 }

DBUtils学习一 增删该查

DBUtils学习一增删该查