Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPA: Join two tables
icon7.gif  JPA: Join two tables [message #1376224] Fri, 23 May 2014 10:08 Go to next message
hun k is currently offline hun kFriend
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
Re: JPA: Join two tables [message #1383881 is a reply to message #1376224] Mon, 26 May 2014 16:03 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1053
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
Re: JPA: Join two tables [message #1401806 is a reply to message #1383881] Wed, 16 July 2014 05:26 Go to previous message
hun k is currently offline hun kFriend
Messages: 3
Registered: May 2014
Junior Member
Thank you for the reply. This worked.
Previous Topic:running dbws from eclipse and deploying on tomcat/geronimo
Next Topic:EclipseLink - @OneToOne mapping does not update MongoDB(NoSQL)
Goto Forum:
  


Current Time: Sat Mar 07 00:24:27 GMT 2015

Powered by FUDForum. Page generated in 0.01687 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software