Hibernate SQLQuery - Get Object by name

Question

I have a method to get a Category by its "id" and I need a similar method to get a Category by its "name". I did these methods by using Hibernate. How can I fix my second method to get a Category by name? My source code is as follow:

// It works
@Override
public Category getById(int id) {
    return (Category) sessionFactory.getCurrentSession().get(Category.class, id);
}

// It doesn't works
@Override
public Category getByName(String name) {
    return (Category) sessionFactory.getCurrentSession().
        createSQLQuery("SELECT FROM Category WHERE name="+name);
}

I have this error with the second method:

java.lang.ClassCastException: org.hibernate.impl.SQLQueryImpl cannot be cast to com.sedae.model.Category

These are my controllers.

@RequestMapping(value = "/getCategoryById/{id}")
public String getCategoryById(Model model, @PathVariable ("id") int id){
    model.addAttribute("category", categoryService.getById(id));
    return "/getCategoryById";
}

@RequestMapping(value = "/getCategoryByName/{name}")
public String getCategoryByName(Model model, @PathVariable("name") String name){
    model.addAttribute("category", categoryService.getByName(name));
    return "/getCategoryByName";
}

Thanks in advance people.

Solution

If you are sure you have only one entry per category name in your table then you can use Query#uniqueResult:

Query query= sessionFactory.getCurrentSession().
        createQuery("from Category where name=:name");
query.setParameter("name", name);
Category category = (Category) query.uniqueResult();

Make sure to handle the exceptions thrown by uniqueResult.

Comments

[wilson]

Thanks dude! One question more please. Suppose I have two categories (with different ids, obviously) How can I get an specific category by avoiding the error "query did not return an unique result: 2". ?

[user3487063]

In that case the query returns a list,you can iterate over the list and get the object you want or if you want any one of the object with same category name then you can use query.setMaxResults(1);, this returns the first encountered category with the specified category name.

[wilson]

Hi @user3487063, thanks for the answer, I've changed my method, now it has two parameters "id" and "name", and when I need to make a list by name my query select the category by id and name, there is no problem with duplicated names. Thanks for ask dude.