526互联

14_Spring_JDBCTemplate的使用

发布时间 2023-07-30 12:44:18作者: AidenDong

14_Spring_JDBCTemplate的使用

JdbcTemplate概述

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

image

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

image
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. /**
    • @Author: Ma HaiYang
    • @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. /**

    • @Author: Ma HaiYang
    • @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. /**

    • @Author: Ma HaiYang
    • @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. /**

    • @Author: Ma HaiYang
    • @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. /**

    • @Author: Ma HaiYang
    • @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. /**
    • @Author: Ma HaiYang
    • @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