Hibernate - New colums from a joined table

Question

I have a class User object. I am trying to load this object from the Database using Hibernate. My SQL statement is:

SELECT
 users.uid          AS uid,
 users.deleted   AS deleted,
 users.username   AS username,
 users.passwd   AS passwd,
 users.disabled   AS disabled,
 users.lockout   AS lockout,
 users.expires   AS expires,
 data_firstname.value         AS firstname,
 data_lastname.value  AS lastname 
FROM 
 ac_users as users
LEFT JOIN
 ac_userdef_data as data_firstname
ON
 users.uid = data_firstname.parentuid AND
 data_firstname.fieldname like 'firstname'
LEFT JOIN
 ac_userdef_data as data_lastname
ON
 users.uid = data_lastname.parentuid AND
 data_lastname.fieldname like 'lastname'
WHERE
 users.deleted = 0

My mapping for the User class is:

<class name="com.agetor.commons.security.User" table="ac_users">
    <id name="uid" column="uid" type="long" >
        <generator class="native"/>
    </id>
    <property name="deleted" column="deleted" />
    <property name="username" column="username" />   
    <property name="password" column="passwd" />
 <property name="firstname" column="firstname" />
 <property name="lastname" column="lastname" />
    <property name="disabled" column="disabled" />
    <property name="lockout" column="lockout" />
    <property name="expires" column="expires" />            
</class>

My problem is that the table ac_users does not have a column 'firstname' or 'lastname' These columns, only exist in my resultset from the SQL-join statement. They do also not exist in the ac_userdef_data table. There i have 2 colums: fieldname and value. and 2 rows: fieldname = 'firstname' with some value in the value column and another row with fieldname = 'lastname' with some value in the value column

How do i change my mapping file, so that Hibernate will understand that it needs to load the firstname and lastname column into my firstname and lastname fields on my POJO, while those columns dont actually exist on the referenced ac_users table.?

Answer 1

I now have some working code. The trick was to specify a loading Query for the User class. Hibernate then validates against the return from the Query instead of the Table design.

Class mapping

<class name="com.agetor.commons.security.User" table="ac_users">
    <id name="uid" column="uid" type="long" >
        <generator class="native"/>
    </id>
    <property name="deleted" column="deleted" />
    <property name="username" column="username" />   
    <property name="password" column="passwd" />
 <property name="firstname" column="firstname" />
 <property name="lastname" column="lastname" />
    <property name="disabled" column="disabled" />
    <property name="lockout" column="lockout" />
    <property name="expires" column="expires" />                 


    <loader query-ref="GetAllUsers" />
</class>

My Query declaration

<sql-query name="GetAllUsers">
  <return alias="user" class="com.agetor.commons.security.User" />
  <![CDATA[ 
    SELECT
     users.uid    AS uid,
     users.deleted   AS deleted,
     users.username   AS username,
     users.passwd   AS passwd,
     users.disabled   AS disabled,
     users.lockout   AS lockout,
     users.expires   AS expires,
     data_firstname.value AS firstname,
     data_lastname.value  AS lastname 
    FROM 
     ac_users as users
    LEFT JOIN
     ac_userdef_data as data_firstname
    ON
     users.uid = data_firstname.parentuid AND
     data_firstname.fieldname like 'firstname'
    LEFT JOIN
     ac_userdef_data as data_lastname
    ON
     users.uid = data_lastname.parentuid AND
     data_lastname.fieldname like 'lastname'
    WHERE
     users.deleted = 0
  ]]>
</sql-query>

I had to use the line <return alias="user" class="com.agetor.commons.security.User" /> so that the returned collection was typed correctly

Answer 2

The solution to this question is the same as to my other question. So im linking to it here and accepting the answer.

http://stackoverflow.com/questions/507358/hibernate-one-to-many-unidirectional-on-an-existing-db