0

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 and DateRange
  2. CUST_ACCT_ID
  3. CUST_ACCT_ID and DateRange

Query..

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'

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

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. Criteria API

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

Himalay Majumdar
  • 3,883
  • 14
  • 65
  • 94

1 Answers1

0

Example

@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

For date use parameters

em.createNamedQuery("xxx").setParameter("srtartDate", ...).setParameter("endDate",..)
Cris
  • 4,947
  • 6
  • 44
  • 73
  • if its a SELECT from Single entity/table (Student here) then using named query is fine, but in my case I need to select from multiple tables, and because named query is just bound to the table on which it sits. I was looking for an example where select is spanning multiple tables. – Himalay Majumdar Apr 24 '13 at 13:58
  • I can't try now but an sql like this migh work as well in the named query: SELECT e.salary FROM Student e , Department d WHERE e.department_id = d.id and d.name = :deptName AND e.name = :empName – Cris Apr 24 '13 at 14:35