How do i use HibernateTemplate.findByNamedParam() ??

Question

HI, i am trying to use the above spring hibernate temnplate method do a simple query based on a specific ID from the database but the problem is that the query doesnt replace the ":" character from the sql string below into the value contained in "id".

i thought that this method replaces ":" with the given parameter i set in the method bit it doesnt?

Code is below:

private static final String SQL_GET_FILE = "select new FileObject(filename, size, id, type, file) from FileObject where id = : limit 1";

FileObject file = (FileObject) hbTemplate.findByNamedParam(SQL_GET_FILE, "id", id); 

//File object POJO:

package com.kc.models;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.SQLException;

import org.hibernate.Hibernate;

public class FileObject {

    private String filename;
    private String type;
    private double size;
    private Blob file;
    private int id;

    public FileObject() {

    }

    public FileObject(String name, double size, int id, String type) {
        this.filename = name;
        this.type = type;
        this.size = size;
        this.id = id;

    }

    public FileObject(String name, double size, int id, String type, Blob file) {
        this.filename = name;
        this.type = type;
        this.size = size;
        this.id = id;
        this.file = file;

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFilename() {
        return filename;
    }

    public void setFilename(String fileName) {
        this.filename = fileName;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public double getSize() {
        return size;
    }

    public void setSize(double size) {
        this.size = size;
    }

    public Blob getFile() {
        return file;
    }

    public void setFile(Blob file) {
        this.file = file;
    }

}

The exception i get is basically this:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 1 near line 1, column 104 [select new FileObject(filename, size, id, type, file) from com.kc.models.FileObject where id = : limit 1]
    org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
    org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
    org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
    org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
    org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
    org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
    org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
    org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
    org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
    org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
    org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
    org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
    org.springframework.orm.hibernate3.HibernateTemplate$31.doInHibernate(HibernateTemplate.java:949)
    org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
    org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    org.springframework.orm.hibernate3.HibernateTemplate.findByNamedParam(HibernateTemplate.java:947)
    org.springframework.orm.hibernate3.HibernateTemplate.findByNamedParam(HibernateTemplate.java:938)
    com.kc.models.DbFileHelper.getFile(DbFileHelper.java:81)
    com.kc.models.FileHelper.getFileFromDb(FileHelper.java:195)
    com.kc.Controllers.DownloadAppController.handle(DownloadAppController.java:48)
    org.springframework.web.servlet.mvc.AbstractCommandController.handleRequestInternal(AbstractCommandController.java:84)
    org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
    org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

Now i have temporarily done a quick fix by simply doing the above

private static final String SQL_GET_FILE = "select new FileObject(filename, size, id, type, file) from FileObject where id = ";


List<FileObject>  file =  hbTemplate.find(SQL_GET_FILE+id);

But i dont like the idea of joining a query string with +.

it would get tedius if i have a sql looking something like this:

SQL_GET_FILE = "select new FileObject(filename, size, id, type, file) 
  from FileObject where id = 10 && size < 1000 && type = jpg";

cheers in advance

Answer 1

You should give the parameter a name, not just a colon:

where id = :id

Also, don't use LIMIT - use template.setMaxResults()

Actually, I would advise for using the hibernate Session directly - the HibernateTemplate is something that the guys at Hibernate criticize a lot - see here the comments by Gaving King.

You can still use HibernateTemplate, but for features (like setFirstResult(..)) you can use the Session.

Finally, I think using EntityManager is the best choice. Spring offers very good JPA integration as well.