Spring Data JPA入门
816人浏览 / 0人评论
在*Reposiory.java中自定义方法
注意：如果自己写sql语句，那么有些拦截器可能并不能起作用，如@PreUpdate
示例一：
@Repository
public interface OrganizationRepository extends JpaRepository<Organization, Integer>, JpaSpecificationExecutor<Organization> {
List<Organization> findAllByDamId(Integer damId);
Organization findByDamIdAndOrgType(Integer damId, Integer orgType);
List<OrganizationPerson> findAllByLayerIdIn(Collection<Integer> id);
int countByOrganizationId(Integer organizationId);
@Modifying
@Transactional
void deleteAllByLayerIdIn(Set<Integer> layerIds);
@Query(value = "update Organization f set f.deleted = 1,f.updateTime = ?1 where f.id in (?2)")
@Modifying
@Transactional(rollbackFor = {Exception.class})
void deleteDirectory(LocalDateTime date,List<Integer> ids);
@Query(value = "select f from Organization f where f.deleted = 0 and f.fatherId = ?1")
List<Organization> listByFatherId(Integer fatherId);
@Query(value = "select new Organization(id,damId,name,fatherId,describe,remark,deleted) from Organization f where f.deleted = 0 and f.fatherId is null and f.damId =?1")
List<Organization> getTopLevel(Integer damId);
//@Query(value = "select new Organization(id,damId,name,fatherId,describe,remark,deleted) from Organization f where f.deleted = 0 and f.fatherId is null and f.damId =?1")
@Query(value = "select f from OrganizationDTO f where f.deleted = 0 and f.fatherId is null and f.damId =?1")
List<Organization> getTopLevel2(Integer damId);
}getTopLevel()方法中“new FloodPreventionOrganization(id,…..)”中的数据要与FloodPreventionOrganization实体类中对应的构造方法的参数一一对应，Ctrl + 左键此对象会跳转到该构造方法。但是这种方式查出来的数据返回给前端时没有包含的字段会全部置为null，所以构造dto类查询，即getTopLevel2()中的写法比较好。
示例二：
@Repository
public interface EntryRepository extends JpaRepository<Entry, Long> {
@Modifying
@Transactional(rollbackFor = {Exception.class})
@Query("update School s set s.name = :#{#school.name} where s.id = :#{#school.id}")
void updateName(@Param("school") School school);
}常用方法
// saveAndFlush()(推荐)
School school = schoolRepository.saveAndFlush(school);
// save()(不推荐，高并发下，使用save()无论怎么同步锁都会出现数据误差推断该方法具有延迟性。)
School school = schoolRepository.save(school);
// saveAll()
schoolRepository.saveAll(list);
// 解决插入大量数据时速度过慢的问题(未验证)：
spring：
jpa:
properties:
hibernate:
jdbc:
batch_size: 500 #每次插入数量
batch_versioned_data: true
order_inserts: true
order_updates: true
// deleteAllInBatch()
schoolRepository.deleteAllInBatch();
// deleteInBatch()(部分批量删除，list集合内数据不宜过多，否则会报堆栈溢出异常)
schoolRepository.deleteInBatch(list);
// deleteAll()(先使用findAll()查出全部，再每条数据执行一次删除语句，效率低)
schoolRepository.deleteAll();
// findById(推荐)
School school = schoolRepository.findById(id).get();
// getOne(不推荐，仅使用getOne()做查询操作没有问题，但是用getOne()查询得到的数据做更新操作会报异常：org.hibernate.LazyInitializationException: could not initialize proxy - no Session，因为getOne()方法返回的是实体对象的代理对象（a reference）。)
School school = schoolRepository.getOne(id);
// 指定条件查询
School school = new School();
school.setName("张三");
school.setAge(16);
Example<School> example = Example.of(school);
Optional<School> optional = schoolRepository.findOne(example);
School result = optional.get();
List<School> all = schoolRepository.findAll(example);
// findOne()方法会返回一个Optional<T>对象，isPresent()方法返回Optional对象是否为null。
boolean bl= schoolRepository.findOne(example).isPresent();
// get()方法返回当前对象
School school = schoolRepository.findOne(example).get();多表关联查询
一对一（方式一）
Person.java
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.io.Serializable;
@Entity
@Data
@Table(name = "person")
@NoArgsConstructor
@AllArgsConstructor
public class Person implements Serializable {
private static final long serialVersionUID = 1;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String firstName;
private String lastName;
// 数据库person表中应含有关联字段address_id
@OneToOne
@JoinColumn(name = "address_id")
private Address address;
public Person(String firstName, String lastName, Address address) {
this.firstName = firstName;
this.lastName = lastName;
this.address = address;
}
}Address.java
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.io.Serializable;
/**
* @Author 风仔
* @Date 2022/6/21
* @Version 1.0
* @Description:
**/
@Entity
@Table(name = "address")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Address implements Serializable {
private static final long serialVersionUID = 1;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String state;
private String city;
private String street;
private String zipCode;
public Address(String state, String city, String street, String zipCode) {
this.state = state;
this.city = city;
this.street = street;
this.zipCode = zipCode;
}
}PersonReposiroty.java
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface PersonRepository extends JpaRepository<Person, Integer> {
}AddressRepository.java
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface AddressRepository extends JpaRepository<Address, Integer> {
}测试
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
@RunWith(SpringRunner.class)
@SpringBootTest
class SpringDataJpaApplicationTests {
@Resource
PersonRepository personRepository;
@Resource
AddressRepository addressRepository;
@BeforeEach
public void init() {
Address address = addressRepository.save(new Address("11", "12", "13", "14"));
personRepository.save(new Person("21","22",address));
}
@AfterEach
public void deleteAll() {
// 删除时必须先删主表，因为jpa在删除之前会查一遍，找不到关联的子表会报错
personRepository.deleteAll();
addressRepository.deleteAll();
}
@Test
void contextLoads() {
System.err.println(personRepository.findAll());
}
}一对一（方式二）
参考：https://blog.csdn.net/johnf_nash/article/details/80587204
多表查询在spring data jpa中有两种实现方式，第一种是利用hibernate的级联查询来实现，第二种是创建一个结果集的接口来接收连表查询后的结果，这里介绍第二种方式。
实体 UserInfo ：用户。
实体 Address：家庭住址。
这里通过外键的方式(一个实体通过外键关联到另一个实体的主键)来实现一对一关联。
User.java
@Entity
@Table(name = "user")
@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
private static final long serialVersionUID = 1;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer userId;
private String name;
private int age;
private String sex;
private String email;
// 与 Address 关联
private Integer addressId;
public User(String name, int age, String sex, String email, Integer addressId) {
this.name = name;
this.age = age;
this.sex = sex;
this.email = email;
this.addressId = addressId;
}
}Address.java
@Entity
@Table(name = "address")
@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Address implements Serializable {
private static final long serialVersionUID = 1;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer addressId;
private String areaCode;
private String country;
private String province;
private String city;
private String area;
private String detailAddress;
public Address(String areaCode, String country, String province, String city, String area, String detailAddress) {
this.areaCode = areaCode;
this.country = country;
this.province = province;
this.city = city;
this.area = area;
this.detailAddress = detailAddress;
}
}UserVO.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserVO implements Serializable {
private static final long serialVersionUID = 1L;
private User user;
private Address address;
}UserRepository.java
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
@Query(value = "SELECT new com.jbritian.springdatajpa.domain.vo.UserVO(u,a) FROM User u, Address a WHERE u.addressId = a.addressId")
List<UserVO> findUserVO();
}AddressRepository.java
@Repository
public interface AddressRepository extends JpaRepository<Address, Integer> {
}测试
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
@RunWith(SpringRunner.class)
@SpringBootTest
class SpringDataJpaApplicationTests {
@Resource
UserRepository userRepository;
@Resource
AddressRepository addressRepository;
@BeforeEach
public void init() {
Address addr1 = new Address("027","CN","HuBei", "WuHan","WuChang", "123 street");
Address addr2 = new Address("023","CN","ChongQing", "ChongQing","YuBei", "123 road");
addressRepository.save(addr1);
addressRepository.save(addr2);
User user1 = new User("ZS", 21,"Male","123@xx.com", addr1.getAddressId());
User user2 = new User("Ww", 25,"Male","234@xx.com", addr2.getAddressId());
userRepository.save(user1);
userRepository.save(user2);
}
@AfterEach
public void deleteAll() {
userRepository.deleteAll();
addressRepository.deleteAll();
}
@Test
void contextLoads() {
System.err.println(userRepository.findUserVO());
}
}一对多
Dict.java
@Entity
@Getter // 不要使用@Data或@ToString注解，不然会报堆栈溢出
@Setter
@Table(name="dict")
@AllArgsConstructor
@NoArgsConstructor
public class Dict implements Serializable {
private static final long serialVersionUID = 1;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
/**
* CascadeType.PERSIST：级联保存，当Dict实体做保存操作时，如果dictDetails集合中有数据，则一并保存；
* CascadeType.REMOVE：级联删除，当Dict实体做删除操作时，如果DictDetail表中有与之关联的数据，则一并删除；
* CascadeType.MERGE：级联更新（合并）
* CascadeType.DETACH：级联脱管/游离操作，如果要删除一个实体，但是它有外键无法删除，就需要使用这个级联权限，它会撤销所有相关的外键关联；
* CascadeType.REFRESH：级联刷新，假设场景 有一个订单,订单里面关联了许多商品,这个订单可以被很多人操作,那么这个时候A对此订单和关联的商品进行了修改,与此同时,B也进行了相同的操作,但是B先一步比A保存了数据,那么当A保存数据的时候,就需要先刷新订单信息及关联的商品信息后,再将订单及商品保存；
* CascadeType.ALL：拥有以上所有级联操作权限。
*/
@JoinColumn(name = "dict_id")
@OneToMany(cascade = {CascadeType.PERSIST,CascadeType.REMOVE})
private List<DictDetail> dictDetails;
private String name;
private String description;
public Dict(List<DictDetail> dictDetails, String name, String description) {
this.dictDetails = dictDetails;
this.name = name;
this.description = description;
}
}DictDetail.java
@Entity
@Getter // 不要使用@Data或@ToString注解，不然会报堆栈溢出
@Setter
@Table(name="dict_detail")
@NoArgsConstructor
@AllArgsConstructor
public class DictDetail implements Serializable {
private static final long serialVersionUID = 1;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
// dict_detail表中应含有dict_id字段
// 此注解和dict属性可不加，不加的话是单向关联，加上就是双向关联
// 使用懒加载可能会报 no Session 错误，解决方法是：1 方法上添加@Transactional开启事务管理。2 spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
@ManyToOne(fetch=FetchType.LAZY)
private Dict dict;
private String label;
private String value;
public DictDetail(String label, String value) {
this.label = label;
this.value = value;
}
}DictRepository.java
@Repository
public interface DictRepository extends JpaRepository<Dict, Integer> {
}DictDetailRepository.java
@Repository
public interface DictDetailRepository extends JpaRepository<DictDetail, Integer> {
}测试
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
class SpringDataJpaApplicationTests {
@Resource
DictRepository dictRepository;
@Resource
DictDetailRepository detailRepository;
@BeforeEach
public void init() {
Dict dict = dictRepository.save(new Dict("用户状态", "用户状态"));
List<DictDetail> dictDetails = Lists.newArrayList(
new DictDetail(dict,"11", "12"),
new DictDetail(dict,"21", "22"),
new DictDetail(dict,"31", "32")
);
detailRepository.saveAll(dictDetails);
// 这种方式添加的三条 DictDetail 数据表中 dict_id 都是null
//List<DictDetail> dictDetails = Lists.newArrayList(
// new DictDetail("11", "12"),
// new DictDetail("21", "22"),
// new DictDetail("31", "32")
//);
//dictRepository.save(new Dict(dictDetails,"用户状态", "用户状态"));
}
@AfterEach
public void deleteAll() {
// 因为添加了级联删除权限，所以删除dict表中的数据时，会同步删除dict_detail表中关联的数据。
dictRepository.deleteAll();
}
@Test
void contextLoads() {
List<Dict> dicts = dictRepository.findAll();
dicts.forEach(dict -> System.err.println(dict.getName()));
List<DictDetail> dictDetails = dicts.get(0).getDictDetails();
dictDetails.forEach(dictDetail -> System.err.println(dictDetail.getLabel()));
}
}多对多
User.java
@Entity
@Getter
@Setter
@Table(name="user")
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
// users_roles是中间表，字段为user_id和role_id，并且都是主键
@ManyToMany
@JoinTable(name = "users_roles",
joinColumns = {@JoinColumn(name = "user_id",referencedColumnName = "id")},
inverseJoinColumns = {@JoinColumn(name = "role_id",referencedColumnName = "id")})
private Set<Role> roles;
private String name;
public User(String name) {
this.name = name;
}
public User(Set<Role> roles, String name) {
this.roles = roles;
this.name = name;
}
}Role.java
@Entity
@Getter
@Setter
@Table(name="role")
@AllArgsConstructor
@NoArgsConstructor
public class Role implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@JSONField(serialize = false)
@ManyToMany(mappedBy = "roles")
private Set<User> users;
private String name;
public Role(String name) {
this.name = name;
}
}UserRepository.java
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
}RoleRepository.java
@Repository
public interface RoleRepository extends JpaRepository<Role, Integer> {
}测试
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
@RunWith(SpringRunner.class)
@SpringBootTest
class SpringDataJpaApplicationTests {
// many2many
@Resource
UserRepository userRepository;
@Resource
RoleRepository roleRepository;
@BeforeEach
public void init() {
List<Role> dictDetails = Lists.newArrayList(
new Role( "增"),
new Role( "删"),
new Role( "改"),
new Role( "查")
);
Set<Role> roles = new HashSet<>(roleRepository.saveAll(dictDetails));
userRepository.save(new User(roles, "admin"));
}
@AfterEach
public void deleteAll() {
userRepository.deleteAll();
roleRepository.deleteAll();
}
@Test
void contextLoads() {
List<User> users = userRepository.findAll();
users.forEach(user -> System.err.println(user.getName()));
Set<Role> roles = users.get(0).getRoles();
roles.forEach(role -> System.err.println(role.getName()));
}
}自关联
Area.java
@Entity
@Getter
@Setter
@Table(name="area")
@AllArgsConstructor
@NoArgsConstructor
public class Area implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
// area 表中应含有 parent_id 字段
@JoinColumn(name = "parent_id")
@ManyToOne(fetch = FetchType.LAZY)
@JsonIgnore
private Area parent;
// 子区域，一个区域信息可以有多级子区域，比如 : 广东省 - 广州市 - 天河区
@OneToMany(mappedBy = "parent", fetch = FetchType.LAZY)
private List<Area> children;
}AreaRepository.java
@Repository
public interface AreaRepository extends JpaRepository<Area, Integer> {
Area findAllByName(String name);
List<Area> findAllByParentId(Integer parentId);
}测试
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.CollectionUtils;
import javax.annotation.Resource;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
class SpringDataJpaApplicationTests {
// 自关联
@Resource
AreaRepository areaRepository;
@BeforeEach
public void init() {
// 广东省 (顶级区域)
Area guangdong = new Area();
guangdong.setName("广东省");
areaRepository.save(guangdong);
//广东省 下面的 广州市(二级区域)
Area guangzhou = new Area();
guangzhou.setName("广州市");
guangzhou.setParent(guangdong);
areaRepository.save(guangzhou);
//广州市 下面的 天河区(三级区域)
Area tianhe = new Area();
tianhe.setName("天河区");
tianhe.setParent(guangzhou);
areaRepository.save(tianhe);
//广东省 下面的 湛江市(二级区域)
Area zhanjiang = new Area();
zhanjiang.setName("湛江市");
zhanjiang.setParent(guangdong);
areaRepository.save(zhanjiang);
//湛江市 下面的 霞山区(三级区域)
Area xiashan = new Area();
xiashan.setName("霞山区");
xiashan.setParent(zhanjiang);
areaRepository.save(xiashan);
}
@AfterEach
public void deleteAll() {
areaRepository.deleteAll();
}
@Test
void contextLoads() {
Area area = areaRepository.findAllByName("广东省");
System.err.println(area.getName());
if(!CollectionUtils.isEmpty(area.getChildren())){
expandTree(area.getChildren());
}
}
void expandTree(List<Area> areaList){
areaList.forEach(area -> {
System.err.println(area.getName());
List<Area> areas = areaRepository.findAllByParentId(area.getId());
if(!CollectionUtils.isEmpty(areas)){
expandTree(areas);
}
});
}
}解决N+1问题
Category.java
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Set;
/**
* @Author 风仔
* @Date 2022/6/23
* @Version 1.0
* @Description:
* 注解@NamedEntityGraph :注解在实体上 , 解决典型的N+1问题；
* name表示实体图名, 与 repository中的注解 @EntityGraph的value属性相对应；
* attributeNodes 表示被标注要懒加载的属性节点 比如此例中 : 要懒加载的子分类集合children
**/
@Entity
@Getter // 不要使用@Data或@ToString注解，不然会报堆栈溢出
@Setter
@Table(name="category")
@AllArgsConstructor
@NoArgsConstructor
@NamedEntityGraph(name = "Category.Graph", attributeNodes = {@NamedAttributeNode("children")})
public class Category implements Serializable {
private static final long serialVersionUID = 1;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
/**
* 一个商品分类下面可能有多个商品子分类(多级) 比如 分类 : 家用电器 (子)分类 : 电脑 (孙)子分类 : 笔记本电脑
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "parent_id")
@JsonIgnore
private Category parent;
@OneToMany(mappedBy = "parent", fetch = FetchType.LAZY)
private Set<Category> children;
}CategoryRepository.java
@Repository
public interface CategoryRepository extends JpaRepository<Category, Integer> {
// 解决 懒加载 JPA 典型的 N + 1 问题
@EntityGraph(value = "Category.Graph", type = EntityGraph.EntityGraphType.FETCH)
List<Category> findAll();
}测试
import com.alibaba.fastjson.JSONArray;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
class SpringDataJpaApplicationTests {
// 解决jpa懒加载典型的N+1问题
@Resource
CategoryRepository categoryRepository;
@BeforeEach
public void init() {
//一个 家用电器分类(顶级分类)
Category appliance = new Category();
appliance.setName("家用电器");
categoryRepository.save(appliance);
//家用电器 下面的 电脑分类(二级分类)
Category computer = new Category();
computer.setName("电脑");
computer.setParent(appliance);
categoryRepository.save(computer);
//电脑 下面的 笔记本电脑分类(三级分类)
Category notebook = new Category();
notebook.setName("笔记本电脑");
notebook.setParent(computer);
categoryRepository.save(notebook);
//家用电器 下面的 手机分类(二级分类)
Category mobile = new Category();
mobile.setName("手机");
mobile.setParent(appliance);
categoryRepository.save(mobile);
//手机 下面的 智能机 / 老人机(三级分类)
Category smartPhone = new Category();
smartPhone.setName("智能机");
smartPhone.setParent(mobile);
categoryRepository.save(smartPhone);
Category oldPhone = new Category();
oldPhone.setName("老人机");
oldPhone.setParent(mobile);
categoryRepository.save(oldPhone);
}
@AfterEach
public void deleteAll() {
categoryRepository.deleteAll();
}
@Test
void contextLoads() {
List<Category> categories = categoryRepository.findAll();
categories.forEach(category -> System.out.println(JSONArray.toJSONString(category)));
}
}specification的基本使用
参考：https://blog.csdn.net/qq_44766883/article/details/107130396
常用方法
cb.and(root.get("id").in(ids));//添加一个条件
cb.or(条件1,条件2);//从两个条件中选一个
cb.equal(root.get("name"),name);//等于
cb.notEqual(root.get("name"),name);//不等于
cb.greaterThanOrEqualTo(root.get("age"),age);//大于或等于
cb.like(root.get("keyword"),"%"+keyword+"%");//模糊查询
cb.isNull(root.get("name"));//空
cb.not(root.get("id").in(ids));//非，相当于!
cb.asc(root.get("id"));//升序
cb.desc(root.get("id"));//降序
cb.selectCase().when(root.get("id").in(ids), 1).otherwise(0);//定义排序方式，当 id 在集合 ids 中时排在前面示例
@Entity
@Data
@Table(name="query_test")
@AllArgsConstructor
@NoArgsConstructor
public class QueryTest implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private Integer age;
private Timestamp birthday;
private String address;
public QueryTest(String name, Integer age, Timestamp birthday, String address) {
this.name = name;
this.age = age;
this.birthday = birthday;
this.address = address;
}
}
@Repository
public interface QueryTestRepository extends JpaRepository<QueryTest, Integer>, JpaSpecificationExecutor<QueryTest> {
List<QueryTest> findAll(Specification<QueryTest> specification);
}
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.CollectionUtils;
import javax.annotation.Resource;
import javax.persistence.criteria.Order;
import javax.persistence.criteria.Predicate;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
class SpringDataJpaApplicationTests {
@Resource
QueryTestRepository testRepository;
@BeforeEach
public void init() {
List<QueryTest> list = Lists.newArrayList(
new QueryTest("李1风", 12, Timestamp.valueOf("2020-01-11 02:33:12"), "李家庄"),
new QueryTest("王2牛", 13, Timestamp.valueOf("2020-02-01 02:33:12"), "王家庄"),
new QueryTest("赵1依", 8, Timestamp.valueOf("2020-01-01 02:33:12"), "赵家庄"),
new QueryTest("周3山", 19, Timestamp.valueOf("2020-01-01 02:33:12"), "周家庄"),
new QueryTest("吴5舞", 2, Timestamp.valueOf("2020-01-08 02:33:12"), "吴家庄"),
new QueryTest("江1然", 16, Timestamp.valueOf("2020-01-07 02:33:12"), "江家庄")
);
testRepository.saveAll(list);
}
@AfterEach
public void deleteAll() {
testRepository.deleteAll();
}
@Test
void contextLoads() {
int age = 10;
String order = "desc";
List<Integer> ids = Lists.newArrayList(1, 2, 3);
List<Integer> overIds = Lists.newArrayList(1, 2, 3, 4, 5);
// 使用specification查询
Specification<QueryTest> specification = (root, query, cb) -> {
List<Predicate> predicates = Lists.newArrayList();
//predicates.add(cb.and(root.get("id").in(ids)));
predicates.add(cb.like(root.get("name"), "%1%"));
predicates.add(cb.greaterThanOrEqualTo(root.get("age"), age));
List<Order> orders = new ArrayList<>();
if (!CollectionUtils.isEmpty(overIds)) {
orders.add(cb.desc(cb.selectCase().when(root.get("id").in(overIds), 1).otherwise(0)));
}
if (StringUtils.isNotEmpty(order)) {
if ("asc".equalsIgnoreCase(order)) {
//降序
orders.add(cb.asc(root.get("birthday")));
} else if ("desc".equalsIgnoreCase(order)) {
// 升序
orders.add(cb.desc(root.get("birthday")));
}
} else {
// 默认排序
orders.add(cb.desc(root.get("birthday")));
}
query.where(predicates.toArray(new Predicate[predicates.size()]));
query.orderBy(orders);
return query.getRestriction();
};
testRepository.findAll(specification).forEach(System.err::println);
}
}拼接子查询
public static Specification<CatDomain> listAdvanceSpec() {
return (Root<CatDomain> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
//创建主查询条件对象('where'后的语句对象)
Predicate predicate = cb.conjunction()；
//创建子查询对象subQuery Subquery subQuery = query.subquery(String.class);
//创建"实体类Hobby"的root对象
Subquery subQuery = query.subquery(String.class);
Root from = subQuery.from(IntentionRecord.class);
//创建子查询条件对象('where'后的语句对象)
Predicate predicate1 = cb.conjunction();
predicate1 = cb.and(predicate1,cb.greaterThanOrEqualTo(from.get("createTime"), newDate2));
predicate1 = cb.and(predicate1,cb.equal(from.get("schoolId"), dto.getSchoolId()));
//完成子查询
subQuery.select(from.get("studentId")).where(predicate1);
//把子查询结果拼接到原查询语句后面---这里是id not in的写法
predicate = cb.and(predicate, cb.not(cb.in(root.get("id")).value(subQuery)));
//id in的写法
predicate = cb.and(predicate, cb.in(root.get("id")).value(subQuery));
//id equals的写法
, cb.equal(root.get("id"),"123"));
};
}QueryDSL的基本使用
官网：https://querydsl.com/static/querydsl/4.1.3/reference/html_single/
import javax.persistence.EntityManager;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Autowired
private EntityManager em;
BooleanBuilder builder = new BooleanBuilder();
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery<SafetyProblem> problemJPAQuery = queryFactory.select(Projections.bean(SafetyProblem.class,
QSafetyProblem.safetyProblem.id,
QSafetyProblem.safetyProblem.name,
QSafetyProblem.safetyProblem.damId,
QSafetyProblem.safetyProblem.defectStatusStr,
QSafetyProblem.safetyProblem.source,
QSafetyProblem.safetyProblem.problemTypeOther,
QSafetyProblem.safetyProblem.problemTypeIds,
QSafetyProblem.safetyProblem.statusStartTime,
QSafetyProblem.safetyProblem.levelSorted,
QSafetyProblem.safetyProblem.firstLiableMan,
QSafetyProblem.safetyProblem.createdDate,
QSafetyProblem.safetyProblem.disposalManId
))
.from(QSafetyProblem.safetyProblem)
.where(builder.and(QSafetyProblem.safetyProblem.damId.eq(damId).and(QSafetyProblem.safetyProblem.deleteStatus.isNull())))
.fetchAll();
List<SafetyProblem> safetyProblems = problemJPAQuery.fetch();问题
findAll()、findById()等方法查询的结果执行set()后自动保存到数据库
https://blog.csdn.net/qq_31363843/article/details/128018411
设置为只读事务：@Transactional(rollbackFor = Exception.class, readOnly = true)。
豫公网安备 41010702003051号
全部评论