Many open-source technologies can implement the business logic focused on database in Java. Among them, JOOQ is more powerful than Hibernate in computing power, and more powerful than MyBatis in migratability, thereby getting increasing attention. Likewise, esProc SPL, as a new data computing language, is also outstanding in terms of computing power and migratability. This article will compare them in many aspects so as to find out the one that is more efficient in developing data business logic. JOOQ commercial edition mainly supports commercial database and stored procedure, which will not be discussed in this article.

Language features

Programming style

JOOQ supports complete object-oriented programming style, which can combine multiple objects (methods) together to form a SQL-like syntax logic. JOOQ can use Java's Lambda expression, function call interface, and flow control syntax, and also supports function-oriented and
procedure-oriented in theory. However, since these expressions/syntaxes are not specially designed for JOOQ's structured data objects (Result), it is inconvenient use them.

In contrast, SPL supports the programming styles of object oriented, function oriented, and procedure oriented, and has made a significant simplification. In addition, SPL has the concept of objects, and can use the dot to access attribute and perform multi-step calculation, yet SPL does not have related contents like inheritance and overloading. In short, the Lambda expression of SPL is simpler and easier to use than SQL, and since SPL’s function call interface and flow control syntax are specially designed for structured data object (table sequence), it is more convenient to use.

Operation mode

JOOQ code is the compiling-executed Java code, which is a bit higher in performance but poorer in flexibility. But, although its performance is a bit higher, JOOQ itself has no computing power, and it has to firstly generate SQL statements after execution, and then send SQL statements to database to calculate and return the calculation result, therefore, the actual performance of JOOQ is not high. Especially in some business logics that need to repeatedly read from and write to the database, the performance is even worse. Unlike JOOQ, SPL is an interpreted language, which is more flexible in coding. Although the performance is slightly worse when executing the same code, SPL has independent computing power that does not depend on database, and there is no need to repeatedly read from and write to the database, and SPL has built in a large number of basic operations with lower time complexity, its computing performance often outperforms that of compiled language.

External library

JOOQ can import any other third-party’s Java library to make up for its shortcomings, such as using Streamto improve its independent computing power. However, these libraries are not designed for structured data objects and offer limited functions. By comparison, SPL has professional built-in data processing functions, and provides a large number of basic operations boasting higher development efficiency and lower time complexity. Usually, there is no need for SPL to use external Java libraries, which can be called in self-defined functions in special cases.

IDE and debugging

Both JOOQ and SPL have the graphical IDE and complete debugging function. JOOQ uses Java IDE. Although this IDE is more general, it is inconvenient to observe structured data objects as a result of not being optimized for data processing. The IDE of SPL is specially designed for data processing, and the structured data objects are presented in the form of table, and hence it is easier to observe.

Learning difficulty

For JOOQ, you need to learn three kinds of syntaxes, namely, SQL, universalJava, and JOOQ. As for the degree of learning, your ability on SQL should be higher than average level, only then can you convert to JOOQ syntax; JOOQ syntax is mainly used during development, which is not difficult, but the conversion process is relatively complex; Your language ability on universalJava can be lower than average level. In contrast, since the objective of SPL is to simplify Java code or even SQL code, the difficulty is not high whether in learning the rudiments of SPL, or in-depth developing in SPL. However, when it involves high-performance computing, you need to learn more algorithms specific to SPL, and the difficulty will increase.

Amount of code

SQL is good at calculating structured data, its syntax is concise, and the code amount is lower. But, in order to translate SQL into JOOQ, it needs to import a lot of functions, resulting in a phenomenon of excessive encapsulation. Consequently, the actual code amount is larger. In addition, the flow control of JOOQ relies on Java syntax, yet Java syntax is not designed for structured data objects, the amount of code is therefore not low.

SPL is more expressive than SQL, and much more expressive than JOOQ, and can implement structured data calculation with lower code amount. Besides, SPL's flow process statements are specially designed for structured data objects, and the code amount is lower than that of Java.

Structured data object

Structured data object is used to objectify the database table, which is the basis for data processing and business logic development. The professional structured data objects can easily exchange the data with database, support rich calculation functions, and simplify the difficulty of flow processing.

Definition

