Spring的JdbcTemplate的基本操作

JdbcTemplate概述 :

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

pom.xml:

    4.0.0

    org.example
    spring_jdbcTemplate_01
    1.0-SNAPSHOT
    
        
            
                org.apache.maven.plugins
                maven-compiler-plugin
                3.8.1
                
                    8
                    8
                
            
        
    

    
        8
        8
    

    
    
        
            org.springframework
            spring-context
            5.3.21
        

        
        
            org.springframework
            spring-aspects
            5.3.16
        

        
        
            org.springframework
            spring-tx
            5.3.21
        

        
        
            org.springframework
            spring-orm
            5.3.16
        

        
        
            org.projectlombok
            lombok
            1.18.22
        

        
        
            org.springframework
            spring-jdbc
            5.3.21
        

        
        
            commons-logging
            commons-logging
            1.2
        

        
        
            mysql
            mysql-connector-java
            8.0.28
        

        
        
            com.alibaba
            druid
            1.2.6
        

        
        
            junit
            junit
            4.13.2
            test
        
    

jdbc.properties:

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=123456

applicationContext.xml:

Emp.class

package com.zi.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
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;
}

EmpDao:

package com.zi.dao;

import com.zi.pojo.Emp;

public interface EmpDao {
    int findEmpCount();

    Emp findEmpByEmpno(int empno);

    int addEmp(Emp emp);

    int updateEmp(Emp emp);

    int deleteEmpByEmpno(int empno);
}

EmpDaoImpl:

package com.zi.dao.impl;

import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class EmpDaoImpl implements EmpDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    
    @Override
    public int findEmpCount() {
        String sql = "select count(1) from emp";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    
    @Override
    public Emp findEmpByEmpno(int empno) {
        String sql = "select * from emp where empno = ?";
        BeanPropertyRowMapper rowMapper = new BeanPropertyRowMapper<>(Emp.class);
        Emp emp = jdbcTemplate.queryForObject(sql, rowMapper, empno);
        return emp;
    }

    
    @Override
    public int addEmp(Emp emp) {
        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()};
        int row = jdbcTemplate.update(sql, args);
        return row;
    }

    @Override
    public int deleteEmpByEmpno(int empno) {
        String sql = "delete from emp where empno = ?";
        int i = jdbcTemplate.update(sql, empno);
        return i;
    }
}

EmpService:

package com.zi.service;

import com.zi.pojo.Emp;

public interface EmpService {
    int findEmpCount();

    Emp findEmpByEmpno(int empno);

    int addEmp(Emp emp);

    int updateEmp(Emp emp);

    int deleteEmpByEmpno(int empno);
}

EmpServiceImpl:

package com.zi.service.impl;

import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpDao empDao;

    @Override
    public int findEmpCount() {
        return empDao.findEmpCount();
    }

    @Override
    public Emp findEmpByEmpno(int empno) {
        return empDao.findEmpByEmpno(empno);
    }

    @Override
    public int addEmp(Emp emp) {
        return empDao.addEmp(emp);
    }

    @Override
    public int updateEmp(Emp emp) {
        return empDao.updateEmp(emp);
    }

    @Override
    public int deleteEmpByEmpno(int empno) {
        return empDao.deleteEmpByEmpno(empno);
    }
}

package com.zi.test;

import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.Date;

public class Test01 {

    private ApplicationContext applicationContext = null;
    private EmpService empService = null;

    @Before
    public void init(){
        applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        empService = applicationContext.getBean(EmpService.class);
    }

    @Test
    public void testFindEmpCount(){
        System.out.println(empService.findEmpCount());
    }

    @Test
    public void testFindEmpByEmpno(){
        Emp emp = empService.findEmpByEmpno(7936);
        System.out.println(emp);
        //Emp(empno=7936, ename=curry, job=SG, mgr=7839, hiredate=2022-07-07 00:00:00.0, sal=6666.0, comm=300.0, deptno=20)
    }

    @Test
    public void testAddEmp(){
        Emp emp = new Emp(null, "Rose", "SmallBoss", 7902, new Date(), 4000.0, 500.0, 20);
        int row = empService.addEmp(emp);
        System.out.println(row);
    }

    @Test
    public void updateEmp(){
        Emp emp = new Emp(7938, "大漂亮郭艾伦", "PG", 7902, new Date(), 6000.0, 700.0, 10);
        int i = empService.updateEmp(emp);
        System.out.println(i);
    }

    @Test
    public void deleteEmpByEmpno(){
        int i = empService.deleteEmpByEmpno(7938);
        System.out.println(i);
    }
}

EmpServiceImpl:

@Override
public int[] empBathAdd(List emps) {
    String sql = "insert into emp values(DEFAULT,?,?,?,?,?,?,?)";
    List args = new ArrayList<>();
    for (Emp emp : emps) {
        Object[] arg = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};
        args.add(arg);
    }
    return jdbcTemplate.batchUpdate(sql, args);
}

测试类：

@Test
public void empBatchAdd(){
    List emps = new ArrayList<>();
    for (int i = 0; i < 15; i++) {
        Emp emp = new Emp(null, "" + i, "" + i, 7902, new Date(), 6000.0, 700.0, 20);
        emps.add(emp);
    }
    int[] row = empService.empBathAdd(emps);
    System.out.println(Arrays.toString(row));
    //[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
}