JPQL自定义搜索查询报错，求创建教程及可行实现方案

一、问题背景 #

用户的Item实体类定义如下：

public class Item extends BaseEntity {
    private String modelName;
    private int discount;
    private double price;

    @ManyToOne
    private Collection collection;

    private boolean isChild;

    private String article;

    @Column(columnDefinition = "TEXT")
    private String content;

    @ManyToOne
    private SubType subType;

    @ManyToMany(fetch = FetchType.LAZY)
    private Set<Material> materials;

    @ManyToMany(fetch = FetchType.LAZY)
    private Set<Color> colors;

    @ManyToOne
    private Gender gender;

    @OneToMany(fetch = FetchType.LAZY)
    private List<Picture> pictures;
}

尝试使用生成的JPQL查询实现多条件搜索，代码如下：

@Query("SELECT i FROM Item i WHERE " +
        "(:modelName IS NULL OR i.modelName LIKE %:modelName%) AND " +
        "(:minPrice IS NULL OR i.price >= :minPrice) AND " +
        "(:maxPrice IS NULL OR i.price <= :maxPrice) AND " +
        "(:brandIds IS NULL OR i.collection.brand.id IN (:brandIds)) AND " +
        "(:typeIds IS NULL OR i.subType.type.id IN (:typeIds)) AND " +
        "(:materials IS NULL OR i.materials = :materials) AND " +
        "(:colors IS NULL OR i.colors = :colors) AND " +
        "(:child IS NULL OR i.isChild = :child) AND " +
        "(:genderId IS NULL OR i.gender.id = :genderId)")
List<Item> searchItems(
        @Param("modelName") String modelName,
        @Param("minPrice") Double minPrice,
        @Param("maxPrice") Double maxPrice,
        @Param("brandIds") List<Long> brandIds,
        @Param("typeIds") List<Long> typeIds,
        @Param("materials") Set<Material> materials,
        @Param("colors") Set<Color> colors,
        @Param("child") Boolean child,
        @Param("genderId") Long genderId
);

运行后出现SQL语法错误，错误日志：

2023-04-12 12:20:59.121 ERROR 11924 --- [nio-8000-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'is null) and (null=null or . is null) and (0 is null or item0_.is_child=0) an...' at line 1
2023-04-12 12:21:23.398 ERROR 11924 --- [nio-8000-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

二、错误原因分析 #

1. ManyToMany集合判断错误：i.materials = :materials 和 i.colors = :colors 的写法不符合JPQL语法，集合不能直接用等号判断相等，需要用关联查询或MEMBER OF语法处理。
2. 关联对象为空风险：如果i.collection或i.subType为null，直接访问i.collection.brand.id会导致空指针转换为SQL语法错误。
3. 集合参数处理问题：当brandIds或typeIds是空列表而非null时，IN (:brandIds)会生成无效SQL（如IN ()），部分数据库不支持该写法。

三、修复后的JPQL查询实现 #

以下是修正后的查询方法，解决了上述问题：

@Query("SELECT DISTINCT i FROM Item i " +
        "LEFT JOIN i.materials mat " +
        "LEFT JOIN i.colors col " +
        "WHERE " +
        "(:modelName IS NULL OR i.modelName LIKE %:modelName%) AND " +
        "(:minPrice IS NULL OR i.price >= :minPrice) AND " +
        "(:maxPrice IS NULL OR i.price <= :maxPrice) AND " +
        "(:brandIds IS NULL OR (i.collection IS NOT NULL AND i.collection.brand.id IN (:brandIds))) AND " +
        "(:typeIds IS NULL OR (i.subType IS NOT NULL AND i.subType.type.id IN (:typeIds))) AND " +
        "(:materials IS NULL OR :materials IS EMPTY OR mat MEMBER OF :materials) AND " +
        "(:colors IS NULL OR :colors IS EMPTY OR col MEMBER OF :colors) AND " +
        "(:child IS NULL OR i.isChild = :child) AND " +
        "(:genderId IS NULL OR (i.gender IS NOT NULL AND i.gender.id = :genderId))")
List<Item> searchItems(
        @Param("modelName") String modelName,
        @Param("minPrice") Double minPrice,
        @Param("maxPrice") Double maxPrice,
        @Param("brandIds") List<Long> brandIds,
        @Param("typeIds") List<Long> typeIds,
        @Param("materials") Set<Material> materials,
        @Param("colors") Set<Color> colors,
        @Param("child") Boolean child,
        @Param("genderId") Long genderId
);

关键修复点： #

• 使用LEFT JOIN关联materials和colors集合，配合MEMBER OF判断集合包含关系。
• 添加关联对象非空判断（如i.collection IS NOT NULL），避免空指针导致的SQL错误。
• 增加集合参数为空列表的处理（:materials IS EMPTY），兼容参数为空列表的场景。
• 添加DISTINCT关键字，避免关联查询导致的重复结果。

四、更灵活的方案：使用Specification动态构建查询 #

对于多条件搜索场景，使用Spring Data JPA的Specification可以更灵活地动态构建查询，避免硬编码JPQL的繁琐：

1. 让ItemRepository继承JpaSpecificationExecutor<Item>：

public interface ItemRepository extends JpaRepository<Item, Long>, JpaSpecificationExecutor<Item> {
}

2. 编写Specification构建类：

public class ItemSpecifications {
    public static Specification<Item> withModelName(String modelName) {
        return (root, query, cb) -> {
            if (modelName == null || modelName.isBlank()) {
                return cb.conjunction();
            }
            return cb.like(root.get("modelName"), "%" + modelName + "%");
        };
    }

    public static Specification<Item> withPriceRange(Double minPrice, Double maxPrice) {
        return (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (minPrice != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("price"), minPrice));
            }
            if (maxPrice != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("price"), maxPrice));
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }

    public static Specification<Item> withBrandIds(List<Long> brandIds) {
        return (root, query, cb) -> {
            if (brandIds == null || brandIds.isEmpty()) {
                return cb.conjunction();
            }
            Join<Item, Collection> collectionJoin = root.join("collection", JoinType.LEFT);
            Join<Collection, Brand> brandJoin = collectionJoin.join("brand", JoinType.LEFT);
            return brandJoin.get("id").in(brandIds);
        };
    }

    public static Specification<Item> withMaterials(Set<Material> materials) {
        return (root, query, cb) -> {
            if (materials == null || materials.isEmpty()) {
                return cb.conjunction();
            }
            Join<Item, Material> materialJoin = root.join("materials", JoinType.LEFT);
            return materialJoin.in(materials);
        };
    }

    // 其他条件的Specification方法类似，如typeIds、colors、child、genderId
}

3. 调用示例：

List<Item> items = itemRepository.findAll(Specification.where(
        ItemSpecifications.withModelName("test")
).and(ItemSpecifications.withPriceRange(100.0, 500.0))
 .and(ItemSpecifications.withBrandIds(Arrays.asList(1L, 2L)))
);

五、学习资源推荐 #

1. Spring Data JPA官方文档：重点关注查询方法定义、JPQL语法、Specification接口的使用章节，包含详细的多条件查询示例。
2. Hibernate官方文档：JPQL的语法规范和集合查询的最佳实践，能帮助理解关联查询的底层逻辑。
3. Spring官方指南：关于Spring Data JPA的入门教程，包含动态查询的实现案例。

内容的提问来源于stack exchange，提问作者yellowdopamine