JOOQ's structured data objects consist of record, and record set. There are many types of record objects. The first type is the Recordobject, which is suitable for the situation where the number, type, and name of fields are all dynamically generated. Although the Recordobject is flexible, its object-oriented degree is low, and its usage is relatively cumbersome. For example, you need to obtain the Mth field through getValue(M). The second type is the Record[N] object (Nrepresents number 1 to 22, such as Record3), which is suitable for the situation where the type and number of fields are already known, and the number of fields doesn’t exceed 22, and the field name is dynamically generated. This type of object is less flexible but a bit higher in object-oriented degree, and easier to use. For example, you can get the Mth field through valueM. The third type is generated by the code tools of JOOQ according to the structure of database table, and the number of objects is the same as that of tables, and the number, type and name of fields correspond strictly to those of database table, such as OrdersRecordand EmployeesRecord. This type of object is inflexible but high in object-oriented degree, and easy to use. You can get a field directly through its name. The third type of object corresponds to database table, which can be called the record object with fixed data structure; The first two types of objects usually come from the query calculation on database table, and can be called the record object with dynamic data structure. These three types of record objects are common, and there are also some uncommon record objects, such as user-defined record type UDT, which will not be discussed here. In short, JOOQ has many types of record objects, which are very different in usage, resulting in an increase in development difficulty. The reason underlying why the development is difficult is that there are a large number of dynamic data structures in business logic, yet Java is a compiled language and good at expressing fixed data structure only, and if you insist on expressing dynamic data structures in Java, you have to design complex interface rules or predefine a lot of objects according to the number of fields.

As for JOOQ's record set, the number of types is relatively small. The common record sets include the native object Resultand its parent class ArrayList, and Streamis used sometimes.

Likewise, SPL's structured data objects also consist of record (Record)and record set (table sequence). SPL has only one type of record object, mainly because SPL is an interpreted language, and it’s equally convenient for SPL to express the dynamic and fixed data structures, and both interfaces are very simple, therefore, there is no need to have multiple types of record objects.In addition, although the record object differs from the single-record set in nature, they have similar business meaning, and thus it is easy to get confused when in use. Fortunately, SPL is an interpreted language, which can keep the external use of both consistent through flexible interfaces, thereby further improving the ease of use. On the contrary, JOOQ is a compiled language, which makes it very difficult to design such flexible interfaces, and hence it has to provide two different types of interfaces for processing the record object and single-record set respectively.

Read data from database

JOOQ reads external database table and generates fixed record set:

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);

Query external database table and generate dynamic record set:

Result<Record3<Integer,String,Double>>R2=context.select(ORDERS.SELLERID,ORDERS.CLIENT,ORDERS.AMOUNT).from(ORDERS).fetch();

The subsequent use of dynamic record set is a bit cumbersome, but it can be compatible with fixed record set. We mainly use dynamic record set in the rest of this article.

SPL reads or queries external database table, and generates the table sequence:

	A
1	=conn=connect("mysql8")
2	=conn.query("select * from Orders")
3	=conn.query("select SellerID,Client,Amount from Orders")

SPL does not make a distinction between fixed record set and dynamic record set, and the generation methods of the two kinds of sets are the same, and the subsequent use are also the same.

Write data to database

JOOQ provides three functions to persist the processed structured data objects and save to the database: insert, update, delete. To modify recordrand then update it to the database:

r.setValue(ORDERS.AMOUNT,r.getValue(ORDERS.AMOUNT).doubleValue()+100);
r.update();

This code is to update a single record. It should be noted that the database table must have the primary key, only in this way will the automatically generated record class inherit UpdatableRecordImpl, and only the record class that inherits UpdatableRecordImpl supports the update function.
Writing data to database in batches is a common scenario in data business logic, which can be implemented in JOOQ. The following code is to modify the record set T in batches and then update to database:

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

This code loops through the record set, and then manually updates each record to achieve the update of entire set. As can be seen that since JOOQ achieves batch writing through hard coding, and does not encapsulate, it is inconvenient in many cases. If the modification, addition and deletion occur in the same batch of records, you have to distinguish three types of records, and then use different functions to store them in a loop way. The common method is to inherit the record class, and add a new “ID" attribute to distinguish.
Alternatively, save an unmodified original record set T, and manually compare the modified set NT with T. No matter which method is used, the process of manual implementation is very troublesome.

