16-Spring5 JdbcTemplate的批量增删改查操作

1.操作语法

初始数据库表t_book

①查询多条记录, 返回集合

    @Override
    public List<Book> findAll(){
        String sql = "select * from t_book";
        //查找一条记录用queryForObject(), 但是查找多条记录用的是query
        List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));//<>里的参数可以省略
        return bookList;
    }

执行结果

 System.out.println(bookDao.findAll());

②批量添加多条记录
注意:批量添加、批量修改和批量删除都是用batchUppdate方法

    @Override
    public int[] batchAdd(List<Object[]> bookList) {
        String sql = "insert into t_book values(?, ?, ?)";
        int[] res = jdbcTemplate.batchUpdate(sql, bookList);
        return res;
    }

执行结果

  		Object[] book1 = {4, "html", 2.55};
        Object[] book2 = {5, "css", 6.25};
        Object[] book3 = {6, "javascript", 8.11};
        List<Object[]> bookList = new LinkedList<>();
        bookList.add(book1);
        bookList.add(book2);
        bookList.add(book3);

        System.out.println(bookDao.batchAdd(bookList));

③批量修改多条记录

    @Override
    public int[] batchUpdateById(List<Object[]> bookList) {
        String sql = "update t_book set name = ?, price = ? where id = ?";
        int[] res = jdbcTemplate.batchUpdate(sql, bookList);
        return res;
    }

执行结果

        Object[] book1 = {"html_batch_update", 6.66, 4}; //注意参数顺序
        Object[] book2 = {"css_batch_update", 6.66, 5};
        Object[] book3 = {"javascript_batch_update", 6.66, 6};
        List<Object[]> bookList = new LinkedList<>();
        bookList.add(book1);
        bookList.add(book2);
        bookList.add(book3);

        System.out.println(bookDao.batchUpdateById(bookList));

④批量删除多条记录

    @Override
    public int[] batchDeleteById(List<Object[]> bookList) {
        String sql = "delete from t_book where id = ?";
        //就算只有一个id参数, 也不能传入{4, 5, 6}这样的数字数组, 只能传入List<Object[]>类型
        int[] res = jdbcTemplate.batchUpdate(sql, bookList);
        return res;
    }

执行结果

        Object[] book1 = {4};
        Object[] book2 = {5};
        Object[] book3 = {6};
        List<Object[]> bookList = new LinkedList<>();
        bookList.add(book1);
        bookList.add(book2);
        bookList.add(book3);

        System.out.println(bookDao.batchDeleteById(bookList));

2.完整代码

配置文件bean1.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: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/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
    <!--开启组件扫描-->
    <context:component-scan base-package="com.limi"></context:component-scan>

    <!-- 1.在 spring 配置文件配置数据库连接池-->
    <!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/db_springtest" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>

    <!--2.配置 JdbcTemplate 对象，注入 DataSource-->
    <!-- JdbcTemplate 对象 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入 dataSource-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

Book

package com.limi.entity;

public class Book {

    private Integer id;

    String name;

    Double price;

    public Book(){}

    public Book(Integer id, String name, Double price) {
        this.id = id;
        this.name = name;
        this.price = price;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                '}';
    }
}

BookDao

package com.limi.dao;

import com.limi.entity.Book;

import java.util.List;

public interface BookDao {

    //查找所有书籍
    List<Book> findAll();

    //批量添加书籍
    int[] batchAdd(List<Object[]> bookList);

    //批量修改书籍
    int[] batchUpdateById(List<Object[]> bookList);

    //批量删除书籍
    int[] batchDeleteById(List<Object[]> bookList);

}

BookDaoImpl

package com.limi.dao;

import com.limi.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class BookDaoImpl implements BookDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Book> findAll(){
        String sql = "select * from t_book";
        //查找一条记录用queryForObject(), 但是查找多条记录用的是query
        List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));//<>里的参数可以省略
        return bookList;
    }

    @Override
    public int[] batchAdd(List<Object[]> bookList) {
        String sql = "insert into t_book values(?, ?, ?)";
        int[] res = jdbcTemplate.batchUpdate(sql, bookList);
        return res;
    }

    @Override
    public int[] batchUpdateById(List<Object[]> bookList) {
        String sql = "update t_book set name = ?, price = ? where id = ?";
        int[] res = jdbcTemplate.batchUpdate(sql, bookList);
        return res;
    }

    @Override
    public int[] batchDeleteById(List<Object[]> bookList) {
        String sql = "delete from t_book where id = ?";
        //就算只有一个id参数, 也不能传入{4, 5, 6}这样的数字数组, 只能传入List<Object[]>类型
        int[] res = jdbcTemplate.batchUpdate(sql, bookList);
        return res;
    }
    
}

测试类MyTest

package com.limi.test;
import com.limi.dao.BookDao;
import com.limi.dao.BookDaoImpl;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MyTest {

    @Test
    public void test1(){
        //1.加载bean的xml文件, 以src为根目录
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

        //2.获取配置的对象, 参数1:bean的id值, 参数2: 类名.class
        BookDao bookDao = context.getBean("bookDaoImpl", BookDaoImpl.class);

        //3.使用对象
        System.out.println(bookDao.findAll());

    }
}

数据库表t_book的结构