14_Spring_JDBCTemplate的使用

14_Spring_JDBCTemplate的使用

JdbcTemplate概述

JdbcTemplate是spring框架中提供的一个对象，是对原始繁琐的Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。例如：操作关系型数据的
JdbcTemplate和，操作nosql数据库的RedisTemplate，操作消息队列的JmsTemplate等等。

按如下项目结构准备 maven jar项目即可

1 导入依赖

4. org.springframework
5. spring-context
6. 5.3.5
10. org.springframework
11. spring-aspects
12. 5.3.5
16. aopalliance
17. aopalliance
18. 1.0
22. com.alibaba
23. druid
24. 1.1.10
28. mysql
29. mysql-connector-java
30. 8.0.22
34. org.springframework
35. spring-jdbc
36. 5.3.5
40. org.springframework
41. spring-tx
42. 5.3.5
46. org.springframework
47. spring-orm
48. 5.3.5
52. commons-logging
53. commons-logging
54. 1.2
58. junit
59. junit
60. 4.13.1
61. test
65. org.projectlombok
66. lombok
67. 1.18.12
68. provided

2 准备JDBC.properties

1. jdbc_username=root
2. jdbc_password=root
3. jdbc_driver=com.mysql.cj.jdbc.Driver
4. jdbc_url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true

3 准备applicationContext.xml

2. <beans xmlns="http://www.springframework.org/schema/beans"
3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4. xmlns:p="http://www.springframework.org/schema/p"
5. xmlns:c="http://www.springframework.org/schema/c"
6. xmlns:util="http://www.springframework.org/schema/util"
7. xmlns:context="http://www.springframework.org/schema/context"
8. xmlns:aop="http://www.springframework.org/schema/aop"
9. xsi:schemaLocation="
10. http://www.springframework.org/schema/beans
11. http://www.springframework.org/schema/beans/spring-beans.xsd
12. http://www.springframework.org/schema/util
13. http://www.springframework.org/schema/util/spring-util.xsd
14. http://www.springframework.org/schema/context
15. http://www.springframework.org/schema/context/spring-context.xsd
16. http://www.springframework.org/schema/aop
17. http://www.springframework.org/schema/aop/spring-aop.xsd
18. ">
20. <context:component-scan base-package="com.msb"/>
22. <context:property-placeholder location="classpath:jdbc.properties"/>

3 准备实体类

1. package com.msb.pojo;
2. import lombok.AllArgsConstructor;
3. import lombok.Data;
4. import lombok.NoArgsConstructor;
5. import java.util.Date;
6. /**
7. • @Author: Ma HaiYang
8. • @Description: MircoMessage:Mark_7001
9. */
10. @AllArgsConstructor
11. @NoArgsConstructor
12. @Data
13. public class Emp implements Serializable{
14. private Integer empno;
15. private String ename;
16. private String job;
17. private Integer mgr;
18. private Date hiredate;
19. private Double sal;
20. private Double comm;
21. private Integer deptno;
22. }

4 准备service层接口和实现类

1. package com.msb.service;

2. import com.msb.pojo.Emp;

3. import java.util.List;

4. /**

5. • @Author: Ma HaiYang
6. • @Description: MircoMessage:Mark_7001

7. */

8. public interface EmpService {

9. int findEmpCount();

10. Emp findByEmpno(int empno);

11. List findByDeptno(int deptno);

12. int addEmp(Emp emp);

13. int updateEmp(Emp emp);

14. int deleteEmp( int empno);

15. }

16. package com.msb.service.impl;

17. import com.msb.dao.EmpDao;

18. import com.msb.pojo.Emp;

19. import com.msb.service.EmpService;

20. import org.springframework.beans.factory.annotation.Autowired;

21. import org.springframework.stereotype.Service;

22. import java.util.List;

23. /**

24. • @Author: Ma HaiYang
25. • @Description: MircoMessage:Mark_7001

26. */

27. @Service

28. public class EmpServiceImpl implements EmpService {

29. @Autowired

30. private EmpDao empDao;

31. @Override

32. public int findEmpCount() {

33. return empDao.findEmpCount();

34. }

35. @Override

36. public Emp findByEmpno(int empno) {

37. return empDao.findByEmpno( empno);

38. }

39. @Override

40. public List findByDeptno(int deptno) {

41. return empDao.findByDeptno( deptno);

42. }

43. @Override

44. public int addEmp(Emp emp) {

45. return empDao.addEmp(emp);

46. }

47. @Override

48. public int updateEmp(Emp emp) {

49. return empDao.updateEmp(emp);

50. }

51. @Override

52. public int deleteEmp(int empno) {

53. return empDao.deleteEmp(empno);

54. }

55. }

5 准备dao层接口和实现类

1. package com.msb.dao;

2. import com.msb.pojo.Emp;

3. import java.util.List;

4. /**

5. • @Author: Ma HaiYang
6. • @Description: MircoMessage:Mark_7001

7. */

