More use of Java to avoid stored procedures and complex SQL is a trend in current application development, which will bring advantages in architecture. However, implementing SQL like operations in Java is not very convenient, and many tasks need to be written from scratch, which actually reduces development efficiency.

Hibernate/Mybatis/JOOQ and Stream/Kotlin, among other pure Java open source technologies, provide structured data objects and some computing class libraries, which can reduce development workload to a certain extent without compromising architectural advantages. However, the functionality of these open source technologies is still far from SQL, and when the requirements are slightly complex, a large amount of Java code still needs to be written, resulting in limited improvement in development efficiency.

In contrast, esProc SPL is the open source technology that can significantly increase the development efficiency of Java applications.

esProc SPL is a pure Java open source software that can be seamlessly integrated into Java applications, just like the code written by application programmers themselves, enjoying the advantages of a mature Java architecture together.

However, unlike the aforementioned open-source class libraries, the functionality of esProc is not provided in the form of Java APIs. esProc has its own programming language SPL, and SPL scripts are called by Java programs through the JDBC interface provided by esProc, just like calling database SQL or stored procedures.

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery("=T(\"Orders.csv\").select(Amount>1000 && like(Client,\"*s*\")

Why design a new programming language instead of directly encapsulating it as a Java API?

Java is a compiled static language that makes it difficult to implement dynamic data structures and convenient Lambda syntax, which is particularly common in structured data operations and the advantage of SQL. 

Any SELECT statement in SQL will generate a new data structure, allowing for the addition and deletion of fields without the need to define the structure (class) beforehand, which is common in structured data operations. However, languages like Java are not effective. To compile the code, it is necessary to define all the structures (classes) used, and it can be considered that new classes cannot be dynamically generated during the execution process (Java theoretically supports dynamic compilation, but the complexity is too high). If a specialized class is used to represent all data tables, and field names are also treated as data members of the class, it is not possible to directly use the class’s attribute syntax to reference fields, and the code is very cumbersome.

Lambda syntax is widely used in SQL, such as the condition in WHERE, which is essentially a Lambda expression. Although Java, a static language, currently supports Lambda syntax, it is far less convenient than SQL. A function header definition is needed to tell the compiler every time a Lambda function is about to be written, and the code looks messy. In Lambda functions, field names in the data table cannot be directly referenced. For example, when calculating amounts using unit price and quantity, if the parameter name used to represent the current member is x, it needs to be written in the verbose form of “x. unit price * x. quantity”. In SQL, it can be more intuitively written as “unit price * quantity”.

Only interpretive dynamic languages can implement these features of SQL, which can generate new data structures at any time, or determine whether the current parameter is a Lambda function based on the host function itself. Therefore, there is no need to write a definition header, and fields with unwritten table names can be correctly referenced based on the context.

SQL is an interpretive dynamic language, and so is SPL. Java, as well as Kotlin and Scala based on Java, are not, so it is difficult to write concise code in these languages.

SPL provides more comprehensive structured data objects (tables, records, cursors) and richer calculation functions than SQL, including basic operations such as filtering, grouping, and join in SQL, as well as missing ordered and set operations in SQL. So, SPL code is usually simpler and easier to maintain than SQL, let alone Java.

For example, in this task, to calculate the longest consecutive days for a stock to rise, SQL needs to be written in multiple nested, verbose, and difficult to understand:

select max(ContinuousDays) from (
    select count(*) ContinuousDays from (
        select sum(UpDownTag) over (order by TradeDate) NoRisingDays from (
             select TradeDate,case when Price>lag(price) over ( order by TradeDate) then 0 else 1 end UpDownTag from Stock ))
     group by NoRisingDays )

Writing such computational logic in Java would be more cumbersome, while using SPL would be much simpler:

Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())

SPL also has comprehensive process control statements, such as for loops and if branches, and supports subroutine calls. Using only SPL can achieve very complex business logic, directly forming a complete business unit, without the need for upper level Java code to cooperate. The main program simply calls the SPL script.

esProc SPL is a pure Java program, it can be called by Java, and it can also call Java. In this way, even some code that is difficult to implement with SPL and needs to be implemented in Java (such as some external interfaces) or existing ready-made Java code can be integrated into SPL. SPL scripts and main Java applications can be integrated seamlessly.

SPL scripts are stored as files and placed outside of the main application program. Code modifications can be made independently and immediately take effect, unlike Java class libraries such as Stream/Kotlin that require recompilation with the main program after code modifications, and the entire application needs to be shut down and restarted. This can achieve hot swap of business logic, especially suitable for supporting frequently changing businesses.

The data sources supported by SPL are also very rich, whether it is a relational database or NoSQL or Kafka or Restful, whether it is a regular two-dimensional table or a multi-level JSON, SPL can all calculate and process, unlike ORM technology, which can only target relational databases.

Very specifically, SPL code is written in a grid, which is very different from the code typically written as text. The independent development environment is simple and easy to use, providing single step execution, breakpoint setting, and WYSIWYG result preview, making debugging and development more convenient.

Here A programming language coding in a grid is a more detailed introduction to SPL.

Finally, esProc SPL is here https://github.com/SPLWare/esProc .