Spring5入门到实战------13、使用JdbcTemplate操作数据库（批量增删改）。具体代码+讲解 【下篇】

Spring5入门到实战------12、使用JdbcTemplate操作数据库（增删改查）。具体代码+讲解 【上篇】

这一篇主要介绍批量操作数据库

1、jdbcTemplate 实现批量添加操作

有两个参数
第一个参数：sql 语句
第二个参数：List 集合，添加多条记录数据

核心

    @Override
    public void batchAdd(List<Object[]> batchArgs) {
        String sql = "insert into t_book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

测试

    @Test
    public void testJdbcTemplate4(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 ={3,"C语言","出售"};
        Object[] o2 ={4,"Python语言","出售"};
        Object[] o3 ={5,"Php语言","出售"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchAdd(batchArgs);

    }

测试结果

2、JdbcTemplate 实现批量修改操作

核心

   @Override
    public void batchUpdate(List<Object[]> batchArgs) {
        String sql = "update t_book set book_name=?,isSale=? where book_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

测试

    @Test
    public void testJdbcTemplate5(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 ={"C语言001","出售",3};
        Object[] o2 ={"Python语言001","出售",4};
        Object[] o3 ={"Php语言001","出售",5};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchUpdate(batchArgs);

    }

测试结果

3、JdbcTemplate 实现批量删除操作

核心部分

   @Override
    public void batchDelete(List<Object[]> batchArgs) {
        String sql = "delete from t_book where book_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

测试

    @Test
    public void testJdbcTemplate6(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 ={3};
        Object[] o2 ={5};
        batchArgs.add(o1);
        batchArgs.add(o2);
        bookService.batchDelete(batchArgs);

    }

测试结果

4、完整的项目案例

4.1 目录结构

4.2 BookDao.java

/**
 * @author Lenovo
 * @version 1.0
 * @data 2022/10/23 15:53
 */
public interface BookDao {

    /**
     * 批量添加
     * @param batchArgs
     * @return
     */
    void batchAdd(List<Object[]> batchArgs);

    /**
     * 批量修改
     * @param batchArgs
     * @return
     */
    void batchUpdate(List<Object[]> batchArgs);

    /**
     * 批量删除
     * @param batchArgs
     * @return
     */
    void batchDelete(List<Object[]> batchArgs);

}

4.3 BookDaoImpl.java

/**
 * @author Lenovo
 * @version 1.0
 * @data 2022/10/23 15:53
 */
@Repository
public class BookDaoImpl implements BookDao{

    /**
     * 注入jdbcTemplate
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void batchAdd(List<Object[]> batchArgs) {
        String sql = "insert into t_book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchUpdate(List<Object[]> batchArgs) {
        String sql = "update t_book set book_name=?,isSale=? where book_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchDelete(List<Object[]> batchArgs) {
        String sql = "delete from t_book where book_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }
}

4.4 BookService.java

/**
 * @author Lenovo
 * @version 1.0
 * @data 2022/10/23 15:53
 */
@Service
public class BookService {
    /**
     * 注入dao
     */
    @Autowired
    private BookDao bookDao;


    /**
     * 批量添加
     * @return
     */
    public void batchAdd(List<Object[]> batchArgs){
       bookDao.batchAdd(batchArgs);
    }

    /**
     * 批量修改
     * @return
     */
    public void batchUpdate(List<Object[]> batchArgs){
        bookDao.batchUpdate(batchArgs);
    }


    /**
     * 批量删除
     * @return
     */
    public void batchDelete(List<Object[]> batchArgs){
        bookDao.batchDelete(batchArgs);
    }

}

4.5 bean.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 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.zyz"></context:component-scan>
    <!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/user_db" />
        <property name="username" value="root" />
        <property name="password" value="root" />
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>

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

</beans>

4.6 TestDemo.java

/**
 * @author Lenovo
 * @version 1.0
 * @data 2022/10/23 16:26
 */
public class TestDemo {

    @Test
    public void testJdbcTemplate4(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 ={3,"C语言","出售"};
        Object[] o2 ={4,"Python语言","出售"};
        Object[] o3 ={5,"Php语言","出售"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchAdd(batchArgs);

    }

    @Test
    public void testJdbcTemplate5(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 ={"C语言001","出售",3};
        Object[] o2 ={"Python语言001","出售",4};
        Object[] o3 ={"Php语言001","出售",5};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchUpdate(batchArgs);

    }

    @Test
    public void testJdbcTemplate6(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 ={3};
        Object[] o2 ={5};
        batchArgs.add(o1);
        batchArgs.add(o2);
        bookService.batchDelete(batchArgs);

    }
}

4.7 Book.java

可以使用lombok插件、简化get/set

/**
 * @author Lenovo
 * @version 1.0
 * @data 2022/10/23 16:10
 */
public class Book {
    private int book_id;
    private String book_name;
    private String isSale;

    public int getBook_id() {
        return book_id;
    }

    public void setBook_id(int book_id) {
        this.book_id = book_id;
    }

    public String getBook_name() {
        return book_name;
    }

    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }

    public String getIsSale() {
        return isSale;
    }

    public void setIsSale(String isSale) {
        this.isSale = isSale;
    }

    @Override
    public String toString() {
        return "Book{" +
                "book_id=" + book_id +
                ", book_name='" + book_name + '\'' +
                ", isSale='" + isSale + '\'' +
                '}';
    }
}

5、后语

主要用的时候、要知道使用哪个方法就行。学无止境。。。。。。