SPL encapsulates the writing to database, and implements the addition, modification and deletion of a single record or batch records only with one update function. Moreover, SPL supports updating the records in a mixed way. For example, the original table sequence is T, and the table sequence after a series of additions, deletions and modifications is NT, the following code is to persist the changed results to the orders table of database:

conn.update(NT:T,orders)

Access the field

JOOQ reads the Clientfield of a single record:

R1.get(0).getClient();
R1.get(0).get(ORDERS.CLIENT);

This code reflects the core advantages of JOOQ: it supports pure object-oriented field access method, and does not need to use string, numeric constant, or other non-Java expression, and is highly uniform in code style. Unfortunately, it only applies to fixed structured data objects. If you want to query the dynamic record objects generated through calculation, you have to use the string field name or numerical sequence number to access the field:

R2.get(0).get("Client");
R2.get(0).get(1);

Dynamic record objects are more common. The above field access method is not pure object-oriented, which is inconsistent in code style, and does not support autocomplete, and is cumbersome to code in general.
Similarly, SPL also supports pure object-oriented field access method. The difference is that it’s generally convenient to code regardless of whether it is fixed or dynamic structured data object:

T(1).Client

Of course, SPL also supports using string field name or numeric sequence number to access field:

T(1).field(2)
T(1).field("Client")

SPL is purer in object-oriented, more uniform in code style, and more convenient in coding. In addition, SPL provides many convenient functions that JOOQ does not support: the default field name can be directly accessed with a dot. For example, to take the second field, we can write: T(1).#2; to take multiple fields and return the set of sets, the code is: T.([Client,Amount]).

Ordered access

Ordered access is one of the difficulties in business logic development. The record set of JOOQ inherits from Java's ordered set ArrayList, which has a certain ordered access capability, and supports fetching the record by index or interval:

R.get(3)
R.subList(3,5);

For further functions, they have to be achieved through hard coding. For example, to fetch the last 3 records, the code is:

Collections.reverse(R);
R.subList(0,3);

As for the functions such as fetching records by position set or fixed interval, it will be more troublesome when hard coding.
SPL’s table sequence is also an ordered set, and provides the basic functions related to order, such as fetching the records by index and interval:

T(3)
T.to(3,5)

Table sequence is a professional structured data object, and directly provides many order-related advanced functions that JOOQ’s Result does not support, such as fetching the records through sequence number that counts backwards can be directly represented by a negative sign:

T.m(-3)				//the 3rd record from last
T.m(to(-3,-5))			//Get records by interval that counts backwards
Another example, fetch the records by position set or fixed interval:
T.m(1,3,5,7:10)		//Records with sequence number 1, 3, 5, 7-10
T.m(-1,-3,-5)			//The 1st, 3rd, 5th records from last
T.step(2,1)			//Get the  first of every 2 records (equivalent to fetching by odd position)

Structured data calculation

The ability to calculate structured data is the core function of data business logic. Here below are several common calculation examples in the order from easy to difficult to compare the computing codes of JOOQ and SPL.

Renaming

//Equivalent SQL
select SellerID eid,Amount amt from Orders
//JOOQ
context.select(ORDERS.SELLERID.as("eid"),ORDERS.AMOUNT.as("amt")).from(ORDERS).fetch()
//SPL
Orders.new(SellerID:EID, Amount:amt)

The syntax logic of JOOQ is basically the same as that of SQL, which can achieve the purpose of using object-oriented method to simulate SQL, this is the important advantage of JOOQ. Although it is an advantage, it brings relevant disadvantages. For example, an operation of JOOQ can only be implemented by using multiple functions in a combined way, and each function has its own parameters and syntax rules, resulting in an increase in learning and coding difficulties. In addition, the field name in many functions must attach a table name, even for single-table computing. This shows that the syntax of JOOQ is less professional, and there is still much room for improvement.

In contrast, SPL directly uses object-oriented syntax to implement the calculation, and one operation corresponds to one function, and there is no need to attach a table name when referencing the field. Therefore, SPL’s syntax is more professional, and its code is shorter.

Conditional query

//Equivalent SQL
select * from Orders where
	((SellerID=2 and Amount<3000) or (SellerID=3 and Amount>=2000 and Amount<5000))
	and
	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();
