An alternative of SQLite for Java Programmer

Many small and micro applications also need some data processing & computing capability, but the framework becomes too heavy if we integrate a database in them. In view of this, the small and lightweight SQLite has become a good choice and been widely used.

SQLite also has its inconveniences. It has weak and complicated support for external data files and other data sources; it does not provide stored procedures and needs the main program’s participation in implementing the procedure, leading to frequent data exchanges between it and the main program (because the procedure flow depends on data) and resulting in inefficient and tedious code; it is also hard to code the complicated computations in SQL and the development efficiency is low.

If you happen to be programming in Java or Android, esProc SPL might be a better choice..All those problems are gone.

esProc is a free, open-source software, which is offered in https://github.com/SPLWare/esProc.

esProc is developed purely in Java. Users just need to import its jars in the Java application for use. The integration is completely seamless.

esProc also supplies standard JDBC driver, through which it can be invoked by the Java main program, like the way to access database. However, the query language esProc uses is SPL, instead of SQL.

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*\")

esProc also supports SQL and does not need to define metadata, simply treating the data file as a table.

ResultSet result = statement.executeQuery("$SELECT * FROM Orders.csv WHERE Amount>1000 AND Client LIKE '%s%'")

However, for a more complex computational goals, SPL is much simpler than SQL,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 )

It is much simpler and easier to write the same logic in SPL:

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

It’s okay to implement in a more natural way (SQL can not implement this process, and can only use the above roundabout multi-layer nested method):

Unlike most of the programming languages that write code as text, SPL code is written in a grid. Find more information HERE.

SPL itself has a complete set of flow control statements, such as for loop and if branch statement, and supports invocation of the subprogram. This amounts to the stored procedure capability. SPL alone can implement complex business logics, almost without cooperation of the main program. The main program just needs to invoke the SPL code as it calls the stored procedure:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call queryOrders()}");
statement.execute();

Difference is that SPL scripts are interpreted execution. Any modifications take effect instantly, while changes to the stored procedure require compilation. Particularly, SPL scripts can be placed separately from the main program and modifications of them do not need recompilation and redeployment of the latter, which enables the real-time hot swapping. The benefit does not exist for logics implemented by the cooperation of the main program and SQL in the database.

SPL supports a large variety of data sources – whether they are familiar or unfamiliar – including text files of various formats, Excel files, relational databases, NoSQL databases, HTTP, Kafka, … and JSON/XML data. esProc provides access interface for each of them. Users just need one or two lines of code to read/write over them.

T("Orders.csv").select(Amount>2000 && Amount<=3000)
Orders=json(httpfile("http://127.0.0.1:6868/api/orders").read())
db=connect("mysql")
db.query("select * from salesR where SellerID=?",10)
...

To access the external data, just read it directly without the need to create a table. It is very convenient. In addition, these files and data sources are writable in SPL. They can be used for data persistence so that the written data can be accessed by other applications.

file("Orders.csv").export@t(A2)
file("Orders.xlsx").xlsexport@t(A2)
db.update(NewTable:OldTable)

SPL also offers its own binary files, which enable higher read/write performance.

Compared to the JVM, esProc is very lightweight, the core package is only 15M, which is almost negligible, and it also runs smoothly on Android.