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 depts);

10. int[] deptBatchUpdate(List depts);

11. int[] deptBatchDelete(List 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 depts) {

30. return deptDao.deptBatchAdd(depts);

31. }

32. @Override

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

34. return deptDao.deptBatchUpdate(depts);

35. }

36. @Override

37. public int[] deptBatchDelete(List 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 depts);

10. int[] deptBatchUpdate(List depts);

11. int[] deptBatchDelete(List 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 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 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 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 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 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 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