Hello,
maybe someone can help me out with a problem. I am trying to accomplish some
complex object mapping like the one described in the user guide. I started with
a simple demo application to figure out how things work. However after trying
several times I had to give up. Maybe someone can help out.
In my use case I try to map a user to a location where he is assigned a role.
There are three tables in the database. User and Location are both not very
special, both have a integer primary key and some other columns. The third
table 'user_roles' maps users to locations and their assigned roles.
Table user_roles has three attributes. User_id, location_id and a varchar
role_type. The primary key is (user_id,location_id,role_type).
I use the following sql query to retrieve the data for a named user.
SELECT
A.ID,
A.USERNAME,
A.LASTNAME,
A.FIRSTNAME,
A.EMAIL,
A.PASSWORD,
A.CREATION_DATE,
B.ID AS LOCATION_ID,
B.NAME AS LOCATION_NAME,
B.STREET AS LOCATION_STREET,
B.ZIP AS LOCATION_ZIP,
B.CITY AS LOCATION_CITY,
C.ROLE_TYPE,
FROM USERS A
LEFT OUTER JOIN USER_ROLES C ON A.ID = C.USER_ID
LEFT OUTER JOIN LOCATIONS B ON C.LOCATION_ID = B.ID
WHERE USERNAME = <some username>
The domain objects are not very special either.
public class Location implements Serializable {
private static final long serialVersionUID = 5114535561318360375L;
private Integer id;
private String name;
private String street;
private String zip;
private String city;
// GETTER AND SETTERS
}
public class User implements Serializable {
private static final long serialVersionUID = -7754283129754600689L;
private Integer id;
private String username;
private String firstname;
private String lastname;
private String email;
private String passwordHash;
private Date creationDate;
private List<Role> roles;
// GETTER AND SETTERS
}
public class Role implements Serializable {
private static final long serialVersionUID = -3582521815310478925L;
private Location location;
private RoleType roleType;
// GETTER AND SETTERS
}
RoleType is an enum.
UserMapper.xml contains the following result map:
<resultMap type="User" id="userResultMap">
<id property="id" column="ID" />
<result property="username" column="USERNAME" />
<result property="lastname" column="LASTNAME" />
<result property="firstname" column="FIRSTNAME" />
<result property="email" column="EMAIL" />
<result property="passwordHash" column="PASSWORD" />
<result property="creationDate" column="CREATION_DATE" />
<collection property="roles" ofType="Role">
<result property="roleType" column="ROLE_TYPE" />
<association property="location" javaType="Location">
<id property="id" column="LOCATION_ID" />
<result property="name" column="LOCATION_NAME" />
<result property="street" column="LOCATION_STREET" />
<result property="zip" column="LOCATION_ZIP" />
<result property="city" column="LOCATION_CITY" />
</association>
</collection>
</resultMap>
Now to my problem. If my table user_roles has two rows like this:
USER_ID, LOCATION_ID, ROLE_TYPE
2, 2 , 'ADMIN'
2, 3 , 'EMPLOYEE'
My user object looks perfect. The property roles has two elements for both
locations. No problem. In case user_roles looks like this:
2, 2 , 'ADMIN'
2, 3 , 'ADMIN'
Then the property roles has only one element.
Any ideas? Thanks in advance!
Bjoern
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
