JPA 动态生成sql

1. 构建格式化时间查询sql：

public static Specification<HelpRecord> tj(String startTime, String endTime) {
            return (Specification<HelpRecord>) (root, query, cb) -> {
                List<Predicate> list = new ArrayList<Predicate>();
                if (StrUtil.isNotBlank(startTime)){
                    //date_format(gmt_create, "%Y-%m-%d") >= stratTime
                    list.add(cb.greaterThanOrEqualTo(cb.function("DATE_FORMAT", String.class, root.get("gmtCreate"), cb.literal("%Y-%m-%d")), startTime));
                }
                if (StrUtil.isNotBlank(endTime)){
                    //date_format(gmt_create, "%Y-%m-%d") <= endTime
                    list.add(cb.lessThanOrEqualTo(cb.function("DATE_FORMAT", String.class, root.get("gmtCreate"), cb.literal("%Y-%m-%d")), endTime));
                }
                Predicate[] p = new Predicate[list.size()];
                return cb.and(list.toArray(p));
            };
}

生成sql：

SELECT
    *
FROM
    help_record
WHERE
    date_format(gmt_create, "%Y-%m-%d") >= "2019-01-01 00:00:00"
AND date_format(gmt_create, "%Y-%m-%d") <= "2019-04-01 00:00:00"

---

2. 构建多条件查询sql：

public static Specification<HelpRecord> buildHelpRecord(List<Integer> status,
                                                                String keyword,
                                                                String beginTime, 
                                                                String endTime) {
            return (Specification<HelpRecord>) (root, query, cb) -> {
                List<Predicate> list = new ArrayList<Predicate>();
                // 状态过滤
                if (status != null && status.size() > 0) {
                    CriteriaBuilder.In<Integer> inStatus = cb.in(root.get("status"));
                    status.forEach(inStatus::value);
                    list.add(inStatus);
                }
                if (StrUtil.isNotBlank(keyword)) {
                    list.add(cb.or(cb.like(root.get("helperName").as(String.class), "%" + keyword.trim() + "%"), cb.like(root.get("helperEmail").as(String.class), "%" + keyword.trim() + "%")));
                }
                if (StrUtil.isNotBlank(beginTime) && StrUtil.isNotBlank(endTime)) {
                    list.add(cb.between(root.get("gmtCreate").as(Date.class), DateUtil.parse(beginTime), DateUtil.parse(endTime)));
                }
                Predicate[] p = new Predicate[list.size()];
                return cb.and(list.toArray(p));
            };
        }

生成sql：

SELECT
    *
FROM
    help_record
WHERE
    STATUS IN (1, 2, 3)
AND (
    helper_name LIKE "%hezhigang%"
    OR helper_email LIKE "%hezhigang%"
)
AND gmt_create BETWEEN "2019-01-01 00:00:00"
AND "2019-04-01 00:00:00";

---

3. 构建子查询：

public static Specification<Org> findOrg(String orgName, String ip, boolean isLike) {
            return (Specification<Org>) (root, query, cb) -> {
                List<Predicate> list = new ArrayList<Predicate>();
                if (orgName != null) {
                    if (isLike) {
                        //模糊查询
                        list.add(cb.like(root.get("name").as(String.class), "%" + orgName + "%"));
                    } else {
                        //精确查询
                        list.add(cb.equal(root.get("name").as(String.class), orgName));
                    }
                }
                if (ip != null) {
                    long ipNumber = NetUtil.ipv4ToLong(ip);
                    Subquery<IpRange> subQuery = query.subquery(IpRange.class);
                    Root<IpRange> subRoot = subQuery.from(IpRange.class);
                    subQuery.select(subRoot.get("orgId")).where(cb.lessThanOrEqualTo(subRoot.get("beginNumber"), ipNumber), cb.greaterThanOrEqualTo(subRoot.get("endNumber"), ipNumber));
                    list.add(cb.equal(root.get("id"), subQuery));
                }
                Predicate[] p = new Predicate[list.size()];
                return cb.and(list.toArray(p));
            };
        }

生成sql：

SELECT
    *
FROM
    org
WHERE
    id = (
        SELECT
            org_id
        FROM
            ip_range
        WHERE
            begin_Number <= 3661721858
        AND end_Number >= 3661721858
    );

---

4. @Query 分页查询

上面的条件组装对于很多新手来说可能过于复杂，还是直接使用原生SQL语句更简单一些。
JPA提供了一个@Query注解，我们来看看怎么使用

@Query(value = "select * from v_help_record where status in (0,1,3) and ((gmt_create between ?1 and ?3 and is_difficult = 0) or (is_difficult = 1 and gmt_create between ?2 and ?3))", nativeQuery = true)
Page<VHelpRecord> findByWaitHelp(Date begin1, Date begin2, Date end, Pageable pageable);

上面的语句@Query中nativeQuery=true表示使用原生sql。同样也可以传pageable参数进行分页查询。