very slowly work EJB with big collection of data
Hello all.
I am doing the applicayion, and want to realize there paginal output of data from DB to jsp page. I use mysql and Sun AppServer 8.2. There are more than 100 000 rows in db. request to database executes near 1 minute. Tell me please how i can to optimize my code or suggest to do it another way.
it is Stateless Session Bean method
public Collection<CallBean> selectAllCalls(Integer page){
try{
ArrayList<CallBean> returnlist =new ArrayList<CallBean>();
// it is not safe conversion, but woks =)
ArrayList<CallLocalObject> list = (ArrayList<CallLocalObject>)
callHome.findAll();
int from = 0;
int to= 0;
Integer i = list.size();
pages = i/5;
if(i%5 > 0) pages++;
curPage = page;
if(page > 0 && page != 1 && page < pages){
from = 5 * (page - 1);
to = from + 5;
}
if(page == 1){
from = 0;
to = 5;
}
if(page == pages){
from = 5 * (page - 1);
to = i;
}
for(CallLocalObject call: list.subList(from, to)){
CallBean bean =new CallBean();
bean.setPk((PrimaryKey) call.getPrimaryKey());
bean.setRingDate(call.getRingDate());
bean.setDescription(call.getDescription());
bean.setDuration(call.getDuration());
bean.setClient(call.getClientBean());
bean.setUser(call.getUserBean());
returnlist.add(bean);
}
return returnlist;
}catch (Exception e){
e.printStackTrace();
thrownew EJBException(e);
}
}
[2503 byte] By [
DeStroyera] at [2007-10-3 2:49:18]
> do you have to execute that object conversion?
Yes, i have to exceute this conversion, because Entity Bean's finder method return Collection<...LocalObject>. I need to use ArrayList methods to move about collection to find specific rows
>Also,
> why fetch all recrods, if you're only going to return
> a subset, while discarding the unused records (as it
> appears in your code)? You should probably only a)
> fetch the rows you need to render the current page or
> b) not discard the fetchAll result set between each
> page request...
Tell me please how can i fetch records, i need?
thanks for answer
>Tell me please how i can to optimize my code
> or suggest to do it another way.
You should not use entity beans to display large data sets.
You can use JDBC and apply this design pattern:
http://www.corej2eepatterns.com/Patterns2ndEd/ValueListHandler.htm
Or you can use Hibernate, it supports pagination and it works very well. Here is my code, you can use it if you wish.
public List searchDomainObjectsWithPaging(Class domainObjectClass,Map searchParams, int firstResult, int numberOfResults) throws PersistenceServiceException
{
Set criterions = new HashSet();
for (Iterator iter=searchParams.keySet().iterator() ; iter.hasNext() ;)
{
String propertyName = (String) iter.next();
criterions.add(Expression.eq(propertyName, searchParams.get(propertyName)));
}
return searchDomainObjectsWithPaging(domainObjectClass,criterions,null, firstResult, numberOfResults);
}
private List searchDomainObjectsWithPaging(Class domainObjectClass,Set setOfCriterions,String orderBy, int firstResult, int numberOfResults) throws PersistenceServiceException
{
List list;
try
{
Session session = HibernateUtil.openSession();
Criteria criteria = session.createCriteria(domainObjectClass).setFirstResult(firstResult).setMaxResults(numberOfResults);
if (orderBy!=null && !orderBy.equals(""))
{
criteria.addOrder(Order.asc(orderBy));
}
for (Iterator iter = setOfCriterions.iterator(); iter.hasNext() ;)
{
criteria.add((Criterion) iter.next());
}
return criteria.list();
}
catch (HibernateException ex)
{
log.error("Cannot read "+CommonLib.simpleClassname(domainObjectClass),ex );
throw new PersistenceServiceException("Cannot read "+CommonLib.simpleClassname(domainObjectClass),ex);
}
}
The only problem is that Hibernate doesn't have any built-in method to find how many records are in database that match you' r search criteria, but you can write a method that creates SELECT COUNT(*) query dynamicaly and it works quite fast.
public int countSearchResultRecords(Class domainObjectClass ,Map searchParams, String extraWhereClause,Map extraSearchParams) throws PersistenceServiceException
{
int result =0;
try
{
Session session = HibernateUtil.openSession();
StringBuffer queryBuff = new StringBuffer("SELECT count(*) FROM "+domainObjectClass.getName()+" o ");
int num=0;
for (Iterator iter = searchParams.keySet().iterator(); iter.hasNext() ;)
{
String name = (String) iter.next();
queryBuff.append(num++==0 ? " WHERE " : " AND ");
queryBuff.append("o."+name+"=:"+name);
}
if (extraWhereClause!=null && !extraWhereClause.equals(""))
{
queryBuff.append(num++==0 ? " WHERE " : " AND ");
queryBuff.append(extraWhereClause);
}
Query queryCount = session.createQuery(queryBuff.toString());
addQueryParameters(queryCount,searchParams);
if (extraSearchParams!=null)
{
addQueryParameters(queryCount,extraSearchParams);
}
result = ((Integer) queryCount.uniqueResult()).intValue();
}
catch (HibernateException ex)
{
log.error("Cannot read "+CommonLib.simpleClassname(domainObjectClass),ex );
throw new PersistenceServiceException("Cannot read "+CommonLib.simpleClassname(domainObjectClass),ex);
}
return result;
}
And one more thing - if you have a lot of records in database (>1 million) then you should include in search critieria at least one parameter that has an index in database table.
