Why esProc SPL Outperforms SQLite for Small Java Applications

For tiny Java applications that need lightweight data processing, SQLite is often chosen because of its simple architecture, easy integration, and persistent storage with SQL support. However, the article points out several limitations of SQLite in more complex scenarios.

Data‑source support

SQLite can only read CSV files and requires a multi‑step command‑line process (create database, create table, import data) before queries can be run. It cannot directly read JSON, XML, or RESTful web services without hard‑coded adapters, and it cannot write to external data sources such as CSV files.

Complex calculations

SQL inherits both the strengths and weaknesses of natural‑language‑like queries. While easy for simple tasks, it becomes verbose for advanced analytics. The article shows a window‑function query to fetch the top‑3 orders per client, which requires a nested SELECT and row_number generation, making the code hard to read. Another example calculates a stock’s maximum consecutive rise days using multiple nested SELECTs, CASE statements, and lag‑like logic, illustrating how SQL lacks direct constructs for such patterns and is difficult to debug.

Workflow handling

SQLite does not support stored procedures, so any business logic must be implemented in the host language (e.g., Java). This forces developers to convert SQL result sets into Java objects, process them with loops and conditionals, and then map the results back to the database, leading to cumbersome and error‑prone code.

esProc SPL as an alternative

esProc SPL is an open‑source data‑processing engine for Java that shares SQLite’s lightweight deployment (just a JAR) but adds a JDBC interface, persistent storage, and a richer feature set.

Simple architecture : No service, node, or cluster configuration required.

JDBC integration :

Class.forName("com.esproc.jdbc.InternalDriver");

Connection conn = DriverManager.getConnection("jdbc:esproc:local://");

Data‑source flexibility : Directly read CSV, TSV, Excel, JSON, XML, RESTful APIs, Elasticsearch, MongoDB, etc., without extra coding.

Rich SQL‑like syntax : Supports grouping, window functions, top‑N, joins, and aggregation directly in SPL code.

Examples include creating a table, inserting records, and exporting to a custom .btx file:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn = DriverManager.getConnection("jdbc:esproc:local://");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("=T(\"D:/Orders.csv\").select(Amount>1000 && like(Client, \"*s*\")");

Data can be persisted to various formats:

file("d:/Orders.csv").export@tc(A2)   // export to CSV
file("d:/Orders.xlsx").xlsexport@t(A2) // export to Excel
file("d:/Orders.json").write(json(A2)) // export to JSON

SPL also supports full‑featured calculations. The same top‑3 orders per client query becomes a single, readable expression: Orders.group(Client).(~.top(3;Amount)) Calculating the maximum consecutive rise days is expressed with ordered sorting and a simple max‑with‑counter construct, avoiding the nested SELECTs required by SQLite.

tbl.sort(day)
=t=0, A1.max(t=if(price>price[-1], t+1, 0))

Complex aggregations, such as selecting the smallest set of customers whose cumulative sales exceed half of total sales, are also concise in SPL using cumulative functions and filters.

sales.sort(amount:-1)
A2.cumulate(amount)
A3.m(-1)/2
A2(to(A5))

Workflow control

SPL provides built‑in flow‑control statements (if/else, for loops) that operate directly on SPL tables, enabling complete business logic without external stored procedures. Example branch logic for bonus calculation:

if T.AMOUNT>10000 =T.BONUS=T.AMOUNT*0.05
else if T.AMOUNT>=5000 && T.AMOUNT<10000 =T.BONUS=T.AMOUNT*0.03
else if T.AMOUNT>=2000 && T.AMOUNT<5000 =T.BONUS=T.AMOUNT*0.02

Loop constructs allow batch processing of query results, updating fields, and performing conditional transformations, all within SPL scripts that can be stored and invoked like stored procedures.

Pros and cons

Compared with SQLite, SPL offers:

Broader data‑source support (JSON, XML, REST, other databases).

More expressive calculation language with ordered collections and built‑in functions.

Native flow control and the ability to externalize business logic.

Simple deployment as a JAR.

Drawbacks include the need for a Java runtime (making it less convenient for non‑Java environments) and limited support on platforms without a JVM (e.g., iOS).

Conclusion

For small Java‑based applications that outgrow SQLite’s basic capabilities, esProc SPL provides a lightweight yet powerful alternative that simplifies data‑source integration, complex analytics, and workflow implementation, while retaining the ease of embedding a single library.