14_Spring_JDBCTemplate的使用

小灰灰 2023-10-03 22:27 17阅读 0赞

JdbcTemplate概述

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

![c57afb252ac683236a3366e757418e98.png][]

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

![5117e0d62e70deb2bb5d8e240cc7beb7.png][]

1 导入依赖

<dependencies>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.3.5</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-aspects</artifactId>
                <version>5.3.5</version>
            </dependency>
            
            <dependency>
                <groupId>aopalliance</groupId>
                <artifactId>aopalliance</artifactId>
                <version>1.0</version>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.10</version>
            </dependency>
            
             <dependency>
                 <groupId>mysql</groupId>
                 <artifactId>mysql-connector-java</artifactId>
                 <version>8.0.22</version>
             </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.3.5</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>5.3.5</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-orm</artifactId>
                <version>5.3.5</version>
            </dependency>
            
            <dependency>
                <groupId>commons-logging</groupId>
                <artifactId>commons-logging</artifactId>
                <version>1.2</version>
            </dependency>
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.13.1</version>
                <scope>test</scope>
            </dependency>
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.12</version>
                <scope>provided</scope>
            </dependency>
        </dependencies>

2 准备JDBC.properties

jdbc_username=root
    jdbc_password=root
    jdbc_driver=com.mysql.cj.jdbc.Driver
    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

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:p="http://www.springframework.org/schema/p"
           xmlns:c="http://www.springframework.org/schema/c"
           xmlns:util="http://www.springframework.org/schema/util"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xsi:schemaLocation="
           http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans.xsd
           http://www.springframework.org/schema/util
           http://www.springframework.org/schema/util/spring-util.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context.xsd
           http://www.springframework.org/schema/aop
           http://www.springframework.org/schema/aop/spring-aop.xsd
    ">
        
        <context:component-scan base-package="com.msb"/>
        
        <context:property-placeholder location="classpath:jdbc.properties"/>
        
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
            <property name="username" value="${jdbc_username}"></property>
            <property name="password" value="${jdbc_password}"></property>
            <property name="url" value="${jdbc_url}"></property>
            <property name="driverClassName" value="${jdbc_driver}"></property>
        </bean>
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            
            <property name="dataSource" ref="dataSource"></property>
        </bean>
    </beans>

3 准备实体类

package com.msb.pojo;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import java.util.Date;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    public class Emp implements Serializable{
        private Integer empno;
        private String ename;
        private String job;
        private Integer mgr;
        private Date hiredate;
        private Double sal;
        private Double comm;
        private Integer deptno;
    }

4 准备service层接口和实现类

package com.msb.service;
    import com.msb.pojo.Emp;
    import java.util.List;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public interface EmpService {
        int findEmpCount();
        Emp findByEmpno(int empno);
        List<Emp> findByDeptno(int deptno);
        int  addEmp(Emp emp);
        int updateEmp(Emp emp);
        int deleteEmp( int empno);
    }

package com.msb.service.impl;
    import com.msb.dao.EmpDao;
    import com.msb.pojo.Emp;
    import com.msb.service.EmpService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    @Service
    public class EmpServiceImpl implements EmpService {
        @Autowired
        private EmpDao empDao;
        @Override
        public int findEmpCount() {
            return empDao.findEmpCount();
        }
        @Override
        public Emp findByEmpno(int empno) {
            return empDao.findByEmpno( empno);
        }
        @Override
        public List<Emp> findByDeptno(int deptno) {
            return empDao.findByDeptno( deptno);
        }
        @Override
        public int addEmp(Emp emp) {
            return empDao.addEmp(emp);
        }
        @Override
        public int updateEmp(Emp emp) {
            return empDao.updateEmp(emp);
        }
        @Override
        public int deleteEmp(int empno) {
            return empDao.deleteEmp(empno);
        }
    }

5 准备dao层接口和实现类

package com.msb.dao;
    import com.msb.pojo.Emp;
    import java.util.List;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public interface EmpDao {
        int findEmpCount();
        Emp findByEmpno(int empno);
        List<Emp> findByDeptno(int deptno);
        int addEmp(Emp emp);
        int updateEmp(Emp emp);
        int deleteEmp(int empno);
    }

