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.

Daniel Neagu · Accepted Answer · 2013-01-16T14:59:56.743

4

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.

edited Jan 16 '13 at 14:59

answered Jan 16 '13 at 09:15

Daniel Neagu

1,711
11
13

Hi Daniel, thanks for reply now I am getting wiered error for order by clause saying com.microsoft.sqlserver.jdbc.SQLServerException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. – Manoj Singh Jan 16 '13 at 09:46
It's not as simple as it might seem. The easiest way to workaround it would be to split your query in 2: "select pb.id from PublishAction pb where pb.tcmuri=:tcmURI and pb.publicationId=:pubID order by pb.lastPublishDate desc" and seconde one "select pa from PublishAction pa where pa.id=:myId" where myId is comming from the first query. – Daniel Neagu Jan 16 '13 at 11:05
Hi Can you please update sample code in your ansewer as it is not much clear with your comments – Manoj Singh Jan 16 '13 at 11:07
my final Object myId = super.executeQuerySingleResult(sb.toString(), queryParams); is always coming null so no result is returned – Manoj Singh Jan 16 '13 at 11:46
Hi there. I updated my answer a bit: made sure we load the whole entity and then we get it's id. Check if this works. There was also a small mistake that I was using the wrong StringBuilder to create the first query. – Daniel Neagu Jan 16 '13 at 11:59
1

Hi Daniel, One question above code will generate exception as queryBuilder.append("from PublishAction pb where pb.tcmuri=:tcmURI and pb.publicationId=:pubID order by pb.last_published_date desc"); will have mutiple records for same entry and you are using executeQuerySingleResult in final PublishAction myAction = super.executeQuerySingleResult(queryBuilder.toString(), queryParams); and this will generate list return super.executeQueryListResult(queryBuilderFinal.toString(), queryParamsFinal), however I want latest last record entry for tcmuri and publication id...please suggest – Manoj Singh Jan 16 '13 at 13:39
Hi there. Sorry, forgot about that. Please use the super.executeQueryListResult(queryBuilder.toString(), queryParams, 1) method instead of super.executeQuerySingleResult(queryBuilder.toString(), queryParams);. This will limit the results to 1. I will update this also in my answer. – Daniel Neagu Jan 16 '13 at 14:09
Hi Daniel, one more thing if my second query is going to return single result then why not to use executeQuerySingleResult instead of return super.executeQueryListResult(queryBuilderFinal.toString(), queryParamsFinal) Please suggest!! – Manoj Singh Jan 16 '13 at 14:37
Hi there. If you're certain that you will never get more than 1 result than please just replace it in the code. I will also update my answer with this information. – Daniel Neagu Jan 16 '13 at 14:58

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

1 Answers1