Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.0.5

I've been using Hibernate for quite a time. And while doing my company's project, some questions arise :

1. Is Hibernate/HQL a good choice for report querying ? We're using Jasper Report and the data queried could be large. Our concern is primarily about memory usage of Hibernate query result and the complexity of the query(whether it's supported by HQL or not)

2. Is Hibernate/HQL suitable for mass data processing ? For example, mass update/delete/insert with explicit subselect in plain SQL. Here's an example :

UPDATE MstEmployee A
SET (A.employeeName, A.startDate, A.resignDate) =
( SELECT B.employeeName, B.startDate, B.resignDate
FROM TrfEmployee B
WHERE B.employeeCode = A.employeeCode
)
WHERE
Exists ( SELECT B.employeeCode
FROM TrfEmployee B
WHERE B.employeeCode = A.employeeCode
)

I've found the way in HQL for the 'Exists' clause, but the subselect in the UPDATE is still not supported. This is the quote from Hibernate Documentation : #
No joins (either implicit or explicit) can be specified in a bulk HQL query. Sub-queries may be used in the where-clause.
#
Is there any way around ?

This statement is still unresolved :

INSERT INTO MstEmployee
(employeeCode, employeeName, startDate, resignDate)
SELECT A.employeeCode, A.employeeName, A.startDate, A.resignDate
FROM TrfEmployee A
WHERE
Not Exists (
SELECT B.employeeCode
FROM MstEmployee B
WHERE B.employeeCode = A.employeeCode
)

As far as i've known, in Hibernate 3, there's been an additional feature of bulk update/delete, but none for insert.

How do we cope with this, should we try native sql for the complex statements or using many models or something else ?
We've been hoping that HQL could really give us an upper hand, so that if we switch database, we just change the dialect and all the HQLs used wont be changed. So the current policy, everything must be HQL-ed. But so far, there've been problems for complex statements, like those above.

Please advice T-T

Albert Kam

Whats wrong with SQL/JDBC?

It's our company's policy, =(
And our company's policy has just changed .. Use JDBC SQL for reports and complex processes, and Hibernate for the rest.
Use the right tool for the right job - Hibernate in Action

Sorry for the lamentation

Thanks :D

[/quote]

This is not black and white. You can use SQL, but get the table and columnnames from Hibernate.

Here is one for Hibernate 3.0.5:

http://beanlib.sourceforge.net/Hibernat ... readme.txt
http://beanlib.sourceforge.net/Hibernat ... ert/3.0.5/

Thanks a lot for the hack and help. ^-^

It's probably the smell of a wet dog: http://www.journalhome.com/codecraft/8401/ :)