Hibernate原生查询抛出NullPointerException，请求排查解决

错误信息 #

java.lang.NullPointerException: Cannot invoke"String.toLowerCase(java.util.Locale)" because "columnName" is null

相关代码与环境 #

Product实体类 #

@Entity
@Table(name = "product", schema = "auction_app_schema")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "product_name", nullable = false)
    private String productName;

    @Column(name = "description", nullable = false)
    private String description;

    @Column(name = "start_price", nullable = false)
    private Float startPrice;

    @ElementCollection
    @CollectionTable(name = "product_images", joinColumns = @JoinColumn(name = "product_id"))
    @Column(name = "image")
    private List<String> images;

    @Column(name = "start_date", nullable = false)
    private LocalDateTime startDate;

    @Column(name = "end_date", nullable = false)
    private LocalDateTime endDate;

    @Formula("(SELECT COUNT(*) FROM auction_app_schema.bid b INNER JOIN auction_app_schema.product p ON p.id = b.product_id WHERE b.product_id = p.id)")
    private BigInteger numberOfBids;

    @Formula("(SELECT b.price FROM auction_app_schema.product p INNER JOIN auction_app_schema.bid b ON p.id =b.product_id ORDER BY b.price DESC LIMIT 1)")
    private BigDecimal highestBid;

    @ManyToOne
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;
}

仓库层本地查询方法 #

@Query(value = "SELECT p.id, p.description, p.product_name, p.start_price, p.start_date, p.end_date, p.category_id, p.is_highlighted, pi.image " +
        "FROM auction_app_schema.product p " +
        "INNER JOIN auction_app_schema.category c ON p.category_id = c.id " +
        "INNER JOIN auction_app_schema.product_images pi ON p.id = pi.product_id " +
        "WHERE c.id = ? LIMIT ? OFFSET ?", nativeQuery = true)
List<Product> findProductsByCategoryId(Long categoryId, int limit, int offset);

背景说明 #

该查询直接在数据库执行正常，但请求服务器时抛出上述NPE，数据库所有字段均存在，此前使用PostgreSQL未遇到该问题，怀疑Hibernate未正确识别数据库模式。

完整堆栈信息 #

FROM auction_app_schema.product p
INNER JOIN auction_app_schema.category c ON p.category_id = c.id
INNER JOIN auction_app_schema.product_images pi ON p.id = pi.product_id
WHERE c.id = ? LIMIT ? OFFSET ?
2023-04-01 22:56:55.147 ERROR 18404 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.NullPointerException: Cannot invoke "String.toLowerCase(java.util.Locale)" because "columnName" is null] with root cause

    java.lang.NullPointerException: Cannot invoke "String.toLowerCase(java.util.Locale)" because "columnName" is null
        at org.postgresql.jdbc.PgResultSet.findColumnIndex(PgResultSet.java:2791) ~[postgresql-42.2.23.jar:42.2.23]
        at org.postgresql.jdbc.PgResultSet.findColumn(PgResultSet.java:2750) ~[postgresql-42.2.23.jar:42.2.23]
        at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:410) ~[postgresql-42.2.23.jar:42.2.23]
        at com.zaxxer.hikari.pool.HikariProxyResultSet.getBigDecimal(HikariProxyResultSet.java) ~[HikariCP-4.0.3.jar:na]
        at org.hibernate.type.descriptor.sql.DecimalTypeDescriptor$2.doExtract(DecimalTypeDescriptor.java:63) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:3131) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1863) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.hydrateEntityState(Loader.java:1791) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1764) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.getRow(Loader.java:1616) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:740) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1039) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.processResultSet(Loader.java:990) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.doQuery(Loader.java:959) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2843) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2825) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2657) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.Loader.list(Loader.java:2652) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2141) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1169) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:176) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1604) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.query.Query.getResultList(Query.java:165) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126) ~[spring-data-jpa-2.5.5.jar:2.5.5]
        at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88) ~[spring-data-jpa-2.5.5.jar:2.5.5]
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155) ~[spring-data-jpa-2.5.5.jar:2.5.5]
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143) ~[spring-data-jpa-2.5.5.jar:2.5.5]

问题原因 #

1. @Formula字段映射缺失：实体类中numberOfBids和highestBid是通过@Formula注解计算的字段，但本地查询未返回对应计算值，Hibernate尝试映射时找不到列名，触发空指针。
2. 集合字段映射错误：实体类中images是List类型的集合，本地查询关联product_images返回单条image值，Hibernate无法自动将多条重复product记录合并为集合，导致映射失败。
3. 关联对象类型不匹配：查询返回category_id（Long类型），但实体类中category是Category对象，类型不匹配引发映射异常。

解决方案 #

方案一：修改本地查询适配实体类映射 #

• 补充@Formula字段的计算逻辑到查询中，例如：

  SELECT 
    p.id, p.description, p.product_name, p.start_price, p.start_date, p.end_date, 
    p.category_id, p.is_highlighted, pi.image,
    (SELECT COUNT(*) FROM auction_app_schema.bid b WHERE b.product_id = p.id) AS number_of_bids,
    (SELECT b.price FROM auction_app_schema.bid b WHERE b.product_id = p.id ORDER BY b.price DESC LIMIT 1) AS highest_bid
  FROM auction_app_schema.product p 
  INNER JOIN auction_app_schema.category c ON p.category_id = c.id 
  INNER JOIN auction_app_schema.product_images pi ON p.id = pi.product_id 
  WHERE c.id = ? LIMIT ? OFFSET ?
  

• 注意：这种方式仍无法解决集合字段的映射问题，因为本地查询会返回多条重复product记录，Hibernate无法自动合并为List，需额外处理结果集。

方案二：改用JPQL查询（推荐） #

使用JPQL让Hibernate自动处理所有字段和关联映射，包括集合和@Formula计算：

@Query("SELECT p FROM Product p JOIN FETCH p.images WHERE p.category.id = :categoryId")
List<Product> findProductsByCategoryId(@Param("categoryId") Long categoryId, Pageable pageable);

通过Pageable参数实现limit和offset分页，无需手动拼接SQL，避免映射错误。

方案三：使用DTO接收查询结果 #

创建仅包含查询返回字段的DTO类，避免实体类映射冲突：

@Data
public class ProductDTO {
    private Long id;
    private String description;
    private String productName;
    private Float startPrice;
    private LocalDateTime startDate;
    private LocalDateTime endDate;
    private Long categoryId;
    private Boolean isHighlighted;
    private String image;
}

修改仓库层方法返回DTO列表：

@Query(value = "SELECT p.id, p.description, p.product_name, p.start_price, p.start_date, p.end_date, p.category_id, p.is_highlighted, pi.image " +
        "FROM auction_app_schema.product p " +
        "INNER JOIN auction_app_schema.category c ON p.category_id = c.id " +
        "INNER JOIN auction_app_schema.product_images pi ON p.id = pi.product_id " +
        "WHERE c.id = ? LIMIT ? OFFSET ?", nativeQuery = true)
List<ProductDTO> findProductsByCategoryId(Long categoryId, int limit, int offset);

适合仅需查询部分字段的场景，避免实体类映射的复杂问题。

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