Hibernate HQL - include columns from PK into query

Question

I've used Spring Roo to reverse engineer an existing database in order to get hibernate modelling on the existing database, in the cases where composite primary keys exist, it generates XyzClass and XyzClassPK.

If I want to do an HQL query on some of the columns that are part of the composite primary key, I get an error, let's say the composite primary key consists of primaryKey1 and primaryKey2:

String hql = "select o from XyxClass o where primaryKey1 = :key1 and nonPrimaryKey = :key2";
    TypedQuery<XyzClass> query = entityManager().createQuery(hql, XyzClass.class);
    query.setParameter(......
    query.setParameter(......
    query.getResultList()

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'primaryKey1' in 'where clause' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)

These are the annotations generated by ROO:

@RooJavaBean
@RooToString
@RooJpaActiveRecord(identifierType = XyzClassPK.class, versionField = "", table = "XYZ_CLASS")
@RooDbManaged(automaticallyDelete = true)
public class XyzClass {

And the Primary Key:

@RooIdentifier(dbManaged = true)
public final class XyzClassPK {

    @Column(name = "PRIMARY_KEY1", nullable = false)
    private Integer primaryKey1;

    @Column(name = "PRIMARY_KEY2", nullable = false)
    private Integer primaryKey2;

My HQL is obviously incorrect, how do I include composite primary keys in my hql query?

Julien Julien · Accepted Answer · 2014-02-27T10:42:19

Your HQL should look like this:

String hql = "select o from XyxClass o where o.xyz.primaryKey1 = :key1 and o.nonPrimaryKey = :key2";

Where xyz is the name of your Id field in XyxClass (I can't see it in your code..) Hope this helps!

Hibernate HQL - include columns from PK into query

2 Answers