java data process without sql

[datathinker]

One Bank Information System needs to figure out mobile average(MA) amount of monthly loan from loan table defined as below,
Time amount …
3/14/2011 $43,334.10 …
3/14/2011 $92,304.10 …
3/14/2011 $45,983.80 …
3/14/2011 $36,973.10 …
3/14/2011 $24,987.87 …
… …

It is easy for SQL to calculate monthly loan by grouping by. But it will be difficult to calculate mobile average which is involved with relative location and cross row computation, that’s why this job is usually done by java rather than sql. The following is how java works:

st=conn.prepareStatement("select sum(amount)amount, to_char(time,'MM')month from loan where to_char(time,'yyyy')=to_char(sysdate,'yyyy') group by to_char(time,'MM') order by month",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs=st.executeQuery();
int size=rs.getFetchSize();
for(int currentPos=1;currentPos<=size;currentPos++){
rs.absolute(currentPos);
float preAmount=-1,thisAmount=-1,nextAmount=-1;
float avgAmount=-1;
String month=rs.getString("month");
thisAmount=rs.getFloat("amount");
if(currentPos==1){
rs.next();
nextAmount=rs.getFloat("amount");
avgAmount=(thisAmount+nextAmount)/2;
}else if(currentPos==size){
rs.previous();
preAmount=rs.getFloat("amount");
avgAmount=(thisAmount+preAmount)/2;
}else{
rs.previous();
preAmount=rs.getFloat("amount");
rs.next();
rs.next();
nextAmount=rs.getFloat("amount");
avgAmount=(thisAmount+nextAmount+preAmount)/3;
}
System.out.println(month+" "+avgAmount);
}
rs.close();
……
Now in order to make this easier, we sometimes apply esProc to do this work as following:
A
1 =db.query(“select sum(amount)amount, to_char(time, ‘MM’) month from loan where to_char(time,’yyyy’)=to_char
(sysdate,’yyyy’) group by to_char(time,’MM’) order by month”)
2 =A1.derive(~{-1,1}.(AMOUNT).avg())
3 Result A2

I am now using esProc to do this kind of work and i can not think out a better and easier tool to work out this data computation.

[dismal-it]

Just an idea, but with Derby, you can have an embedded good enough database in your java application (even in memory I think). Maybe the best way would be for you to use your sql queries after having loading in the Derby DB all your data.