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

}

}