Eclipse Community Forums: EclipseLink

Help

Home

Home » Eclipse Projects » EclipseLink » JPA: Join two tables

Show: Today's Messages :: Show Polls :: Message Navigator

JPA: Join two tables [message #1376224]

Fri, 23 May 2014 10:08

hun k

Messages: 3
Registered: May 2014

Junior Member

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

Report message to a moderator

Re: JPA: Join two tables [message #1383881 is a reply to message #1376224]

Mon, 26 May 2014 16:03

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

JPA does not support a select in the from clause, so you cannot accomplish this in JPQL. From the look of the query, you should be fine just using the Contact table as none of the clauses seem to touch FollowContactsInfo. If a native query is causing you a deadlock, you need to find out what is involved in the deadlock and fix it. Is it a java deadlock or a database lock? Something like "select distinct c from Contact c, f Followcontactsinfo where c.user = f.user and c.user= :User order by c.timestamp" should work

Report message to a moderator