JdbcTemplate操作数据库（批量操作）

1、加入依赖，并创建spring配置文件（bean.xml）

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>5.3.22</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.22</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-core</artifactId>
    <version>5.3.22</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

<?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"
       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
">

    <!--开启组件扫描-->
    <context:component-scan base-package="com.jin"></context:component-scan>

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

    <!--数据库连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/user_db?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    </bean>

</beans>

2、对应数据库创建实体类

public class User {
    private int userId;
    private String userName;
    private String ustatus;

    //Getter和Setter方法
    //toString方法
    ...
}

2、编写service和dao

（1）首先创建Dao接口和业务逻辑层

//Dao接口
public interface BookDao {

    //批量添加
    public void bathAdd(List<Object[]> batchArgs);

    //批量修改
    public void bathUpdate(List<Object[]> batchArgs);

    //批量删除
    public void bathDelete(List<Object[]> batchArgs);

}

//业务逻辑层
@Service
public class BookService implements BookDao {

    @Autowired
    private  BookDao bookDao;

    批量添加的方法
    @Override
    public void bathAdd(List<Object[]> batchArgs) {
        bookDao.bathAdd(batchArgs);
    }

    @Override
    public void bathUpdate(List<Object[]> batchArgs) {
        bookDao.bathUpdate(batchArgs);
    }

    @Override
    public void bathDelete(List<Object[]> batchArgs) {
        bookDao.bathDelete(batchArgs);
    }

}

（2）在Dao接口实现类中调用JdbcTemplate对象里面的方法实现添加操作

//Dao接口实现类
@Repository
@Primary   //@Primary 告诉spring 在犹豫的时候优先选择哪一个具体的实现。
public class BookDaoImpl implements BookDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void bathAdd(List<Object[]> batchArgs) {

        String sql = "insert into user_db.t_book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

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

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

3、测试类

public class test {

  @Test
    public void MyTest01(){
      ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
      BookService bookService = context.getBean("bookService", BookService.class);
    ArrayList<Object[]> batchArgs = new ArrayList<>();
   //批量添加信息
    Object[] o1 =  {"121","张三","a"};
    Object[] o2 =  {"122","李四","b"};
    Object[] o3 =  {"123","王五","c"};
    batchArgs.add(o1);
    batchArgs.add(o2);
    batchArgs.add(o3);
    //调用批量添加
    bookService.bathAdd(batchArgs);

    //批量修改信息
//    Object[] o1 =  {"张三","aaa","121"};
//    Object[] o2 =  {"李四","bbbb","122"};
//    Object[] o3 =  {"王五","ccccc","123"};
//    batchArgs.add(o1);
//    batchArgs.add(o2);
//    batchArgs.add(o3);
//    //调用批量修改
//    bookService.bathUpdate(batchArgs);

    //批量删除信息
//    Object[] o1 =  {"121"};
//    Object[] o2 =  {"122"};
//    Object[] o3 =  {"123"};
//    batchArgs.add(o1);
//    batchArgs.add(o2);
//    batchArgs.add(o3);
//    //调用批量删除
//    bookService.bathDelete(batchArgs);
  }
}