15_Spring_JDBCTemplate批操作

一次连接,操作表格里的多条数据,就是批量操作

1 批量增加

2 批量修改

3 批量删除

实体类

package com.msb.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Dept implements Serializable {
    private Integer deptno;
    private String dname;
    private String loc;
}

DeptService

package com.msb.service;
import com.msb.pojo.Dept;
import java.util.List;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
public interface DeptService {
    int[] deptBatchAdd(List<Dept> depts);
    int[] deptBatchUpdate(List<Dept> depts);
    int[] deptBatchDelete(List<Integer> deptnos);
}

package com.msb.service.impl;
import com.msb.dao.DeptDao;
import com.msb.pojo.Dept;
import com.msb.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptDao deptDao;
    @Override
    public int[] deptBatchAdd(List<Dept> depts) {
        return deptDao.deptBatchAdd(depts);
    }
    @Override
    public int[] deptBatchUpdate(List<Dept> depts) {
        return  deptDao.deptBatchUpdate(depts);
    }
    @Override
    public int[] deptBatchDelete(List<Integer> deptnos) {
        return  deptDao.deptBatchDelete(deptnos);
    }
}

DeptDao

package com.msb.dao;
import com.msb.pojo.Dept;
import java.util.List;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
public interface DeptDao {
    int[] deptBatchAdd(List<Dept> depts);
    int[] deptBatchUpdate(List<Dept> depts);
    int[] deptBatchDelete(List<Integer> deptnos);
}

package com.msb.dao.impl;
import com.msb.dao.DeptDao;
import com.msb.pojo.Dept;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.LinkedList;
import java.util.List;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
@Repository
public class DeptDaoImpl implements DeptDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int[] deptBatchAdd(List<Dept> depts) {
        String sql ="insert into dept values(DEFAULT,?,?)";
        List<Object[]> args =new LinkedList<>();
        for (Dept dept : depts) {
            Object[] arg ={dept.getDname(),dept.getLoc()};
            args.add(arg);
        }
        return jdbcTemplate.batchUpdate(sql, args);
    }
    @Override
    public int[] deptBatchUpdate(List<Dept> depts) {
        String sql ="update dept set dname =? ,loc =? where deptno=?";
        List<Object[]> args =new LinkedList<>();
        for (Dept dept : depts) {
            Object[] arg ={dept.getDname(),dept.getLoc(),dept.getDeptno()};
            args.add(arg);
        }
        return jdbcTemplate.batchUpdate(sql, args);
    }
    @Override
    public int[] deptBatchDelete(List<Integer> deptnos) {
        String sql ="delete from dept where deptno =?";
        List<Object[]> args =new LinkedList<>();
        for (Integer deptno : deptnos) {
            Object[] arg ={deptno};
            args.add(arg);
        }
        return jdbcTemplate.batchUpdate(sql, args);
    }
}

测试

package com.msb.test;
import com.msb.pojo.Dept;
import com.msb.service.DeptService;
import com.msb.service.EmpService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
public class Test2 {
    @Test
    public void testBatchAdd(){
        ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
        DeptService deptService = context.getBean(DeptService.class);
        List<Dept> depts =new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            depts.add(new Dept(null,"name"+i,"loc"+i));
        }
        int[] ints = deptService.deptBatchAdd(depts);
        System.out.println(Arrays.toString(ints));
    }
    @Test
    public void testBatchUpdate(){
        ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
        DeptService deptService = context.getBean(DeptService.class);
        List<Dept> depts =new ArrayList<>();
        for (int i = 51; i <=60; i++) {
            depts.add(new Dept(i,"newname","newLoc"));
        }
        int[] ints = deptService.deptBatchUpdate(depts);
        System.out.println(Arrays.toString(ints));
    }
    @Test
    public void testBatchDelete(){
        ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
        DeptService deptService = context.getBean(DeptService.class);
        List<Integer> deptnos =new ArrayList<>();
        for (int i = 51; i <=69; i++) {
            deptnos.add(i);
        }
        int[] ints = deptService.deptBatchDelete(deptnos);
        System.out.println(Arrays.toString(ints));
    }
}