package com.msb.dao.impl;
    import com.msb.dao.EmpDao;
    import com.msb.pojo.Emp;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.stereotype.Repository;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    @Repository
    public class EmpDaoImpl implements EmpDao {
        @Autowired
        private JdbcTemplate jdbcTemplate;
        @Override
        public int findEmpCount() {
            /*查询员工个数
            * queryForObject 两个参数
            * 1 SQL语句
            * 2 返回值类型
            *
            * */
            Integer empCount = jdbcTemplate.queryForObject("select count(1) from emp", Integer.class);
            return empCount;
        }
        @Override
        public Emp findByEmpno(int empno) {
            /*
            * 查询单个员工对象
            * queryForObject三个参数
            * 1 SQL语句
            * 2 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper
            * 3 SQL语句中需要的参数 (可变参数)
            * */
            BeanPropertyRowMapper<Emp> rowMapper =new BeanPropertyRowMapper<>(Emp.class);
            Emp emp = jdbcTemplate.queryForObject("select * from emp where empno =?", rowMapper, empno);
            return emp;
        }
        @Override
        public List<Emp> findByDeptno(int deptno) {
            /*
             * 查询单个员工对象
             * query三个参数
             * 1 SQL语句
             * 2 RowMapper接口的实现类对象,用于执行返回的结果用哪个类来进行封装 ,实现类为BeanPropertyRowMapper
             * 3 SQL语句中需要的参数 (可变参数)
             * */
            BeanPropertyRowMapper<Emp> rowMapper =new BeanPropertyRowMapper<>(Emp.class);
            List<Emp> emps = jdbcTemplate.query("select * from emp where deptno =?", rowMapper, deptno);
            return emps;
        }
        @Override
        public int addEmp(Emp emp) {
            /*增删改
             * 统统用update方法 两个参数
             * 1 SQL语句
             * 2 SQL语句需要的参数 (可变参数)
             *
             * */
            String sql ="insert into emp values(DEFAULT ,?,?,?,?,?,?,?)";
            Object[] args ={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno()};
            return jdbcTemplate.update(sql,args);
        }
        @Override
        public int updateEmp(Emp emp) {
            String sql ="update emp set ename =? , job =?, mgr=? , hiredate =?, sal=?, comm=?, deptno =? where empno =?";
            Object[] args ={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno(),emp.getEmpno()};
            return jdbcTemplate.update(sql,args);
        }
        @Override
        public int deleteEmp(int empno) {
            String sql ="delete  from emp where empno =?";
            return jdbcTemplate.update(sql, empno);
        }
    }

6 测试代码

package com.msb.test;
    import com.msb.pojo.Emp;
    import com.msb.service.EmpService;
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import java.util.Date;
    import java.util.List;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public class Test1 {
        @Test
        public void testEmpService(){
            ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
            EmpService empService = context.getBean(EmpService.class);
            // 查询员工个数
            /*int empCount = empService.findEmpCount();
            System.out.println(empCount);*/
            // 根据员工编号查询员工对象
           /* Emp byEmpno = empService.findByEmpno(7521);
            System.out.println(byEmpno);*/
           /*根据部门编号查询多个员工对象集合*/
            /*List<Emp> emps = empService.findByDeptno(20);
            emps.forEach(System.out::println);*/
            /*增加员工信息*/
            /*int rows = empService.addEmp(new Emp(null, "TOM", "SALESMAN", 7521, new Date(), 2000.0, 100.0, 10));
            System.out.println(rows);*/
            /*根据员工编号修改员工信息*/
            /*int rows = empService.updateEmp(new Emp(7939, "JERRY", "MANAGER", 7839, new Date(), 3000.0, 0.0, 20));
            System.out.println(rows);*/
            /*根据员工编号删除员工信息*/
            /*int rows = empService.deleteEmp(7939);
            System.out.println(rows);*/
        }
    }

[c57afb252ac683236a3366e757418e98.png]: https://img-blog.csdnimg.cn/img_convert/c57afb252ac683236a3366e757418e98.png
[5117e0d62e70deb2bb5d8e240cc7beb7.png]: https://img-blog.csdnimg.cn/img_convert/5117e0d62e70deb2bb5d8e240cc7beb7.png