July 8, 2013

Don’t Limit On SQL for Database Computing in Java Development

Original post: http://www.sourcecodester.com/blog/5454/don%E2%80%99t-limit-sql-database-computing-java-development.html
Check the full content below:

During Java development, SQL is often used to address the database related computation. However, "Ordered Computation" is very inconvenient to realize by writing SQL statements. For example, according to Contract table, compute the monthly growth rate of contract values for each sales man in a specified period.

The result set of SQL follows a weird convention that the result set returned directly does not have any sequence numbers, and SQL does not offer any direct support for the algorithm related to sequence number. The former problem is easy to solve since most ResultSet classes of JDBC support row number, and a few ones which doesn’t support can reload the class by itself. As we all know that there is another solution to the poor portability, such as the rownum in Oracle.

The true trouble is that SQL does not offer the direct support for the algorithms relating to sequence number, such as, the last three, the previous one of the current record, the top five, the last but one, and the ranking of a certain record. For another example of the problem mentioned at the very first beginning, we need to firstly group by salesman, group by year within each group, group by month for each year and summarize, and lastly, perform the inter-row computation between the current month and its previous month. These algorithms are inconvenient to represent with SQL. Many trivial inconveniences make a tough problem. That is how a “tough computational problem” comes into being.

Are there any better solutions?

The answer is yes. esProc can address this problem well.

esProc is a professional Java development tool for database computation and direct support for ordered computation is one of its characteristics. For instance, the scripts for the above mentioned computational goal is shown as bellow:

esProc can retrieve and compute on multiple databases, text files, or Excel sheets. Its grid style and agile syntax are tailored for the massive structured data computation. esProc supports external parameters and the computational result in esProc can be exported though JDBC for direct invoking by Java language and reporting tools. Therefore, esProc can greatly enhance the computational capability of Java. With the native supports for the cross-database computation and code reuse, and the complete and perfect debugging function, esProc offers higher development efficiency than SQL, and is ideal to work with Java.