passing object[] to sql query

Question

In my application i have list of category ids.Now i want to get products from database based on product category which will be contains in that list and also product name like some "XYZ".How can achieve this by using IN clause. Is anyone have any idea i tried this by passing way but i get empty list

My Code is :

Object[] cids=final_cats.toArray();
String cds=final_cats.toString();
String query="select fpd from FTextProductDetails ftpd inner join ftpd.fProductDetails fpd WHERE fpd.categoryId in( "+cds+" )and ftpd.productName like ?" ;
Object[] values=new Object[]{'%'+productName+'%'};
List<Object[]> list=HibernatePaginationImpl.findProductsByName(query, values,pageSizeLimit , pageNumber,this.baseDao);

here final_cats have all categories.

Since you seem to be using Hibernate, you could use a Hibernate Criteria Query (http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querycriteria.html) and use `Restrictions.in` (http://docs.jboss.org/hibernate/orm/3.3/api/org/hibernate/criterion/Restrictions.html) — Dawood ibn Kareem, Apr 01 '14 at 10:24
Thank You Mr.David.Here I write customized class HibernatePaginationImpl which will have the static method that takes a query.so I need to pass query and restricted to use criteria. — user3354457, Apr 01 '14 at 10:32
OK, if you need SQL that you can build a `PreparedStatement` out of, then you really need to have a whole lot of `?` marks in your SQL. Since CuE has already given you an answer that uses that technique, I won't bother. I hope you manage to make this work. Good luck. — Dawood ibn Kareem, Apr 01 '14 at 10:43
I have rolled back your edit where you added [Colsed] to the title as 1) it is a misspelling of Closed, 2) it is not how you signal solved problems in Stack Overflow: instead accept your own answer (or another answer) once the timeout has passed. — Mark Rotteveel, Apr 01 '14 at 13:48

score 1 · Answer 1 · answered Apr 01 '14 at 10:26

Lets take final_cats to be a List

 int locSize = final_cats.size();
 StringBuilder builder = new StringBuilder();
 Object[] args = new Object[locSize];               
 for( int i = 0; i < locSize; i++ ) {
      builder.append(" ?, " );
      args[i]= final_cats.get(i);
 }
....
String queryString = StringUtils.substringBeforeLast(builder.toString(), ",");      
String query="select fpd from FTextProductDetails ftpd inner join ftpd.fProductDetails fpd WHERE fpd.categoryId in("+ builder.toString() +")and ftpd.productName like ?" ;

Hope this logic helps..

user3354457 · Accepted Answer · 2014-04-09T05:36:52.100

0

I got solution to my problem.

Here's the code :

Object[] cids=final_cats.toArray();
String cds="";
for(Object o:cids) {

        cds=cds+o+",";
}

cds=cds.substring(0, cds.length()-1);

String query="select fpd from FTextProductDetails ftpd inner join ftpd.fProductDetails fpd WHERE fpd.categoryId in( "+cds+")and ftpd.productName like ?" ;
Object[] values=new Object[]{'%'+productName+'%'};
List<Object[]> list = HibernatePaginationImpl.findProductsByName(query, values,pageSizeLimit , pageNumber,this.baseDao);

edited Apr 09 '14 at 05:36

answered Apr 01 '14 at 13:43

user3354457

69
2
17

Do you realise that `if(cds==null && cds.isEmpty())` can never be true? In fact, if you're not above using Apache Commons, those first ten lines can be written as just one - `String cds = StringUtils.join(final_cats, ',');` I find this much more readable. I hope it's of some use. – Dawood ibn Kareem Apr 02 '14 at 08:23

passing object[] to sql query

2 Answers2

Linked