Bind multiple parameters with hibernate Query

Question

I want to bind multiple parameters with this query dynamically,

 Query selectList = session.createSQLQuery("select * from Txn where recNo =: recNo")  
.addEntity(Txn.class);

selectist.setParameter("recNo",recNo);    
selectTxnList.setFirstResult(startRowNo);          
selectTxnList.setMaxResults(scrollValue);

List list = selectTxnList.list();

Suppose here i check that if i have not null in txnNo then i want to bind this parameter also with this query

Example - select *from Txn where recNo = 123 and txnNo = txnNo;

and if i have null in txnNo then i only want to bind recNo with this query.

Example - select *from Txn where recNo = 123;

Please tell me the way if i can do this with the help of HQL, because i have more than 50 parameters. i dont want to increase the parameter the the query(select * from Txn where recNo =: recNo )

Would there be anything wrong with using an if statement in your Java logic to handle this? — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 11, 2016 at 7:09
No nothing is wrong with this but what whould we done with the query (select * from Txn where recNo =: recNo ) how can i increase parameter here?? there is no problem if it can be convert in to the HQL query. but how can i do that — Lokesh Rathor
– Lokesh Rathor, Commented Mar 11, 2016 at 7:11

Tim Biegeleisen · Accepted Answer · 2016-03-11 07:33:49Z

Here is one option you would have for handling this logic inside the query itself:

String query = "select * from Txn where recNo = :recNo and ";
       query += "(case when :txnNo is not null then txnNo = :txnNo else true end)";

This will enforce the equality on txnNo only in the case that it be not NULL, otherwise this parameter will be ignored.

But I would prefer to handle your situation by using an if statement in the Java logic rather than using this query.

Update:

Another option would be to use the following idiom for each of your 50 columns:

where (col = :col or col is null)

This condition will fire true when col is not NULL and matches a record, and it will also fire true when col is NULL (in which case the equality should evaluate to NULL for most RDBMS).

In general, if you want entire portions of a WHERE clause to conditionally be present in query, then you need dynamic SQL for that. In the context of Java and Hibernate, this means forming the query string in your application layer.

Collectives™ on Stack Overflow

Bind multiple parameters with hibernate Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related