Conditional AND using hibernate

Question

I have the following query:

@NamedQuery(name = "User.findByParams", query=
    "SELECT * 
     FROM User user 
     WHERE user.name type = :inputType")

And I wish to add AND statement, that will take place only if the inputs are supplied:

@NamedQuery(name = "User.findByParams", query=
    "SELECT * 
     FROM User user 
     WHERE user.name type = :inputType AND (:ageInput != null AND user.age > :ageInput")

It means that if the ageInput is supplied, filter by it as well. If not- ignore this param. Any ideas?

Any ideas?

not in a named query (as far as i know). use Criteria API for this — XtremeBaumer
– XtremeBaumer, Commented Aug 30, 2017 at 10:38
Not possible on SQL way! As @XtremeBaumer said, as from JPA2 you can use Criteria API for that — elrado
– elrado, Commented Aug 30, 2017 at 10:43

maciej · Accepted Answer · 2017-08-30 12:04:26Z

1

As the previous speakers wrote, you can use Criteria

Criteria criteria = createCriteria()
        .add(Restrictions.eq("type", type));

if (ageInput != null) {
    criteria.add(Restrictions.eq("ageInput", ageInput));
}

List<User> list = criteria.list();

or SQLQuery

String sql = "SELECT * " +
       "FROM User user " +
       "WHERE user.type = :inputType ";

sql += (ageInput != null) ? "AND ageInput = :ageInput " : "";

Query query = sessionFactory.getCurrentSession().createSQLQuery(sql)
        .setParameter("inputType", inputType);

if(ageInput != null) {
    query.setParameter("ageInput", ageInput);
}

return (List<User>) query.list();

edited Aug 30, 2017 at 12:04

answered Aug 30, 2017 at 11:45

maciej

512 bronze badges

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

1 Comment

Nikolaj Hansen Over a year ago

Consider sql injection carefully .. I would build this using the category framework / hibernate

Sharad · Accepted Answer · 2017-08-30 10:45:55Z

0

You will have to check if ageInput is supplied or not in code and will have to call different methods accordingly. Means if ageInput is supplied then you will have to call a method having ageInput constraint o/w call method which do not have ageInput constraint.

Alternatively, you can use predicates and execute a query.

answered Aug 30, 2017 at 10:45

Sharad

11 bronze badge

Collectives™ on Stack Overflow

Conditional AND using hibernate

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related