JPA各种姿势查询

---

一、JPA动态查询

1. 建实体

多对多，以及一对一关系：

@Data
@Entity
@Builder
@Table(name = "customer", uniqueConstraints = {
        @UniqueConstraint(columnNames = {"account_no"})})
@NoArgsConstructor
@AllArgsConstructor
public class Customer {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(name = "id", length = 32)
    protected String id;

    /**
     * 用户账号
     */
    @Column(name = "account_no", unique = true, nullable = false, length = 20)
    private String accountNo;

    /**
     * 用户密码
     */
    @Column(name = "pwd", nullable = false, length = 32)
    private String pwd;

    /**
     * 盐
     */
    @Column(name = "salt", nullable = false, length = 32)
    private String salt;

    /**
     * token
     */
    @Column(name = "token", nullable = false, length = 32)
    private String token;

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true)
    private List<Favorites> favorites;

    /**
     * 标签列表
     */
    @ManyToMany
    @JoinTable(name = "customer_tag",
            joinColumns = @JoinColumn(name = "customer_id"),
            inverseJoinColumns = @JoinColumn(name = "tag_id"))
    private List<Tag> tags;
}

一对一关系：

@Data
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "customer_profile")
public class CustomerProfile {

    @Id
    private String id;

    @OneToOne(fetch = FetchType.LAZY)
    @MapsId
    @JsonIgnore
    private Customer customer;

    /**
     * 用户名称
     */
    @Column(name = "name", nullable = false, length = 50)
    private String name;

    /**
     * 用户年龄
     */
    @Column(name = "age", nullable = false, columnDefinition = "int(2)")
    private int age;

    /**
     * 用户性别
     */
    @Enumerated(EnumType.STRING)
    @Column(name = "gender", nullable = false, length = 5)
    private GenderEnum gender;
}

一对一关系：

@Data
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "favorites")
public class Favorites {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(name = "id", length = 32)
    protected String id;

    /**
     * 收藏夹名称
     */
    @Column(name = "name", length = 50, nullable = false)
    private String name;

    /**
     * 客户id
     */
    @Column(name = "customer_id", insertable = false, updatable = false)
    private String customerId;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id", nullable = false)
    @JsonIgnore
    private Customer customer;
}

多对多关系：

/**
 * tag
 *
 * @author wj
 * @date 2020/9/30 15:28
 */
@Data
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "tag")
@EqualsAndHashCode(exclude = {"customers"})
public class Tag {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(name = "id", length = 32)
    protected String id;

    /**
     * tag名称
     */
    @Enumerated(EnumType.STRING)
    @Column(name = "name", nullable = false, length = 10)
    private TagNameEnum name;

    @ManyToMany(mappedBy = "tags")
    private List<Customer> customers;
}

二、查询

1.动态查询

代码如下（示例）：

    @Override
    public Page<CustomerProfile> listByNameAndGenderAndCustomerAccountNo(String name, GenderEnum gender, String accountNo, Pageable pageable) {
        return customerProfileRepository.findAll((Specification<CustomerProfile>) (root, criteriaQuery, criteriaBuilder) -> {

            List<Predicate> predicateList = Lists.newArrayList();
            if (StringUtils.isNotEmpty(name)) {
                predicateList.add(criteriaBuilder.equal(root.get("name"), name));
            }
            if (gender != null) {
                predicateList.add(criteriaBuilder.equal(root.get("gender"), gender));
            }
            if (StringUtils.isNotEmpty(accountNo)) {
                Join<CustomerProfile, Customer> customerJoin = root.join("customer", JoinType.LEFT);
                predicateList.add(criteriaBuilder.equal(customerJoin.get("accountNo"), accountNo));

            }
            return criteriaQuery.where(predicateList.toArray(new Predicate[0])).getRestriction();
        }, pageable);
    }

    @Override
    public List<CustomerProfile> listByTagNameAndLessThenAgeAndFavoritesName(TagNameEnum name, Integer age, String favoritesName) {
        return customerProfileRepository.findAll((Specification<CustomerProfile>) (root, criteriaQuery, criteriaBuilder) -> {

            List<Predicate> predicateList = Lists.newArrayList();
            boolean nameNotNull = name != null;
            boolean favoritesNameNotNull = StringUtils.isNotEmpty(favoritesName);
            if (nameNotNull || favoritesNameNotNull) {
                Join<CustomerProfile, Customer> customerJoin = root.join("customer", JoinType.LEFT);
                if (nameNotNull) {
                    Join<Customer, Tag> tagJoin = customerJoin.join("tags", JoinType.LEFT);
                    predicateList.add(criteriaBuilder.equal(tagJoin.get("name"), name));
                }
                if (favoritesNameNotNull) {
                    Join<Customer, Favorites> favoritesJoin = customerJoin.join("favorites", JoinType.LEFT);
                    predicateList.add(criteriaBuilder.equal(favoritesJoin.get("name"), favoritesName));
                }
            }
            if (age != null) {
                predicateList.add(criteriaBuilder.lessThan(root.get("age"), age));
            }
            return criteriaQuery.where(predicateList.toArray(new Predicate[0])).getRestriction();
        });
    }

