I have an outer join on two tables, user u and contact c. Both have a first_name, last_name; joining on user_id. I am trying to coalesce(u.first_name, c.first_name) as first_name. So... if user dosen't have a first/last name then use contact first/last name as the contact data is never null. I am returning a list of users, but only the user data is populated and the contact data is always null. If I execute the same sql from the loggers if get the correct results? I'm thinking it is a mapping issue??? Please help, I have tried everything.
code below...
Code:
public class User implements persistent{
@entity
@Table(name="user")
...
@SqlResultSetMappings({
@SqlResultSetMapping(name="contactUser", entities=@EntityResult(entityClass=...model.User.Class,
fields={@FieldResult(name="id", column="user_id"),
@FieldResult(name="firstName", column="FIRST_NAME"),
@FieldReuslt(name="lastName", column="LAST_NAME")}
})
@NamedNativeQueries({
@NamednativeQuery(name="User.WithContact",
query="select u.user_id, coalesce(u.first_name, c.first_name) as first_name, coalesce(u.last_name, c.last_name) as last_name from user u left outer join contact c on (u.user_id = c.contact_user_id", resultSetMapping="contactUser")
})
.....
@Id
@Column(name="user_id")
private Long id
@Column(name="first_name")
private String firstName;
@Column(name="last_name")
private String lastname;
...
public class Contact implements persistent{
@Id
@Column(name="contact_id")
private Long id
@Column(name="contact_user_id")
private Long userId
@Column(name="first_name")
private String firstName;
@Column(name="last_name")
private String lastName;
...
sample data...
user
id first_name last_name
1 ted rose
2 ron jeremy
3 fat frank
4 null null
5 null null
contact
id user_id first_name last_name
1 4 ron micD
2 5 jerry mcloven
The returned user object user_id 4 and 5 the first/last names are still null???
many thanks if anyone can help.
Login
FAQ
Search