//SPL
Orders.select(
	((SellerID==2 && Amount<3000) || (SellerID==3 && Amount>=2000 && Amount<5000))
	&&
	year(OrderDate)>2010)

SQL’s conditional expression itself is simple enough. Although JOOQ simulates SQL, the conditional expression is over-encapsulated. Too many functions and multi-layer brackets make JOOQ code difficult to read, and far less easy to understand than SQL. Conversely, SPL uses only one function to implement conditional query, its conditional expression is thus short and easy to read.

Grouping and aggregating

//Equivalent SQL:
select Client, extract(year from OrderDate) y,count(1) cnt
from Orders
group by Client, extract(year from OrderDate)
having 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();
//SPL
Orders.groups(Client,year(OrderDate):y;sum(Amount):amt,count(1):cnt)
.select(amt<20000)

In order to simulate SQL, JOOQ uses a lot of functions, and the rules are very complex, leading to too long code. SPL directly uses object-oriented syntax, and thus the rules are simpler and code is shorter.

The above examples are all relatively simple calculations. Similar calculations also include the sorting, de-duplication, association, and intersection, union and difference of sets, etc., the examples of which will not be given here. In general, when using JOOQ to perform simple calculations, its code is longer than SQL and SPL codes, and not easy to understand in most cases, and hence the development efficiency is lower.

Find out the top 3 in each group

//Equivalent SQL
select * from (select *, row_number() over (partition by Client order by Amount) rn from Orders) T where rn<=3
//JOOQ
WindowDefinition CA = name("CA").as(partitionBy(ORDERS.CLIENT).orderBy(ORDERS.AMOUNT));
context.select().from(select(ORDERS.ORDERID,ORDERS.CLIENT,ORDERS.SELLERID,ORDERS.AMOUNT,ORDERS.ORDERDATE,rowNumber().over(CA).as("rn")).from(ORDERS).window(CA) ).where(field("rn").lessOrEqual(3)).fetch(); 
//SPL
Orders.group(Client).(~.top(3;Amount)).conj()

This computing task is a little difficult. Although JOOQ simulates SQL, multiple functions are used. As a result, JOOQ code is much longer than that of SQL, and its syntax looks increasingly less like SQL, and it is more difficult to code and understand. Unlike JOOQ, SPL uses a different method that first groups the clients, and then calculates the top 3of each group (i.e., ~), and finally concatenates the calculation results of each group. We can see that SPL code is shorter and easier to understand.

JOOQ uses window function, which only applies to the database of specific version such as MySQL8, and does not apply to databases of other versions. To implement the same calculation in MySQL5, it is very cumbersome to modify the code. SPL has independent computing power, and the code can be used in any database.

Calculate the maximum consecutive days that a stock keeps rising

JOOQ:

