How to use setParameterList () method in Hibernate?

Question

I have a requirement to retrieve selected rows from an ids based Oracle database, supplied as an array, something like a SELECT ... FROM table_name WHERE id IN() query.

In my attempts to do this, I am trying to use the org.hibernate.setParameterList(String name, Object[] values) method in my DAO as follows.

 @Service @Transactional(readOnly = true, propagation=Propagation.REQUIRES_NEW) public final class ProductImageDAO implements ProductImageService { @SuppressWarnings("unchecked") public List<Object[]> getFileName(String[] list) { return sessionFactory .getCurrentSession() .createQuery("SELECT prodImageId, prodImage FROM ProductImage WHERE prodImageId=:list") .setParameterList("list", list).list(); } } 

A parameter of type String[] in this method is provided from the corresponding Spring controller class.

This raises the following exception.

org.hibernate.hql.ast.QuerySyntaxException: unexpected token :, next to row 1, column 78 [select prodImageId, prodImage from model.ProductImage, where prodImageId =: id0_ ,: id1_ ,: id2_,: id3_,: id4_ ,: id5 _]

How can I get selected rows based on the ids list using Hibernate?

Answer 1

 String queryString = "select acc from cgix.trust.domain.PtbnAccount as acc where acc.accountId IN (:accountdIds)"; Query query = session.createQuery (queryString); query.setParameterList("accountIds", accountFilter); 

Assuming accountFilter is a List object. Keep in mind that you should not pass empty lists, as this will result in the following SQL (which will not work): ... WHERE xyz IN () ... (note the empty inside clause).

Answer 2

The problem that most people do here is that they still want to use,

 query.setParameter("accountIds", accountFilter); 

instead

 query.setParameterList("accountIds", accountFilter); 

use setParameterList() when working with lists