ORM technology indeed simplifies basic CRUD operations, but it also has many limitations when dealing with complex calculations. Hibernate’s HQL is notably inadequate, making it difficult to implement dynamic column calculations and multi-layer associations. While JOOQ improves flexibility through its DSL, its grouping calculations require multi-layer nesting, leading to more verbose code than native SQL.

esProc SPL is like a “plug-in” for data computation! To write a multi-layer JOIN based on dynamic conditions, you had to use chain calls in Java for half an hour with JOOQ. Now you can implement this in just a few lines of SPL script, and its syntax is more intuitive than SQL. For example, calculating ‘top 3 sales by department’, which even requires nesting in SQL:

SELECT dept, name FROM (
  SELECT dept, name, RANK() OVER (PARTITION BY dept ORDER BY sales DESC) as rank 
  FROM employee
) WHERE rank <=3

can be implemented in SPL with just one line of code:

employee.groups(dept; top(-3, sales))

SPL also supports dynamic data structures. It eliminates the need to predefine entity classes, and you can dynamically add fields in the script at any time: Orders.derive(Amount*0.1:tax, Amount+tax:total_amount). This is in contrast to JOOQ’s need for predefinition. Performing calculations in SPL is as simple as SQL. For example, filtering conditions can be directly written as Orders.select(amount>1000 && like(client,"*s*")), with field names used directly without object prefixes. In contrast, JOOQ’s approach, such as ORDERS.AMOUNT.gt(1000), pales in comparison.

When performing cross-database analysis (e.g., MySQL user data + Elasticsearch logs), you had to write ETL scripts to extract and load the data. With SPL, you can now process data across databases directly:

No need for extracting data, no need for creating intermediate tables. In this scenario, ORM is left helpless!

ORM is more suitable for handling simple tasks, while complex calculations, cross-source operations, and dynamic logic can all be handed over to SPL. Whether it’s real-time risk control, dynamic reporting, or IoT stream processing, SPL can handle them all with ease. Additionally, SPL’s cursor mechanism allows for reading and computing simultaneously without exceeding memory, and its syntax is concise and code is flexible. Try processing Kafka stream data with JOOQ, the threading model in Java alone can drive you crazy. In contrast, SPL can perform real-time aggregation directly with kafka_open().kafka_poll@c().groups(hour(time);avg(value)). The gap is like the difference between a wagon and a Tesla.

Similar to ORM, SPL is also developed purely in Java and can be seamlessly integrated into Java applications for deployment and distribution. However, unlike ORM, using SPL for computations typically needs to write the business logic into scripts, which are then called by Java through JDBC.

Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
Statement st = con.prepareCall("call SplScript()");	//SPL script name
st.execute();
ResultSet rs = st.getResultSet();

This will cause a separation of calculation code and Java code, which is quite different from the style where ORM is tightly integrated into Java applications. ORM programmers may find it unfamiliar at first. In fact, SPL has full support for flow control, such as if and for, making business function implementation more convenient than Java.

The advantage of independent SPL scripts is the hot-update feature. SPL scripts are interpreted at runtime. When running in standalone applications, if the statistical logic changes, you can leisurely modify the SPL script, upload it directly to the server, and the business system will take effect in seconds without even needing a restart. However, with tools like JOOQ, you have to recompile and redeploy after modifying the Java code, which results in a poor experience.

Essentially, SPL does not objectify data tables; rather, it directly manipulates the database using SPL. While this approach might be less convenient than MyBatis for simple single-table CRUD operations, SPL will absolutely rescue you from the ORM quagmire when facing the three major challenges: complex calculations, heterogeneous data, and frequently changing requirements. Programmers shouldn’t make things harder on themselves. Let ORM handle what it’s good at – object mapping – and leave computations to the specialized SPL. Isn’t that far more elegant than wrestling with SQL in Java?