mysql - 如何创建 hibernate 不同的查询

Question

我已经在网上看了看，并没有真正找到明确的答案。

我有两个表 A 和 B。B 是 A 的子表。我需要根据 A 的一些限制从 B 获取不同属性的列表。

例如:

SQL:

select distinct sirm.attribute
from store_item_received_material sirm
where sirm.store_item_id in (select si.id from store_item si where si.program_id = 9 and si.customer_id = 1 and si.date_processed is not null);

当然，SQL 工作得很好。

现在，我需要在我的项目中运行它。

我正在运行 hibernate 3.3.1。我尝试了以下方法:

@NamedNativeQueries ({
    @NamedNativeQuery (name = "select.distinct.sirm.for.customer.program", query = "select distinct(sirm.attribute) as attribute from store_item_received_material as sirm where sirm.store_item_id in (select si.id from store_item as si where si.customer_id = ? and si.program_id = ? and si.date_processed is not null)")
})

但失败并出现以下错误:

嵌套异常是 org.hibernate.cfg.NotYetImplementedException:尚不支持纯 native 标量查询

所以我尝试了以下方法:

@NamedNativeQueries ({
    @NamedNativeQuery (name = "select.distinct.sirm.for.customer.program", query = "select distinct(sirm.attribute) as attribute from store_item_received_material as sirm where sirm.store_item_id in (select si.id from store_item as si where si.customer_id = ? and si.program_id = ? and si.date_processed is not null)", resultClass=StoreItemReceivedMaterial.class)
})
@SqlResultSetMapping(name = "select.distinct.sirm.for.customer.program", entities=@EntityResult(entityClass = StoreItemReceivedMaterial.class))

但这也不起作用，因为该对象是一个实体对象并且没有 ID 列。

那么，关于如何做到这一点的任何帮助

Answer 1

对于标量查询，您需要使用 @ColumnResult 进行结果集映射:

@NamedNativeQueries ({
    @NamedNativeQuery (name = "select.distinct.sirm.for.customer.program",
        query = "select distinct(sirm.attribute) as attribute from store_item_received_material as sirm where sirm.store_item_id in (select si.id from store_item as si where si.customer_id = ? and si.program_id = ? and si.date_processed is not null)", resultSetMapping = "select.distinct.sirm.for.customer.program" }) 

@SqlResultSetMapping(name = "select.distinct.sirm.for.customer.program",
    columns = @ColumnResult(name = "attribute"))