Help with foreign key mapping in Hibernate (Spring)?

Question

I have the following two table (which are tied in with Spring security - but the issue I believe is with Hibernate):

Table user
Table authority

The user table is tied (through Hibernate) to a domain object in my application: class User, which has the following attributes (and corresponding getters and setters), which correspond to columns in the user table (except for the Collection which is explained later):

long uId
String username
String password
...
Collection<GrantedAuthority> authorities

The authority table has 2 columns: UserId (foreign key into user table), and Authority (e.g. "ROLE_USER"). This table is NOT represented as a domain object, but is simply a collection in the User class.

To create the mapping, in my .hbm file I use the following:

<class name="com.business.project.domain.User" table="user">
    <id name="uId" column="UserId"></id>
    <property name="username" column="Name" type="java.lang.String" />
    <property name="password" column="Password" type="java.lang.String" />
    ...
    <set name="authorities" table="authority">
        <key column="UserId" />
        <element column="Authority" type="java.lang.String" />
    </set>
</class>

In my hibernate DAO implementation, I create a query object, execute the query, and cast the result to a User object:

...
Query query = session.createQuery("from User where name = :username");
...
User user = (User) query.uniqueResult();

At this point, I would expect this object to be populated with the data that it pulled from the DB (I made sure that the tables are populated properly with test data and the mapping names are correct in the mapping file).

However, calling the getter methods for various attributes on the user object all return NULL for some reason. Can somebody see something immediately wrong with my setup? Perhaps I mapped the collection (to foreign key relationship) wrong? THANKS!

Update: here is the sql query that hibernate generated (taken from its DEBUG output):

Hibernate: select user0_.UserId as UserId1_, user0_.Name as Name1_, 
  user0_.Password as Password1_ from user user0_ where user0_.Name=?

For some reason, it doesn't show anything related to the authority table...does this mean that my mapping is incorrect?

Edit: Per bozho's suggestion, I took a look at the messages on the consol on startup (tomcat), but didn't see anything out of the ordinary:

Feb 16, 2010 10:35:12 AM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: com.me.project.domain.User -> user
Feb 16, 2010 10:35:12 AM org.hibernate.cfg.HbmBinder bindCollection
INFO: Mapping collection: com.me.project.domain.User.authorities -> authority

Answer 1

Try with "from User where username = :username"

HQL uses class properties, not db column names

Answer 2

Query query = session.craeteQuery("FROM User WHERE username = :username");
query.setString("username", "SomeUserName");

That should pretty much do it.

Answer 3

Hi, Are you clearing the database everytime you run the test/java code? In hibernate there is a setting and when it is turned on, it can clear the database everytime a test/java code is run.

Can you do a findAll() and print the size? I very much suspect it is the wrong db, or no data in the db or clearing of data.

NOTE: Check for "hibernate.hbm2ddl.auto" property in your config file. If it is set to "create-drop" hibernate will auto create the schema on startup and delete the schema when VM shuts down. Just point it to "update" or something

http://docs.atlassian.com/hibernate2/2.1.8/reference/session-configuration.html

Answer 4

"Password" usually is a keyword in databases. It seems like you're hitting a name collision problem. Try the following. Put tick mark around the column name for escaping.

<property name="username" column="`Name`" type="java.lang.String" />
<property name="password" column="`Password`" type="java.lang.String" />

Assume you're using org.springframework.security.GrantedAuthority, then your mapping for authorities is incorrect. Based on the way you're mapping, when you access the collection, you most likely will get a ClassCastException. You probably want to use something like UerType to avoid the problem.