How-to: Write a named query for an entity bean with composite key

I was working on an EJB3 project where I had to write an entity bean with a composite primary key. And I needed to write a named query to which had to use a sub set of the composite key. And this is what I did.

The entity bean and the composite key:

import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table (name = "myTable")
public class MyEntityBean implements Serializable
{
    @EmbeddedId
    private MyCompositeKey key;

    // Other instance members

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

    // Getters & setters
    ...
}
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Embeddable;

@Embeddable
public class MyCompositeKey implements Serializable
{
    @Column (name = "key1", nullable = false)
    private String key1;

    @Column (name = "key2", nullable = false)
    private String key2;

    // Getters & setters
    ....
}

The equivalent database table will look like:

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| key1          | varchar(255) | NO   | PRI |         |       |
| key2          | varchar(255) | NO   | PRI |         |       |
| value         | varchar(255) | YES  |     |         |       |
+---------------+--------------+------+-----+---------+-------+

Writing a named query which makes use of the elements of the composite key is a li’l tricky. Take a look at the following SQL statement which tries to fetch all the records from the table for a matching key1.

SELECT * FROM myTable WHERE key1='some value';

Embedding the named query in MyEntityBean.

@Entity
@Table (name = "myTable")
@NamedQueries
({
    @NamedQuery(
        name = "myQuery",
        query = "SELECT FROM MyEntityBean b WHERE key.key1 = :key1" )
})
public class MyEntityBean implements Serializable
{
    ...
}

We should be careful about the use of the variable name in the named query. In the named query, we are supposed to use the name of the composite key exactly the same as what we have specified in the bean.

This query can be accessed from a session bean with the help of EntityManager.

import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Remote
@Stateless (name = "MySessionBean")
public class MySessionBeanImpl implements SessionBean
{
    @PersistenceContext
    private EntityManager entityManager;

    public void myMethod(String key)
    {
    	Query aQuery = null;

    	aQuery = this.entityManager.createNamedQuery("myQuery");
        aQuery.setParameter("key1", key);

        List resultList = aQuery.getResultList();

        .....
    }
}

Hope this helps. Have a good day 🙂

Advertisements