I really like the feature of Hibernate 3 that you can define custom SQL for loading and updating objects. Although it seems that it is not yet well documented. I would like to load a set with a custom SQL. In this example I want to load User objects that are defined by one or more textual filters that is stored in a column.
3 tables are relevant for this:
- Zusammenstellung (this a compilation where the filter set is defined)
- Benutzerfilter (the table with the filters)
- Benutzer (the table with the users)
The only example for using custom SQL with sets is the article from Gavin on TheServerSide
http://www.theserverside.com/blogs/showblog.tss?id=Hibernate3_JDBC
But it seems that this was not the final syntax then (25.08.2005)
When try it with the posted code I have the problem that I am using a join and the key is only in the other table (since the join is based on the stored filters) but the query expands to b.ZusammenstellungID which of course does not exist. Is there a way to tell Hibernate to expand the query to ZusammenstellungID?
Hibernate version:
3.0.1
Mapping documents:
Code:
<hibernate-mapping>
<class name="SCompilation" table="Zusammenstellung">
<id name="id" type="int" column="ID">
<generator class="identity" />
</id>
<property name="name" type="string" column="Name"/>
<property name="description" type="string" column="Beschreibung"/>
<set name="access" table="Berechtigung">
<key column="ZusammenstellungID"/>
<element type="string" column="DN"/>
</set>
<set name="user" lazy="true" inverse="true">
<key column="ZusammenstellungID"/>
<one-to-many class="SUser"/>
<loader query-ref="compilationUser"/>
</set>
</class>
<sql-query name="compilationUser" cacheable="true">
<return-scalar type="int" column="ZusammenstellungID"/>
<load-collection alias="b" role="SCompilation.user"/>
SELECT DISTINCT {b.*}, bf.ZusammenstellungID AS ZusammenstellungID
FROM Benutzer b, Benutzerfilter bf
WHERE bf.Typ = 'U'
AND bf.Exclude = 0
AND b.DN LIKE bf.Filter
AND bf.ZusammenstellungID = :id
AND b.id NOT IN (
SELECT b1.ID
FROM Benutzer AS b1, Benutzerfilter AS bf1
WHERE b1.DN Like bf1.Filter
AND bf1.Typ = 'U'
AND bf1.Exclude = 1
AND bf1.ZusammenstellungID = :id)
</sql-query>
</hibernate-mapping>
session.find("select c from SCompilation c where c.id = " + username);
Name and version of the database you are using:MySQL 4.1.11
The generated SQL (show_sql=true):Code:
SELECT DISTINCT b.ZusammenstellungID as Zusammen5___, b.ID as ID__, b.ID as ID0_, b.Name as Name5_0_, b.AuswertungID as Auswertu3_5_0_, b.SystemID as SystemID5_0_, bf.ZusammenstellungID AS ZusammenstellungID
FROM Benutzer b, Benutzerfilter bf
WHERE bf.Typ = 'U'
AND bf.Exclude = 0
AND b.DN LIKE bf.Filter
AND bf.ZusammenstellungID = ?
AND b.id NOT IN (
SELECT b1.ID
FROM Benutzer AS b1, Benutzerfilter AS bf1
WHERE b1.DN Like bf1.Filter
AND bf1.Typ = 'U'
AND bf1.Exclude = 1
AND bf1.ZusammenstellungID = ?)
Login
FAQ
Search
