HQL with "in" and Parameter

eltonk · **Joined:** Tue Jul 12, 2005 9:04 am **Posts:** 13

Hi guys...

I have a HSQL like this:

Code:

    select pagamento.cdConvenio, pagamento.cdPagamento, pagamento.cdStatus
    from PagamentoBean as pagamento
    inner join pagamento.convenio as convenio
    inner join convenio.banco as banco
    where pagamento.cdStatus in ( ? )

When I create this query in the session like

Code:

Query q = session.createQuery(query);

and I put the parameter with

Code:

q.setParameter(0, '521,265,235,888', (Type) Hibernate.STRING)

Question:
This isn't found... Whow can i do it? Whow can i use "in" at my HQL with parameters?
Anyone can help-me?

Thanks a lot!

andersonbd27 · **Joined:** Wed Sep 14, 2005 9:11 am **Posts:** 22

have you tried it without using a parameter? I don't know for sure, but I'd make a guess that could possibly be the problem

In other words try just...

Code:

    select pagamento.cdConvenio, pagamento.cdPagamento, pagamento.cdStatus
    from PagamentoBean as pagamento
    inner join pagamento.convenio as convenio
    inner join convenio.banco as banco
    where pagamento.cdStatus in ( 521,265,235,888 )

timrobertson100 · **Posted:** Fri Jan 20, 2006 9:47 am

The only way I managed was to do it in hql string manipulation for the number of params to produce HQL like so

Code:

in(?,?,?....?)

and then bind the params as an array

Better yet though is to use the criteria api if you can

Code:

      
   public List<Taxa> findByRecordIds(Collection<Long> recordIds) {
      return getSession()
            .createCriteria(Taxa.class)
            .add(Restrictions.in("recordId", recordIds))
            .addOrder(Order.asc("recordId")).list();
   }

eltonk · **Joined:** Tue Jul 12, 2005 9:04 am **Posts:** 13

andersonbd27 wrote:

have you tried it without using a parameter? I don't know for sure, but I'd make a guess that could possibly be the problem

In other words try just...

Code:

    select pagamento.cdConvenio, pagamento.cdPagamento, pagamento.cdStatus
    from PagamentoBean as pagamento
    inner join pagamento.convenio as convenio
    inner join convenio.banco as banco
    where pagamento.cdStatus in ( 521,265,235,888 )

yeah... this was my first solution...
But, when i execute

Code:

Query q = session.createQuery(query);

this isn't create a "cache" at db like the preparedStatement of the SQL?

timrobertson100 · **Posted:** Fri Jan 20, 2006 10:42 am

Doing string manipulation to create (?,?,?....?) on the other hand does allow some form of caching - it's create it once for 1 param, once for 2 params etc etc. Not ideal but better than creating HQL for each query.

I use the criteria API as posted above for this though...

eltonk · **Joined:** Tue Jul 12, 2005 9:04 am **Posts:** 13

timrobertson100 wrote:

Doing string manipulation to create (?,?,?....?) on the other hand does allow some form of caching - it's create it once for 1 param, once for 2 params etc etc. Not ideal but better than creating HQL for each query.

I use the criteria API as posted above for this though...

yeah... I think this is better. I will use criteria...

Ty guys!

CSwinney · **Joined:** Mon Aug 16, 2004 6:15 am **Posts:** 24

How about using Query.setParameterList()

steve · **Posted:** Fri Jan 20, 2006 6:00 pm

Yes, the correct way is:

Code:

String query = "select pagamento.cdConvenio, pagamento.cdPagamento, pagamento.cdStatus " +
        " from PagamentoBean as pagamento " +
        "       inner join pagamento.convenio as convenio " +
        "       inner join convenio.banco as banco " +
        " where pagamento.cdStatus in ( :statuses )";
Query q = session.createQuery( query );
q.setParameterList( "statuses", new String[] { "521", "265", "235", "888" } );