Java 可以在 txt/csv 上执行 SQL 吗?

 

有一些文件的JDBC驱动可以直接在txt/csv上执行SQL,比如simoc csvjdbc/xiao321 csvjdb,但计算能力太弱,使用价值不大。JAVA内嵌数据库有一定计算能力,比如HSQLDB/Derby/H2,以及虽然不是纯JAVA但有JDBC接口的SQLite,但内嵌库不能直接在文件上执行SQL,必须入库才行,而入库的过程很繁琐,要检查表名重复、删表、建表、解析文件、插入数据、建索引等等,还经常遇到格式不够规整无法入库的情况,入库最令人无法容忍的是占用大量时间,整体性能差出天际。

计算能力强,又可以直接在文件上执行SQLJAVA开源库,集算器SPL是个更好的选择。

SPL用法简单易懂,比如Orders.txttab分隔的订单表,对该表进行条件查询:


Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="$select * from d:/sOrder.txt where Client like'%S%'or (Amount>1000 and Amount<=2000)";
ResultSet result = statement.executeQuery(str);

SPL 支持常见的SQL语法,可实现各类日常计算,下面再举一些例子:

Sort
$select * from sales.txt order by Client,Amont desc

Distinct
$ select distinct(sellerid) from sales.csv       

group by…having
$select year(orderdate) y,sum(amount) s from sales.csv group by year(orderdate) having sum(amount)>=2000000

join
$select e.name, s.orderdate, s.amount from sales.txt  s left join employee.xlsx e on s.sellerid= e.eid

SPL支持高级SQL语法,可实现难度较大的计算,比如case  whenwith、嵌套子查询等,详见《在文件上使用 SQL 查询的示例》

 

格式不规范的文件,一般的SQL工具很难解析,而SPL提供了扩展函数,可轻松解析此类文件。比如分隔符为"||"的文件,SPL 只需简单写作:
$select * from {file("sep.txt").import@t(;,"||")}

非默认格式的数据类型,一般的SQL工具同样很难解析,SPL同样可用扩展函数轻松解析。比如日期类型不是常见的"2012-01-01",而是形如"01-01-2012"SPL只需简单写作:
$select * from
{file("style.txt").import@t(orderid,client,sellerid,amount,orderdate📅"dd-MM-yyyy")}

利用扩展函数,SPL还可解析格式更复杂的文件。

有时计算逻辑较为复杂,即使存储过程也难以实现,而SPL的扩展函数有足够的计算能力,可轻松解决此类问题。比如:商品在出库\入库时,日期上通常不连续,要据此计算出多种商品在指定的连续日期内的库存情况。首先,编写代码并存为SPL脚本文件。脚本文件的作用相当于存储过程,可降低JAVA代码的耦合性,而且修改时无须编译:


A

1

=T("d:/inout.txt").group(product;~.align(A2,date):g)

2

=periods(argBeginDate,argEndDate)

3

=A2.news(g;A1.product,A2(#):date,ifn(in,0):in,   ifn(out,0):out, stock[-1]+in-out:stock)

4

$select * from {A3}

再在JAVA代码中以存储过程的方式调用脚本文件:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call getStock(?, ?)}");
statement.setObject(1, "2020-01-01");
statement.setObject(2, "2020-01-31");
statement.execute();
...

对于计算逻辑复杂的脚本文件,SPL提供了专业的IDE,不仅有完整的调试功能,还能用表格的形式观察每一步的中间计算结果:

IDEpng

SPL还支持ExcelXMLJson等文件数据源,支持大文件高性能计算,支持文件数据源和数据库、NoSQLrestful的跨源计算,这里不再详述。