记录一下jpa关联查询...
实体
left join查询
Page<TestName> page = testNameRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
Predicate predicate = QueryHelp.getPredicate(root, criteria, criteriaBuilder);
CriteriaQuery<?> where = criteriaQuery.where(predicate);
List<Predicate> listAnd = new ArrayList<>();
listAnd.add(criteriaBuilder.isNotNull(root.get("name")));
List<Predicate> listOr = new ArrayList<>();
listOr.add(criteriaBuilder.like(root.get("name"), "%" + criteria.getName() + "%"));
// left join
Join<TestName, TestNameRemark> remarkJoin = root.join("remarks", JoinType.LEFT);
remarkJoin.on(criteriaBuilder.equal(remarkJoin.get("userId"), criteria.getUserId()));
listOr.add(criteriaBuilder.and(criteriaBuilder.like(remarkJoin.get("remark"), "%" + criteria.getName() + "%")));
Predicate predicateOR = criteriaBuilder.or(listOr.toArray(new Predicate[listOr.size()]));
listAnd.add(predicateOR);
Predicate preAnd = criteriaBuilder.and(listAnd.toArray(new Predicate[listAnd.size()]));
where.where(preAnd);
return where.getRestriction();
}, pageable);
使用这种形式,需要实体里添加关联关系
其中实体的关联关系已经标识了一个关联条件,其他的关联条件需要使用这个方法进行
输出sql
SELECT
testname0_.id AS id1_39_,
testname0_.create_time AS create_t2_39_,
testname0_.NAME AS name3_39_,
testname0_.update_time AS update_t4_39_
FROM
t_test_name testname0_
LEFT OUTER JOIN t_test_name_remark remarks1_ ON testname0_.id = remarks1_.name_id
AND ( remarks1_.user_id = 1 )
WHERE
( testname0_.NAME IS NOT NULL )
AND (
testname0_.NAME LIKE ?
OR remarks1_.remark LIKE ?)
ORDER BY
testname0_.id DESC
LIMIT ?
子查询
page = testNameRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
Predicate predicate = QueryHelp.getPredicate(root, criteria, criteriaBuilder);
CriteriaQuery<?> where = criteriaQuery.where(predicate);
List<Predicate> listAnd = new ArrayList<>();
listAnd.add(criteriaBuilder.isNotNull(root.get("name")));
List<Predicate> listOr = new ArrayList<>();
listOr.add(criteriaBuilder.like(root.get("name"), "%" + criteria.getName() + "%"));
// 子查询
Subquery<TestNameRemark> subquery = where.subquery(TestNameRemark.class);
Root<TestNameRemark> subRoot = subquery.from(TestNameRemark.class);
List<Predicate> subListAnd = new ArrayList<>();
subListAnd.add(criteriaBuilder.equal(subRoot.get("nameId"), root));
subListAnd.add(criteriaBuilder.equal(subRoot.get("userId"), criteria.getUserId()));
subListAnd.add(criteriaBuilder.like(subRoot.get("remark"), "%" + criteria.getName() + "%"));
Predicate subAnd = criteriaBuilder.and(subListAnd.toArray(new Predicate[subListAnd.size()]));
subquery.select(subRoot.get("nameId")).where(subAnd);
listOr.add(criteriaBuilder.equal(root.get("id"), subquery));
Predicate predicateOR = criteriaBuilder.or(listOr.toArray(new Predicate[listOr.size()]));
listAnd.add(predicateOR);
Predicate preAnd = criteriaBuilder.and(listAnd.toArray(new Predicate[listAnd.size()]));
where.where(preAnd);
return where.getRestriction();
}, pageable);
其中
可以改成exists形式
输出sql
SELECT
testname0_.id AS id1_39_,
testname0_.create_time AS create_t2_39_,
testname0_.NAME AS name3_39_,
testname0_.update_time AS update_t4_39_
FROM
t_test_name testname0_
WHERE
( testname0_.NAME IS NOT NULL )
AND (
testname0_.NAME LIKE ?
OR testname0_.id =(
SELECT
testnamere1_.name_id
FROM
t_test_name_remark testnamere1_
WHERE
testnamere1_.name_id = testname0_.id
AND testnamere1_.user_id = 1
AND (
testnamere1_.remark LIKE ?)))
ORDER BY
testname0_.id DESC
LIMIT ?
SELECT
testname0_.id AS id1_39_,
testname0_.create_time AS create_t2_39_,
testname0_.NAME AS name3_39_,
testname0_.update_time AS update_t4_39_
FROM
t_test_name testname0_
WHERE
( testname0_.NAME IS NOT NULL )
AND (
testname0_.NAME LIKE ?
OR EXISTS (
SELECT
testnamere1_.id
FROM
t_test_name_remark testnamere1_
WHERE
testnamere1_.name_id = testname0_.id
AND testnamere1_.user_id = 1
AND (
testnamere1_.remark LIKE ?)))
ORDER BY
testname0_.id DESC
LIMIT ?