CODEWITHSUNDEEP
javasqlhibernatespringparameters
Cliff Casey
Cliff Casey

Reputation: 31

Consecutive named parameter problems in Hibernate (Spring HibernateTemplate)

I am trying to use named parameters to inject some strings into a query. I am using spring and hibernateTemplate, and and I am fairly new to Hibernate. I have gotten unnamed and named parameters to work in the past, but it seems as though when they are right next to each other they throw an error. I have made similar example code to my problem below (simplified to highlight the problem at hand). orderBy contains a string of a field and orderDirection contains ASC or DESC.

public List<HashMap<String, Object>> exampleList(String orderBy, String orderDirection) {
    logger.debug(orderBy);
    logger.debug(orderDirection);

    final String queryString =  "SELECT new map("+
                                "a.id AS id, "+
                                "a.name AS name, "+
                                "a.number AS number ) "+
                                "FROM Asset a "+
                                "ORDER BY :orderBy :orderDirection";

    String[] parameterNames = {"orderBy", "orderDirection"};
    Object[] parameterValues = {orderBy, orderDirection};

    List<HashMap<String, Object>> results = (List<HashMap<String, Object>>) hibernateTemplate.find(queryString, parameterNames, parameterValues);

    return results;
}

The console debugs my parameters: 

DEBUG: com.myOrg.myProject.asset.AssetDAO - a.id
DEBUG: com.myOrg.myProject.asset.AssetDAO - desc

And I know this query would run fine on its own, as I have tested it with putting the values directly into the query string. I know that this is bad practice and can expose me to injection which is why I am not doing that.

The error I get is as follows (stack trace is shortened for ease of reading, but this is the only error and I can provide full stack trace upon request):

SEVERE: Servlet.service() for servlet dmat3 threw exception
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: : near line 1, column 118 [SELECT new map(a.id AS id, a.name AS name, a.number AS number ) FROM com.gsipt.dmat3.asset.Asset a ORDER BY :orderBy :orderDirection]
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
    at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:914)
    at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:1)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912)
    at com.myOrg.myProject.asset.AssetDAO.exampleList(AssetDAO.java:142)
    at com.myOrg.myProject.navigator.NavigatorController.assetGrid(NavigatorController.java:163)

Again, this works just fine when there are separate parameters, but when they are placed together this happens. The same error can be recreated using unnamed parameters only the "unexpected token" is the second "?" in the query string. How do I put two parameters, named or unnamed, next to each other as would make sense in an ORDER BY statement.

Thanks, -Cliff

Upvotes: 3

Views: 1985

Answers (4)

James Scriven
James Scriven

Reputation: 8144

Params are for specifying values not for builing up sql syntax. Just concat the strings.

Upvotes: 0

Stevi Deter
Stevi Deter

Reputation: 1683

You cannot use parameters (named or positional) in the ORDER BY clause. You can only use them in the WHERE clause.

Upvotes: 1

matt b
matt b

Reputation: 140011

Why do you need to parameterize these anyway? As long as the values for orderBy and orderDirection are not coming from user input, you can safely just concatenate the values into the query.

if orderDirection == Sort.ASC) {
    queryString += " ASC";
}
else {
    queryString += " DESC";
}

etc

Upvotes: 0

hvgotcodes
hvgotcodes

Reputation: 120268

You are using find, not

findByNamedParams

See here. For the Spring 3 API, see here.

Also note that the HibernateTemplate documentation says

NOTE: As of Hibernate 3.0.1, transactional Hibernate access code can also be coded in plain Hibernate style. Hence, for newly started projects, consider adopting the standard Hibernate3 style of coding data access objects instead, based on SessionFactory.getCurrentSession().

Upvotes: 1

Related Questions