SELECT query with composite primary key
Solution 1
To use parts of composite primary key in JPA query, you have to address them using its variable names:
public Concept findConceptById(BigInteger id) {
Query query = this.em.createQuery("SELECT conc FROM Concept conc WHERE conc.conceptPK.id =:cid order by conc.conceptPK.effectiveTime desc");
query.setParameter("cid", id);
return (Concept) query.getSingleResult();
}
I used Concept
as entity name assuming the class with @Entity
annotation is also named Concept
.
This question contains information about similar problem, you may find it useful.
Solution 2
Please try this
@SuppressWarnings("unchecked")
public Concept findConceptById(BigInteger id) {
Query query = this.em.createQuery("from Concept conc WHERE conc.conceptPK.id = :cid order by conc.conceptPK.effectiveTime desc");
query.setParameter("cid", id);
return (Concept) query.getSingleResult();
}
Make sure conceptPK has getter and setter methods in Concept class.
CodeMed
Updated on June 04, 2022Comments
-
CodeMed almost 2 years
In a spring mvc app using hibernate and jpa, I recently switched to a composite primary key using an
@Embeddable
class. As a result, I need to update the JPA query that returns a given object based on its unique id. The following is the JPA code that used to work, but which no longer returns a result:@SuppressWarnings("unchecked") public Concept findConceptById(BigInteger id) { Query query = this.em.createQuery("SELECT conc FROM Concept conc WHERE conc.id =:cid"); query.setParameter("cid", id); return (Concept) query.getSingleResult(); }
How do I change the above query so that it returns the Concept with the most recent
effectiveTime
for the givenid
? Note thatid
andeffectiveTime
are the two properties of theConceptPK
composite primary key, and that thus the property definitions and getters and setters forid
andeffectiveTime
are in theConceptPK
class and NOT in theConcept
class.The error thrown by the above is:
Caused by: java.lang.IllegalArgumentException: Parameter value [786787679] did not match expected type [myapp.ConceptPK]
This is how the primary key is now defined in the
Concept
class:private ConceptPK conceptPK;
And here is the code for the
ConceptPK
class:@Embeddable class ConceptPK implements Serializable { @Column(name="id", nullable=false) protected BigInteger id; @Column(name="effectiveTime", nullable=false) @Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime") private DateTime effectiveTime; public ConceptPK() {} public ConceptPK(BigInteger bint, DateTime dt) { this.id = bint; this.effectiveTime = dt; } /** getters and setters **/ public DateTime getEffectiveTime(){return effectiveTime;} public void setEffectiveTime(DateTime ad){effectiveTime=ad;} public void setId(BigInteger id) {this.id = id;} public BigInteger getId() {return id;} @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; final ConceptPK other = (ConceptPK) obj; if (effectiveTime == null) { if (other.effectiveTime != null) return false; } else if (!effectiveTime.equals(other.effectiveTime)) return false; if (id == null) { if (other.id != null) return false; } else if (!id.equals(other.id)) return false; return true; } @Override public int hashCode() { int hash = 3; hash = 53 * hash + ((effectiveTime == null) ? 0 : effectiveTime.hashCode()); hash = 53 * hash + ((id == null) ? 0 : id.hashCode()); return hash; } }
-
CodeMed over 9 yearsI put up a bounty on another hibernate question. Are you willing to help me with it? here is the link: stackoverflow.com/questions/25454703/…