SELECT query with composite primary key

Question

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 given id? Note that id and effectiveTime are the two properties of the ConceptPK composite primary key, and that thus the property definitions and getters and setters for id and effectiveTime are in the ConceptPK class and NOT in the Concept 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;
    }
}

I think you should consider query `SELECT conc FROM Concept conc WHERE conc.id.id =:cid order by conc.id.effectiveTime desc` and get its `uniqueResult()`. — Alexey Malev, May 05 '14 at 19:36
@AlexeyMalev Thank you. Do you see that I would need to create a `conceptPK`? And that `effectiveTime` is not nullable in the code above? So I don't see how I can get the most recent `effectiveTime` without making some changes. In the query function above, `id` is a biginteger and not the primary key. — CodeMed, May 05 '14 at 19:42
Why would you need to create a `conceptPK`? Where? Its fields is not nullable, yes, but I do not see how this relates to the question and my previous comment. Regarding `Concept` class - I assumed it has `ConceptPK` in the field named `id`. If it's not - just rename the `id` between two dots in my query above. — Alexey Malev, May 05 '14 at 19:48
@AlexeyMalev I tried your code and got the following new error: `Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Concept is not mapped [SELECT conc FROM Concept conc WHERE conc.conceptPK.id =:cid order by conc.conceptPK.effectiveTime desc]`. What do you suggest? — CodeMed, May 05 '14 at 19:53
First - remove `order by` clause and check whether the error is still here. Second - put a `Concept` class also here, I think taking a look at its mapping may help resolving your problem. Third - do not be such agressive. — Alexey Malev, May 05 '14 at 19:57
@AlexeyMalev I did not intend to sound aggressive. Sorry. I did remove the `order by` clause but got the same error. I uploaded `Concept` to a file sharing site. What do you think? Here is the link: https://jumpshare.com/v/aYAM5sqo64M8Lb8DZfiv?b=1PQWFj47uEeL0Tqvpx5o — CodeMed, May 05 '14 at 20:05
Well.. it seems your entity name is not `Concept`. Try replacing `Concept` with `SnomedConcept` in the query in `from` clause. — Alexey Malev, May 05 '14 at 20:10
@AlexeyMalev Yes, I know. I already accounted for that. I just removed Snomed for the posting. I am sorry if that caused confusion, but that is not the problem. What else do you see? — CodeMed, May 05 '14 at 20:11
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/52062/discussion-between-alexey-malev-and-codemed) — Alexey Malev, May 05 '14 at 20:17
@AlexeyMalev your `conc.conceptPk.id` approach solved my problem. If you post that as an answer, I will mark it as the accepted answer, because you said it before the other answerer said it. — CodeMed, May 05 '14 at 23:28

score 6 · Accepted Answer · edited May 23 '17 at 11:47

6

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.

edited May 23 '17 at 11:47

Community

1
1

answered May 05 '14 at 23:49

Alexey Malev

6,408
4
34
52

I put up a bounty on another hibernate question. Are you willing to help me with it? here is the link: http://stackoverflow.com/questions/25454703/constraint-violation-when-persisting-one-to-many-relation – CodeMed Aug 26 '14 at 02:00

score 4 · Answer 2 · answered May 05 '14 at 20:52

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.

SELECT query with composite primary key

2 Answers2