Springboot使用JPA框架对数据库实现增删改查（附详细代码）
程序员文章站
2022-03-02 22:25:13
...
前言
1、本文将详细阐述如何使用JPA框架对数据库实现增删改查操作，业务中比较常见的应用场景几乎在这里都能看到，并且有详尽的代码可供直观演示，其中遇到的坑也进行了实时标注。
2、JPA的环境配置在前面的章节已有总结，不再赘述，直接上干货。
环境准备
步骤1：创建实体类对象
@Entity //该注解必须加，表明这是一个与数据库映射的实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "merchant_info") //项目启动后数据库会自动创建merchant_info表
@ApiModel(value = "商户信息表") //该注解以及后续controller中所有以@Api开头的注解表明引入swagger框架，可不加，丝毫不影响，我是为了便于用wagger调用接口演示。
public class MerchantInfo {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY) //必须加该注解，表明id自增，且唯一
@ApiModelProperty(value = "主键id")
private Long id;
@ApiModelProperty(value = "商户名称")
private String merchantName;
@ApiModelProperty(value = "城市名称")
private String cityName;
@ApiModelProperty(value = "父对象id")
private Long parentId;
@ApiModelProperty(value = "商户状态: 1 生效 2 失效")
private Long status;
@ApiModelProperty(value = "随机生成码")
private String invitationCode;
@ApiModelProperty(value = "创建时间")
private Date createTime;
@ApiModelProperty(value = "更新时间")
private Date updateTime;
@ApiModelProperty(value = "备注")
private String remark;
@ApiModelProperty(value = "负责人")
private String principal;
@ApiModelProperty(value = "负责人联系方式")
private String principalPhone;
}
步骤2：创建与实体类MerchantInfo相关的JPA接口，并使其继承JpaRepository接口，泛型传递<MerchantInfo, Long>
//此注解必加，表明它是一个bean类，项目启动后spring容器扫描到该注解，会将该类初始化成bean对象，便于其他程序调用
@Component
public interface MerchantInfoJpaRepository extends JpaRepository<MerchantInfo,Long> {
}
步骤3：SpringBoot主程序入口，如果只用JPA框架，相关的配置只需加@EnableJpaRepositories注解即可
@SpringBootApplication
@EnableJpaRepositories
public class WebapitestApplication {
public static void main(String[] args) {
SpringApplication.run(WebapitestApplication.class, args);
}
}
步骤4：创建Controller类
下面是controller类整体格式，后面对数据库表merchant_info进行增删改查的所有代码都会在这个controller类里书写。
@RestController //此注解等同于@Controller(用在类上) + @ResponseBody(用在方法上)注解组合
@Slf4j //该注解可以在程序中直接使用log.info()打印日志
@Api(tags = "数据库表merchant_info进行增删改查") //此项是swagger的注解，可以不加，丝毫不影响
public class MerchantInfoController {
@Autowired
private MerchantInfoJpaRepository merchantInfoJpaRepository;
方法1: ...{}
方法2: ...{}
方法3: ...{}
}
}
JPA查询功能
1、findAll()方法：无条件查询merchant_info表中所有数据
步骤1：Controller中代码如下：
public class MerchantInfoController {
@Autowired
private MerchantInfoJpaRepository merchantInfoJpaRepository;
/**
* JPA findAll() 无条件查询表中所有数据
* @return
*/
@GetMapping("/getAllMerchantInfo")
@ApiOperation(value = "获取所有商户信息")
public List<MerchantInfo> getAllMerchantInfo(){
List<MerchantInfo> merchantInfoList = merchantInfoJpaRepository.findAll();
log.info("打印所有商户列表:{}",merchantInfoList);
return merchantInfoList;
}
}
}
步骤2：接口调用：http://localhost:8080/getAllMerchantInfo
步骤3：接口返回结果：
[
{
"id": 1,
"merchantName": "广州泽天君成科技有限公司",
"cityName": "广州",
"parentId": null,
"status": 2,
"invitationCode": "207717",
"createTime": "2021-03-23T03:37:00.000+00:00",
"updateTime": "2021-04-27T09:02:11.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
},
{
"id": 2,
"merchantName": "广州市青豪企业管理有限责任公司",
"cityName": "温州",
"parentId": null,
"status": 2,
"invitationCode": "126155",
"createTime": "2021-03-23T03:43:43.000+00:00",
"updateTime": "2021-04-27T06:47:00.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
},
...
省略若干
...
]
2、findById(Long id)方法：根据id查询merchant_info表中对应的数据
步骤1：Controller中代码如下：
/**
* JPA findById(Long id) 根据主键id查询表中相应数据
* @param id
* @return
*/
@GetMapping("/getMerchantById")
@ApiOperation(value = "获取指定id的商户信息")
@ApiImplicitParam(name = "id",value = "商户id",required = true,defaultValue = "15")
public MerchantInfo getMerchantById(@RequestParam(name = "id") Long id){
Optional<MerchantInfo> merchantInfoOptional = merchantInfoJpaRepository.findById(id);
MerchantInfo merchantInfo = merchantInfoOptional.get();
log.info("打印id={}的商户信息为:{}",id,merchantInfo);
return merchantInfo;
}
步骤2：调用接口：http://localhost:8080/getMerchantById?id=15
步骤3：接口返回结果如下：
{
"id": 15,
"merchantName": "浙江橙米企业管理有限公司",
"cityName": "绍兴",
"parentId": null,
"status": 2,
"invitationCode": "342149",
"createTime": "2021-03-23T03:43:43.000+00:00",
"updateTime": "2021-04-26T09:11:50.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
}
3、findAllById(Iterable<ID> var1)方法：根据传入的多个id集合查询merchant_info表中对应的数据
步骤1：Controller中代码
/**
* JPA findAllById(Iterable<ID> var1) 查询多个id表中相应数据
* @param ids
* @return
*/
@GetMapping("/getMerchantByIds")
@ApiOperation(value = "获取多个id对应的商户信息")
public List<MerchantInfo> getMerchantByIds(@RequestParam(name = "ids") String ids ){
log.info("打印后端接收post请求体数据 String[] ids：{}",ids);
//将传入的数组字符串解析成JsonArray对象
JSONArray jsonArray = JSON.parseArray(ids);
log.info("打印转换后的JsonArray对象：{}",jsonArray);
List<Long> longList = new ArrayList<Long>();
//遍历jsonArray对象取出id元素并添加到List集合中
for (int i = 0; i < jsonArray.size(); i++){
longList.add(jsonArray.getLong(i));
}
log.info("打印转换后的Long集合：{}",longList);
//查询longList集合中所有id对应的数据
List<MerchantInfo> merchantInfoList = merchantInfoJpaRepository.findAllById(longList);
log.info("打印商户集合：{}",merchantInfoList);
return merchantInfoList;
}
步骤2：接口调用：http://localhost:8080/getMerchantByIds?ids=%5B2%2C3%2C6%5D
Note：%5B %2C %5D分别代表[ , ]，它们都是URL编码，实际上ids参数传递的是[2, 3, 6]只是不能明文传输，必须转成符合URL编码规则的格式。
步骤3：接口返回结果如下：
[
{
"id": 2,
"merchantName": "广州市青豪企业管理有限责任公司",
"cityName": "温州",
"parentId": null,
"status": 2,
"invitationCode": "126155",
"createTime": "2021-03-23T03:43:43.000+00:00",
"updateTime": "2021-04-27T06:47:00.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
},
{
"id": 3,
"merchantName": "苏州合和众科技有限公司",
"cityName": "苏州",
"parentId": null,
"status": 2,
"invitationCode": "807624",
"createTime": "2021-03-24T09:23:05.000+00:00",
"updateTime": "2021-04-27T06:46:58.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
},
{
"id": 6,
"merchantName": "义乌市铃芝电动车行",
"cityName": "杭州",
"parentId": null,
"status": 2,
"invitationCode": "798048",
"createTime": "2021-03-23T03:43:43.000+00:00",
"updateTime": "2021-04-27T06:46:57.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
}
]
4、findAll(Example<S> example)方法：构建example查询条件进行查询
步骤1：Controller中代码
/**
* JPA List<S> findAll(Example<S> example) 构建example查询条件进行查询
* @param merchantInfo
* @return
*/
@PostMapping("/getMerchantByExample")
@ApiOperation(value = "根据example查询符合条件的商户信息")
public List<MerchantInfo> getMerchantByExample(@RequestBody MerchantInfo merchantInfo){
log.info("打印传入的请求体merchantInfo:{}",merchantInfo);
//构建查询example
Example<MerchantInfo> merchantInfoExample = Example.of(merchantInfo);
//返回数据库中所有符合example查询条件对应的数据
List<MerchantInfo> merchantInfoList = merchantInfoJpaRepository.findAll(merchantInfoExample);
log.info("打印符合条件的商户列表：{}",merchantInfoList);
return merchantInfoList;
}
步骤2：接口调用post：
http://localhost:8080/getMerchantByExampleparameters:
{
"cityName": "广州",
"remark": null,
"status": 2
}
步骤3：接口返回结果
[
{
"id": 1,
"merchantName": "广州泽天君成科技有限公司",
"cityName": "广州",
"parentId": null,
"status": 2,
"invitationCode": "207717",
"createTime": "2021-03-23T03:37:00.000+00:00",
"updateTime": "2021-04-27T09:02:11.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
},
{
"id": 4,
"merchantName": "成都欧韵聚网络科技有限公司",
"cityName": "广州",
"parentId": null,
"status": 2,
"invitationCode": "859655",
"createTime": "2021-03-23T03:37:00.000+00:00",
"updateTime": "2021-05-10T03:22:48.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
}
]
5、findAll(Example<S> example, Pageable pageable)方法：构建example查询条件并进行分页查询
步骤1：Controller中代码
/**
* JPA <S extends T> Page<S> findAll(Example<S> example, Pageable pageable) 构建example查询条件，并进行分页查询
* note: page起始页 0
* @param page
* @param size
* @param merchantInfo
* @return
*/
@PostMapping("/findAllByPageAble/{page}/{size}")
@ApiOperation(value = "查询结果翻页处理")
public List<MerchantInfo> findAllByPageAble(@PathVariable("page") Integer page, @PathVariable("size") Integer size, @RequestBody MerchantInfo merchantInfo){
//根据前端传入的page和size，构建Pageable对象，用于后续的分页查询
Pageable pageable = PageRequest.of(page-1,size);
//根据前端传入的请求体构建Example查询条件
Example<MerchantInfo> merchantInfoExample = Example.of(merchantInfo);
Page<MerchantInfo> merchantInfoPage = merchantInfoJpaRepository.findAll(merchantInfoExample, pageable);
//获取对应页的数据内容
List<MerchantInfo> merchantInfoList = merchantInfoPage.getContent();
log.info("打印第{}页,每页数量{}条,\n该页查询结果为:{}",page,size,merchantInfoList);
return merchantInfoList;
}
步骤2：接口调用post：http://localhost:8080/findAllByPageAble/2/3parameters：
{
"cityName": "广州",
"remark": null,
"status": 2
}
步骤3：接口返回结果
[
{
"id": 12,
"merchantName": "中山市古镇超途餐饮服务部",
"cityName": "广州",
"parentId": null,
"status": 2,
"invitationCode": "689589",
"createTime": "2021-03-23T03:43:43.000+00:00",
"updateTime": "2021-05-10T03:22:34.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
},
{
"id": 13,
"merchantName": "广州骑士之家数字科技有限公司",
"cityName": "广州",
"parentId": null,
"status": 2,
"invitationCode": "513000",
"createTime": "2021-03-23T03:43:43.000+00:00",
"updateTime": "2021-04-27T06:46:52.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
},
{
"id": 16,
"merchantName": "六安市煜祥人力资源开发有限公司(深圳)",
"cityName": "广州",
"parentId": null,
"status": 2,
"invitationCode": "422057",
"createTime": "2021-03-23T03:43:43.000+00:00",
"updateTime": "2021-05-10T03:22:35.000+00:00",
"remark": null,
"principal": null,
"principalPhone": null
}
]
JPA新增、修改功能
1、save(S s)方法：可对传入的单个Entity对象映射到数据库表中实现新增数据或修改表数据功能
步骤1：Controller中代码
/**
* JPA <S extends T> S save(S s) save方法可是实现新增或修改功能
* @apiNote 传入一个Entity对象，若该对象中存在主键id，则是修改；若主键不存在，则是新增
* @param merchantInfo
* @return
*/
@PostMapping("/saveMerchantByEntity")
@ApiOperation(value = "新增或修改商户信息")
public MerchantInfo saveMerchantByEntity(@RequestBody MerchantInfo merchantInfo){
//判断主键是否存在，若存在，则修改该主键对应的表记录
if(merchantInfo.getId()!= null && merchantInfo.getId() > 0){
MerchantInfo merchantInfo1 = merchantInfoJpaRepository.findById(merchantInfo.getId()).get();
/**
* copyProperties(source,target,ignoreProperties) 将源对象属性值反射到目标对象对应的属性上，如果不加ignoreProperties参数
* 则源对象中属性值为null的值会覆盖掉目标对象中对应属性不为null的值，ignoreProperties参数可以指定源对象中不覆盖目标对象的属性。
* 如果源对象不存在某个属性，而目标对象存在某个属性，这种情况反射后，目标对象的该属性不会被覆盖
*/
BeanUtils.copyProperties(merchantInfo,merchantInfo1,new String[]{"createTime","invitationCode"});
merchantInfo1.setUpdateTime(new Date());
return merchantInfoJpaRepository.save(merchantInfo1);
}else{
//若主键id不存在，则新增一条记录到数据库表中
merchantInfo.setCreateTime(new Date());
merchantInfo.setUpdateTime(new Date());
MerchantInfo merchantInfo1 = merchantInfoJpaRepository.save(merchantInfo);
return merchantInfo1;
}
}
步骤2：接口调用post：http://localhost:8080/saveMerchantByEntityparameters 1：
//请求体中不存在id主键，因此是新增
{
"cityName": "大理01",
"invitationCode": "333333",
"merchantName": "大理测试商户05",
"principal": "马二麻子",
"principalPhone": "18900001111"
}
parameters 2：
//请求体中存在id主键54，因此是修改
{
"id":54,
"cityName": "大理01",
"invitationCode": "333333",
"merchantName": "大理测试商户099",
"principal": "马二麻子",
"principalPhone": "18900000000"
}
步骤3：接口返回结果response 1:
{
"id": 54,
"merchantName": "大理测试商户05",
"cityName": "大理01",
"parentId": null,
"status": null,
"invitationCode": "333333",
"createTime": "2021-05-12T08:56:25.763+00:00",
"updateTime": "2021-05-12T08:56:25.763+00:00",
"remark": null,
"principal": "马二麻子",
"principalPhone": "18900001111"
}
response 2:
{
"id": 54,
"merchantName": "大理测试商户099",
"cityName": "大理01",
"parentId": null,
"status": null,
"invitationCode": "333333",
"createTime": "2021-05-12T08:56:26.000+00:00",
"updateTime": "2021-05-12T09:01:40.810+00:00",
"remark": null,
"principal": "马二麻子",
"principalPhone": "18900000000"
}
Note：
a）接口传parameters 1，无id，则新增一条表记录，自增生成的主键ID值54，见response 1；
b）接口传parameters 2，有id，则修改该id对应的表记录，见response 1。
2、saveAll(Iterable<S> iterable)方法：可对传入的多个Entity对象集合映射到数据库表中实现批量新增数据或修改表数据功能
步骤1：Controller中代码
/**
* JPA <S extends T> List<S> saveAll(Iterable<S> iterable) saveAll方法可实现批量新增或修改功能
* @param merchantInfoList
* @return
*/
@PostMapping("/saveMerchantByEntityList")
@ApiOperation(value = "批量新增或修改商户信息")
public List<MerchantInfo> saveMerchantByEntityList(@RequestBody List<MerchantInfo> merchantInfoList){
//创建一个新List集合，装载处理后的Entity对象
List<MerchantInfo> updateMerchantList = new ArrayList<MerchantInfo>();
for (MerchantInfo merchantInfo : merchantInfoList){
if (merchantInfo.getId() != null && merchantInfo.getId() > 0){
//获取数据库中对应id的原始数据映射到entity
MerchantInfo merchantInfo1 = merchantInfoJpaRepository.findById(merchantInfo.getId()).get();
BeanUtils.copyProperties(merchantInfo,merchantInfo1,new String[]{"createTime","invitationCode"});
merchantInfo1.setUpdateTime(new Date());
updateMerchantList.add(merchantInfo1);
}else{
merchantInfo.setCreateTime(new Date());
merchantInfo.setUpdateTime(new Date());
updateMerchantList.add(merchantInfo);
}
}
log.info("打印处理后的MerchantInfo对象集合：\n{}",updateMerchantList);
//批量进行新增或修改
List<MerchantInfo> merchantInfoList1 = merchantInfoJpaRepository.saveAll(updateMerchantList);
return merchantInfoList1;
}
步骤2：接口调用post：http://localhost:8090/saveMerchantByEntityListparameters：
//参数数据中既有存在id的条目，又有不存在id的条目，有id的条目执行修改，无id的条目执行插入新增
[
{
"id":50,
"merchantName": "大理测试商户01",
"cityName": "大理01",
"status": 1,
"invitationCode": "888888",
"principal":"臧三",
"principalPhone":"13811112222"
},
{
"id":51,
"merchantName": "大理测试商户02",
"cityName": "大理01",
"status": 1,
"invitationCode": "888888",
"principal":"女四",
"principalPhone":"1381111333333"
},
{
"id":52,
"merchantName": "大理测试商户03",
"cityName": "大理01",
"status": 1,
"invitationCode": "999999",
"principal":"万五",
"principalPhone":"13811114444"
},
{
"merchantName": "大理测试商户04",
"cityName": "大理01",
"status": 1,
"invitationCode": "713713",
"principal":"万五赵六",
"principalPhone":"13811115555"
}
]
步骤3：接口返回结果
//前三条数据进行了修改，最后一条数据新增插入数据库中。
[
{
"id": 50,
"merchantName": "大理测试商户01",
"cityName": "大理01",
"parentId": null,
"status": 1,
"invitationCode": "111222",
"createTime": "2021-05-12T03:29:23.000+00:00",
"updateTime": "2021-05-12T03:43:45.732+00:00",
"remark": null,
"principal": "臧三",
"principalPhone": "13811112222"
},
{
"id": 51,
"merchantName": "大理测试商户02",
"cityName": "大理01",
"parentId": null,
"status": 1,
"invitationCode": "2222333",
"createTime": "2021-05-12T03:29:23.000+00:00",
"updateTime": "2021-05-12T03:43:45.845+00:00",
"remark": null,
"principal": "女四",
"principalPhone": "1381111333333"
},
{
"id": 52,
"merchantName": "大理测试商户03",
"cityName": "大理01",
"parentId": null,
"status": 1,
"invitationCode": "333444",
"createTime": "2021-05-12T03:29:23.000+00:00",
"updateTime": "2021-05-12T03:43:45.923+00:00",
"remark": null,
"principal": "万五",
"principalPhone": "13811114444"
},
{
"id": 53,
"merchantName": "大理测试商户04",
"cityName": "大理01",
"parentId": null,
"status": 1,
"invitationCode": "713713",
"createTime": "2021-05-12T03:43:45.923+00:00",
"updateTime": "2021-05-12T03:43:45.923+00:00",
"remark": null,
"principal": "万五赵六",
"principalPhone": "13811115555"
}
]
JPA删除功能1、deleteAll()方法：删除merchant_info表中所有数据
/**
* JPA deleteAll() 删除表中所有数据
* 删除对应数据库表中所有数据，谨慎操作
*/
@GetMapping("/deleteAllMerchantInfo")
@ApiOperation(value = "删除merchant_info表中所有数据")
public void deleteAllMerchantInfo(){
merchantInfoJpaRepository.deleteAll();
}
2、deleteById(Long)方法：删除merchant_info表中对应id的数据
/**
* JPA deleteById(Long) 删除对应id的表数据
* 删除数据库表中对应id的条目
* @param id
*/
@GetMapping("/deleteMerchantInfoById")
@ApiOperation(value = "删除merchant_info表中对应id的数据")
public void deleteMerchantInfoById(@RequestParam("id") Long id){
merchantInfoJpaRepository.deleteById(id);
}
3、delete(T t)方法：删除Entity对应的表数据
/**
* JPA delete(T t) 删除Entity对应的表数据
* @param merchantInfo
*/
@PostMapping("/deleteMerchantInfoByEntity")
@ApiOperation(value = "删除Entity对象映射对应的表数据")
public void deleteMerchantInfoByEntity(@RequestBody MerchantInfo merchantInfo){
log.info("打印请求体merchantInfo对象:{}",merchantInfo);
Example<MerchantInfo> merchantInfoExample = Example.of(merchantInfo);
MerchantInfo merchantInfo1 = merchantInfoJpaRepository.findOne(merchantInfoExample).get();
merchantInfoJpaRepository.delete(merchantInfo1);
}
4、deleteAll(Iterable<? extends T> iterable)方法：删除merchant_info表中多条记录
/**
* JPA deleteAll(Iterable<? extends T> iterable)
* @param merchantInfo
*/
@PostMapping("/deleteMerchantInfoByEntitysList")
@ApiOperation(value = "删除merchant_info表中多条记录")
public void deleteMerchantInfoByEntitysList(@RequestBody MerchantInfo merchantInfo){
Example<MerchantInfo> merchantInfoExample = Example.of(merchantInfo);
List<MerchantInfo> merchantInfoList = merchantInfoJpaRepository.findAll(merchantInfoExample);
merchantInfoJpaRepository.deleteAll(merchantInfoList);
}
附merchant_info表结构
CREATE TABLE `merchant_info` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
`merchant_name` varchar(150) DEFAULT NULL COMMENT '商户名称',
`city_name` varchar(255) DEFAULT NULL COMMENT '城市名称',
`parent_id` bigint DEFAULT NULL COMMENT '父商户Id',
`status` int DEFAULT '1' COMMENT '状态：1：有效 2：无效',
`invitation_code` varchar(64) DEFAULT NULL COMMENT '邀请码',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '修改时间',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
`principal` varchar(64) DEFAULT NULL COMMENT '负责人',
`principal_phone` varchar(64) DEFAULT NULL COMMENT '负责人电话',
PRIMARY KEY (`id`)
)