Business logic usually involves complicated procedures and computations and database writing and reading. Stored procedures are not suitable for handling many scenarios due to various reasons, including complex privilege granting, potential security risks, code un-migratability, high technological threshold, programming difficulty, and so on. Being not good at handling complex procedures, SQL is not fit for implementing business logic alone. It has to work with a high-level language like Java to get the job done. Yet, SQL and high-level languages have disparate syntax styles and data structures. Their cooperation is hard, and development efficiency has been low. In view of this, ORM emerges as the situation calls for.

ORM is a technique that maps structured data (tables/records) into a high-level language's object. It enables using the uniform data structure and syntax style to achieve business logic, which leads to the realization of the ultimate aim – high development efficiency. Common ORM techniques include Hibernate, QueryDSL and JOOQ, etc.

ORM is proved excellent in unifying data structures and syntax styles, and has been extensively applied in a great number of projects. The technique also has many shortcomings. It does not have structured data types and hot swap support, is inconvenient for performing set-oriented calculations and roundabout in database read/write, does not have enough library functions necessary for accomplishing complex computations. The drawbacks offset, sometimes seriously damage, the development efficiency.

SPL is the Java-based open-source structured data processing class library. It can accomplish the goals ORM has been trying to achieve, even better.

Business logic implementation with uniform structure and syntax style

Here is a typical example of business logics. We are trying to calculate bonuses according to the existing rule and insert records containing a BONUS field to the database. SPL does this with the following code:

	A	B	C
1	=db=connect@e("dbName")		/Connect   to database and start transaction
2	=db.query@1("select   sum(Amount)  from sales where sellerID=? and year(OrderDate)=? and   month(OrderDate)=?", p_SellerID,year(now()), month(now()))		/Query   sales amount of the current month
3	=if(A2>=10000:200,   A2<10000 && A2>=2000 :100, 0)		/The   cumulative bonus of the current month
4	=p_Amount*0.05		/The   fixed bonus for the current order
5	=BONUS=A3+A4		/Total   bonus
6	=create(ORDERID,CLIENT,SELLERID,AMOUNT,BONUS,ORDERDATE)		/Create   data structure for orders table
7	=A6.record([p_OrderID,p_Client,p_SellerID,p_Amount,BONUS, date(now())])		/Generate   an orders record
8	>db.update@ik(A7,sales;ORDERID)		/Write   orders records to a database table
9	=db.error()		/Check   database write result
10	if   A9==0	>A1.commit()	/Commit   transaction if data is successfully written
11	else	>A1.rollback()	/Rollback   transaction if database write fails
12	>db.close()		/Close   database connection
13	return   A9		/Return   database write result

SPL uses uniform data structure, syntax and functions to program the whole business logic, including database read/write, transaction and procedure handling, and data computations.

Java code does not contain business logic. It invokes the above SPL script through JDBC driver to achieve the business logic.

…
//The invocation with parameters omitted
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call InsertSales(?, ?,?,?)}");
statement.setObject(1, d_OrderID);
statement.setObject(2, d_Client);
statement.setObject(3, d_SellerID);
statement.setObject(4, d_Amount);
statement.execute();
...

Java invokes an SPL script in the same way it calls a stored procedure, without the need of any privileges granted by database administrator. The invocation is secure and convenient to migrate and debug, boasts powerful data processing capability and concise code.

 

SPL offers specialized structured data objects, all-around internal-external data exchange and procedure control mechanisms.

Structured data objects

To achieve business logics revolving around structured data objects, SQL, ORM and SPL offer specialized built-in structured data objects and design their own structured data computation functions. ORM has entity and List<entity>, and SPL gives record and table sequence.

Get field value from a record: =r.AMOUNT*0.05

Modify field value of a record: =r.AMOUNT= T.AMOUNT*1.05

Get a column from a table sequence: T.(AMOUNT)

Add a record to a table sequence: T.insert(0,31,"APPL",10,2400.4)

Based on table sequence, SPL offers a weal of SQL-style functions.

Filtering: T.select(Amount>1000 && Amount<=3000 && like(Client,"*bro*"))

Sorting: T.sort(-Client,Amount)

Distinct: T.id(Client)

Aggregation: T.max(Amount)

Filtering after grouping & aggregation:  T.groups(year(OrderDate),Client; avg(Amount):amt).select(amt>2000)

Join: join(Orders:o,SellerId ; Employees:e,EId).groups(e.Dept; sum(o.Amount))

Intersection: T1.id(Client) ^ T2.id(Client)

TopN: T.top(-3;Amount)

Intra-group topN: T.groups(Client;top(3,Amount))

