Hibernate dynamic parameters query

Question

I have a query that need to use "dynamic" parameters set. But coundn´t realize how to do it, i tried to not add the where string but it gives me erros because the parameter is seted.

Any tips?

String queryS = "select object(c) from "
                           + entityClassName + " as c " +
                            "where 1 = 1" ;

            if(codigoPaciente.compareTo("") != 0)
            {
                queryS += " and c.CodigoDoPaciente =:paciente";
            }
            if(codServicoPrincipal.compareTo("") != 0)
                queryS += " and c.codigoServicoPrincipal =:servico";
            if(data != null)
                queryS += " and c.codigoServicoPrincipal =:data";
            if(TipoServico.compareTo("") != 0)
                queryS += " and c.codigoServicoPrincipal =:tipoServico";


            Query query = em.createQuery(queryS);
            query.setParameter("paciente", codigoPaciente);
            query.setParameter("codigoServicoPrincipal", codServicoPrincipal);
            query.setParameter("data", data);
            query.setParameter("tipoServico", TipoServico);


            return query.getResultList();

digitaljoel · Accepted Answer · 2013-10-22 22:28:44Z

2

Rather than doing a bunch of string concatenation to create a query, the criteria API could be used to construct the query in a more "safe" manner. Then you could add the condition and the value in a single conditional check.

answered Oct 22, 2013 at 22:28

digitaljoel

26.6k15 gold badges94 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

João Rocha da Silva Over a year ago

I agree with joel on this.

digitaljoel Over a year ago

I only partially agree with myself. The criteria api is no joy to work with.

João Rocha da Silva Over a year ago

It may be a pain, but avoiding concatenating stuff usually pays off in the long run...

Alan Araya Over a year ago

I was trying to avoid the criteriaAPI....but it seams that that is the only way to do that

Alan Hay Over a year ago

See QueryDSL. Strongly typed dynamic queries without the pain of the criteria API. blog.mysema.com/2010/04/querydsl-as-alternative-to-jpa-2.html

João Rocha da Silva · Accepted Answer · 2013-10-22 23:52:16Z

0

You are not setting your parameters inside if conditions... Maybe set the parameter for each string you concatenate?

if(codigoPaciente.compareTo("") != 0)
            {
                queryS += " and c.CodigoDoPaciente =:paciente";
            }

Query query = em.createQuery(queryS);

// etc etc...
if(codigoPaciente.compareTo("") != 0)
    query.setParameter("paciente", codigoPaciente);

edited Oct 22, 2013 at 23:52

answered Oct 22, 2013 at 22:28

João Rocha da Silva

4,3294 gold badges28 silver badges36 bronze badges

4 Comments

João Rocha da Silva Over a year ago

Another point; if you remove the WHERE clause and keep concatenating AND's without checking if some condition has already been concatenated you will have a criteria that starts with <nothing> AND <something>. This will give you an error of course.

Alan Araya Over a year ago

Can´t set the parameter on the IF that build the query becausethe object "QUERY" doesn´t exists yet

João Rocha da Silva Over a year ago

The if is intended to be used after you create the query object obviously.

slifty Over a year ago

This solution violates DRY -- how can we define the query and set params at the same time.

Collectives™ on Stack Overflow

Hibernate dynamic parameters query

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related