spring--JdbcTemplate--03--批量增删改操作

一、JdbcTemplate批量增删改api

int[] batchUpdate(String sql, List<Object[]> batchArgs)

batchUpdate增删改多个或0个时，不会有queryForObject抛异常问题

二、举例

（1）dao层代码

package com.fuping3.dao;

import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class BookDaoBatchImpl implements BookDaoBatch{
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int[] batchAddBook(List<Object[]> batchArgs) {
        String sql="INSERT INTO book(name,price) VALUES(?,?)";
        int[] ints=jdbcTemplate.batchUpdate(sql,batchArgs);
        return ints;
    }

    @Override
    public int[] batchDeleteBook(List<Object[]> batchArgs) {
        String sql="DELETE FROM book WHERE id=?";
        int[] ints=jdbcTemplate.batchUpdate(sql,batchArgs);
        return ints;
    }

    @Override
    public int[] batchUpdateBook(List<Object[]> batchArgs) {
        String sql="UPDATE book SET name=?,price=price-? WHERE id=?";
        int[] ints=jdbcTemplate.batchUpdate(sql,batchArgs);
        return ints;
    }


}

（2）service代码

package com.fuping3.service;

import com.fuping3.dao.BookDaoBatch;

import java.util.List;

public class BookServiceBatch {
    private BookDaoBatch bookDaoBatch;

    public void setBookDaoBatch(BookDaoBatch bookDaoBatch) {
        this.bookDaoBatch = bookDaoBatch;
    }

    public int[] batchAdd(List<Object[]> batchArgs){
        System.out.println("-------batchAdd...");
        return bookDaoBatch.batchAddBook(batchArgs);
    }
    public int[] batchDelete(List<Object[]> batchArgs){
        System.out.println("-------batchDelete...");
        return bookDaoBatch.batchDeleteBook(batchArgs);
    }

    public int[] batchUpdate(List<Object[]> batchArgs){
        System.out.println("-------batchUpdate...");
        return bookDaoBatch.batchUpdateBook(batchArgs);
    }
}

（3）测试代码

import com.fuping3.service.BookServiceBatch;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class TestBatch {
    public static void main(String[] args) {
        ClassPathXmlApplicationContext context=new ClassPathXmlApplicationContext("bean.xml");
        BookServiceBatch bookService = context.getBean("bookServiceBatch", BookServiceBatch.class);
        {
            List<Object[]> batchArgs = new ArrayList<>();
            Object[] o1={"aa",10};
            Object[] o2={"bb",11};
            Object[] o3={"cc",12};
            batchArgs.add(o1);
            batchArgs.add(o2);
            batchArgs.add(o3);
            int[] ints = bookService.batchAdd(batchArgs);
            System.out.println("批量添加结果:"+Arrays.toString(ints));
        }


        {
            List<Object[]> batchArgs = new ArrayList<>();
            Object[] o1={"aaa",5,13};
            Object[] o2={"bbb",6,14};
            Object[] o3={"ccc",10,60};
            batchArgs.add(o1);
            batchArgs.add(o2);
            batchArgs.add(o3);
            int[] ints = bookService.batchUpdate(batchArgs);
            System.out.println("批量修改结果:"+Arrays.toString(ints));
        }
        {
            List<Object[]> batchArgs = new ArrayList<>();
            Object[] o1={15};
            Object[] o2={16};
            Object[] o3={60};
            batchArgs.add(o1);
            batchArgs.add(o2);
            batchArgs.add(o3);
            int[] ints = bookService.batchDelete(batchArgs);
            System.out.println("批量删除结果:"+Arrays.toString(ints));
        }

    }
}

（4）输出

-------batchAdd...
四月 22, 2021 2:51:31 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
批量添加结果:[1, 1, 1]
-------batchUpdate...
批量修改结果:[1, 1, 0]
-------batchDelete...
批量删除结果:[1, 1, 0]