In hibernate how can write a query for retrieving data with a composite key?

Question

Here I have a table named receipt and its primary key is a composite key referred with other class Receipt_compoundKey with two variables named voucher_id and company_id,

How I retrive data from receipt with condition like company_id=1;

Receipt :

@Entity

@Table(name = AMAM_Constants.tb_name.RECEIPT_FROM, catalog = AMAM_Constants.db_name)
public class Receipt implements Serializable {

@Id
private Receipt_CompoundKey id;
@OneToOne
@JoinColumn(name = "FROM_LEDGER")
private Ledger fromLedger;
@Column(name = "VOU_DATE")
private Date voucher_Date;
@Column(name = "TOTAL_AMOUNT")
private double total_amount;
@Column(name = "ACTIVE")
private char active;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = AMAM_Constants.tb_name.RECEIPT_FROM_LINK_TO, joinColumns = {
    @JoinColumn(name = "COMPANY_ID"),
    @JoinColumn(name = "VOUCHER_ID")
}, inverseJoinColumns = {
    @JoinColumn(name = "RECP_TO")})
private List<Receipt_To> recptToList = new ArrayList<Receipt_To>();
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = AMAM_Constants.tb_name.RECEIPT_FROM_LINK_ADJMTS, joinColumns = {
    @JoinColumn(name = "COMPANY_ID"),
    @JoinColumn(name = "VOUCHER_ID")
}, inverseJoinColumns = {
    @JoinColumn(name = "RECPT_ADJS")})
private List<Receipt_Adj> recptAdjments = new ArrayList<Receipt_Adj>();

public List<Receipt_Adj> getRecptAdjments() {
    return recptAdjments;
}

public void setRecptAdjments(List<Receipt_Adj> recptAdjments) {
    this.recptAdjments = recptAdjments;
}

public List<Receipt_To> getRecptToList() {
    return recptToList;
}

public void setRecptToList(List<Receipt_To> recptToList) {
    this.recptToList = recptToList;
}

public char getActive() {
    return active;
}

public void setActive(char active) {
    this.active = active;
}

public double getTotal_amount() {
    return total_amount;
}

public void setTotal_amount(double total_amount) {
    this.total_amount = total_amount;
}

public Ledger getFromLedger() {
    return fromLedger;
}

public void setFromLedger(Ledger fromLedger) {
    this.fromLedger = fromLedger;
}

public Receipt_CompoundKey getId() {
    return id;
}

public void setId(Receipt_CompoundKey id) {
    this.id = id;
}

public Date getVoucher_Date() {
    return voucher_Date;
}

public void setVoucher_Date(Date voucher_Date) {
    this.voucher_Date = voucher_Date;
}

}

Receipt_CompoundKey :

@Embeddable
public class Receipt_CompoundKey implements Serializable {

@Column(name = "VOUCHER_ID")
private long voucher_Id;
@Column(name = "COMPANY_ID")
private long company_Id;

public Receipt_CompoundKey() {
}

public Receipt_CompoundKey(long voucher_Id) {
    this.voucher_Id = voucher_Id;
}

public Receipt_CompoundKey(long voucher_Id, long company_Id) {
    this.company_Id = company_Id;
    this.voucher_Id = voucher_Id;
}

public long getCompany_Id() {
    return company_Id;
}

public void setCompany_Id(long company_Id) {
    this.company_Id = company_Id;
}

public long getVoucher_Id() {
    return voucher_Id;
}

public void setVoucher_Id(long voucher_Id) {
    this.voucher_Id = voucher_Id;
}

}

Query :

String query = "from Receipt where active='Y' and  id=:id ";
begin();
objList = getSession().createQuery(query).setLong("id", key.getCompany_Id()).setLong("id", key.getVoucher_Id()).list();
commit();

Answer 1

First you have to do a select not just "from Receipt where active='Y' and id=:id " (I assume that this is unmeant mistake but just in case it is't):

String query = "
SELECT receipt 
FROM Receipt receipt 
WHERE receipt.id = :yourId "

For creating a query I use EntityManager. Here is some example class and how it works for me.

public class EntityManagerUtil {

    private static EntityManagerFactory entityManagerFactory = null;

    private static final ThreadLocal<EntityManager> entitymanager = 
        new ThreadLocal<EntityManager>();

    private static final ThreadLocal<Map<Class<?>, Set<Serializable>>>collectionFieldNameValues = 
        new ThreadLocal<Map<Class<?>, Set<Serializable>>>();

    public static EntityManagerFactory initializeEntityManagerFactory( String persistenceUnit ) {
        if ( entityManagerFactory == null ) {

            entityManagerFactory = Persistence.createEntityManagerFactory( persistenceUnit );
        }
        return entityManagerFactory;
    }

    public static EntityManager getEntityManager() {

        EntityManager entityManager = entitymanager.get();
        // Create a new EntityManager
        if ( entityManager == null || !entityManager.isOpen()) {
            entityManager = entityManagerFactory.createEntityManager();
            entitymanager.set( entityManager );
        }
        return entityManager;
    }

    public static void close() {
        final EntityManager entityManager = entitymanager.get();
        entitymanager.set( null );
        if ( entityManager != null && entityManager.isOpen()) { 
            entityManager.close();
        }
        if ( entityManagerFactory != null && entityManagerFactory.isOpen()) {
            entityManagerFactory.close();
        }
    } 
}

And in your class that you want to get the data you first initialize the EntityManagerFactory with your persistence unit (lest say it is called ReceiptPersistence). Here is how would it looks like:

EntityManagerUtil.initializeEntityManagerFactory("ReceiptPersistence");
EntityManagerUtil.getEntityManager().getTransaction().begin();
Query getReceiptWithId = EntityManagerUtil.getEntityManager().createQuery(query);
getReceiptWithId.setParameter("yourId", idThatYouWant);

Now when your query is ready you can get a single result from it with getSingleResult() :

Receipt receipt = (Receipt) getReceiptWithId.getSingleResult();
EntityManagerUtil.getEntityManager().getTransaction().commit();
EntityManagerUtil.getEntityManager().close();

if you do this is try/catch (recommended) you can do the EntityManagerUtil.getEntityManager().getTransaction().rollback(); It's needed when you are doing some changes on the DB.

I use ThreadLocal to be sure that there will be 1 thread for every user.

Answer 2

You do it as you would with a single-column ID:

getSession().createQuery(query).setParameter("id", key);

You could also query on individual values of the key, but it's unnecessary here:

String query = "select r from Receipt r where r.active = 'Y'"
               + " and id.company_Id = :companyId"
               + " and id.voucher_Id = :voucherId";
objList = getSession().createQuery(query)
                      .setLong("companyId", key.getCompany_Id()) 
                      .setLong("voucherId", key.getVoucher_Id())
                      .list();

Please please, respect the Java naming conventions: voucherId and not voucher_Id, ReceiptCompoundKey and not Receipt_CompoundKey, etc.