I have two entities, a User and Role with a one-to-many relationship from user to role. Here's what the tables look like:
mysql> select * from User;
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 1 | admin | admin |
+----+-------+----------+
1
row in set (0.00 sec)
mysql> select * from Role;
+----+----------------------+---------------+----------------+
| id | description | name | summary |
+----+----------------------+---------------+----------------+
| 1 | administrator's role | administrator | Administration |
| 2 | editor's role | editor | Editing |
+----+----------------------+---------------+----------------+
2 rows in set (0.00 sec)
And here's the join table that was created:
mysql> select * from User_Role;
+---------+----------+
| User_id | roles_id |
+---------+----------+
| 1 | 1 |
| 1 | 2 |
+---------+----------+
2 rows in set (0.00 sec)
And here's the subset of orm.xml that defines the tables and relationships:
<entity class="User" name="User">
<table name="User" />
<attributes>
<id name="id">
<generated-value strategy="AUTO" />
</id>
<basic name="name">
<column name="name" length="100" unique="true" nullable="false"/>
</basic>
<basic name="password">
<column length="255" nullable="false" />
</basic>
<one-to-many
name="roles"
fetch="EAGER"
target-entity="Role"
/>
</attributes>
</entity>
<entity class="Role" name="Role">
<table name="Role" />
<attributes>
<id name="id">
<generated-value strategy="AUTO"/>
</id>
<basic name="name">
<column name="name" length="40" unique="true" nullable="false"/>
</basic>
<basic name="summary">
<column name="summary" length="100" nullable="false"/>
</basic>
<basic name="description">
<column name="description" length="255"/>
</basic>
</attributes>
</entity>
Yet, despite that, when I retrieve the admin user, I get back an
empty collection. I'm using Hibernate as my JPA provider and it shows the following debug SQL:
select
user0_.id as id8_,
user0_.name as name8_,
user0_.password as password8_
from
User user0_
where
user0_.name=? limit ?
When the one-to-many mapping is lazy loaded, that's the only query that's made. This correctly retrieves the one admin user. I changed the relationship to use eager loading and then the following query is made in addition to the above:
select
roles0_.User_id as User1_1_,
roles0_.roles_id as roles2_1_,
role1_.id as id9_0_,
role1_.description as descript2_9_0_,
role1_.name as name9_0_,
role1_.summary as summary9_0_
from
User_Role roles0_
left outer join
Role role1_
on roles0_.roles_id=role1_.id
where
roles0_.User_id=?
Which results in the following results:
+----------+-----------+--------+----------------------+---------------+----------------+
| User1_1_ | roles2_1_ | id9_0_ | descript2_9_0_ | name9_0_ | summary9_0_ |
+----------+-----------+--------+----------------------+---------------+----------------+
| 1 | 1 | 1 | administrator's role | administrator | Administration |
| 1 | 2 | 2 | editor's role | editor | Editing |
+----------+-----------+--------+----------------------+---------------+----------------+
2 rows in set (0.00 sec)
Hibernate obviously knows about the roles, yet getRoles() still returns an
empty collection. Hibernate also recognized the relationship sufficiently to put the data in the first place.
What problems can cause these symptoms?
