CODEWITHSUNDEEP
javahibernate
Lokesh Rathor
Lokesh Rathor

Reputation: 169

Bind multiple parameters with hibernate Query

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 )

Upvotes: 1

Views: 4248

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

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.

Upvotes: 1

Related Questions