Skip to main content

esProc:a tool Improve the Computation and Analysis Ability in Java

Please note these forums are being decommissioned and use the new and improved forums at
No replies
Joined: 2012-02-15

Improve the Computation and Analysis Ability in Java

free download:

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.

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:

Algorithm of Moving Average: average of the 3 neighboring months, 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

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

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

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:

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.


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