在SpringBoot项目中使用JPA实现简单的数据库操作
程序员文章站
2022-07-03 15:54:36
...
集成准备
首先确定使用SpringData Jpa，那么集成工作如下
1. pom文件引入依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
</dependencies>2. application.yml文件中加入如下
spring:
datasource:
url: jdbc:mysql://localhost:3306/db01?characterEncoding=utf8&useSSL=false&rewriteBatchedStatements=true
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
jpa:
database: mysql
database-platform: org.hibernate.dialect.MySQL5Dialect
show-sql: true
hibernate:
ddl-auto: none其中hibernate.ddl-auto的值的含义如下
- create：每次运行程序时，都会重新创建表，故而数据会丢失
- create-drop：每次运行程序时会先创建表结构，然后待程序结束时清空表
- upadte：每次运行程序，没有表时会创建表，如果对象发生改变会更新表结构，原有数据不会清空，只会更新
- validate：运行程序会校验数据与数据库的字段类型是否相同，字段不同会报错
- none: 禁用DDL处理
只要这这两步，SpringData Jpa就集成到SpringBoot中了，非常简单
实现简单增删改查
首先在数据库建立一张表
CREATE TABLE
tb_user
(
id bigint(10) NOT NULL AUTO_INCREMENT,
user_id VARCHAR(20) COMMENT '用户号',
name VARCHAR(50) COMMENT '姓名',
cert_id VARCHAR(20) COMMENT '证件号',
cert_type VARCHAR(2) COMMENT '证件类型',
state VARCHAR(2) COMMENT '状态',
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
INDEX ap_kafka_nbr (user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci;然后创建一个对应的实体类User
package com.demo.bean;
import lombok.Data;
import javax.persistence.*;
import java.util.Date;
@Entity
@Table(name = "tb_user")
@Data
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private String id;
@Column(name = "user_id")
private String userId;
@Column(name = "name")
private String name;
@Column(name = "cert_id")
private String certId;
@Column(name = "cert_type")
private String certType;
@Column(name = "state")
private String state;
@Column(name = "create_time")
private Date createTime;
@Column(name = "update_time")
private Date updateTime;
}
接着写一个DAO接口，继承JpaRepository接口
public interface UserDao extends JpaRepository<User, String>{
}因为是简单的增删改查，所以Service层就不写了，直接在Controller层展示具体操作
package com.demo.controller;
import com.demo.bean.User;
import com.demo.mapper.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.Optional;
@RequestMapping("/test")
@RestController
public class UserController {
@Autowired
private UserDao dao;
@PostMapping("/save")
public User save(@RequestBody User user) {
return dao.save(user);
}
@RequestMapping("/delete/{id}")
public void deleteById(@PathVariable("id") String id) {
dao.deleteById(id);
}
@RequestMapping("/update/{id}")
public User update(@PathVariable("id") String userId, @RequestBody User user) {
user.setId(userId);
return dao.saveAndFlush(user);
}
@RequestMapping("/search/{id}")
public User getUserInfo(@PathVariable("id") String userId) {
Optional<User> optional = dao.findById(userId);
return optional.orElseGet(User::new);
}
}
这样简单的CURD就实现了，但是如何实现条件查询呢，比如我想通过用户的名字查询出所有叫做张三的用户的信息？
实现条件查询
首先我们创建一个UserSearchDao的查询接口，继承JpaRepository和JpaSpecificationExecutor接口
public interface UserSearchDao extends JpaRepository<User, String>, JpaSpecificationExecutor<User> {
}注意JpaSpecificationExecutor接口不能单独使用，需要配合其他Jpa接口一起使用
具体实现
package com.demo.controller;
import com.demo.bean.User;
import com.demo.mapper.UserSearchDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
@RequestMapping("/searchTest")
@RestController
public class UserSearchController {
@Autowired
private UserSearchDao searchDao;
/**
* 单条件查询
*/
@PostMapping("/searchByName")
public void searchByName(@RequestBody String name) {
Specification<User> spec = new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
Predicate pre = builder.equal(root.get(name), "张三");
return pre;
}
};
List<User> userList = searchDao.findAll(spec);
for (User user1 : userList) {
System.out.println(user1);
}
}
/**
* 多条件查询方式1
*/
@PostMapping("/searchByNameAndState")
public void searchByNameAndState(@RequestBody User user) {
Specification<User> spec = new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> list = new ArrayList<>();
list.add(builder.equal(root.get(user.getName()), "张三"));
list.add(builder.equal(root.get(user.getState()), "0"));
Predicate[] arr = new Predicate[list.size()];
Predicate pre = builder.and(list.toArray(arr));
return pre;
}
};
List<User> userList = searchDao.findAll(spec);
for (User user1 : userList) {
System.out.println(user1);
}
}
/**
* 多条件查询方式2
*/
@PostMapping("/searchByStateAndName")
public void searchByStateAndName(@RequestBody User user) {
Specification<User> spec = new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
Predicate pre = builder.and(builder.equal(root.get(user.getState()), "0"),
builder.equal(root.get(user.getName()), "张三"));
return pre;
}
};
List<User> userList = searchDao.findAll(spec);
for (User user1 : userList) {
System.out.println(user1);
}
}
}
上面的代码可以通过lambda表达式简化为
@RequestMapping("/searchTest")
@RestController
public class UserSearchController {
@Autowired
private UserSearchDao searchDao;
/**
* 单条件查询
*/
@PostMapping("/searchByName")
public void searchByName(@RequestBody String name) {
Specification<User> spec = (Specification<User>) (root, query, builder) -> {
return builder.equal(root.get(name), "张三");
};
List<User> userList = searchDao.findAll(spec);
for (User user1 : userList) {
System.out.println(user1);
}
}
/**
* 多条件查询方式1
*/
@PostMapping("/searchByNameAndState")
public void searchByNameAndState(@RequestBody User user) {
Specification<User> spec = (Specification<User>) (root, query, builder) -> {
List<Predicate> list = new ArrayList<>();
list.add(builder.equal(root.get(user.getName()), "张三"));
list.add(builder.equal(root.get(user.getState()), "0"));
Predicate[] arr = new Predicate[list.size()];
return builder.and(list.toArray(arr));
};
List<User> userList = searchDao.findAll(spec);
for (User user1 : userList) {
System.out.println(user1);
}
}
/**
* 多条件查询方式2
*/
@PostMapping("/searchByStateAndName")
public void searchByStateAndName(@RequestBody User user) {
Specification<User> spec = (Specification<User>) (root, criteriaQuery, builder) ->
builder.and(builder.equal(root.get(user.getState()), "0"),
builder.equal(root.get(user.getName()), "张三"));
List<User> userList = searchDao.findAll(spec);
for (User user1 : userList) {
System.out.println(user1);
}
}
}这样看起来代码就简洁多了