Spring-JDBCTemplate操作数据库（批量增删改）

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

1.批量操作：操作表里面多条记录

 

2.批量操作

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

两个参数

第一个参数：sql语句

第二个参数：List集合，添加多条数据

 

package com.orzjiangxiaoyu.spring.dao;

import com.orzjiangxiaoyu.spring.entity.User;

import java.util.List;

/**
 * @author orz
 * @create 2020-08-18 9:36
 */
public interface UserDao {


    public void  batchInsertUser(List<Object []> batchArgs);

    public void  batchUpdateUser(List<Object []> batchArgs);

    public void  batchDeleteUser(List<Object []> batchArgs);
}

package com.orzjiangxiaoyu.spring.dao;

import com.orzjiangxiaoyu.spring.entity.User;
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.Arrays;
import java.util.List;

/**
 * @author orz
 * @create 2020-08-18 9:37
 */
@Repository
public class UserDaoImpl implements UserDao {

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void batchInsertUser(List<Object[]> batchArgs) {
        //1.创建sql语句
        String sql = "insert into t_user(user_id,username,ustatus) values(?,?,?)";
        //2.调用方法实现

        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchUpdateUser(List<Object[]> batchArgs)
    {
        //1.创建sql语句
        String sql = "update t_user set username=?,ustatus=? where user_id=?";
        //2.调用方法实现

        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void  batchDeleteUser(List<Object []> batchArgs)
    {
        //1.创建sql语句
        String sql = "delete from t_user where user_id=?";
        //2.调用方法实现
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }

}

package com.orzjiangxiaoyu.spring.service;

import com.orzjiangxiaoyu.spring.dao.UserDao;
import com.orzjiangxiaoyu.spring.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author orz
 * @create 2020-08-18 9:36
 */
@Service
public class UserService {
    //注入dao
    @Autowired
    private UserDao userDao;


    //批量添加
    public void  batchInsertUser(List<Object []> batchArgs)
    {
        userDao.batchInsertUser(batchArgs);
    }

    //批量修改
    public void  batchUpdateUser(List<Object []> batchArgs)
    {
        userDao.batchUpdateUser(batchArgs);
    }

    //批量删除
    public void  batchDeleteUser(List<Object []> batchArgs)
    {
        userDao.batchDeleteUser(batchArgs);
    }
}

测试

package com.orzjiangxiaoyu.spring.mysqltestdemo;

import com.orzjiangxiaoyu.spring.entity.User;
import com.orzjiangxiaoyu.spring.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

/**
 * @author orz
 * @create 2020-08-18 10:20
 */
public class Test2 {

    //批量增加
    @Test
    public void test7()
    {
        ApplicationContext context=new ClassPathXmlApplicationContext("jdbcbean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        List<Object []> batchArgs=new ArrayList<>();
        Object [] o1={4,"java","a"};
        Object [] o2={5,"c++","b"};
        Object [] o3={6,"mysql","c"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);

        //调用批量添加
        userService.batchInsertUser(batchArgs);

    }


    //批量修改
    @Test
    public void test8()
    {
        ApplicationContext context=new ClassPathXmlApplicationContext("jdbcbean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        List<Object []> batchArgs=new ArrayList<>();
        Object [] o1={"李华","fat",4};
        Object [] o2={"PHP","B",5};
        Object [] o3={"SQL","A",6};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);

        //调用批量修改
        userService.batchUpdateUser(batchArgs);

    }


    //批量删除
    @Test
    public void test9()
    {
        ApplicationContext context=new ClassPathXmlApplicationContext("jdbcbean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        List<Object []> batchArgs=new ArrayList<>();
        Object [] o1={4};
        Object [] o2={5};
        Object [] o3={6};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);

        //调用批量修改
        userService.batchDeleteUser(batchArgs);

    }

}