JavaWeb6.3【Spring JDBC：JDBCTemplate 】

 1 package com.haifei.jdbctemplate;
 2 
 3 import com.haifei.utils.JDBCUtils;
 4 import org.springframework.jdbc.core.JdbcTemplate;
 5 
 6 /*
 7 Spring JDBC
 8 
 9 * Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
10 * 步骤：
11     1. 导入jar包5个
12         commons-logging-1.2.jar
13         spring-beans-5.0.0.RELEASE.jar
14         spring-core-5.0.0.RELEASE.jar
15         spring-jdbc-5.0.0.RELEASE.jar
16         spring-tx-5.0.0.RELEASE.jar
17 
18     2. 创建JdbcTemplate对象。依赖于数据源DataSource
19         * JdbcTemplate template = new JdbcTemplate(ds);
20 
21     3. 调用JdbcTemplate的方法来完成CRUD的操作
22         * update():执行DML语句。增、删、改语句
23         * queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
24             * 注意：这个方法查询的结果集长度只能是1
25         * queryForList():查询结果将结果集封装为list集合
26             * 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
27         * query():查询结果，将结果封装为JavaBean对象
28             * query的参数：RowMapper接口
29                 1、手动实现：自己实现RowMapper接口
30                 2、简便实现：直接使用BeanPropertyRowMapper实现类（推荐）
31                     * 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
32                     * new BeanPropertyRowMapper<类型>(类型.class)，类型即实体类类型
33                     * 注意需要将实体类的基本数据类型都转换为对应的包装类类型（引用数据类型）
34         * queryForObject：查询结果，将结果封装为对象
35             * 一般用于聚合函数的查询
36             * 如
37                     String sql = "select count(id) from emp;";
38                     Long total = template.queryForObject(sql, Long.class);
39  */
40 public class JdbcTemplateDemo1 {
41     public static void main(String[] args) {
42         //2 创建JdbcTemplate对象
43         JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
44 
45         //3 调用JdbcTemplate的方法
46         String sql = "update account set balance = 5000 where id = ?";
47         int count = template.update(sql, 3);
48         System.out.println(count); //1
49     }
50 }

  1 package com.haifei.jdbctemplate;
  2 
  3 import com.haifei.domain.Emp;
  4 import com.haifei.utils.JDBCUtils;
  5 import org.junit.Test;
  6 import org.springframework.jdbc.core.BeanPropertyRowMapper;
  7 import org.springframework.jdbc.core.JdbcTemplate;
  8 import org.springframework.jdbc.core.RowMapper;
  9 
 10 import java.sql.ResultSet;
 11 import java.sql.SQLException;
 12 import java.util.Date;
 13 import java.util.List;
 14 import java.util.Map;
 15 
 16 /*
 17 spring JDBCTemplate联系
 18     emp表需求：
 19         1. 修改1号数据的 salary 为 10000
 20         2. 添加一条记录
 21         3. 删除刚才添加的记录
 22         4. 查询id为1的记录，将其封装为Map集合
 23         5. 查询所有记录，将其封装为List
 24         6. 查询所有记录，将其封装为Emp对象的List集合
 25                手动实现：自己实现RowMapper接口
 26         6.1 查询所有记录，将其封装为Emp对象的List集合
 27                 简便实现：直接使用BeanPropertyRowMapper实现类（推荐）
 28         7. 查询总记录数
 29 
 30     标准测试类写法：利用Junit单元测试
 31                    可以让每个测试方法单独运行
 32                    @Test
 33                    public void testXxx(){}
 34  */
 35 public class JdbcTemplateDemo2 {
 36 
 37 
 38     private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
 39 
 40 
 41     /**
 42      * 修改1001号数据的 salary 为 10000
 43      */
 44     @Test
 45     public void test1(){
 46         String sql = "update emp set salary = 10000 where id = 1001";
 47         int count = template.update(sql);
 48         System.out.println(count); //1
 49     }
 50 
 51 
 52     /**
 53      * 添加一条记录
 54      */
 55     @Test
 56     public void test2(){
 57         String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
 58         int count = template.update(sql, 1015,"郭靖", 10);
 59         System.out.println(count); //1
 60     }
 61 
 62 
 63     /**
 64      *  删除刚才添加的记录
 65      */
 66     @Test
 67     public void test3(){
 68         String sql = "delete from emp where id = ?";
 69         int count = template.update(sql, 1015);
 70         System.out.println(count); //1
 71     }
 72 
 73 
 74     /**
 75      * 查询id为1001的记录，将其封装为Map集合
 76      */
 77     @Test
 78     public void test4(){
 79         /*String sql = "select * from emp where id = ?";
 80         Map<String, Object> map = template.queryForMap(sql, 1001);
 81         System.out.println(map);*/
 82         //{id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
 83 
 84         String sql = "select * from emp where id = ? or id = ?";
 85         Map<String, Object> map = template.queryForMap(sql, 1001, 1002);
 86         System.out.println(map);
 87         //IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
 88         //queryForMap这个方法查询的结果集长度只能是1
 89     }
 90 
 91 
 92     /**
 93      *  查询所有记录，将其封装为List
 94      */
 95     @Test
 96     public void test5(){
 97         String sql = "select * from emp";
 98         List<Map<String, Object>> mapList = template.queryForList(sql);
 99 
100         for (Map<String, Object> stringObjectMap: mapList){
101             System.out.println(stringObjectMap);
102         }
103         /*
104         {id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
105         {id=1002, ename=卢俊义, job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.00, bonus=3000.00, dept_id=30}
106         {id=1003, ename=林冲, job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.00, bonus=5000.00, dept_id=30}
107         {id=1004, ename=唐僧, job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.00, bonus=null, dept_id=20}
108         {id=1005, ename=李逵, job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.00, bonus=14000.00, dept_id=30}
109         {id=1006, ename=宋江, job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.00, bonus=null, dept_id=30}
110         {id=1007, ename=刘备, job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.00, bonus=null, dept_id=10}
111         {id=1008, ename=猪八戒, job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.00, bonus=null, dept_id=20}
112         {id=1009, ename=罗贯中, job_id=1, mgr=null, joindate=2001-11-17, salary=50000.00, bonus=null, dept_id=10}
113         {id=1010, ename=吴用, job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.00, bonus=0.00, dept_id=30}
114         {id=1011, ename=沙僧, job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.00, bonus=null, dept_id=20}
115         {id=1012, ename=李逵, job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.00, bonus=null, dept_id=30}
116         {id=1013, ename=小白龙, job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.00, bonus=null, dept_id=20}
117         {id=1014, ename=关羽, job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.00, bonus=null, dept_id=10}
118          */
119     }
120 
121 
122     /**
123      * 查询所有记录，将其封装为Emp对象的List集合
124      * 手动实现：自己实现RowMapper接口
125      */
126     @Test
127     public void test6(){
128         String sql = "select * from emp";
129         List<Emp> empList = template.query(sql, new RowMapper<Emp>() { //匿名内部类
130             @Override
131             public Emp mapRow(ResultSet rs, int i) throws SQLException {
132                 Emp emp = new Emp();
133                 int id = rs.getInt("id");
134                 String ename = rs.getString("ename");
135                 int job_id = rs.getInt("job_id");
136                 int mgr = rs.getInt("mgr");
137                 Date joindate = rs.getDate("joindate");
138                 double salary = rs.getDouble("salary");
139                 double bonus = rs.getDouble("bonus");
140                 int dept_id = rs.getInt("dept_id");
141 
142                 emp.setId(id);
143                 emp.setEname(ename);
144                 emp.setJob_id(job_id);
145                 emp.setMgr(mgr);
146                 emp.setJoindate(joindate);
147                 emp.setSalary(salary);
148                 emp.setBonus(bonus);
149                 emp.setDept_id(dept_id);
150                 return emp;
151             }
152         });
153 
154         //tips：键入iter + 回车，快速生成foreach
155         for (Emp emp : empList) {
156             System.out.println(emp);
157         }
158         /*
159         Emp{id=1001, ename='孙悟空', job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.0, bonus=0.0, dept_id=20}
160         Emp{id=1002, ename='卢俊义', job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.0, bonus=3000.0, dept_id=30}
161         Emp{id=1003, ename='林冲', job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.0, bonus=5000.0, dept_id=30}
162         Emp{id=1004, ename='唐僧', job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.0, bonus=0.0, dept_id=20}
163         Emp{id=1005, ename='李逵', job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.0, bonus=14000.0, dept_id=30}
164         Emp{id=1006, ename='宋江', job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.0, bonus=0.0, dept_id=30}
165         Emp{id=1007, ename='刘备', job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.0, bonus=0.0, dept_id=10}
166         Emp{id=1008, ename='猪八戒', job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.0, bonus=0.0, dept_id=20}
167         Emp{id=1009, ename='罗贯中', job_id=1, mgr=0, joindate=2001-11-17, salary=50000.0, bonus=0.0, dept_id=10}
168         Emp{id=1010, ename='吴用', job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.0, bonus=0.0, dept_id=30}
169         Emp{id=1011, ename='沙僧', job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.0, bonus=0.0, dept_id=20}
170         Emp{id=1012, ename='李逵', job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.0, bonus=0.0, dept_id=30}
171         Emp{id=1013, ename='小白龙', job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.0, bonus=0.0, dept_id=20}
172         Emp{id=1014, ename='关羽', job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.0, bonus=0.0, dept_id=10}
173          */
174     }
175 
176 
177     /**
178      * 查询所有记录，将其封装为Emp对象的List集合
179      * 简便实现：直接使用BeanPropertyRowMapper实现类（推荐）
180      */
181     @Test
182     public void test61(){
183         String sql = "select * from emp";
184         List<Emp> empList = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
185         for (Emp emp : empList) {
186             System.out.println(emp);
187         }
188         /*
189         Emp实体类
190             基本数据类型时报错，原因是基本数据类型int、double等不能接受null，默认值为0，但数据库中有的数值为null
191             基本数据类型改为包装类后 ok
192 
193         Emp{id=1001, ename='孙悟空', job_id=4, mgr=1004, joindate=2000-12-17 00:00:00.0, salary=10000.0, bonus=null, dept_id=20}
194         Emp{id=1002, ename='卢俊义', job_id=3, mgr=1006, joindate=2001-02-20 00:00:00.0, salary=16000.0, bonus=3000.0, dept_id=30}
195         Emp{id=1003, ename='林冲', job_id=3, mgr=1006, joindate=2001-02-22 00:00:00.0, salary=12500.0, bonus=5000.0, dept_id=30}
196         Emp{id=1004, ename='唐僧', job_id=2, mgr=1009, joindate=2001-04-02 00:00:00.0, salary=29750.0, bonus=null, dept_id=20}
197         Emp{id=1005, ename='李逵', job_id=4, mgr=1006, joindate=2001-09-28 00:00:00.0, salary=12500.0, bonus=14000.0, dept_id=30}
198         Emp{id=1006, ename='宋江', job_id=2, mgr=1009, joindate=2001-05-01 00:00:00.0, salary=28500.0, bonus=null, dept_id=30}
199         Emp{id=1007, ename='刘备', job_id=2, mgr=1009, joindate=2001-09-01 00:00:00.0, salary=24500.0, bonus=null, dept_id=10}
200         Emp{id=1008, ename='猪八戒', job_id=4, mgr=1004, joindate=2007-04-19 00:00:00.0, salary=30000.0, bonus=null, dept_id=20}
201         Emp{id=1009, ename='罗贯中', job_id=1, mgr=null, joindate=2001-11-17 00:00:00.0, salary=50000.0, bonus=null, dept_id=10}
202         Emp{id=1010, ename='吴用', job_id=3, mgr=1006, joindate=2001-09-08 00:00:00.0, salary=15000.0, bonus=0.0, dept_id=30}
203         Emp{id=1011, ename='沙僧', job_id=4, mgr=1004, joindate=2007-05-23 00:00:00.0, salary=11000.0, bonus=null, dept_id=20}
204         Emp{id=1012, ename='李逵', job_id=4, mgr=1006, joindate=2001-12-03 00:00:00.0, salary=9500.0, bonus=null, dept_id=30}
205         Emp{id=1013, ename='小白龙', job_id=4, mgr=1004, joindate=2001-12-03 00:00:00.0, salary=30000.0, bonus=null, dept_id=20}
206         Emp{id=1014, ename='关羽', job_id=4, mgr=1007, joindate=2002-01-23 00:00:00.0, salary=13000.0, bonus=null, dept_id=10}
207          */
208     }
209 
210 
211     /**
212      * 查询总记录数
213      */
214     @Test
215     public void test7(){
216         String sql = "select count(id) from emp;";
217         Long total = template.queryForObject(sql, Long.class);
218         System.out.println(total); //14
219     }
220 
221 }

  1 package com.haifei.domain;
  2 
  3 import java.util.Date;
  4 
  5 /**
  6  * 封装Emp表数据的JavaBean
  7  */
  8 public class Emp {
  9     /*private int id; //int
 10     private String ename; //varchar
 11     private int job_id; //职位编号，外键
 12     private int mgr; //管理这个员工的上级编号
 13     private Date joindate; //date
 14     private double salary; //工资 decimal
 15     private double bonus; //奖金
 16     private int dept_id; //部门编号，外键
 17 
 18     public int getId() {
 19         return id;
 20     }
 21 
 22     public void setId(int id) {
 23         this.id = id;
 24     }
 25 
 26     public String getEname() {
 27         return ename;
 28     }
 29 
 30     public void setEname(String ename) {
 31         this.ename = ename;
 32     }
 33 
 34     public int getJob_id() {
 35         return job_id;
 36     }
 37 
 38     public void setJob_id(int job_id) {
 39         this.job_id = job_id;
 40     }
 41 
 42     public int getMgr() {
 43         return mgr;
 44     }
 45 
 46     public void setMgr(int mgr) {
 47         this.mgr = mgr;
 48     }
 49 
 50     public Date getJoindate() {
 51         return joindate;
 52     }
 53 
 54     public void setJoindate(Date joindate) {
 55         this.joindate = joindate;
 56     }
 57 
 58     public double getSalary() {
 59         return salary;
 60     }
 61 
 62     public void setSalary(double salary) {
 63         this.salary = salary;
 64     }
 65 
 66 
 67     public int getDept_id() {
 68         return dept_id;
 69     }
 70 
 71     public void setDept_id(int dept_id) {
 72         this.dept_id = dept_id;
 73     }
 74 
 75 
 76     public double getBonus() {
 77         return bonus;
 78     }
 79 
 80     public void setBonus(double bonus) {
 81         this.bonus = bonus;
 82     }
 83 
 84     @Override
 85     public String toString() {
 86         return "Emp{" +
 87                 "id=" + id +
 88                 ", ename='" + ename + '\'' +
 89                 ", job_id=" + job_id +
 90                 ", mgr=" + mgr +
 91                 ", joindate=" + joindate +
 92                 ", salary=" + salary +
 93                 ", bonus=" + bonus +
 94                 ", dept_id=" + dept_id +
 95                 '}';
 96     }*/
 97 
 98     //基本数据类型 --> 引用数据类型，包装类
 99     private Integer id; //int
100     private String ename; //varchar
101     private Integer job_id; //职位编号，外键
102     private Integer mgr; //管理这个员工的上级编号
103     private Date joindate; //date
104     private Double salary; //工资 decimal
105     private Double bonus; //奖金
106     private Integer dept_id; //部门编号，外键
107 
108     public void setId(Integer id) {
109         this.id = id;
110     }
111 
112     public void setEname(String ename) {
113         this.ename = ename;
114     }
115 
116     public void setJob_id(Integer job_id) {
117         this.job_id = job_id;
118     }
119 
120     public void setMgr(Integer mgr) {
121         this.mgr = mgr;
122     }
123 
124     public void setJoindate(Date joindate) {
125         this.joindate = joindate;
126     }
127 
128     public void setSalary(Double salary) {
129         this.salary = salary;
130     }
131 
132     public void setBonus(Double bonus) {
133         this.bonus = bonus;
134     }
135 
136     public void setDept_id(Integer dept_id) {
137         this.dept_id = dept_id;
138     }
139 
140     @Override
141     public String toString() {
142         return "Emp{" +
143                 "id=" + id +
144                 ", ename='" + ename + '\'' +
145                 ", job_id=" + job_id +
146                 ", mgr=" + mgr +
147                 ", joindate=" + joindate +
148                 ", salary=" + salary +
149                 ", bonus=" + bonus +
150                 ", dept_id=" + dept_id +
151                 '}';
152     }
153 }

PS：JDBUTemplate 需要用到 Druid连接池工具类JDBCUtils

posted @ 2021-06-26 09:41 yub4by 阅读(72) 评论(0) 收藏举报

刷新页面返回顶部

happy

To make a good person better.

JavaWeb6.3【Spring JDBC：JDBCTemplate 】

PS：JDBUTemplate 需要用到 Druid连接池工具类JDBCUtils

公告