java - 使用注释的 native hibernate/jpa 查询中标量值的问题

Question

我正在尝试运行一个包含 group by 子句的简单 SQL 查询。

历史实体:

@Entity
@NamedNativeQueries(value = {
    @NamedNativeQuery(name = HistoryEntity.FIND_ALL_BY_REFERENCE,
                      query = "SELECT h.id, h.reference, h.lrn "
                          + "FROM dataHistory h "
                          + "WHERE h.reference = :referenceNumber OR h.lrn = :referenceNumber",
                      resultSetMapping = HistoryEntity.FIND_ALL_BY_REFERENCE_MAPPING),
    @NamedNativeQuery(name = HistoryEntity.FIND_REFERENCE_BY_LRN,
                      query = "SELECT h.reference as reference "
                          + "FROM dataHistory h "
                          + "WHERE h.lrn = :lrn "
                          + "GROUP BY h.reference",
                      resultSetMapping = "resultMapping")                   
})
@SqlResultSetMappings(value = {
    @SqlResultSetMapping(name = HistoryEntity.FIND_ALL_BY_REFERENCE_MAPPING, entities = {
            @EntityResult(entityClass = HistoryEntity.class, fields = {
                @FieldResult(name = "id", column = "id"),
                @FieldResult(name = "reference", column = "reference"),
                @FieldResult(name = "lrn", column = "lrn")
            })
    }),
    @SqlResultSetMapping(name = "resultMapping", columns = {
            @ColumnResult(name = "reference")
    })
})
public class HistoryEntity implements Serializable {

/**
 * @param referenceNumber Referenz (LRN oder BRN)
 * @param brnList Liste von BRNs
 */
public static final String FIND_ALL_BY_REFERENCE = "HistoryEntity.findAllByReference";

public static final String FIND_ALL_BY_REFERENCE_MAPPING = "HistoryEntity.findAllByReferenceMapping";

private Integer id;

private String reference;
private String lrn;

public HistoryEntity() {}

@Id
public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public String getReference() {
    return reference;
}

public void setReference(String reference) {
    this.reference= reference;
}

public String getLrn() {
    return lrn;
}

public void setLrn(String lrn) {
    this.lrn = lrn;
}

在服务类中，我执行如下查询:

Query query = entityManager.createNamedQuery("myQuery");
query.setParameter("lrn", lrn);

List resultList = query.getResultList();

根据查询的结果，列表包含 java.lang.Character 的:

案例1:
SQL-Result(如果我在 sql 客户端中运行 sql):
|引用|
| 123456780678MMM |
| 123456781678MMM |
| 123456782678MMM |
| 123456783678MMM |

Java-List-Result(在 Java 调试 View 中):
[1,1,1,1]

案例2:
SQL 结果:
|引用|
| 123456780678MM |

Java 列表结果:
[1]

我正在寻找一种方法来运行带有标量值的简单 SQL 查询(使用 hibernate/jpa)，以获取包含结果值的列表。

有没有办法在不使用标准 API 的情况下做到这一点？

如果您需要更多信息，请告诉我!

非常感谢您的帮助。

马可

Answer 1

问题是 hibernate 中的一个错误，它将结果转换为 java.lang.Character 而不是 java.lang.String。结果中仅返回每行的第一个字符。

还有一个问题更详细地描述了解决方案:
Hibernate native query - char(3) column

如果我在查询中使用强制转换函数，如下所示，它会起作用:

@NamedNativeQuery(name = HistoryEntity.FIND_REFERENCE_BY_LRN,
                  query = "SELECT cast(h.reference as VARCHAR(27)) as reference "
                      + "FROM dataHistory h "
                      + "WHERE h.lrn = :lrn "
                      + "GROUP BY h.reference",
                  resultSetMapping = "resultMapping")  

PS:我发现引用的问题已经晚了。抱歉!