8. public interface EmpDao {

9. int findEmpCount();

10. Emp findByEmpno(int empno);

11. List findByDeptno(int deptno);

12. int addEmp(Emp emp);

13. int updateEmp(Emp emp);

14. int deleteEmp(int empno);

15. }

16. package com.msb.dao.impl;

17. import com.msb.dao.EmpDao;

18. import com.msb.pojo.Emp;

19. import org.springframework.beans.factory.annotation.Autowired;

20. import org.springframework.jdbc.core.BeanPropertyRowMapper;

21. import org.springframework.jdbc.core.JdbcTemplate;

22. import org.springframework.jdbc.core.RowMapper;

23. import org.springframework.stereotype.Repository;

24. import java.sql.ResultSet;

25. import java.sql.SQLException;

26. import java.util.List;

27. /**

28. • @Author: Ma HaiYang
29. • @Description: MircoMessage:Mark_7001

30. */

31. @Repository

32. public class EmpDaoImpl implements EmpDao {

33. @Autowired

34. private JdbcTemplate jdbcTemplate;

35. @Override

36. public int findEmpCount() {

37. /*查询员工个数

38. * queryForObject 两个参数

39. * 1 SQL语句

40. * 2 返回值类型

41. *

42. * */

43. Integer empCount = jdbcTemplate.queryForObject("select count(1)
    from emp", Integer.class);

44. return empCount;

45. }

46. @Override

47. public Emp findByEmpno(int empno) {

48. /*

49. * 查询单个员工对象

50. * queryForObject三个参数

51. * 1 SQL语句

52. * 2 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper

53. * 3 SQL语句中需要的参数 (可变参数)

54. * */

55. BeanPropertyRowMapper rowMapper =new
    BeanPropertyRowMapper<>(Emp.class);

56. Emp emp = jdbcTemplate.queryForObject("select * from emp where
    empno =?", rowMapper, empno);

57. return emp;

58. }

59. @Override

60. public List findByDeptno(int deptno) {

61. /*

62. * 查询单个员工对象

63. * query三个参数

64. * 1 SQL语句

65. * 2 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper

66. * 3 SQL语句中需要的参数 (可变参数)

67. * */

68. BeanPropertyRowMapper rowMapper =new
    BeanPropertyRowMapper<>(Emp.class);

69. List emps = jdbcTemplate.query("select * from emp where deptno
    =?", rowMapper, deptno);

70. return emps;

71. }

72. @Override

73. public int addEmp(Emp emp) {

74. /*增删改

75. * 统统用update方法 两个参数

76. * 1 SQL语句

77. * 2 SQL语句需要的参数 (可变参数)

78. *

79. * */

80. String sql ="insert into emp values(DEFAULT ,?,?,?,?,?,?,?)";

81. Object[] args
    ={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),em
    .getComm(),emp.getDeptno()};

82. return jdbcTemplate.update(sql,args);

83. }

84. @Override

85. public int updateEmp(Emp emp) {

86. String sql ="update emp set ename =? , job =?, mgr=? , hiredate =?,
    sal=?, comm=?, deptno =? where empno =?";

87. Object[] args
    ={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),em
    .getComm(),emp.getDeptno(),emp.getEmpno()};

88. return jdbcTemplate.update(sql,args);

89. }

90. @Override

91. public int deleteEmp(int empno) {

92. String sql ="delete from emp where empno =?";

93. return jdbcTemplate.update(sql, empno);

94. }

95. }

6 测试代码

1. package com.msb.test;
2. import com.msb.pojo.Emp;
3. import com.msb.service.EmpService;
4. import org.junit.Test;
5. import org.springframework.context.ApplicationContext;
6. import org.springframework.context.support.ClassPathXmlApplicationContext;
7. import java.util.Date;
8. import java.util.List;
9. /**
10. • @Author: Ma HaiYang
11. • @Description: MircoMessage:Mark_7001
12. */
13. public class Test1 {
14. @Test
15. public void testEmpService(){
16. ApplicationContext context=new
    ClassPathXmlApplicationContext("applicationContext.xml");
17. EmpService empService = context.getBean(EmpService.class);
18. // 查询员工个数
19. /*int empCount = empService.findEmpCount();
20. System.out.println(empCount);*/
21. // 根据员工编号查询员工对象
22. /* Emp byEmpno = empService.findByEmpno(7521);
23. System.out.println(byEmpno);*/
24. /根据部门编号查询多个员工对象集合/
25. /*List emps = empService.findByDeptno(20);
26. emps.forEach(System.out::println);*/
27. /增加员工信息/
28. /*int rows = empService.addEmp(new Emp(null, "TOM", "SALESMAN",
    7521, new Date(), 2000.0, 100.0, 10));
29. System.out.println(rows);*/
30. /根据员工编号修改员工信息/
31. /*int rows = empService.updateEmp(new Emp(7939, "JERRY", "MANAGER",
    7839, new Date(), 3000.0, 0.0, 20));
32. System.out.println(rows);*/
33. /根据员工编号删除员工信息/
34. /*int rows = empService.deleteEmp(7939);
35. System.out.println(rows);*/
36. }
37. }

---

Generated with Mybase Desktop 8.2.13