In addition, SPL supplies various other types of functions, including string functions, date functions and mathematical functions, to cater to computations for achieving business logics.

 

Internal-external data exchange ability

To read the external database records as internal structured data objects, ORM mainly uses primary key-based query, HQL query, SQL query and chain type programming, and SPL executes SQL through the query function.

Get one record:

=r=db.query("select * from sales where orderid=?",201)

Get a table sequence (a set of records):

=T=db.query("select * from salesR where SellerID=?",10)

In order to create more migratable code, SPL provides general SQL syntax and sqltranslate function to transform it to the mainstream SQL dialects, which will still be executed using the query function. Here we'll skip the detailed explanation.

To persist the internal structured data objects into the database, ORM employs methods such as save (newly added entity), update (modified entity) and delete (removed entity), and SPL uses update function.

For instance, the original table sequence is T, the table sequence after data insertion, update and deletion is NT, and we are trying to save the latter in the database:

=db.update(NT:T,sales;ORDERID)

 

Procedure control ability

One difficulty of implementing business logic is the complex procedure control (judge and loop). SQL lacks the procedure control ability. ORM maps a database table into the Java object in an effort to make convenient use of the language's procedure control ability. SPL, like Java, has a complete set of procedure control capabilities.

SPL branch judgement statement:

	A	B
2	…
3	if   T.AMOUNT>10000	=T.BONUS=T.AMOUNT*0.05
4	else   if T.AMOUNT>=5000 && T.AMOUNT<10000	=T.BONUS=T.AMOUNT*0.03
5	else   if T.AMOUNT>=2000 && T.AMOUNT<5000	=T.BONUS=T.AMOUNT*0.02

SPL loop statement:

	A	B
1	=db=connect("db")
2	=T=db.query@x("select   * from sales where SellerID=? order by OrderDate",9)
3	for   T	=A3.BONUS=A3.BONUS+A3.AMOUNT*0.01
4		=A3.CLIENT=CONCAT(LEFT(A3.CLIENT,4),   "co.,ltd.")
5		…

Similar to Java, SPL can use the break keyword to exit (or terminate) the current loop body, or the next keyword to skip (ignore) the current round of loop.

 

Higher development efficiency than ORM

SPL's ability is no more than using unified data structure and syntax style to achieve business logics. The language can accomplish the ultimate goal of ORM, that is, significantly increase the development efficiency in achieving business logics.

Rich supply of functions

ORM does not have enough functions for achieving a lot of functionalities straightforwardly, or it has to write very long code to get them done. SPL offers a wealth of functions, making it convenient to achieve those functionalities and reducing code amount.

For instance, use a datetime function to get a date before or after the specified date: elapse("2020-02-27",5)  //Return 2020-03-03

Get what day the specified date is: day@w("2020-02-27")             //Return 5, that is, Thursday

Get the date N workdays after the specified date: workday(date("2022-01-01"),25)   //Return 2022-02-04

Use a string function to check whether a string is all made up numbers: isdigit("12345")    //Return true

Get the string before a specified substring: substr@l("abCDcdef","cd")      //Return abCD

Split a string by vertical line to generate an array of strings: "aa|bb|cc".split("|")     //Return ["aa","bb","cc"]

There are a lot of more functionalities that SPL supports, such as getting the year before or after a specified year/which day is the date in the year/the quarter, splitting a string by a regular expression, extracting the where or select part of a SQL statement/words from a string, splitting an HTML string by identifiers.

 

Convenient set-oriented calculations

ORM has two methods of performing set-oriented calculations on structured data objects (entity sets). They are loop statement + hardcoding and Stream & lambda expression + set-oriented functions. Obviously, the former produces a great deal of code. The latter seems to be able to achieve the calculation with less code, yet most calculations need hardcoding since the compiled Lambda expression are complicated and ORM has insufficient and weak set-oriented functions. A simple grouping & aggregation, for instance, involves a number of classes and methods including groupingBy, Collectors, summarizingDouble and DoubleSummaryStatistics.

SPL designs concise and easy to understand, interpreted Lambda syntax and a great number of set-oriented functions, enabling accomplishing set-oriented calculations conveniently. For instance:

Modify records in batch: T.run(BONUS+AMOUNT*0.01: AMOUNT, concat(left(CLIENT,4), "co.,ltd."): CLIENT)

Filtering: T.select(Amount>1000 && Amount<=3000)

Filter an ordered table sequence with binary search: T.select@b(Amount>1000 && Amount<=3000)

Grouping & aggregation: T.groups(Client;sum(Amount))