WindowDefinition woDay1 = name("woDay").as(orderBy(APPL.DAY));
Table<?>T0=table(select(APPL.DAY.as("DAY"),when(APPL.PRICE.greaterThan(lag(APPL.PRICE).over(woDay1)),0). otherwise(1).as("risingflag")).from(APPL).window(woDay1)).as("T0");
WindowDefinition woDay2 = name("woDay1").as(orderBy(T0.field("DAY")));
Table<?>T1=table(select(sum(T0.field("risingflag").cast(java.math.BigDecimal.class)).over(woDay2). as("norisingdays")).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();

This problem is more difficult and requires comprehensive application of many simple calculations. Since it is difficult for JOOQ to directly express the concept of consecutive rising, it has to use an indirect way, that is, calculate the number of days we want through accumulating the number of days that the stock does not rise. Specifically, first, mark each record with a rising or falling flag (risingflag)in chronological order. If the stock price falls, mark 1, otherwise, mark 0; Then, accumulate the number of records that fall (norisingdays) in chronological order. This number increases only when the current record falls, and remains unchanged when the current record rises; Next, group according to norisingdaysand calculate the number of records of each group. Obviously, a batch of records that fall consecutively have different norisingdays, and each record will be divided into different group that is marked as 1, and thus this numberis not what we want. Conversely, a batch of records that rise consecutively have the same noristingdays, and they will be divided into the same group, and the record number of this group is the number of consecutive rising days, which is what we want; Finally, use the maxfunction to calculate the maximum number of consecutive rising days.

The programming process of JOOQ is to write SQL code first, and then translate into JOOQ code. For simple calculations, it is easy to write SQL code, and it is not difficult to translate. But for comprehensive calculation like this problem, a high skill on the calculation logic is required, and hence it is not easy to write SQL code, it’s more difficult to translate. In addition, JOOQ is superficially Java that is easy to debug, but it is SQL in essence, and it is as difficult to debug as SQL, which leaves a big trouble for future maintenance.

SPL code is much simpler:

APPL.sort(day).group@i(price<price[-1]).max(~.count())

The calculation logic of this SPL statement is the same as that of JOOQ, it also puts the consecutively rising records into the same group and then calculates the maximum number of members, but the difference is that it is much more convenient to express. In this code, group@i()means to traverse the table sequence. If the condition is satisfied, a new group will be created (and the previous records will be put into a same group); The condition price<price[-1]means that when the stock price falls, the previous records that stock price rises will be put into a same group; [-1]represents the previous record, which is the representation method of relative position; price[-1]represents the stock price of the previous trading day, which is more intuitive than moving the whole row (lag.over).

Relative position belongs to ordered computing. SPL is a professional structured computing language, and supports ordered computing, so the code is simpler. In addition to ordered set, SPL can also simplify a variety of complex calculations, including multi-step calculation, set calculation, and association calculation. However, these calculations are not what JOOQ is good at, and usually require special techniques to implement, and it is difficult to code.

SPL function options and cascaded parameter

It is worth mentioning that in order to further improve the development efficiency, SPL also provides unique function syntax. When there are a large number of functions with similar functionality, JOOQ can only use different names or parameters to distinguish, which is not convenient. SPL provides very unique function options, which allow the functions to share one function name, and their difference can be distinguished just by options. For example, the basic function of select function is to filter, if you only want to filter out the first record that meets the condition, you can use option @1:

T.select@1(Amount>1000)

When using the binary search to quickly filter the sorted data, you can use the option @b:

T.select@b(Amount>1000)

The options of function can also be used in a combined way, for example:

Orders.select@1b(Amount>1000)

The parameters of some functions are very complex and may be divided into multiple layers. For this situation, JOOQ does not have a special syntax solution, and can only split into multiple functions nested with each other to try to simulate SQL syntax, resulting in a long and cumbersome code. SPL creatively invents the cascaded parameter to simplify the expression of complex parameter, it can divide the parameters into three layers from high to low by semicolon, comma and colon. For example, associate two tables:

join(Orders:o,SellerId ; Employees:e,EId)

Flow processing

JOOQ supports part of stored procedure syntaxes, including loop statement and judgment statement. However, these functions are available only in JOOQ commercial edition. In addition, using these functions requires high privilege, and there exists high potential security risk, and it is difficult to migrate, therefore, they are rarely used. In addition to stored procedures, JOOQ can also use Java to implement flow processing, which requires low privilege on database, leading to low potential security risk, and it supports seamless migration. For example, calculate the bonus according to rules:

Orders.forEach(r->{
	 Double amount=r.getValue(ORDERS.AMOUNT);
	 if (amount>10000) {
		 r.setValue(ORDERS.BONUS), amount * 0.05);
	 }else if(amount>=5000 && amount<10000){
		 r.setValue(ORDERS.BONUS),amount*0.03);
	 }else if(amount>=2000 && amount<5000){
		 r.setValue(ORDERS.BONUS),amount*0.02);
	 }
});

The forEach loop function is optimized for JOOQ’s structured data objects, and can simplify the definition of loop structure through Lambda expression, and conveniently process each member of set objects (the loop variable r in the code). The use of forEach function combined with Lambda syntax makes the overall code simpler than traditional loop statements. However, it should be noted that when the field is used in forEach, it needs to attach the name of loop variable, which is unnecessary for single-table calculation. For SQL that also uses Lambda syntax, the name of loop variable can be omitted. In addition, defining the name of loop variable is also unnecessary because it is not required in SQL. All these shortcomings indicate that JOOQ is not professional in flow processing, and there is still much room to optimize the code.
Likewise, SPL also has the loop function optimized for structured data objects, and directly uses parentheses to represent. For the same calculation task, SPL code:

Orders.(Bonus=if(Amount>10000,Amount*0.05,
	 if(Amount>5000 && Amount<10000, Amount*0.03,
	 if(Amount>=2000 && Amount<5000, Amount*0.02)
)))

