CODEWITHSUNDEEP
javasqlhibernatejpa
CodeMed
CodeMed

Reputation: 9189

SELECT query with composite primary key

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;
    }
}

Upvotes: 3

Views: 24491

Answers (2)

Alexey Malev
Alexey Malev

Reputation: 6533

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.

Upvotes: 6

Zahid M
Zahid M

Reputation: 1063

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.

Upvotes: 4

Related Questions