9、Spring JDBCTemplate 批量操作数据

最新推荐文章于 2023-07-26 11:41:42 发布

上官修平

最新推荐文章于 2023-07-26 11:41:42 发布

阅读量249

点赞数

分类专栏： spring 文章标签： spring java junit maven

本文链接：https://blog.csdn.net/chf_liu/article/details/130593797

版权

spring 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Dept implements Serializable {
    private Integer deptno;
    private String dname;
    private String loc;
}
public interface DeptService {
    int[] addDeptBatch(List<Dept> deptList);

    int[] updateDeptBatch(List<Dept> deptList);

    int[] deleteDeptBatch(List<Integer> deptnos);
}
@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptDAO deptDAO;

    public int[] addDeptBatch(List<Dept> deptList) {
        return deptDAO.addDeptBatch(deptList);
    }

    public int[] updateDeptBatch(List<Dept> deptList) {
        return deptDAO.updateDeptBatch(deptList);
    }

    @Override
    public int[] deleteDeptBatch(List<Integer> deptnos) {
        return deptDAO.deleteDeptBatch(deptnos);
    }
}
public interface DeptDAO {
    int[] addDeptBatch(List<Dept> deptList);

    int[] updateDeptBatch(List<Dept> depts);

    int[] deleteDeptBatch(List<Integer> deptnos);

}

@Repository
public class DeptDAOImpl implements DeptDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public int[] addDeptBatch(List<Dept> deptList) {
        String sql = "insert into dept values (default,?,?)";
        List<Object[]> args = new ArrayList<Object[]>();

        for (Dept dept : deptList) {
            Object[] objects = {dept.getDname(), dept.getLoc()};
            args.add(objects);
        }

        return jdbcTemplate.batchUpdate(sql, args);
    }

    public int[] updateDeptBatch(List<Dept> depts) {
        String sql = "update dept set dname=?,loc=? where deptno=?";
        List<Object[]> args = new ArrayList<Object[]>();

        for (Dept dept : depts) {
            Object[] objects = {dept.getDname(), dept.getLoc(), dept.getDeptno()};
            args.add(objects);
        }

        return jdbcTemplate.batchUpdate(sql, args);
    }

    public int[] deleteDeptBatch(List<Integer> deptnos) {
        String sql = "delete from dept where deptno=?";
        List<Object[]> args = new ArrayList<>();
        for (Integer deptno : deptnos) {
            Object[] objects = {deptno};

            args.add(objects);
        }

        return jdbcTemplate.batchUpdate(sql, args);
    }
}

public class Test02 {
    // 批量增加部门
    @Test
    public void addDeptBatch() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        DeptService deptService = applicationContext.getBean(DeptService.class);
        List<Dept> depts = new ArrayList<Dept>();
        for (int i = 0; i < 10; i++) {
            depts.add(new Dept(null, "dname" + i, "loc" + i));
        }
        int[] ints = deptService.addDeptBatch(depts);
        System.out.println("Arrays.toString(ints) = " + Arrays.toString(ints));

    }

    // 批量修改部门
    @Test
    public void updateDeptBatch() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        DeptService deptService = applicationContext.getBean(DeptService.class);
        List<Dept> depts = new ArrayList<Dept>();
        for (int i = 45; i < 55; i++) {
            depts.add(new Dept(i, "newdname" + i, "newloc" + i));
        }
        int[] ints = deptService.updateDeptBatch(depts);
        System.out.println("Arrays.toString(ints) = " + Arrays.toString(ints));

    }


    // 批量删除部门
    @Test
    public void deleteDeptBatch() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        DeptService deptService = applicationContext.getBean(DeptService.class);
        List<Integer> deptnos = new ArrayList<>();
        for (int i = 45; i < 55; i++) {
            deptnos.add(i);
        }
        int[] ints = deptService.deleteDeptBatch(deptnos);
        System.out.println("Arrays.toString(ints) = " + Arrays.toString(ints));

    }

}