A. 多表復(fù)雜查詢技巧
需要的類
- 實(shí)體類Entity
- 用于操作數(shù)據(jù)庫(kù)的JPA Repository接口類
- domain接受查詢參數(shù)的查詢類
- Predicate類拨齐，用于生成查詢條件
- Projection結(jié)果類挺尿，用于儲(chǔ)存查詢到的結(jié)果
- Service類，Controller用于執(zhí)行查詢并與前端進(jìn)行交互
步驟
1.創(chuàng)建實(shí)體類及Repository類:
@Entity
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "person")
@Builder
public class Person {
@Id
@Column(name = "id")
private Long id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@Column(name = "birthday")
private LocalDate birthday;
@Column(name = "age")
private int age;
@OneToOne(mappedBy = "person")
// @OneToMany(mappedBy = "person", cascade = CascadeType.PERSIST)
private Address address;
}
@Entity
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "address")
@Builder
public class Address {
@Id
@Column(name = "id")
private Long id;
@OneToOne
// @ManyToOne
// @JoinColumn(name = "id")
private Person person;
@Column(name = "state")
private String state;
@Column(name = "city")
private String city;
@Column(name = "street")
private String street;
@Column(name = "zip_code")
private String zipCode;
}
public interface PersonRepository extends JpaRepository<Person, Long> {
}
public interface AddressRepository extends JpaRepository<Person, Long> {
}
2.在domain package中創(chuàng)建查詢類:
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Slf4j
public class PersonSearch implements Serializable {
private String firstName;
private String lastName;
private String zipCode;
private int ageFrom;
private int ageTo;
}
3.在repository package中創(chuàng)建查詢語(yǔ)句類：
@RequiredArgsConstructor
public class PersonSearchSpecification implements Specification<Person> { //設(shè)置Root類型
private final PersonSearch personSearch;
@Override
public Predicate toPredicate(
@NonNull Root<Person> personRoot,
@NonNull CriteriaQuery<?> query,
@NonNull CriteriaBuilder builder
) {
List<Predicate> predicates = new ArrayList<>();
Path<Adresse> addressRoot = personRoot.get("address");
if (personSearch.getLastName() != null) {
predicates.add(isLike(builder, personRoot.get("last_name"), personSearch.getLastName()));
}
if (personSearch.getZipCode() != null) {
predicates.add(isLike(builder, addressRoot.get("zip_code"), personSearch.getZipCode()));
}
addTimePredicate(personRoot.get("age"),
personSearch.getAgeFrom(), personSearch.getAgeTo(),
builder, predicates);
return builder.and(predicates.toArray(new Predicate[0]));
}
private Predicate isLike(CriteriaBuilder builder, Path<?> path, String value) {
return builder.like(
builder.lower(path.as(String.class)),
"%" + value.toLowerCase() + "%"
);
}
private void addTimePredicate(Path<LocalDate> dateField, LocalDate from, LocalDate to,
CriteriaBuilder builder, List<Predicate> predicates) {
if (from == null && to == null) {
return;
}
if (from != null) {
if (to != null) {
predicates.add(builder.between(dateField, from, to));
} else {
predicates.add(builder.greaterThanOrEqualTo(dateField, from));
}
} else {
predicates.add(builder.lessThanOrEqualTo(dateField, to));
}
}
}
4.使用JPA Projection 創(chuàng)建聯(lián)合查詢結(jié)果類：
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonInclude.Include;
import org.springframework.beans.factory.annotation.Value;
@JsonInclude(Include.NON_NULL)
public interface PersonSearchResult {
String getFirstName();
String getLastName();
@Value("#{target.address.zipCode}") // zipCode來(lái)自Address類窄俏，需要標(biāo)注
String getZipCode();
@Value("#{target.address.city}") // city來(lái)自Address類碘菜，需要標(biāo)注
String getCity();
}
5.執(zhí)行查詢,首先使用findAll并傳入Predicates將結(jié)果查出來(lái)忍啸，然后再使用ProjectionFactory將結(jié)果join好并村委PersonSearchResult對(duì)象列表進(jìn)行返回：
@Service
@RequiredArgsConstructor
@Slf4j
public class PersonSearchService {
private final PersonRepository countryDataRepository;
private final ProjectionFactory projectionFactory;
public List<SearchResult> findSearchResults(PersonSearch personSearch) {
PersonSearchSpecification searchSpecification = new PersonSearchSpecification(personSearch);
List<Person> personList = countryDataRepository.findAll(searchSpecification);
return personList.stream()
.map(person -> projectionFactory.createProjection(PersonSearchResult.class, person))
.collect(Collectors.toList());
}
}
B. 簡(jiǎn)單多表聯(lián)合查詢
@JsonInclude(Include.NON_NULL)
public interface PersonView {
String getFirstName();
String getLastName();
}
@JsonInclude(Include.NON_NULL)
public interface AddressResult {
@JsonUnwrapped
PersonView getPerson();
String getZipCode();
String getCity();
}
@Repository
public interface AddressRepository extends JpaRepository<Address, Long>,
JpaSpecificationExecutor<Address> {
Optional<AddressResult> findByPersonIdAndZipCode(Long id, String zipCode);
}
@Service
@RequiredArgsConstructor
public class AddressResultService {
private final AddressRepository addressRepository;
public Optional<AddressResult> findAddressResult(Long id, String zipCode) {
return addressRepository
.findByPersonIdAndZipCode(id, zipCode);
}
}
References
- One-to-Many: https://www.baeldung.com/hibernate-one-to-many
- 使用JPA創(chuàng)建動(dòng)態(tài)Query: https://www.baeldung.com/rest-api-search-language-spring-data-specifications
- JPA Projection： https://www.baeldung.com/spring-data-jpa-projections
