文件（不在内存中）基于JDBC驱动程序的CSV文件 [英] file (not in memory) based JDBC driver for CSV files
问题描述
是否有一个基于开放源文件（基于内存的）JDBC驱动程序的CSV文件？我的CSV是根据用户选择从UI动态生成的，每个用户将有一个不同的CSV文件。我这样做是为了减少数据库命中，因为信息包含在CSV文件中。我只需要执行 SELECT 操作。
Is there a open source file based (NOT in-memory based) JDBC driver for CSV files? My CSV are dynamically generated from the UI according to the user selections and each user will have a different CSV file. I'm doing this to reduce database hits, since the information is contained in the CSV file. I only need to perform SELECT operations.
如果我们指定索引，HSQLDB允许索引搜索，不能提供可用作索引的唯一列，因此它在内存中执行SQL操作。
HSQLDB allows for indexed searches if we specify an index, but I won't be able to provide an unique column that can be used as an index, hence it does SQL operations in memory.
修改：
我尝试过CSVJDBC，但不支持 c $ c>和 group by 。仍然不清楚它是从文件读取还是加载到内存中。
I've tried CSVJDBC but that doesn't support simple operations like order by and group by. It is still unclear whether it reads from file or loads into memory.
我试过xlSQL，但是这又依赖于HSQLDB，只能使用Excel而不是CSV。此外，它不在开发或支持。
I've tried xlSQL, but that again relies on HSQLDB and only works with Excel and not CSV. Plus its not in development or support anymore.
H2，但只读取CSV。不支持SQL。
H2, but that only reads CSV. Doesn't support SQL.
推荐答案
您可以使用 H2数据库。
以下groovy脚本演示：
The following groovy script demonstrates:
- 将数据加载到数据库
- 运行GROUP BY和ORDER BYsql查询
注意： H2支持内存数据库，因此您可以选择是否保留数据。
Note: H2 supports in-memory databases, so you have the choice of persisting the data or not.
// Create the database
def sql = Sql.newInstance("jdbc:h2:db/csv", "user", "pass", "org.h2.Driver")
// Load CSV file
sql.execute("CREATE TABLE data (id INT PRIMARY KEY, message VARCHAR(255), score INT) AS SELECT * FROM CSVREAD('data.csv')")
// Print results
def result = sql.firstRow("SELECT message, score, count(*) FROM data GROUP BY message, score ORDER BY score")
assert result[0] == "hello world"
assert result[1] == 0
assert result[2] == 5
// Cleanup
sql.close()
CSV数据示例：
0,hello world,0
1,hello world,1
2,hello world,0
3,hello world,1
4,hello world,0
5,hello world,1
6,hello world,0
7,hello world,1
8,hello world,0
9,hello world,1
10,hello world,0
这篇关于文件（不在内存中）基于JDBC驱动程序的CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
