How to write JPQL SELECT with embedded id?

Question

I'm using Toplink essentials (JPA) + GlassFish v3 + NetBean 6.9

I have one table with composite primary key:

table (machine)
----------------
|PK machineId  |
|PK workId     |
|              |
|______________|

I created 2 entity classes one for entity itself and second is PK class.

public class Machine {
   @EmbeddedId
   protected MachinePK machinePK;

   //getter setters of fields..
}

public class MachinePK {
    @Column(name = "machineId")
    private String machineId;

    @Column(name = "workId")
    private String workId;

}

Now.. how do I write SELECT clause with JPQL with WHERE???

This fails.

SELECT m FROM Machine m WHERE m.machineId = 10

http://www.mail-archive.com/users@openjpa.apache.org/msg03073.html

According to the web page, add "val"? No it fails too.

   SELECT m FROM Machine m WHERE m.machineId.val = 10

In both case, the error is:

    Exception Description: Error compiling the query 
[SELECT m FROM Machine m WHERE m.machineId.val = 10], 
line 1, column 30: unknown state or association field 
[MachineId] of class [entity.Machine].

score 96 · Accepted Answer · answered Jan 13 '11 at 06:07

96

SELECT m FROM Machine m WHERE m.machinePK.machineId = 10

answered Jan 13 '11 at 06:07

Adeel Ansari

39,541
12
93
133

2

How to convert that to JPA Criteria? – Yazan Jaber Feb 21 '12 at 21:47
SELECT m FROM Machine m WHERE m.machinePK.machineId = :criteria – Darrell Teague Mar 31 '13 at 19:18
if using HQL could you also do SELECT m FROM Machine m WHERE m.machinePK in (:listOfKeys) ? http://stackoverflow.com/questions/30095676/composite-key-query-using-hql-and-separate-idclass – NimChimpsky May 07 '15 at 08:34
i have a situation where the fields in the embeddable class is also other user defined classes and i have to evaluate a field in that user defined class. any idea how to do that? @Query("SELECT e from ElectionContestant e where e.primaryKey.contestant.contestId=?#{0} AND e.primaryKey.election.elecId=?#{1}") – Dilini Peiris Nov 12 '18 at 17:07

Marco · Answer 2 · 2016-07-19T16:29:14.430

Tested with Hibernate 4.1 and JPA 2.0

Make the following changes in order to work:

Class Machine.java

public class Machine {
   @EmbeddedId
   protected MachinePK machinePK;

   //getter & setters...
}

Class MachinePK.java

@Embeddable
public class MachinePK {
    @Column(name = "machineId")
    private String machineId;

    @Column(name = "workId")
    private String workId;

    //getter & setters...
}

...and for the JPQL query pass all column names:

Query query = em.createQuery("SELECT c.machinePK.machineId, c.machinePK.workId, "
                + "FROM Machine c WHERE c.machinePK.machineId=?");
query.setParameter(1, "10");

Collection results = query.getResultList();

Object[] obj = null;
List<MachinePK> objPKList = new ArrayList<MachinePK>();
MachinePK objPK = null;
Iterator it = results.iterator();
while(it.hasNext()){
    obj = (Object[]) it.next();
    objPK = new MachinePK();
    objPK.setMachineId((String)obj[0]);
    objPK.setWorkId((String)obj[1]);
    objPKList.add(objPK);
    System.out.println(objPK.getMachineId());
}

score 0 · Answer 3 · answered Nov 15 '16 at 14:23

0

If you use annotation @Query, you can use element nativeQuery = true.

answered Nov 15 '16 at 14:23

Yura Shinkarev

5,134
7
34
57

How to write JPQL SELECT with embedded id?

3 Answers3

Linked