A. 多表复杂查询技巧

需要的类

• 实体类Entity
• 用于操作数据库的JPA Repository接口类
• domain接受查询参数的查询类
• Predicate类，用于生成查询条件
• Projection结果类，用于储存查询到的结果
• Service类，Controller用于执行查询并与前端进行交互

步骤

1.创建实体类及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中创建查询类:

@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中创建查询语句类：

@RequiredArgsConstructor
public class PersonSearchSpecification implements Specification<Person> { //设置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 创建联合查询结果类：

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来自Address类，需要标注
    String getZipCode();

    @Value("#{target.address.city}")  // city来自Address类，需要标注
    String getCity();
}

5.执行查询,首先使用findAll并传入Predicates将结果查出来，然后再使用ProjectionFactory将结果join好并村委PersonSearchResult对象列表进行返回：

@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. 简单多表联合查询

@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创建动态Query: https://www.baeldung.com/rest-api-search-language-spring-data-specifications
• JPA Projection： https://www.baeldung.com/spring-data-jpa-projections