 JPA: Join two tables [message #1376224] |
Fri, 23 May 2014 06:08  |
Eclipse User |
|
|
|
I am trying to fetch records from Contact table based on FollowContactInfo table's hPhoneNumber column. Here my entities which I created:
@Entity
@Table(name="sms")
public class SMA {
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
@Column(name="smsid")
private long lSMSId;
@ManyToOne
@JoinColumn(name="myid", referencedColumnName="myid")
private User user;
@Column(name="contactname", unique=false, length=50, nullable=true)
private String strContactName;
@Column(name="hphonenumber", nullable=true)
private String hPhoneNumber;
@Column(name="timestamp", unique=false, length=15, nullable=false)
private String strTimestamp;
...
}
@Entity
@Table(name="followcontactsinfo")
public class FollowContactsInfo {
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
@Column(name="followcontactid")
private long lFollowContactId;
@ManyToOne
@JoinColumn(name="myid", referencedColumnName="myid")
private User user;
@Column(name="hphonenumber", nullable=true)
private String hPhoneNumber;
}
@Entity
@Table(name="user")
public class User {
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
@Column(name="myid")
private long lId;
}
I had tried executing the following:
Query query = entityManager.createQuery("SELECT m FROM SMS m JOIN (SELECT a FROM FollowContactsInfo a where m.User = :User) topconv on m.hPhoneNumber = :hPhoneNumber order by m.strTimestamp DESC");
query.setParameter("hPhoneNumber", contact.getHPhoneNumber());
query.setParameter("User", contact.getUser());
@SuppressWarnings("unchecked")
List<SMS> resultList = query.getResultList();
But got back the following error:
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Syntax error parsing [SELECT m FROM SMS m JOIN (SELECT a FROM FollowContactsInfo a where m.User = :User) topconv on m.hPhoneNumber = :hPhoneNumber order by m.strTimestamp DESC].
[25, 82] The join association path is not a valid expression.
Can someone tell me how can I fetch Contact table details based on FollowContactsInfo hPhoneNumber? Any help is appreciated.
Thanks
|
|
|
|
|
Powered by
FUDForum. Page generated in 1.32112 seconds
Home