使用JPA Criteria多表关联查询时结果异常问题排查

问题描述 #

实现多表动态搜索功能时，仅关联单个表能得到预期结果，但关联所有表后结果仅为预期的一半甚至为空；即使不在criteriaBuilder.or()中添加Predicate，仅声明JOIN也会导致查询异常。

核心原因 #

1. 默认内连接过滤数据：JPA的root.join()默认使用内连接（INNER JOIN），这要求所有关联表必须存在匹配记录，否则主表数据会被直接过滤，导致结果为空或数量骤减。
2. 笛卡尔积导致重复/异常：多表内连接会产生笛卡尔积，不仅会放大结果数量，还可能因无匹配关联数据直接返回空结果。
3. 空指针风险：关联表无数据时，直接访问关联实体属性会触发空指针异常。

解决方案代码 #

修改fullSearch方法，使用左外连接、开启去重并处理空值判断：

private Specification<ImageInfoEntity> fullSearch(String search) {
    return (root, query, criteriaBuilder) -> {
        // 使用左外连接，保留无关联数据的主表记录
        Join<ImageInfoEntity, GenreImageInfoMEntity> joinG = root.join(ImageInfoEntity_.GENRES, JoinType.LEFT);
        Join<ImageInfoEntity, LocationImageInfoMEntity> joinL = root.join(ImageInfoEntity_.LOCATIONS, JoinType.LEFT);
        Join<ImageInfoEntity, PersonImageInfoMEntity> joinP = root.join(ImageInfoEntity_.PERSONS, JoinType.LEFT);
        Join<ImageInfoEntity, KeywordImageInfoMEntity> joinK = root.join(ImageInfoEntity_.KEYWORDS, JoinType.LEFT);

        // 开启去重，避免多表连接产生的笛卡尔积重复结果
        query.distinct(true);

        String searchFinal = "%" + Utility.unaccent(search.toLowerCase()) + "%";

        // 为关联表Predicate添加非空判断，避免空指针
        Predicate genrePredicate = criteriaBuilder.and(
            criteriaBuilder.isNotNull(joinG.get(GenreImageInfoMEntity_.GENRE)),
            criteriaBuilder.like(
                criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinG.get(GenreImageInfoMEntity_.GENRE).get(GenreEntity_.NAME))),
                searchFinal
            )
        );

        Predicate personPredicate = criteriaBuilder.and(
            criteriaBuilder.isNotNull(joinP.get(PersonImageInfoMEntity_.PERSON)),
            criteriaBuilder.like(
                criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinP.get(PersonImageInfoMEntity_.PERSON).get(PersonEntity_.SEARCH_NAME))),
                searchFinal
            )
        );

        Predicate keywordPredicate = criteriaBuilder.and(
            criteriaBuilder.isNotNull(joinK.get(KeywordImageInfoMEntity_.KEYWORD)),
            criteriaBuilder.like(
                criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinK.get(KeywordImageInfoMEntity_.KEYWORD).get(KeywordEntity_.KEYWORD))),
                searchFinal
            )
        );

        Predicate locationPredicate = criteriaBuilder.and(
            criteriaBuilder.isNotNull(joinL.get(LocationImageInfoMEntity_.LOCATION)),
            criteriaBuilder.like(
                criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinL.get(LocationImageInfoMEntity_.LOCATION).get(LocationEntity_.NAME))),
                searchFinal
            )
        );

        Predicate locationHUNPredicate = criteriaBuilder.and(
            criteriaBuilder.isNotNull(joinL.get(LocationImageInfoMEntity_.LOCATION)),
            criteriaBuilder.like(
                criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinL.get(LocationImageInfoMEntity_.LOCATION).get(LocationEntity_.HUN_NAME))),
                searchFinal
            )
        );

        Predicate locationENGPredicate = criteriaBuilder.and(
            criteriaBuilder.isNotNull(joinL.get(LocationImageInfoMEntity_.LOCATION)),
            criteriaBuilder.like(
                criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinL.get(LocationImageInfoMEntity_.LOCATION).get(LocationEntity_.ENG_NAME))),
                searchFinal
            )
        );

        Predicate photographerPredicate = criteriaBuilder.and(
            criteriaBuilder.isNotNull(root.get(ImageInfoEntity_.PHOTOGRAPHER)),
            criteriaBuilder.like(
                criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(root.get(ImageInfoEntity_.PHOTOGRAPHER).get(PersonEntity_.ABC_NAME))),
                searchFinal
            )
        );

        Predicate ownerPredicate = criteriaBuilder.like(
            criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(root.get(ImageInfoEntity_.OWNER))),
            searchFinal
        );

        return criteriaBuilder.or(genrePredicate, personPredicate, keywordPredicate, locationPredicate, locationENGPredicate, locationHUNPredicate, ownerPredicate, photographerPredicate);
    };
}

关键修改点说明 #

• 左外连接替代内连接：通过JoinType.LEFT指定左外连接，确保主表数据不会因关联表无匹配记录被过滤。
• 开启查询去重：query.distinct(true)解决多表左连接产生的笛卡尔积导致的重复结果问题。
• 空值安全判断：为所有关联实体的Predicate添加isNotNull检查，避免关联表无数据时触发空指针异常。

内容的提问来源于stack exchange，提问作者Andor Pál