15_Spring_JDBCTemplate批操作

15_Spring_JDBCTemplate批操作

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

1 批量增加

2 批量修改

3 批量删除

实体类

1. package com.msb.pojo;
2. import lombok.AllArgsConstructor;
3. import lombok.Data;
4. import lombok.NoArgsConstructor;
5. import java.io.Serializable;
6. /**
7. • @Author: Ma HaiYang
8. • @Description: MircoMessage:Mark_7001
9. */
10. @AllArgsConstructor
11. @NoArgsConstructor
12. @Data
13. public class Dept implements Serializable {

14. private Integer deptno;
    

15. private String dname;
    

16. private String loc;
    

17. }

DeptService

1. package com.msb.service;

2. import com.msb.pojo.Dept;

3. import java.util.List;

4. /**

5. • @Author: Ma HaiYang
6. • @Description: MircoMessage:Mark_7001

7. */

8. public interface DeptService {

9.  int[] deptBatchAdd(List<Dept> depts);
   

10. int[] deptBatchUpdate(List<Dept> depts);
    

11. int[] deptBatchDelete(List<Integer> deptnos);
    

12. }

13. package com.msb.service.impl;

14. import com.msb.dao.DeptDao;

15. import com.msb.pojo.Dept;

16. import com.msb.service.DeptService;

17. import org.springframework.beans.factory.annotation.Autowired;

18. import org.springframework.stereotype.Service;

19. import java.util.List;

20. /**

21. • @Author: Ma HaiYang
22. • @Description: MircoMessage:Mark_7001

23. */

24. @Service

25. public class DeptServiceImpl implements DeptService {

26. @Autowired
    

27. private DeptDao deptDao;
    

28. @Override
    

29. public int[] deptBatchAdd(List<Dept> depts) {
    

30.     return deptDao.deptBatchAdd(depts);
    

31. }
    

32. @Override
    

33. public int[] deptBatchUpdate(List<Dept> depts) {
    

34.     return  deptDao.deptBatchUpdate(depts);
    

35. }
    

36. @Override
    

37. public int[] deptBatchDelete(List<Integer> deptnos) {
    

38.     return  deptDao.deptBatchDelete(deptnos);
    

39. }
    

40. }

DeptDao

1. package com.msb.dao;

2. import com.msb.pojo.Dept;

3. import java.util.List;

4. /**

5. • @Author: Ma HaiYang
6. • @Description: MircoMessage:Mark_7001

7. */

8. public interface DeptDao {

9.  int[] deptBatchAdd(List<Dept> depts);
   

10. int[] deptBatchUpdate(List<Dept> depts);
    

11. int[] deptBatchDelete(List<Integer> deptnos);
    

12. }

13. package com.msb.dao.impl;

14. import com.msb.dao.DeptDao;

15. import com.msb.pojo.Dept;

16. import org.springframework.beans.factory.annotation.Autowired;

17. import org.springframework.jdbc.core.JdbcTemplate;

18. import org.springframework.stereotype.Repository;

19. import java.util.LinkedList;

20. import java.util.List;

21. /**

22. • @Author: Ma HaiYang
23. • @Description: MircoMessage:Mark_7001

24. */

25. @Repository

26. public class DeptDaoImpl implements DeptDao {

27. @Autowired
    

28. private JdbcTemplate jdbcTemplate;
    

29. @Override
    

30. public int[] deptBatchAdd(List<Dept> depts) {
    

31.     String sql ="insert into dept values(DEFAULT,?,?)";
    

32.     List<Object[]> args =new LinkedList<>();
    

33.     for (Dept dept : depts) {
    

34.         Object[] arg ={dept.getDname(),dept.getLoc()};
    

35.         args.add(arg);
    

36.     }
    

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

38. }
    

39. @Override
    

40. public int[] deptBatchUpdate(List<Dept> depts) {
    

41.     String sql ="update dept set dname =? ,loc =? where deptno=?";
    

42.     List<Object[]> args =new LinkedList<>();
    

43.     for (Dept dept : depts) {
    

44.         Object[] arg ={dept.getDname(),dept.getLoc(),dept.getDeptno()};
    

45.         args.add(arg);
    

46.     }
    

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

48. }
    

49. @Override
    

50. public int[] deptBatchDelete(List<Integer> deptnos) {
    

51.     String sql ="delete from dept where deptno =?";
    

52.     List<Object[]> args =new LinkedList<>();
    

53.     for (Integer deptno : deptnos) {
    

54.         Object[] arg ={deptno};
    

55.         args.add(arg);
    

56.     }
    

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

58. }
    

59. }

测试

1. package com.msb.test;
2. import com.msb.pojo.Dept;
3. import com.msb.service.DeptService;
4. import com.msb.service.EmpService;
5. import org.junit.Test;
6. import org.springframework.context.ApplicationContext;
7. import org.springframework.context.support.ClassPathXmlApplicationContext;
8. import java.util.ArrayList;
9. import java.util.Arrays;
10. import java.util.List;
11. /**
12. • @Author: Ma HaiYang
13. • @Description: MircoMessage:Mark_7001
14. */
15. public class Test2 {

16. @Test
    

17. public void testBatchAdd(){
    

18.     ApplicationContext context=new
    

    ClassPathXmlApplicationContext("applicationContext.xml");

19.     DeptService deptService = context.getBean(DeptService.class);
    

20.     List<Dept> depts =new ArrayList<>();
    

21.     for (int i = 0; i < 10; i++) {
    

22.         depts.add(new Dept(null,"name"+i,"loc"+i));
    

23.     }
    

24.     int[] ints = deptService.deptBatchAdd(depts);
    

25.     System.out.println(Arrays.toString(ints));
    

26. }
    

27. @Test
    

28. public void testBatchUpdate(){
    

29.     ApplicationContext context=new
    

    ClassPathXmlApplicationContext("applicationContext.xml");

30.     DeptService deptService = context.getBean(DeptService.class);
    

31.     List<Dept> depts =new ArrayList<>();
    

32.     for (int i = 51; i <=60; i++) {
    

33.         depts.add(new Dept(i,"newname","newLoc"));
    

34.     }
    

35.     int[] ints = deptService.deptBatchUpdate(depts);
    

36.     System.out.println(Arrays.toString(ints));
    

37. }
    

38. @Test
    

39. public void testBatchDelete(){
    

40.     ApplicationContext context=new
    

    ClassPathXmlApplicationContext("applicationContext.xml");

41.     DeptService deptService = context.getBean(DeptService.class);
    

42.     List<Integer> deptnos =new ArrayList<>();
    

43.     for (int i = 51; i <=69; i++) {
    

44.         deptnos.add(i);
    

45.     }
    

46.     int[] ints = deptService.deptBatchDelete(deptnos);
    

47.     System.out.println(Arrays.toString(ints));
    

48. }
    

49. }

---

Generated with Mybase Desktop 8.2.13