15_Spring_JDBCTemplate批操作

本是古典何须时尚 2023-09-27 15:19 66阅读 0赞

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

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;

![4b346887c3e776b26ced36a5c68a7f30.gif][]

![8f84ff8569b799e4d351c8f7aeb372d4.gif][]

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

![d06fff1dc28a9b804492df09bffb55b1.gif][]

![708b7f8931be4617ce900f79b3c39bc2.gif][]

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

![69a994edf9cba1f6f628fb4df16b1afb.gif][]

![287a51c7c0318078cdcd516a51634c84.gif][]

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

![b9e663b50856c63bdff66d9706afdff4.gif][]

![1ea0bbd90e80461c5eeae7999832a96b.gif][]

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

![3d982be99198ac342b645b50e335eb06.gif][]

![517130b5963059a910205ab6c69e6c4c.gif][]

测试

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

![5435b0546b44dd6483e99e4ed7d92857.gif][]

![9575cab4e2c58405f23b6f9c8d8598b6.gif][]

[4b346887c3e776b26ced36a5c68a7f30.gif]: https://img-blog.csdnimg.cn/img_convert/4b346887c3e776b26ced36a5c68a7f30.gif
[8f84ff8569b799e4d351c8f7aeb372d4.gif]: https://img-blog.csdnimg.cn/img_convert/8f84ff8569b799e4d351c8f7aeb372d4.gif
[d06fff1dc28a9b804492df09bffb55b1.gif]: https://img-blog.csdnimg.cn/img_convert/d06fff1dc28a9b804492df09bffb55b1.gif
[708b7f8931be4617ce900f79b3c39bc2.gif]: https://img-blog.csdnimg.cn/img_convert/708b7f8931be4617ce900f79b3c39bc2.gif
[69a994edf9cba1f6f628fb4df16b1afb.gif]: https://img-blog.csdnimg.cn/img_convert/69a994edf9cba1f6f628fb4df16b1afb.gif
[287a51c7c0318078cdcd516a51634c84.gif]: https://img-blog.csdnimg.cn/img_convert/287a51c7c0318078cdcd516a51634c84.gif
[b9e663b50856c63bdff66d9706afdff4.gif]: https://img-blog.csdnimg.cn/img_convert/b9e663b50856c63bdff66d9706afdff4.gif
[1ea0bbd90e80461c5eeae7999832a96b.gif]: https://img-blog.csdnimg.cn/img_convert/1ea0bbd90e80461c5eeae7999832a96b.gif
[3d982be99198ac342b645b50e335eb06.gif]: https://img-blog.csdnimg.cn/img_convert/3d982be99198ac342b645b50e335eb06.gif
[517130b5963059a910205ab6c69e6c4c.gif]: https://img-blog.csdnimg.cn/img_convert/517130b5963059a910205ab6c69e6c4c.gif
[5435b0546b44dd6483e99e4ed7d92857.gif]: https://img-blog.csdnimg.cn/img_convert/5435b0546b44dd6483e99e4ed7d92857.gif
[9575cab4e2c58405f23b6f9c8d8598b6.gif]: https://img-blog.csdnimg.cn/img_convert/9575cab4e2c58405f23b6f9c8d8598b6.gif