说说自己玩 spring data jpa 遇到的一个坑
spring boot升级到2.0之后,就没有下面这个方法了,如果要批量插入,还是要用1.5,给save方法传一个list,直接可以批量插入,亲测8w条数据没问题;
下面帖代码,从页面上传一个excel,读取8万行表格数据之后,插入数据库
pom.xml:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.10.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.14</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-annotations</artifactId>
<version>1.5.9</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.4.0</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.4.0</version>
</dependency>
</dependencies>
application.yml:
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/data_test
username: root
password: root
jpa:
hibernate:
ddl-auto: update
show-sql: true
http:
multipart:
max-file-size: 20MB
max-request-size: 20MB
在templates文件夹下新建一个html:
<!DOCTYPE html>
<html>
<head>
<title>upload</title>
<meta name="keywords" content="keyword1,keyword2,keyword3"></meta>
<meta name="description" content="this is my page"></meta>
<meta name="content-type" content="text/html; charset=UTF-8"></meta>
</head>
<body>
<form enctype="multipart/form-data" method="post" action="/upload/excel">
文件<input type="file" name="file"/>
<input type="submit" value="上传"/>
</form>
</body>
</html>
然后是controller层:
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
@Controller
public class UploadController {
@Autowired
private ExcelService excelService;
//跳转到上传文件的页面
@RequestMapping(path = "/upload", method = RequestMethod.GET)
public String goUpload() {
//跳转到 templates 目录下的 upload.html
return "upload";
}
@ApiOperation(value = "导入Excel表", notes = "", httpMethod = "POST")
@RequestMapping(path = "/upload/excel",method = RequestMethod.POST)
@ResponseBody
public String upload(MultipartFile file) throws Exception {
excelService.getExcel(file);
return "上传成功";
}
然后是service层:
@Service
@Slf4j
public class ExcelServiceImpl implements ExcelService {
@Autowired
private ExcelRepository excelRepository;
@Override
@Transactional(rollbackFor = Exception.class)
public void getExcel(MultipartFile file) throws Exception {
List<Excel> list = new ArrayList<Excel>();
//1.得到上传的表
Workbook workbook2 = WorkbookFactory.create(file.getInputStream());
//2、获取sheet1工作表
Sheet sheet2 = workbook2.getSheet("sheet1");
//获取表的总行数
int num = sheet2.getLastRowNum();
//总列数
int col = sheet2.getRow(0).getLastCellNum();
//遍历excel每一行
for (int j = 1; j < num; j++) {
Row row1 = sheet2.getRow(j);
//获取表中第i行,第2列的单元格
Cell cell3 = row1.getCell(1);
//excel表的第i行,第3列的单元格
Cell cell4 = row1.getCell(2);
//如果单元格中有数字或者其他格式的数据,则调用setCellType()转换为string类型
Cell cell7 = row1.getCell(4);
cell7.setCellType(CellType.STRING);
//这里new 一个对象,用来装填从页面上传的Excel数据,字段根据上传的excel决定
Excel excel= new Excel();
excel.setName(cell3.getStringCellValue());
excel.setAge(cell4.getStringCellValue());
excel.setAddress(cell7.getStringCellValue());
list.add(command);
}
excelRepository.save(list);//批量插入数据
}
然后是Repository:
@Repository
public interface ExcelRepository extends CrudRepository<Excel,Long> {
}
这里的repository不需要写任何方法,默认继承了CrudRepository就有了jpa的增删改查的方法,具体用法可以查官网的api
这里注意CrudRepository<Excel,Long> 表示要用该方法对Excel实体类做增删改查
下面是实体类:
@Entity
@Table(name = "excel_test")
@Data
public class Excel {
@Id
@GeneratedValue
private Integer id;
@Column
private String name;
@Column
private String age;
@Column
private String address;
}
用了上面这些注解之后,服务启动,spring jpa就会在数据库生成相应的表,然后跑起来
访问:localhost:8080/upload ,上传一个8w条的带有name,age,address字段的excel表,就会批量插入到数据库了