CODEWITHSUNDEEP
mysqlsqlhibernateselect
Amit Kumar
Amit Kumar

Reputation: 2745

Hibernate query multiple times or fetch relevant rows by one query and process them in java

I am having a problem ; which approach is better.

I have to query a database 10000 times(or more) to fetch exactly 10000 rows & then display them.

I can do the same in one query to the database by writing a long query carefully & arrange them locally & then display them.

For eg. I have name of people like this

Id Name
1 Amit
2 Mohan
3 Rohan
4 John
5 Kuneel
6 Monu
7 Savita
8 Hari
9 Raju
10 Sita

List one="Amit","Mohan","Rohan","John"
List two="Kuneel","Monu","Savita"
List three="Hari","Raju"
List n="Sita",...

for first list I will query using or operator & a List of integers will be returned. Can I query for getting Ids of all Lists as a List of Integers & then count on them & arrange them to show Ids? My question is should I go for querying all List separately or do this in one query & then arrange the result. Please help. Any Link,clue or reference is appreciable. Thank you.

Mysql queries : For getting list one's IDs

Select Id from User where(name) IN("Amit","Mohan","Rohan",John");

Now in HQl the equivalent query will return a list of integers. Also the names will be stored in an array of String & will be used to make query string.

But I want to do it like:

 Select Id from User where(name) IN("Amit","Mohan","Rohan","John","Kuneel",
"Monu","Savita","Hari","Raju","Sita","& other names");

and the list of string returned by running this query can be seen as first four ids correspond to first list(List one), next three to second & so on.Also the first id of the list corresponds to the first name of the first list i.e. list one.

Plz tell if you need more explaination.

Thank you

Upvotes: 0

Views: 854

Answers (1)

erencan
erencan

Reputation: 3763

You need to use sql IN clause equivalent for hibernate. I am not sure what is your JPA configuration, but it will be likely in the following.

List<?> listForParameters  = //initialize the list
Session session = //obtain the session 
Criteria crit = session.createCriteria(YourEntityClass.class);
crit.add(Restrictions.in("ParameterName", listForParameters  ));
List<?> list = crit.list();

See also

Restrictions api

Criteria Queries

Upvotes: 1

Related Questions