Order-based grouping (neighboring records having same values in the specified field are put into the same group): T.groups@b(Client;sum(Amount))

The inter-row set-oriented calculations are a difficulty. Example are calculating link relative ratio and YOY growth. ORM does not have optimization methods for inter-row calculations. The code is very complicated. SPL uses "field[relative position]" to reference another record or a value of it, generating extremely simple and clear code. It can automatically handle special cases like out-of-bounds value. To add a computed column rate and calculate the growth rate of amount for each orders, for instance:

=T.derive(AMOUNT/AMOUNT[-1]-1: rate)

Being able to use SPL lambda syntax and set-oriented functions flexibly can greatly simplify complex set-oriented calculations. To find employees in each department whose ages are below the average of the department, for instance:

	A
1	…//Skip   the generation of table sequence Employees
2	=Employees.group(DEPT;   (a=~.avg(age(BIRTHDAY)),~.select(age(BIRTHDAY)<a)):YOUNG)
3	=A2.conj(YOUNG)

And to count the longest continuously rising days for each stock:

	A
1	…//Skip   the generation of table sequence AAPL
2	=a=0,AAPL.max(a=if(price>price[-1],a+1,0))

 

Specialized structured data types

ORM's structured data types entity and List<entity> are general enough but not so specialized. Many common access methods are not applicable, such as getting one or more columns by field name(s). SPL offers more specialized and more powerful structured data types, records and table sequences, which support all common accesses methods. For instance, based on table sequence T:

Get one column and return a simple set: T.(AMOUNT)

Get columns and return a set of sets: T.([CLIENT,AMOUNT])

Get columns and return a new table sequence: T.new(CLIENT,AMOUNT)

Traditionally, it is difficult to access records by sequence numbers. But a SPL table sequence is naturally ordered, making it easy to do sequence-number-based accesses. For instance:

Get columns by column numbers and return a new table sequence: T.new(#2,#4)

Get records backwards by sequence numbers: T.m(-2)

Get certain records by sequence numbers to generate a table sequence: T([3,4,5])

Get records by a specified range to generate a table sequence: T(to(3,5))

Get values by fields and then by their sequence numbers: T.(AMOUNT)(2), which is equivalent to getting them by sequence numbers and then by fields: T(2).AMOUNT

SPL also provides advanced functionalities based on table sequences like getting topN, getting values distributed in the zigzag pattern and order-based join.

Interpreted execution & hot swap

Business logics are various, complicated and dynamic. An excellent system framework should be able to deal with the volatile business logics. ORM is Java code in essence, which requires compilation before execution and shutting down the machine before deployment. This makes it really complicated in handling dynamic business logics.

SPL is a Java-based interpreted execution language that executes without compilation. Modification of a script takes effect immediately, and hot deployment without having to shutting down the server is supported. This is the right tool for handling dynamic business logics.

 

Convenient database read/write

Many business logics involve reading and writing records in batches. ORM loops ArrayList to do this and handles one record each time, producing lengthy code. The ORM code becomes even more complicated when there are newly added, modified, and deleted records during the batch database write.

Based on table sequences, SPL offers convenient methods of batch reading and writing, simplifying code substantially. To modify records in batch, for instance:

	A	B
1	…
2	=T=db.query("select   * from salesR where SellerID=?",10)	/Batch   query table sequence T
3	=NT=T.derive()	/Copy-generate   the new table sequence NT
4	=NT.field("SELLERID",9)	/Batch   modification
5	=db.update(NT:T,sales;ORDERID)	/Persist   the modification into the database

In the above code, update function does not use the complicated loop statement to achieve batch modification. It is sophisticatedly designed to have the ability to perform batch database writing in a unified way. The SPL advantage is clear, particularly when the newly added, modified and deleted are all involved.

	A	B
1	…
2	=T=db.query("select   * from salesR where SellerID=?",10)	/Query   a batch of records
3	=NT=T.derive()	/Copy-generate   a new table sequence
4	=NT.delete(NT.select(ORDERID==209   || ORDERID==208))	/Batch   deletion
5	=NT.field("SELLERID",9)	/Batch   modification
6	=NT.record([220,"BTCH",9,5200,100,date("2022-01-02"),   221,"BTCH",9,4700,200,date("2022-01-03")])	/Batch   appending
7	=db.update(NT:T,salesR;ORDERID)	/Persist   the modification into the database

Compared with ORM, SPL is a specialized structured data processing language that offers powerful data objects, agile syntax and rich functions. It enables convenient and easy database read/write and simplifies the traditionally complex procedure control and data computations, markedly increasing development efficiency in achieving business logics.