Use HSQLDB to manipulate CSV data

Did I tell you that I love hsqldb? It's fantastic. Today, i'll show you how to manipulate (write!) CSV data in a nicely abstracted way, so you don't have to care about escape characters, delimiters and stuff. Suppose you've got a table with currencies:

"ID","NAME"
1,"Euro"
2,"Schweizer Franken"
3,"US Dollar"

Now let's run a simple test case which adds a new row into the CSV file:

// Database preparation
final String url = "jdbc:hsqldb:file:/db_texttable";

final StringBuilder createTable = new StringBuilder();
createTable.append("CREATE TEXT TABLE currency (");
createTable.append("id INT PRIMARY KEY, name VARCHAR)");

final StringBuilder linkTable = new StringBuilder();
linkTable.append("SET TABLE currency SOURCE ");
linkTable.append("\"/temp/currencies.csv");
linkTable.append(";ignore_first=true;all_quoted=true\"");
Connection conn = DriverManager.getConnection(url,"sa","");
conn.setAutoCommit(true);
conn.createStatement().execute(createTable.toString());
conn.createStatement().execute(linkTable.toString());

// Query
WebRowSet rowSet = new WebRowSetImpl();
rowSet.setCommand("SELECT id,name FROM currency");
rowSet.execute(conn);

// Insert new currency
rowSet.moveToInsertRow();
rowSet.updateInt("id", 4);
rowSet.updateString("name","British Pounds");
rowSet.insertRow();
rowSet.moveToCurrentRow();

// Write and dump to console
rowSet.acceptChanges(conn);
conn.close();

The result will be written to the CSV text file:

"ID","NAME"
1,"Euro"
2,"Schweizer Franken"
3,"US Dollar"
"4","British Pounds"

The parameter ignore_first in the SET TABLE statement defines that the CSV file has a header line, which will then be ignored. all_quoted will escape all data into double quotes, as you can see in the added row. The manually populated data can be read nevertheless, although it's missing the quotes.

• Comments (0)
• Trackbacks (0)