Java CSV/XML JDBC Driver

[bacterozoid]

I'm hoping to create a simple application using a flat file database...instead of using CSV files and manually trying to edit them, I'd like to be able to use SQL-type queries to manage this data for me...because it's a pain to try and modify the text files on my own.

I did some searching and found some results, but I wasn't too sure about any of it.

For example, I might have several different XML files (or CSV, but I would prefer to use XML if possible) with a bunch of data in it...I want to query it directly from my program and use a driver to handle modification of the data.

I found this: http://www.devarticles.com/c/a/Java/Using-CSV-Files-as-Databases-and-Interacting-with-Them-Using-Java/

...but that says it uses an ODBC data source. My impression is that in order to use this, you have to configure the data source...which I don't want to do, nor do I want anyone else using the app to have to do. I'd like a driver that will just work with a flat file database.

Any ideas?

Thanks!

[servlet]

Look at
http://www.h2database.com/html/tutorial.html#csv
http://hsqldb.org/doc/guide/ch06.html

H2database and hsqldb has support for CSV file based DB.

[bacterozoid]

H2Database looks promising - I was able to get it installed and I ran a test to create and read a CSV file. I did post to the google group for it, but do you know if it's possible to edit a CSV file with H2Database? I can create and read one, but I can't manage to insert and delete rows from it.

Thanks!

[servlet]

Yes, its possible. But I suggest you go with hsql db. it is widely known and under active development.

[bacterozoid]

I was having some trouble with it when I tried to use it - but since you're suggesting it again I'll give it another go.

[servlet]

Yes, give it a try, I strongly recommend HSQLDB over h2database

[bacterozoid]

(Same person, finally got some account stuff straightened out)

Thanks for the help, servlet. It took a while, but I was able to get hlsql working. I'm pretty excited about this. Also, I did post on the Google group for the h2database, and they told me that reading is the only CSV function that is supported, not writing. It wouldn't have worked anyways.

[collekeith]

Hi Bacterozoid,

I am a newbie to java and want to use hsqldb driver to read data from csv by executing sql queries. I have been trying to use it but failed.Can you please share javacode on how you got it working?

All I want to do is to read the data in csv file by executing sql queries.


Thanks for your help.
Keith

[servlet]

'collekeith' Instead of bumping this thread, you should create a new thread. of if you want to contact 'bacterozoid' you should send him PM instead.

[bacterozoid]

Since this has already been bumped, let me post some sample code for the good of all since it is hard to come by in online searches:


jdbcDataSource dataSource = new jdbcDataSource();
dataSource.setDatabase("jdbc:hsqldb:file:data/testdb");

// Connect to the database
// It will be create automatically if it does not yet exist
// "sa" is the user name and "" is the (empty) password
Connection conn = dataSource.getConnection("SA", "");

final StringBuilder createTable = new StringBuilder();
createTable.append("CREATE TEXT TABLE tblTest (");
createTable.append("name VARCHAR, value VARCHAR PRIMARY KEY, notes VARCHAR, quantity INT)");

final StringBuilder linkTable = new StringBuilder();
linkTable.append("SET TABLE tblTest SOURCE ");
linkTable.append("\"test.csv");
linkTable.append(";ignore_first=false;all_quoted=false\"");

conn.setAutoCommit(true);
// Only execute the following two lines one time. After that, the tables have been created and you don't have to do it again.
// conn.createStatement().execute(createTable.toString());
// conn.createStatement().execute(linkTable.toString());

// Query
WebRowSet rowSet = new WebRowSetImpl();
rowSet.setCommand("SELECT name,value,notes,quantity FROM tblTest");
rowSet.execute(conn);

// Insert new row

rowSet.moveToInsertRow();
rowSet.updateString("name", "The name");
rowSet.updateString("value", "The value");
rowSet.updateString("notes", "The notes");
rowSet.updateInt("quantity", 6);
rowSet.insertRow();
rowSet.moveToCurrentRow();

// Write the changes
rowSet.acceptChanges(conn);

// An example query
rowSet = new WebRowSetImpl();
// rowSet.setCommand("SELECT * FROM test WHERE quantity=101");

// An example update
rowSet.setCommand("UPDATE tblTest SET quantity=10234 WHERE quantity=5");
rowSet.execute(conn);
while (rowSet.next()) {
System.out.println("FOUND A RESULT");
System.out.println(rowSet.getString("value"));
}

// Removes blank lines from CSV file
conn.createStatement().execute("SHUTDOWN COMPACT");
conn.close();

It's kind of messy, but it's just example code. collekeith: PM me if you need additional help using the code or setting up your project to use hlsql. Good luck!