Improve the Computation and Analysis Ability in Java

Java is the most widely used programming language with an outstanding architecture. It is the top preferred language to develop the enterprise application. However, Java is not fit for the mass data computation. If encountering the computation that is too complex to be represented in a single SQL statement or it is not allowed to add stored procedures to database, then drawbacks of Java would be highlighted: no specific library function, lack of concise syntax for computation, lengthy and unreadable codes, and high demand on technical capability of developers.

esProc is the query language of a new generation with Java architecture and JDBC interface. esProc can uplift the computational capability of Java all-aroundly.
esProc_java_computing_1

Case Description

In an information system of Java architecture in a certain merchant bank, it is required to compute the Moving Average (MA) of the monthly loan amount in this year. The row data is stored in the loan table of a database, mainly comprising the field time (i.e. date of loan), and the field amount (i.e. amount of loan). Some data is shown as given in the below table:

esProc_java_computing_2

Algorithm of Moving Average: average of the 3 neighboringmonths, for example, the Moving Average in March is February, March, and April. In addition, the first record and the last record only have 2 neithboring months.

It is easy to compute the monthly loan amount because you are only required to group the data of loan by month via SQL and then sum up the amount of loan. However, the Moving Average involves the “Relative Position” and “Computation between Rows”. Therefore, it is not easy to represent via SQL. In addition, adding stored procedures to the database of bank is under strict control. Usually, such computation is implemented with Java.

Java Solution
………………………………………………………………………………………………………………

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;
          }elseif(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();
………………………………………………………………………………………………………………

First, use SQL to perform the group and sum operations. Then, use Java to compute Moving Average.
In Java solution, loops and traversals across the result sets are required to handle the below three situations: 1. Regarding the data from the first record, only perform the average operations on the current and the next records; 2. Regarding the data from the last record, only perform the average operations on the current and the previous records; 3. Regarding the other situations, only perform the average operation on the current record, previous record, and the next record.
Java is the traditional practice. Now we can try esProc as a new alternative.

esProc plus Java Solution

esProc_java_computing_3

Firstly, compute in the esProc IDE as follows:
In A1, execute the simple summarization in groups via SQL;
In A2, proceed with the computation and compute the Moving Average.
In this procedure, ”~” represents every record in the group, and ”{startPosition,endPosition}” represents the relative scope of range; ”{-1,1}” represents the range from the -1 position to the 1 position (3 records in total) relative to the current record.
We save the above cellsets as ”ma3.dfx” and call the computation in Java:
………………………………………………………………………………………………………………
    st=conn.prepareCall(“call ma3()”);
    st.execute();
    ResultSet rs = st.getResultSet();
………………………………………………………………………………………………………………

Java application can call esProc just like calling the stored procedure through the standard JDBC interface. In which, the name of stored procedure is just the cellset name.

Comparison
As we can see in this case, if using Java alone to solve this problem, then the computation logic is complex, the workload of coding is heavy, and it is not easy for code maintenance. For example, to compute the Moving Average of the 5 months in a row, or the Moving Average of any N months, the developer will have to code all over again to solve this problem. In fact, in order to facilitate the use, more codes are required to convert the type of current returns to ”List+Map” or ”ResultSet”.

esProc is the specific computing tool for mass data, providing the agile syntax and powerful library functions to solve such problem easily. In addition, esProc is easy for scaling and maintenance, such as computing the Moving Average of the five months. The developers are only required to change the range of data in A2 from {-1,1} to {-2,2}. If computing the Moving Average of the N months, the developer can simply replace the range with the input parameters. Finally, the result set itself of esProc is the easy-to-use ”ResultSet” type that is easy for Java application to directly use it.

Owing to its advantageous features, esProc is able to uplift the computational capacity of Java greatly.

Feature: JDBC Interface
esProc is built with the pure Java, providing the easy-to-use JDBC development interface to enable the convenient calling by various Java applications (such as report), and capable of being called as embedded database with Java. The calling style of esProc follows the public JDBC interface specification. esProc can be called in the same way as calling the stored procedure, receive the parameters before calling, and return multiple result sets after calling.

Feature: Professional Computation Ability
esProc is the specific analysis tool for mass data, not only having all analysis capability of SQL in respect of data querying, filtering, grouping, and statistics, but also allowing for the parameter entry or loops and branches judgment. Other than database, you can directly analyze the data from Excel spreadsheet and text file.

SQL statement is the query language initially developed in 70s for the “host name/command line” style programming. The disadvantages of SQL are obvious, that is, non-stepwise computation, incomplete set-lization, unordered set, and lack of object reference. As a new query language over-performing SQL, esProc has provided remedies for all these disadvantages.

Advantages of esProc
esProc provides friendly and easy-to-use JDBC interface to facilitate the convenient calling by Java application, and improves the computational capacity of Java significantly.

esProc is built with pure Java, which is an architecture advantage by nature for seamlessly integrating with the external Java system and co-building the application that is able to cluster, platform-independently, and robust.

esProc is a powerful and easy-to-use analysis tool, enabling developers to solve complex problems appear in the business analysis and save the time and manpower significantly

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Reporting tool and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s