命名查询，本机查询或条件API [英] Named Query, Native Query or Criteria API

问题描述

我需要以报告的形式报告来自3个不同表的数据.

I need to report data from 3 different tables in form of a report.

用户可以使用的各种搜索组合..

The various search combinations a user can search with are..

1. INS_USER_ID和DateRange
2. CUST_ACCT_ID
3. CUST_ACCT_ID和DateRange

查询..

select DISTINCT W.CUST_ACCT_ID, W.INS_USER_ID, WS.STTI_DATE, WS.STTI_AMT, WC.CMMT  from T_WRK W
       INNER JOIN T_WRK_STTI WS ON W.WRK_STTI_ID = WS.WRK_STTI_ID
       INNER JOIN T_WRK_CMMT WC ON W.WRK_ID = WC.WRK_ID
       WHERE W.CUST_ACCT_ID = 90610194 AND WS.STTI_DATE BETWEEN '01-JAN-12' AND '31-DEC-12'


select DISTINCT W.CUST_ACCT_ID, W.INS_USER_ID, WS.STTI_DATE, WS.STTI_AMT, WC.CMMT  from T_WRKCS W  
       INNER JOIN T_WRKCS_STTI WS ON W.WRKCS_STTI_ID = WS.WRKCS_STTI_ID
       INNER JOIN T_WRKCS_CMMT WC ON W.WRKCS_ID = WC.WRKCS_ID
       WHERE W.INS_USER_ID = 231 AND WS.STTI_DATE BETWEEN '01-JAN-12' AND '31-DEC-12' 

所有现有表都已经使用Hibernate/JPA进行了映射.

All are existing tables are already mapped using Hibernate/JPA.

我对google中的各种方法已经读够了，有人可以告诉我以下哪种休眠方法最适合我的情况.

I have read enough on the various approaches in google, can someone tell me which one of the following hibernate approaches is best suited for my scenario.

1. NamedQuery
2. NativeQuery
3. 标准API

我在考虑NamedQuery，但是还没有看到跨越多个表的NamedQuery，那么一个例子将是很好的.谢谢.

I am thinking NamedQuery, but have not seen a NamedQuery spanning across multiple tables, an example would be great. Thank you.

推荐答案

示例

@Entity
@NamedQuery(name="findSalaryForNameAndDepartment",
    query="SELECT e.salary " +
          "FROM Student e" +
          "WHERE e.department.name = :deptName AND " +
          "      e.name = :empName")

您也可以在该表上放置一个联接，然后对查询进行一些更改

You can as well put there an join to the department table and change the query a little bit

对于日期使用参数

em.createNamedQuery("xxx").setParameter("srtartDate", ...).setParameter("endDate",..)

这篇关于命名查询，本机查询或条件API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！