Hibernate, Mybatis, and emerging ORM technologies such as JOOQ can easily map database tables into Java objects and provide automatic read and write methods. ORM technology makes developing database applications in Java more efficient.

However, mapping data tables is only the most basic part, and the development of business logic also involves a lot of process control and data calculation work. Process control is a strong point of Java, without pressure; However, batch structured data computing has always been a weakness of Java, and programming directly in Java can be very cumbersome.

Unfortunately, the computing capability provided by these ORM technologies is also unsatisfactory. Hibernate relies almost entirely on converting from HQL to SQL, while HQL can only correspond to a very small subset of SQL, resulting in significantly weak computational capability; JOOQ is much stronger, providing many basic operations in the Java style, such as filtering, grouping, etc. This is also one of the reasons why it has surpassed Hibernate in recent years, but the code is still not as concise as SQL.

---

Developing database applications in Java, esProc SPL is a better choice.

esProc SPL is an open-source software written in Java. As a pure Java software, esProc can be seamlessly integrated into Java applications like ORM, enjoying the advantages of mature Java frameworks together.

---

Unlike ORM technology, esProc provides a new programming language SPL based on JVM for programming, rather than directly using Java. SPL scripts are called by Java programs via the JDBC driver 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 Java APIs?

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 won’t work effectively. It is necessary to define all the structures (classes) used when compiling the code, 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 without table names can be correctly referenced based on the context.

SQL is an interpretive dynamic language, and so is SPL. Java is not, so it is difficult to write concise code in Java, no matter how APIs are provided, it is not possible.

---

On the basis of interpretive dynamic languages, SPL provides more comprehensive structured data objects (tables, records, cursors) and richer computational 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 more concise and easier to maintain than SQL, and much stronger than Java code based on ORM. Here are some simple comparisons (using only JOOQ, which has the strongest computing capability, as an example):

Filtering

SQL: select * from Orders where ((SellerID=2 and Amount<3000) or (SellerID=3 and Amount>=2000 and Amount<5000)) and year(OrderDate)>2010
SPL: Orders.select( ((SellerID==2 && Amount<3000) || (SellerID==3 && Amount>=2000 && Amount<5000)) && year(OrderDate)>2010)
JOOQ:
    context.select().from(ORDERS)
        .where(((ORDERS.SELLERID.equal(2).and(ORDERS.AMOUNT.lessThan(3000.0)))
    .or((ORDERS.SELLERID.equal(3).and(ORDERS.AMOUNT.greaterOrEqual(2000.0).and(ORDERS.AMOUNT.lessThan(5000.0))))))
    .and(year(ORDERS.ORDERDATE).greaterThan(2012)))
    .fetch(); 

Grouping

SQL: select Client, extract(year from OrderDate) y,count(1) cnt from Orders group by Client, extract(year from OrderDate) having amt<20000
SPL: Orders.groups(Client,year(OrderDate):y;sum(Amount):amt,count(1):cnt).select(amt<20000)
JOOQ:
    context.select(ORDERS.CLIENT,year(ORDERS.ORDERDATE).as("y"),sum(ORDERS.AMOUNT).as("amt"),count(one()).as("cnt"))
    .from(ORDERS)
    .groupBy(ORDERS.CLIENT,year(ORDERS.ORDERDATE))
    .having(field("amt").lessThan(20000)).fetch();

For more complex tasks, such as this one, calculating the longest consecutive days for a stock to rise, SQL needs to be written in multiple nested, lengthy, 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 )

JOOQ also needs to use window functions, which are more complex than SQL:

WindowDefinition woDay1 = name("woDay").as(orderBy(Stock.TradeDate));
Table<?>T0=table(select(Stock.TradeDate.as("TradeDate"),when(Stock.Price.greaterThan(lag(Stock.Price).over(woDay1)),0).otherwise(1).as("risingflag")).from(Stock).window(woDay1)).as("T0");
WindowDefinition woDay2 = name("woDay1").as(orderBy(T0.field("TradeDate")));
    Table<?>T1=table(select(sum(T0.field("UpDownTag").cast(java.math.BigDecimal.class)).over(woDay2).as("NoRisingdDys")).from(T0).window(woDay2)).as("T1");
Table<?>T2=table(select(count(one()).as("ContinuousDays")).from(T1).groupBy(T1.field("NoRisingDays"))).as("T2");
Result<?> result=context.select(max(T2.field("ContinuousDays"))).from(T2).fetch();

The same calculation logic, while using SPL is very simple:

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

---

Convenient reading and writing of databases is a fundamental feature of ORM technology, and SPL is certainly not a problem in this regard:

Hibernate:
    String hql ="from OrdersEntity where sellerId=10";
    Query query = session.createQuery(hql);
    List<OrdersEntity> orders = query.list();

    Transaction tx = session.beginTransaction();
    for ( int i=0; i<orders.size(); i++ ) {
        session.save(orders.get(i));
    }
    tx.commit();

JOOQ:
    java.sql.Connection conn = DriverManager.getConnection(url, userName, password);
    DSLContext context = DSL.using(conn, SQLDialect.MYSQL);
    Result<OrdersRecord> R1=context.select().from(ORDERS).fetchInto(ORDERS);

    R1.forEach(r->{r.setValue(ORDERS.AMOUNT,r.getValue(ORDERS.AMOUNT).doubleValue()+100);}); R1.forEach(r->{r.update();});

SPL:
    T=db.query("select * from test.Orders where sellerId=?",10)
    db.update(T,orders)
    db.commit()

These features are not significantly different, and SPL is still simpler due to its aforementioned language advantages.

---

SPL itself has well-established process control statements, such as for loops and if branches, and also 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. This is equivalent to moving stored procedures from the database to Java.

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 in SPL and needs to be implemented in Java (such as some external interfaces) or existing ready-made Java code can be integrated in SPL. SPL scripts and main Java applications can be integrated seamlessly.

As an interpretive language, SPL scripts can be stored as files and placed outside the main application program. Code modifications can be made independently and immediately take effect, unlike code based on ORM that needs to be recompiled with the main program after modification, 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. However, ORM technology can generally only target relational databases and cannot directly support other data sources.

---

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. Debugging and development are also more convenient than Java.

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 .