esProc Assists Java to Implement Quasi Stored Procedure outside Database

In JAVA development, this is a pretty common thing to use the stored procedure implement business logic. If a database cannot be modified or is awkward to be modified, programmers need to program a computing module outside database similar to a stored procedure. Here give some common scenarios as follows:

  • Database used by finance, ERP, CRM or other business Software disables manual modification, so the stored procedure in the database cannot be created.
  • l  For the sake of safety, the third party development team is generally not allowed to make any change on the production database.
  • Many database managements are more stringent. It is the most annoying thing when you are creating or modifying the stored procedure, because you have to submit it to get approval at all levels.

In this case, the general solution is to write your own Java code, but this will make you involved in a huge workload, plus a higher degree of coupling with the applications, it is more troublesome for you to recompile it every time when modifying and maintaining it.

esProc can help you easily implement the quasi stored procedure outside database in these scenarios.

Let’s look at a specific example, the target to be computed by a development team is: in a specified time period of a year, to compute which are the top 20 niche products sold in all states? And to count up monthly sales of these niche products, as well as month-on-month growth rate in the sales.

Among them, the product sales are available from Sales Table of Oracle database, and the main fields are shown below:


The solution in esProc consists of two parts: JAVA-side code and esProc script, of which, JAVA-side code is mainly responsible for receiving the parameters from periphery, and calling esProc script via JDBC, finally getting the computed results. The computing part is implemented in esProc script, whose task is to receive JAVA parameters, and retrieve data from database with simple SQL, complete the complex business logic, finally output the results to JAVA via JDBC.

JAVA-side codes:


Connection conn = DriverManager.getConnection(“jdbc:esProc:local://”);

CallableStatement cstmt = conn.prepareCall(“call salesSP(?,?)”);

cstmt.setString(1, start);

cstmt.setString(2, end);


ResultSet rs = cstmt.getResultSet();

As you see, the methods used to call esProc script and call the stored procedure of common database are exactly the same. The difference is that esProc script, as acomputing module outside database, can be stored in the file system, but the stored procedure must be managed by the database.

In the codes, Driver Name and URL must follow the naming rule of esProc; salesSPis the module name, i.e., prefix of esProc script file name salesSP.dfx; salesSPhas two input parameters: start, end. The computed results are stored in variable rs, which can be directly output to a Web or a client application.

esProc script:


A1: Retrieve data from Sales Table based on time parameters.

A2: Group the data in A1 according to states.

B2: In each group (state) of A2, evaluate the record numbers of the top 20 selling products.

A3: Retrieve the products corresponding to each state from A2 according to sequence numbers.

A4: Evaluate intersections of each group in A3. Here the computed results are niche products.

A5: Filter out the sales records for niche products.

A6: According to A5, count up monthly sales for each product.

A7: Add a field Rate to A6, which is used for storing the growth rate in the future.

A8: Re-group A7 by products.

A9: Compute month-on-month growth rate for each product.

A10: Merge grouped data in A9. This is the final computing target.

A11: Return the computed results to JAVA program via JDBC.

You can see that esProc incorporates a complete set of library functions for computing structured data, for example, in the codes, as mentioned above, there are: group, filter, rank, aggregate, intersection, union, etc. Similarly, the distinct value, maximum, minimum, average and others are all embedded in the basic function library of esProc. A complete set of library functions is a strong basis for achieving the specific effect of this case.

Stored procedure often needs to traverse batch data with branch and loop statements, for which, esProc offers supports as well. And beyond that, esProc also support loop functions, you can simplify the use of most loop statements, such as, an expression in B2: A2.(~.rank(Amount).pselect@a(~<=10)), indicates loop through the members of A2, only processing one every time; where, ~ represents the current member. What you should note here is that: for A2, each member is not an individual record, but all records for each state.

Order-related algorithms are often more complex, and not easy to express even if using a database stored procedure, such as, an inter-row computing in a group encountered in this case: the growth rate of sales for each product every month. esProc supports the ordered set, so it is very easy to express such algorithms, for example, the variable MonthAmountin A9 represents the sales of current month, MonthAmount[-1] is the sales of previous month, so that the growth rate of sales every month can be expressed as MonthAmount[-1]/MonthAmount-1 with ease.

As described above, the effect similar to stored procedure outside database can also be achieved directly in JAVA, but JAVA lacks a basic algorithm function library, such as: group, aggregate, sort, filter, join, distinct value, intersection, rank, etc., the programmershave to write these basic algorithms manually to achieve computing modules. It is obviously unreasonable for you to implement these basic algorithms directly in the business logic, because thiswould lead to repeatedly writing similar codes for each computing module, further to make them too enormous and less readable. The ideal practice is to implement a set of basic algorithm library functions, then call these library functions in eachcomputing module.But it is harder for application programmers to design a complete set of well-built systematic basic-algorithm function library,this often leads the codes to a higher coupling, poor stability, eventually muddled maintenance process. In addition, when there are lots of similar computing processes, whether they would be used or not, these Java class/jar will occupy a certain memory space but cannot be released, producing a certain impaction performance.

esProc itself is a complete set of well-designed basic library functions, and only required one-tenth of JAVA codes, it can implement the quasi stored procedure outside database with equivalent functions, so as to return you the most elaborate algorithms and a higher execution efficiency; esProc’s main program and the script file are separated from each other, between which, a lower coupling makes maintenance job far more convenient. In addition, esProc will not be loaded into memory in advance, but loaded as needed, then released immediately after computation is finished, not taking up too much memory over a long time.


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s