这篇博客是在上一遍博客搭建好spring-boot demo项目的基础上开发的,本来在家里早就已经实现了自己的demo,只是最近加班多,我个人也有点懒,所以知道今天才写到博客上来。需要参考spring-boot项目搭建和简单rest接口实现的朋友可以参考我的上一篇博客:使用IntelliJ IDEA 开发工具搭建spring-boot项目笔记(一)
首先,采用Java Persistence API(Java 持久层 API,简称JPA,用于对象持久化的API)连接数据库实现数据的增删改查你要有自己的数据库,我个人用的数据库是mysql5.6, 数据库图形化界面工具用的是Mysql Workbench,mysql5.6的msi安装包可至Mysql官网下载:http://www.mysql.com/
mysql5.6及图形化界面工具Mysql Workbench的安装和配置可参考脚本之家的相关教程:https://www.jb51.net/article/127229.htm
1. Spring Data概述
1)SpringData:Spring 的一个子项目。用于简化数据库访问,支持NoSQL 和 关系数据存储。其主要目标是使数据库的访问变得方便快捷。
2)关系型数据库访问框架 Spring Data JPA:致力于减少数据访问层 (DAO) 的开发量. 开发者唯一要做的,就只是声明持久层的接口,其他都交给 Spring Data JPA 来完成!关于JPA + SpringData 操作数据库 的详细介绍可参考这篇博客:JPA + SpringData 操作数据库 ---- 深入了解 SpringData
2. 在pom.xml文件中增加以下两个模块的依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
3.application-test.yaml文件中增加连接数据库的配置
若一开始数据库没有创建实体类映射的表,启用jpa.hibernate.ddl-auto=create语句则会在项目启动时自动创建表及对应的字段
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?useSSL=false
username: root
password: heshengfu2018 #个人数据库密码
dbcp2:
initial-size: 5
min-idle: 5
max-idle: 5
#最大连接等待时间10s
max-wait-millis: 10000
#最大连接生命周期30min
max-conn-lifetime-millis: 1800000
max-total: 20
jpa:
hibernate:
ddl-auto: update #开启数据库自动化自定义语言:第一次启动项目时可用create,之后改为Update
show-sql: true
4. jpa-dta 使用的是hibernate,所以我们使用hibernate的有有关注解实现实体类与表及字段的关联
(1)新建实体类EmpInfoTO 对应表userinfo
package com.example.demo.entity;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
@Table(name="userinfo")
@Entity
public class EmpInfoTO implements Serializable {
@Id
@Column(name="id")
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer id;
@Column(name="userAccount",length=20,nullable = false)
private String userAccount;
@Column(name="password",length=16,nullable = false)
private String password;
@Column(name="nickName",length=20)
private String nickName;
@Column(name="deptNo",length=4,nullable=false)
private Integer deptNo;
@Column(name="phoneNum",length=11,nullable = false)
private Long phoneNum;
@Column(name="emailAddress",length=20)
private String emailAddress;
@Column(name="birthDay",nullable = false)
@Temporal(TemporalType.DATE)
private Date birthDay;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserAccount() {
return userAccount;
}
public void setUserAccount(String userAccount) {
this.userAccount = userAccount;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public Integer getDeptNo() {
return deptNo;
}
public void setDeptNo(Integer deptNo) {
this.deptNo = deptNo;
}
public Long getPhoneNum() {
return phoneNum;
}
public void setPhoneNum(Long phoneNum) {
this.phoneNum = phoneNum;
}
public String getEmailAddress() {
return emailAddress;
}
public void setEmailAddress(String emailAddress) {
this.emailAddress = emailAddress;
}
public Date getBirthDay() {
return birthDay;
}
public void setBirthDay(Date birthDay) {
this.birthDay = birthDay;
}
@Override
public String toString() {
return "EmpInfoTO{" +
"id=" + id +
", userAccount='" + userAccount + '\'' +
", password='" + password + '\'' +
", nickName='" + nickName + '\'' +
", deptNo=" + deptNo +
", phoneNum=" + phoneNum +
", emailAddress='" + emailAddress + '\'' +
", birthDay=" + birthDay +
'}';
}
}
注意,当在实体类中定义的属性不需要映射到对应数据库表中时,只需要在实体类属性字段上添加 @transient
注解即可。
hibernate会把实体类中驼峰命名的字段映射成数据库中下划线命名的列名,如下所示
(2)新建接口响应实体类UnistarResponse<T> 用于接收返回的消息
package com.example.demo.entity;
import java.io.Serializable;
public class UnistarResponse<T> implements Serializable {
private Integer status = 0;
private String message = "";
private String serviceId = "";
private T data;
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public String getServiceId() {
return serviceId;
}
public void setServiceId(String serviceId) {
this.serviceId = serviceId;
}
public T getData() {
return data;
}
public void setData(T data) {
this.data = data;
}
}
(3)新建Dao类并继承JpaRepository<T,Integer>
package com.example.demo.dao;
import com.example.demo.entity.EmpInfoTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import java.sql.SQLException;
public interface EmpJpaRepository extends JpaRepository<EmpInfoTO,Integer> {
EmpInfoTO findByUserAccount(String userAccount) throws SQLException;
}
(4) 首先看一下JpaRepository 接口已经具备了那些数据库的增删改查功能:选中JpaRepository,Ctrl+Click进入该接口类
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package org.springframework.data.jpa.repository;
import java.util.List;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.QueryByExampleExecutor;
@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
List<T> findAll();
List<T> findAll(Sort var1);
List<T> findAllById(Iterable<ID> var1);
<S extends T> List<S> saveAll(Iterable<S> var1);
void flush();
<S extends T> S saveAndFlush(S var1);
void deleteInBatch(Iterable<T> var1);
void deleteAllInBatch();
T getOne(ID var1);
<S extends T> List<S> findAll(Example<S> var1);
<S extends T> List<S> findAll(Example<S> var1, Sort var2);
}
JpaRepository 接口继承了PagingAndSortingRepository和QueryByExampleExecutor两个接口类(注意从java8开始接口已经可以实现多继承)
JpaRepository接口
PagingAndSortingRepository接口类主要实现数据的分页和排序,PagingAndSortingRepository接口类又继承了CrudRepository接口,源代码如下
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package org.springframework.data.repository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
Iterable<T> findAll(Sort var1);
Page<T> findAll(Pageable var1);
}
CrudRepository接口类实现了一组操作数据库增删改查的基本方法,源代码如下
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package org.springframework.data.repository;
import java.util.Optional;
@NoRepositoryBean
public interface CrudRepository<T, ID> extends Repository<T, ID> {
<S extends T> S save(S var1);
<S extends T> Iterable<S> saveAll(Iterable<S> var1);
Optional<T> findById(ID var1);
boolean existsById(ID var1);
Iterable<T> findAll();
Iterable<T> findAllById(Iterable<ID> var1);
long count();
void deleteById(ID var1);
void delete(T var1);
void deleteAll(Iterable<? extends T> var1);
void deleteAll();
}
废话不多说,下面贴上从Dao层到Controller层代码
package com.example.demo.dao;
import com.example.demo.entity.EmpInfoTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
import java.sql.SQLException;
public interface EmpJpaRepository extends JpaRepository<EmpInfoTO,Integer> {
EmpInfoTO findByUserAccount(String userAccount) throws SQLException;
List<EmpInfoTO> findByEmailAddressLike(String emailAddress) throws SQLException;
@Modifying
@Query(value="update userinfo set user_account=?1,email_address=?2 where id=?3",nativeQuery = true)
void updateUserInfoById(String userAccount,String email_address,Integer id) throws SQLException;
}
package com.example.demo.service;
import com.example.demo.entity.EmpInfoTO;
import com.example.demo.entity.UnistarResponse;
import java.util.List;
public interface IEmpService {
UnistarResponse<EmpInfoTO> queryEmpByUserAccount(String account);
UnistarResponse<String> saveEmpInfo(EmpInfoTO empInfoTO);
UnistarResponse<String> updateEmpInfo(String user_account,String emailAddress,Integer id);
UnistarResponse<List<EmpInfoTO>> batchSaveEmp(List<EmpInfoTO> empList);
UnistarResponse<List<EmpInfoTO>> findAllEmpInfo();
UnistarResponse<List<EmpInfoTO>> findByEmpInfoByLike(String emailAddress);
UnistarResponse<String> deleteEmpById(Integer id);
UnistarResponse<String> deleteEmpOne(EmpInfoTO empInfoTO);
UnistarResponse<String> batchDeleteEmp(List<EmpInfoTO> empInfoList);
}
package com.example.demo.service.impl;
import com.example.demo.dao.EmpJpaRepository;
import com.example.demo.entity.EmpInfoTO;
import com.example.demo.entity.UnistarResponse;
import com.example.demo.service.IEmpService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.transaction.Transactional;
import java.sql.SQLException;
import java.util.List;
@Service("empService")
public class EmpService implements IEmpService {
private static final Logger logger = LoggerFactory.getLogger(EmpService.class);
@Autowired
private EmpJpaRepository empDao;
@Override
public UnistarResponse<EmpInfoTO> queryEmpByUserAccount(String account) {
UnistarResponse<EmpInfoTO> response = new UnistarResponse<EmpInfoTO>();
try{
EmpInfoTO empInfo = empDao.findByUserAccount(account);
response.setStatus(200);
response.setMessage("ok");
response.setData(empInfo);
}catch(SQLException e){
response.setStatus(500);
response.setMessage(e.getMessage());
logger.error(e.getMessage(),e);
}
return response;
}
@Override
@Transactional
public UnistarResponse<String> saveEmpInfo(EmpInfoTO empInfoTO) {
UnistarResponse<String> response = new UnistarResponse<String>();
try{
empDao.save(empInfoTO);
response.setStatus(200);
response.setMessage("ok");
response.setData("success");
}catch(RuntimeException e){
response.setStatus(500);
response.setMessage(e.getMessage());
response.setData("failed");
}
return response;
}
@Override
@Transactional
public UnistarResponse<String> updateEmpInfo(String userAccount,String emailAddress,Integer id) {
UnistarResponse<String> response = new UnistarResponse<String>();
try{
empDao.updateUserInfoById(userAccount,emailAddress,id);
response.setStatus(200);
response.setMessage("ok");
response.setData("success");
} catch(SQLException e){
response.setStatus(500);
response.setMessage(e.getMessage());
response.setData("failed");
logger.error(e.getMessage(),e);
}
return response;
}
@Override
@Transactional
public UnistarResponse<List<EmpInfoTO>> batchSaveEmp(List<EmpInfoTO> empList) {
UnistarResponse<List<EmpInfoTO>> response = new UnistarResponse<>();
try{
List<EmpInfoTO> data = empDao.saveAll(empList);
response.setStatus(200);
response.setMessage("ok");
response.setData(data);
}catch(RuntimeException e){
response.setStatus(500);
response.setMessage(e.getMessage());
logger.error(e.getMessage(),e);
}
return response;
}
@Override
public UnistarResponse<List<EmpInfoTO>> findAllEmpInfo() {
UnistarResponse<List<EmpInfoTO>> response = new UnistarResponse<>();
try{
List<EmpInfoTO> data = empDao.findAll();
response.setStatus(200);
response.setMessage("ok");
response.setData(data);
}catch(RuntimeException e){
response.setStatus(500);
response.setMessage(e.getMessage());
logger.error(e.getMessage(),e);
}
return response;
}
@Override
public UnistarResponse<List<EmpInfoTO>> findByEmpInfoByLike(String emailAddress) {
UnistarResponse<List<EmpInfoTO>> response = new UnistarResponse<>();
try{
List<EmpInfoTO> data = empDao.findByEmailAddressLike(emailAddress);
response.setStatus(200);
response.setMessage("ok");
response.setData(data);
}catch (SQLException e){
response.setStatus(500);
response.setMessage(e.getMessage());
logger.error(e.getMessage(),e);
}
return response;
}
@Override
@Transactional
public UnistarResponse<String> deleteEmpById(Integer id) {
UnistarResponse<String> response = new UnistarResponse<>();
try{
empDao.deleteById(id);
response.setStatus(200);
response.setMessage("ok");
response.setData("delete success");
}catch(RuntimeException e){
response.setStatus(500);
response.setMessage(e.getMessage());
response.setData("delete failed");
}
return response;
}
@Override
@Transactional
public UnistarResponse<String> deleteEmpOne(EmpInfoTO empInfoTO) {
UnistarResponse<String> response = new UnistarResponse<>();
try{
empDao.delete(empInfoTO);
response.setStatus(200);
response.setMessage("ok");
response.setData("delete success");
}catch(RuntimeException e){
response.setStatus(500);
response.setMessage("ok");
response.setData("delete failed");
}
return response;
}
@Override
@Transactional
public UnistarResponse<String> batchDeleteEmp(List<EmpInfoTO> empInfoList) {
UnistarResponse<String> response = new UnistarResponse<>();
try{
empDao.deleteInBatch(empInfoList);
response.setStatus(200);
response.setMessage("ok");
response.setData("batch delete success");
}catch(RuntimeException e){
response.setStatus(500);
response.setMessage(e.getMessage());
response.setData("batch delete failed");
}
return response;
}
}
package com.example.demo.controller;
import com.example.demo.entity.EmpInfoTO;
import com.example.demo.entity.StudentConfig;
import com.example.demo.entity.UnistarResponse;
import com.example.demo.service.IEmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping(value="/test")
public class TestController {
@Autowired
private StudentConfig student;
@Autowired
private IEmpService empService;
@RequestMapping(value="/studentInfo",method = RequestMethod.GET)
public StudentConfig getStudentInfo(){
return student;
}
//根据查询关键字查询单条记录
@RequestMapping(value="/findEmpInfo",method=RequestMethod.GET)
public UnistarResponse<EmpInfoTO> findEmpInfoByAccount(@RequestParam("userAccount") String userAccount){
return empService.queryEmpByUserAccount(userAccount);
}
// 添加或修改单条数据(带上数据库中已存在id时为update)
@RequestMapping(value="/saveEmp",method = RequestMethod.POST)
public UnistarResponse<String> saveEmpInfo(@RequestBody EmpInfoTO empInfoTO){
return empService.saveEmpInfo(empInfoTO);
}
// 批量添加数据
@RequestMapping(value="/batchSaveEmp",method = RequestMethod.POST)
public UnistarResponse<List<EmpInfoTO>> batchSaveEmpInfo(@RequestBody List<EmpInfoTO> empList){
return empService.batchSaveEmp(empList);
}
//查询表中所有记录
@RequestMapping(value="/findAllEmp",method = RequestMethod.GET)
public UnistarResponse<List<EmpInfoTO>> findAllEmpInfo(){
return empService.findAllEmpInfo();
}
//模糊查询
@RequestMapping(value="/findByEmailLike",method = RequestMethod.GET)
public UnistarResponse<List<EmpInfoTO>> findEmpInfoByEmailLike(@RequestParam("shortEmail") String emailAddress){
return empService.findByEmpInfoByLike(emailAddress);
}
//自定义sql本地修改
@RequestMapping(value="/updateEmp/{userAccount}/{emailAddress}/{id}",method=RequestMethod.PUT)
public UnistarResponse<String> updateEmpInfo(@PathVariable String userAccount,@PathVariable String emailAddress,@PathVariable Integer id){
return empService.updateEmpInfo(userAccount,emailAddress,id);
}
//根据Id删除单条记录
@RequestMapping(value="/deleteEmp/{id}",method=RequestMethod.DELETE)
public UnistarResponse<String> deleteById(@PathVariable Integer id){
return empService.deleteEmpById(id);
}
//根据对象中信息删除单条记录
@RequestMapping(value="/deleteEmpOne",method=RequestMethod.DELETE)
public UnistarResponse<String> deleteOne(@RequestBody EmpInfoTO empInfo){
return empService.deleteEmpOne(empInfo);
}
//批量删除
@RequestMapping(value="/batchDelete",method=RequestMethod.DELETE)
public UnistarResponse<String> batchDeleteEmpInfo(@RequestBody List<EmpInfoTO> empInfoList){
return empService.batchDeleteEmp(empInfoList);
}
}
在Postman工具中利用批量插入数据接口向数据库中插入数据,验证结果如下: 请求方法:POST; 请求URL:
http://localhost:8081/test/batchSaveEmp请求体中数据如下 [{ "userAccount": "zwx123", "password": "abc123456", "nickName": "test5", "deptNo": 1001, "phoneNum": 13145789012, "emailAddress": "test5@163.com", "birthDay": "1990-12-10" },{ "userAccount": "zwx456", "password": "abcd654321", "nickName": "test6", "deptNo": 1002, "phoneNum": 13245679076, "emailAddress": "test6@163.com", "birthDay": "1985-10-10" },{ "userAccount": "hwx764", "password": "efghk123", "nickName": "test7", "deptNo": 1001, "phoneNum": 13345679076, "emailAddress": "test7@163.com", "birthDay": "1986-08-10" },{ "userAccount": "hwx234", "password": "efghk456", "nickName": "test8", "deptNo": 1002, "phoneNum": 13545679076, "emailAddress": "tes8@163.com", "birthDay": "1987-06-10" },{ "userAccount": "hwx678", "password": "efghk892", "nickName": "test9", "deptNo": 1001, "phoneNum": 13645679076, "emailAddress": "tes9@163.com", "birthDay": "1987-06-10" },{ "userAccount": "lwx123", "password": "ckdfh123", "nickName": "test10", "deptNo": 1002, "phoneNum": 13745679076, "emailAddress": "tes10@163.com", "birthDay": "1991-06-10" },{ "userAccount": "lwx456", "password": "ckdfh456", "nickName": "test11", "deptNo": 1001, "phoneNum": 13845679076, "emailAddress": "tes11@163.com", "birthDay": "1992-02-10" },{ "userAccount": "lwx789", "password": "ckdfh789", "nickName": "test12", "deptNo": 1002, "phoneNum": 13945679076, "emailAddress": "tes12@163.com", "birthDay": "1993-01-10" }] 接口出参: { "status": 200, "message": "ok", "serviceId": "", "data": [ { "id": 7, "userAccount": "zwx123", "password": "abc123456", "nickName": "test5", "deptNo": 1001, "phoneNum": 13145789012, "emailAddress": "test5@163.com", "birthDay": "1990-12-10T00:00:00.000+0000" }, { "id": 8, "userAccount": "zwx456", "password": "abcd654321", "nickName": "test6", "deptNo": 1002, "phoneNum": 13245679076, "emailAddress": "test6@163.com", "birthDay": "1985-10-10T00:00:00.000+0000" }, { "id": 9, "userAccount": "hwx764", "password": "efghk123", "nickName": "test7", "deptNo": 1001, "phoneNum": 13345679076, "emailAddress": "test7@163.com", "birthDay": "1986-08-10T00:00:00.000+0000" }, { "id": 10, "userAccount": "hwx234", "password": "efghk456", "nickName": "test8", "deptNo": 1002, "phoneNum": 13545679076, "emailAddress": "tes8@163.com", "birthDay": "1987-06-10T00:00:00.000+0000" }, { "id": 11, "userAccount": "hwx678", "password": "efghk892", "nickName": "test9", "deptNo": 1001, "phoneNum": 13645679076, "emailAddress": "tes9@163.com", "birthDay": "1987-06-10T00:00:00.000+0000" }, { "id": 12, "userAccount": "lwx123", "password": "ckdfh123", "nickName": "test10", "deptNo": 1002, "phoneNum": 13745679076, "emailAddress": "tes10@163.com", "birthDay": "1991-06-10T00:00:00.000+0000" }, { "id": 13, "userAccount": "lwx456", "password": "ckdfh456", "nickName": "test11", "deptNo": 1001, "phoneNum": 13845679076, "emailAddress": "tes11@163.com", "birthDay": "1992-02-10T00:00:00.000+0000" }, { "id": 14, "userAccount": "lwx789", "password": "ckdfh789", "nickName": "test12", "deptNo": 1002, "phoneNum": 13945679076, "emailAddress": "tes12@163.com", "birthDay": "1993-01-10T00:00:00.000+0000" } ] } 在数据库中检验结果,截图如下(数据库中原先一使用插入单条记录接口插入一部分数据),由此验证了使用Spring data-jpa操作数据库的正确性和简易性。利用postman可一一验证其他接口,限于这篇博客的篇幅,下面只贴上调用查询全量数据的接口截图,其他接口不再一一截图 登录mysql-workbench后查询sql语句: select * from userinfo; 其他接口也可利用postman一一验证其正确性,限于篇幅,本博客不再一一截图列举