Getting could not locate named parameter from Hibernate Query in Tridion 2011

Question

I have below method in my DAO class.

public PublishAction findbyLatestPublishedDate(int tcmURI,int pubID) throws StorageException
{
    log.info("Entering Method: JPAPublishActionDAO.PublishAction.findbyLatestPublishedDate");
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("select pa from PublishAction pa where pa.id in(select pb.id from PublishAction pb where pb.ITEM_REFERENCE_ID=:tcmURI and pb.PUBLICATION_ID=:pubID and rownum <= 1 order by pb.LAST_PUBLISHED_DATE desc)");

    Map<String, Object> queryParams = new HashMap<String, Object>();   
    queryParams.put("ITEM_REFERENCE_ID", tcmURI);
    queryParams.put("PUBLICATION_ID", pubID);

    log.debug("JPAPublishActionDAO findbyLatestPublishedDate -> queryBuilder- "+ queryBuilder.toString());        
    return executeQuerySingleResult(queryBuilder.toString(), queryParams);
}

It is giving below error:

2013-01-16 12:17:01,381 ERROR DeployPipelineExecutor - Original stacktrace for transaction: tcm:0-5607662-66560
java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter [PUBLICATION_ID]

This is query generated by HQL internally in my logs:

2013-01-16 12:17:01,365 DEBUG QueryTranslatorImpl - SQL: select publishact0_.ID as ID66_, publishact0_.ITEM_REFERENCE_ID as ITEM2_66_, publishact0_.LAST_PUBLISHED_DATE as LAST3_66_, publishact0_.PUBLICATION_ID as PUBLICAT4_66_, publishact0_.ACTION as ACTION66_, publishact0_.FLAG as FLAG66_, publishact0_.ITEM_TYPE as ITEM7_66_, publishact0_.SCHEMA_ID as SCHEMA8_66_, publishact0_.URL as URL66_ from AUTN_ITEMS publishact0_ where publishact0_.ID in (select publishact1_.ID from AUTN_ITEMS publishact1_ where publishact1_.ITEM_REFERENCE_ID=? and publishact1_.PUBLICATION_ID=? and rownum<=1 order by publishact1_.LAST_PUBLISHED_DATE desc)

I can see the PUBLICATION_ID column exists in my entity as well as in my SQL Table.

Please suggest.

Answer 1

The beautiful thing about JPQL (or HQL) is the fact that you can use in your queries the properties of your Java class. You should not combine JPQL syntax with plain SQL syntax. You should replace pb.ITEM_REFERENCE_ID=:tcmURI, pb.PUBLICATION_ID=:pubID and pb.LAST_PUBLISHED_DATE with pb.tcmuri=:tcmURI, pb.publicationId=:pubID and pb.last_published_date. In addition to this, your parameter map should contain tcmURI and pubID instead of what you placed in there. Please note that I replaced the columns in the database with the actual fields from your entity. As a conclusion, your method should look something like this:

queryBuilder.append("select pa from PublishAction pa where pa.id in(select pb.id from PublishAction pb where pb.tcmuri=:tcmURI and pb.publicationId=:pubID and rownum <= 1 order by pb.last_published_date desc)");

Map<String, Object> queryParams = new HashMap<String, Object>();   
queryParams.put("tcmURI", tcmURI);
queryParams.put("pubID", pubID);

Alternatively you can just split your query in 2 like this:

StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append("from PublishAction pb where pb.tcmuri=:tcmURI and pb.publicationId=:pubID order by pb.last_published_date desc");

Map<String, Object> queryParams = new HashMap<String, Object>();   
queryParams.put("tcmuri", tcmURI);
queryParams.put("pubID", pubID);

final List<PublishAction> myActions = super.executeQueryListResult(queryBuilder.toString(), queryParams, 1);

if (myActions != null && !myActions.isEmpty()) {
    StringBuilder queryBuilderFinal = new StringBuilder();
    queryBuilderFinal.append("select pa from PublishAction pa where pa.id=:myId");

    Map<String, Object> queryParamsFinal = new HashMap<String, Object>();   
    queryParamsFinal.put("myId", myActions.get(0).getId());

    return super.executeQuerySingleResult(queryBuilderFinal.toString(), queryParamsFinal)
}

Note that I can only guess what are the names of the properties in your Java class so I just guessed that your have the tcmuri, publicationId and last_published_date properties.

Hope this helps.