Hello
I am working with EJB3 and the JPA is Hibernate 3 with Oracle 10g. In one of the SessionBeans there is a query (sql) which i am running using the createNativeQuery method of the EntityManager.
The query is supposed to return 20 Ids of an Entity. The query returns all 20 Ids. Then if i want the first 10 records by setting the setFirstResult(0) and setMaxResults(10). Then the ids arraylist contains the first 10 ids of my database table.
Then if i want to see only the last ten ids, by setting setFirstResult(10) and setMaxResults(10) and then:
Code:
ArrayList ids = (ArrayList) query.getResultList();
System.out.println("no of users found: "+ids.size());
if(ids != null && !ids .isEmpty()){
for (Iterator it = ids.iterator(); it.hasNext();) {
BigDecimal userId= (BigDecimal)it.next();
System.out.println("userid: "+userId);
}
}
In this case the query finds 10 records as the print statement to show the size of ids arraylist prints 10 but then in the loop it gives this exception i.e.
Code:
03:09:47,451 ERROR [STDERR] java.lang.ClassCastException: [Ljava.lang.Object;
03:09:47,451 ERROR [STDERR] at com.server.servicefacades.UserSessionBean.searchUser(UserSessionBean.java:802)
03:09:47,451 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
That was because the array list which was supposed to contain simple primary keys (BigDecimal) was instead containing Object[] at every index.
Since the query was returning an Object array, i printed its contents
Code:
if(ids != null && !ids .isEmpty()){
for (Iterator it = ids.iterator(); it.hasNext();) {
Object[] obj = (Object[])it.next();
System.out.println(""+Arrays.deepToString(obj));
}
}
and the result was:
Code:
13:58:53,124 INFO [STDOUT] [206, 11]
13:58:53,124 INFO [STDOUT] [207, 12]
13:58:53,124 INFO [STDOUT] [208, 13]
13:58:53,124 INFO [STDOUT] [209, 14]
13:58:53,124 INFO [STDOUT] [210, 15]
13:58:53,124 INFO [STDOUT] [211, 16]
13:58:53,124 INFO [STDOUT] [212, 17]
13:58:53,124 INFO [STDOUT] [213, 18]
13:58:53,124 INFO [STDOUT] [214, 19]
13:58:53,124 INFO [STDOUT] [215, 20]
I checked the log of my server to see what query is getting executed and found that the query used to get the first 10 records is different from the query which is used to get the last 10 records.
For first 10 i.e. setfirstResult(0) and setMaxResults(10) it is:
Code:
select * from (SELECT DISTINCT USR.USERID FROM USER USR, USERWATCH UW WHERE USR.ADDEDBYID =1 AND USR.COUNTRY = CASE WHEN (UW.COUNTRY is null) THEN USR.COUNTRY ELSE UW.COUNTRY END AND USR.ALIVE = 'A
') where rownum <= ?
and for the last 10 i.e. setfirstResult(10) and setMaxResults(10) it is:
Code:
select * from ( select row_.*, rownum rownum_ from (SELECT DISTINCT USR.USERID FROM USER USR, USERWA
TCH UW WHERE USR.ADDEDBYID =1 AND USR.COUNTRY = CASE WHEN (UW.COUNTRY is null) THEN USR.COUNTRY ELS
E UW.COUNTRY END AND USR.ALIVE = 'A') row_ where rownum <= ?) where rownum_ > ?
Any help what can i do to get only the ids or atleast some consistent behavior?
Many Thanks
Login
FAQ
Search