14_Spring_JDBCTemplate的使用

14_Spring_JDBCTemplate的使用

JdbcTemplate概述

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

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

1 导入依赖

1.  <dependencies>
   

2.      <!--spring核心容器包-->
   

3.      <dependency>
   

4.          <groupId>org.springframework</groupId>
   

5.          <artifactId>spring-context</artifactId>
   

6.          <version>5.3.5</version>
   

7.      </dependency>
   

8.      <!--spring切面包-->
   

9.      <dependency>
   

10.         <groupId>org.springframework</groupId>
    

11.         <artifactId>spring-aspects</artifactId>
    

12.         <version>5.3.5</version>
    

13.     </dependency>
    

14.     <!--aop联盟包-->
    

15.     <dependency>
    

16.         <groupId>aopalliance</groupId>
    

17.         <artifactId>aopalliance</artifactId>
    

18.         <version>1.0</version>
    

19.     </dependency>
    

20.     <!--德鲁伊连接池-->
    

21.     <dependency>
    

22.         <groupId>com.alibaba</groupId>
    

23.         <artifactId>druid</artifactId>
    

24.         <version>1.1.10</version>
    

25.     </dependency>
    

26.     <!--mysql驱动-->
    

27.      <dependency>
    

28.          <groupId>mysql</groupId>
    

29.          <artifactId>mysql-connector-java</artifactId>
    

30.          <version>8.0.22</version>
    

31.      </dependency>
    

32.     <!--springJDBC包-->
    

33.     <dependency>
    

34.         <groupId>org.springframework</groupId>
    

35.         <artifactId>spring-jdbc</artifactId>
    

36.         <version>5.3.5</version>
    

37.     </dependency>
    

38.     <!--spring事务控制包-->
    

39.     <dependency>
    

40.         <groupId>org.springframework</groupId>
    

41.         <artifactId>spring-tx</artifactId>
    

42.         <version>5.3.5</version>
    

43.     </dependency>
    

44.     <!--spring orm 映射依赖-->
    

45.     <dependency>
    

46.         <groupId>org.springframework</groupId>
    

47.         <artifactId>spring-orm</artifactId>
    

48.         <version>5.3.5</version>
    

49.     </dependency>
    

50.     <!--Apache Commons日志包-->
    

51.     <dependency>
    

52.         <groupId>commons-logging</groupId>
    

53.         <artifactId>commons-logging</artifactId>
    

54.         <version>1.2</version>
    

55.     </dependency>
    

56.     <!--Junit单元测试-->
    

57.     <dependency>
    

58.         <groupId>junit</groupId>
    

59.         <artifactId>junit</artifactId>
    

60.         <version>4.13.1</version>
    

61.         <scope>test</scope>
    

62.     </dependency>
    

63.     <!--lombok -->
    

64.     <dependency>
    

65.         <groupId>org.projectlombok</groupId>
    

66.         <artifactId>lombok</artifactId>
    

67.         <version>1.18.12</version>
    

68.         <scope>provided</scope>
    

69.     </dependency>
    

70. </dependencies> 
    

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. ">

19. <!--spring 注解扫描-->
    

20. <context:component-scan base-package="com.msb"/>
    

21. <!--读取jdbc配置文件-->
    

22. <context:property-placeholder location="classpath:jdbc.properties"/>
    

23. <!--配置德鲁伊连接池-->
    

24. <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    

25.     <property name="username" value="${jdbc_username}"></property>
    

26.     <property name="password" value="${jdbc_password}"></property>
    

27.     <property name="url" value="${jdbc_url}"></property>
    

28.     <property name="driverClassName" value="${jdbc_driver}"></property>
    

29. </bean>
    

30. <!--配置JDBCTemplate对象,并向里面注入DataSource-->
    

31. <bean id="jdbcTemplate"
    

    class="org.springframework.jdbc.core.JdbcTemplate">

32.     <!--通过set方法注入连接池-->
    

33.     <property name="dataSource" ref="dataSource"></property>
    

34. </bean>
    

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<Emp> 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<Emp> 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<Emp> 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<Emp> 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<Emp> findByDeptno(int deptno) {
    

61.     /*
    

62.      * 查询单个员工对象
    

63.      * query三个参数
    

64.      * 1 SQL语句
    

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

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

67.      * */
    

68.     BeanPropertyRowMapper<Emp> rowMapper =new
    

    BeanPropertyRowMapper<>(Emp.class);

69.     List<Emp> 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<Emp> 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