2. JPA自带查询

代码如下（示例）：

    @Override
    public Customer findById(String id) {
        return customerRepository.findById(id).orElse(null);
    }

    @Override
    public Customer findByAccountNo(String accountNo) {
        return customerRepository.findByAccountNo(accountNo).orElse(null);
    }

更多JPA查询可以参考: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#reference

3. JPA SQL查询

代码如下（示例）：

    @Query("SELECT cp FROM CustomerProfile cp " +
            "LEFT JOIN cp.customer c " +
            "LEFT JOIN c.tags t " +
            "WHERE t.name = ?1 AND cp.name = ?2")
    Page<CustomerProfile> listByTagNameAndProfileName(TagNameEnum name, String profileName, Pageable pageable);

4. 原生SQL查询

代码如下（示例）：

    @Query(nativeQuery = true,
            value = "SELECT " +
                    "c.id, DATE_FORMAT( CONCAT( DATE(c.register_date),' ',HOUR(c.register_date), ':', FLOOR( MINUTE(c.register_date)/ 15) *15), '%H:%i') AS time " +
                    "FROM customer c " +
                    "WHERE c.register_date BETWEEN ?1 AND ?2 " +
                    "GROUP BY c.id, time")
    List<CustomerDTO> listByRegisterDateBetween(Date registerDateStart, Date registerDateEnd);

5. 如何处理，JPA中没有的MySQL函数

代码如下（示例）：

    public List<CustomerDTO> listByRegisterDateBetween2(Date registerDateStart, Date registerDateEnd) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<CustomerDTO> criteriaQuery = criteriaBuilder.createQuery(CustomerDTO.class);
        Root<Customer> root = criteriaQuery.from(Customer.class);
		// date_format 是自定义的，具体详情看源码
        Expression<String> timeStr = criteriaBuilder.function("date_format", String.class, root.<Date>get("registerDate"), criteriaBuilder.parameter(String.class, "formatStr"));
        criteriaQuery.multiselect(timeStr, criteriaBuilder.count(root.<String>get("id")));

        List<Predicate> predicateList = Lists.newArrayList();
        boolean queryRegisterDate = registerDateStart != null && registerDateEnd != null;
        if (queryRegisterDate) {
            predicateList.add(criteriaBuilder.between(root.get("registerDate"), registerDateStart, registerDateEnd));
        }
        criteriaQuery.where(predicateList.toArray(new Predicate[0]));
        criteriaQuery.groupBy(timeStr);
        TypedQuery<CustomerDTO> query = entityManager.createQuery(criteriaQuery);
        if (queryRegisterDate) {
            query.setParameter("formatStr", "%Y-%m-%d");
        }
        return query.getResultList();
    }

---

总结(源码)

以上就是博文内容，本文仅仅简单对JPA的查询举了几个例子。欢迎大家一起讨论哈

更多JPA使用以及查询方法可以参考: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#project

源码地址:

1. 国外：https://github.com/DuoLaAMengDianHuanXiang/JPAQueryDemo
2. 国内： https://gitee.com/DLAMDXH/JPAQueryDemo