java jpa 关联查询，left join、子查询

最新推荐文章于 2024-04-03 00:22:10 发布

小小小码农丶

最新推荐文章于 2024-04-03 00:22:10 发布

阅读量1.1k

点赞数 9

文章标签： java

本文链接：https://blog.csdn.net/u013263373/article/details/135971028

版权

记录一下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 ?