單表查詢

//select * from s_user where username like '%zt%' and id > 3
List<SUser> sUserList = sUserDao.findAll((root, query, cb) -> {
    //root.get("username")表示獲取username這個字段名稱,like表示執(zhí)行l(wèi)ike查詢,%zt%表示值
    Predicate p1 = cb.like(root.get("username"), "%zt%");
    Predicate p2 = cb.greaterThan(root.get("id"), 3);
    //將兩個查詢條件聯(lián)合起來之后返回Predicate對象,username模糊查詢，id>3
    return cb.and(p1, p2);
});

//select * from s_user where (id = 2 or id = 3) and (email like 'zt%' or username like 'foo%')
//第一個Specification定義了兩個or的組合
Specification<SUser> s1 = (root, query, cb) -> {
    Predicate p1 = cb.equal(root.get("id"), 2);
    Predicate p2 = cb.equal(root.get("id"), 3);
    return cb.or(p1, p2);
};

//第二個Specification定義了兩個or的組合
Specification<SUser> s2 = (root, query, cb) -> {
    Predicate p1 = cb.like(root.get("email"), "kk%");
    Predicate p2 = cb.like(root.get("username"), "foo%");
    return cb.or(p1, p2);
};
        
//通過Specifications將兩個Specification連接起來，第一個條件加where，第二個是and
List<SUser> sUserList = sUserDao.findAll(Specifications.where(s1).and(s2));

//實例根據(jù)訂單狀態(tài)篩選訂單列表
Specification<Orders> spec = (root, query, cb) -> {
    List<Predicate> predicates = Lists.newArrayList();
    if (StringUtils.isNotEmpty(state)) {
        if ("return".equals(state)) {
            predicates.add(cb.and(
                    cb.isTrue(root.get("returned")),
                    cb.or(
                            cb.equal(root.get("state"), String.valueOf(STATE_SEND)),
                            cb.equal(root.get("state"), String.valueOf(STATE_RECEIVE))
                    )));
        } else {
            predicates.add(cb.and(cb.equal(root.get("state"), state)));
        }
    }
    if (StringUtils.isNotEmpty(id)) {
        predicates.add(cb.like(root.get("id"), "%" + id.trim() + "%"));
    }
    predicates.add(cb.equal(root.get("shopId"), shop.getId()));
    return cb.and(predicates.toArray(new Predicate[predicates.size()]));
};

Pageable pageable = new PageRequest(pageNumber, pageSize, new Sort(Sort.Direction.DESC, "createTime"));
Page<Orders> ordersList = ordersService.findAll(spec, pageable);

多表查詢

/**
 * 構(gòu)建查詢條件 三張表內(nèi)聯(lián)查詢實例
 *
 * @param categoryId 分類id
 * @param name       商品名稱
 * @param shopId     店鋪id
 * @return 查詢條件
 */
public Specification<ProductGroup> buildProductGroupSpec(Integer shopId, String name, Integer categoryId, Integer shopKindId, Boolean health) {
    return (root, query, cb) -> {
        Join<ProductGroup, Shop> shopJoin = root.join("shop", JoinType.INNER);
        Join<Shop, ShopDatum> shopDatumJoin = shopJoin.join("shopDatum", JoinType.INNER);
        Join<Shop, ShopConfig> shopConfigJoin = shopJoin.join("shopConfig", JoinType.INNER);
        List<Predicate> predicates = Lists.newArrayList();
        if (shopId != null) {
            predicates.add(cb.equal(root.get("shop"), shopId));
        }
        if (StringUtils.isNotEmpty(name)) {
            predicates.add(cb.like(root.get("name"), "%" + name.trim() + "%"));
        }
        if (categoryId != null) {
            predicates.add(cb.equal(root.get("category"), categoryId));
        }
        if (shopKindId != null && shopKindId != 0) {
            predicates.add(cb.like(root.get("shopKindIds"), "," + shopKindId + ","));
        }
        predicates.add(cb.equal(root.get("auditState"), AUDIT_STATE_YES.getKey()));
        predicates.add(cb.equal(root.get("offline"), Boolean.FALSE));
        predicates.add(cb.greaterThanOrEqualTo(shopDatumJoin.get("openShopExpire"), new Date()));
        predicates.add(cb.equal(shopConfigJoin.get("openShop"), Boolean.TRUE));
        Predicate p1 = cb.equal(root.get("health"), Boolean.FALSE);
        Predicate p2 = cb.equal(root.get("health"), Boolean.TRUE);
        Predicate p3 = cb.greaterThanOrEqualTo(shopDatumJoin.get("openHealthShopExpire"), new Date());
        if (health == null) {
            predicates.add(cb.or(p1, cb.and(p2, p3)));
        }
        if (health != null) {
            if (health) {
                predicates.add(cb.and(p2, p3));
            } else {
                predicates.add(cb.and(p1));
            }
        }
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}

//兩張表內(nèi)聯(lián)，排序我的是
Specification<User> spec = (root, query, cb) -> {
    Join<User, UserAsset> userJoin = root.join("userAsset", JoinType.INNER);
    List<Predicate> predicates = Lists.newArrayList();
    predicates.add(cb.gt(userJoin.get("wpoint"), 0));
    cb.and(predicates.toArray(new Predicate[predicates.size()]));
    query.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));
    query.orderBy(cb.desc(userJoin.get("wpoint")));
    return query.getRestriction();
};