SPL’s loop function also supports Lambda expression. The interface is simpler, and there is no need to define the loop variables, and no need to reference variable’s name when using the field. Thus, SPL is more convenient and professional than JOOQ. In addition to loop function, SPL provides more professional flow processing functions, such as: taking a batch of records rather than one record in each round of loop; looping one round when the value of a certain field changes.

Based on professional flow processing function, and combined with professional structured data objects and structured data computing ability, SPL can greatly improve the development efficiency of data business logic. Here below is a complete example: calculate the bonus, and insert new records into the database. To accomplish this computing task, it needs to generate multiple files and writes a large amount of code in JOOQ, while it is much simpler in SPL:

	A	B	C
1	=db=connect@e("dbName")		/Connect to database and begin 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 of the order table
7	=A6.record([p_OrderID,p_Client,p_SellerID,p_Amount,BONUS, date(now())])		/Generate an order record
8	>db.update@ik(A7,sales;ORDERID)		/Try to update the order record to database table
9	=db.error()		/Result of writing to database
10	if A9==0	>A1.commit()	/Commit the transaction if the writing action succeeds
11	else	>A1.rollback()	/Rollback the transaction if the writing action fails
12	>db.close()		/Close database connection
13	return A9		/Return the result of writing to database

Using SPL's flow process statements can achieve all functions of stored procedures, including the loop and judgment of cursors. Since SPL does not rely on database, and the privilege to write database is not required, and there is no potential security risk, it is equivalent to a stored procedure outside the database. At the same time, these functions are open source.

Application framework

Java integration

JOOQ itself is Java, and can be called directly by other Java code.

SPL is a JVM-based data computing language, and provides an easy-to-use JDBC driver, and can be seamlessly integrated in JAVA. For example, store the business logic code as script file, and call the file name in JAVA as the form of stored procedure:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("call genBonus()");

Hot deployment

JOOQ (Java) is a compiled language and does not support hot deployment. After modifying the code, it needs to recompile,and restart the whole application, which increases the maintenance difficulty and reduces the system stability.

SPL is an interpreted language, and its code is placed outside the JAVA as the form of script file. SPL supports hot deployment. After the code is modified, there is no need to compile, and no need to restart the application. Since SPL code does not depend on JAVA, and the business logic and front-end code are physically separated, the coupling is lower.

Code migration

Part of JOOQ codes can be migrated, which is more convenient than MyBatis that cannot be migrated. For example, the limit(M).offset(N)that is often used for paging in business logic will be translated into rownumsubquery in the Oracle11g database; If the database is MSSQL2012, the same code will be translated into offset nextstatement as long as the entity class is regenerated and deployed without the need to modify the business logic.

However, the codes that can be migrated are in a minority after all. Most of JOOQ codes cannot be migrated, such as the window function in the previous example. Moreover, when migrating, you need to read and understand the original JOOQ code first, and then translate it back to original SQL, after that, modify the translated SQL to new SQL, and finally, translate new SQL into new JOOQ code. You can see that this process is quite complicated and difficult. Furthermore, the business logic is generally complex, it makes the migration more difficult.

SPL has independent computing power. Depending on its rich built-in function library, SPL can implement complex structured data calculation without SQL, and SPL calculation code can be seamlessly migrated between databases. In the code for fetching the data from database, SPL also needs to execute dialect SQL to generate table sequence. Although it is relatively simple for SQL to fetch the data, and it not difficult to manually migrate, it still needs a certain workload. In order to make the data-fetching code easy to be migrated, SPL specially provides general SQL that does not depend on specific database, which allows you to seamlessly migrate the code between mainstream databases.

By comparing JOOQ and SPL in many aspects, we know that JOOQ can perform relatively simple querying and counting calculations, but for developing complex business logic, it is quite cumbersome. Especially when using JOOQto handle ordered computing, multi-step computing, set computing, or complex association query computing, there will be a serious of problems such as heavy workload in translating SQL, long code, difficult to modify, and difficult to migrate. In contrast, SPL has the advantages of concise syntax, high expressive efficiency, convenient code migration, more professional structured data objects, richer functions, more powerful computing power and more convenient flow processing, therefore, the development efficiency is much higher than JOOQ.