Mike`s Blog

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)

Once in a while, you want to have your data on the client. Then, let the client fiddle with the data and write it back into the database.

You can do that using a Service layer, sure. That's the good way to do it. However, what if you don't want to have the overhead. Probably because you have a huge amount of tables. And you just want to have a very quick way to load and save data into them. So you can start creating screens and forms immediately.



Have a look at the RowSet stuff which is already in Java since ages. There is a CachedRowSet which can be serialized to the client. On the client side, the CachedRowSet can be used to execute SQL statments and update the data. Then, serialize it back to the server and apply the changes. Simple, isn't it?

Let's have a look on how to do that:

// On the Server: Populate some data
final CachedRowSet set1 = new CachedRowSetImpl();
set1.setCommand("SELECT id,name FROM currency");
set1.execute(newConnection());
byte[] serialized1 = serialize(set1);

// On the Client: Modify the data, add a new currency!
final CachedRowSet set2 = deserialize(serialized1);
set2.moveToInsertRow();
set2.updateInt("id", 3);
set2.updateString("name", "Dollar");
set2.insertRow();
byte[] serialized2 = serialize(set2);

// On the Server: store the new currency
assertEquals(2, countAmountOfCurrenciesOnServer());
final CachedRowSet set3 = deserialize(serialized2);
set3.moveToCurrentRow();
set3.acceptChanges(newConnection());
assertEquals(3, countAmountOfCurrenciesOnServer());

Note the set3.moveToCurrentRow(), it's mandatory. If you omit it, you will receive NullPointerException in the Sun implementation of CachedRowSet.

• Comments (0)
• Trackbacks (0)

Don't use comments in SQL statements when mapping with iBatis SqlMaps.

The whole statement (even if you've got multiple sql statments within one element) gets merged into a single line. The first comment will also comment-out all further statements.

For example

<sqlmap>
 <!-- For Unit Testing -->
 <statement id="createTable">
 CREATE TABLE (...);
 -- Sample data
 INSERT INTO VALUES (...);
 </statement>
</sqlmap>
 

will result into the following SQL statement on the JDBC level:

CREATE TABLE (...); -- Sample data INSERT INTO VALUES (...);
 

and no data will be inserted into your table.

• Comments (0)
• Trackbacks (0)

Top Three Reasons For NoClassDefFoundError

3) The class is twice in the classpath (happens often with J2EE container)

2) Your class requires another class (in a signature or as a static field) which is not in the class path

1) You class is really not in the class path (check manifest.mf)

• Comments (0)
• Trackbacks (0)

A slightly annoying discussion was going on tonight on a Java IRC channel. It was all about how to remove null elements from an ArrayList.

There have been a lot of questions by various people. Besided suggestions, technical questions, there have also been the
usual suspects like "Why on earth are you adding null elements anyway?".

But let's have a look at the result, which seems to be the easiest way:

list.removeAll(Collections.singleton(null));

If you don't know the Collections class, you might have been come up with something like this:

list.removeAll(Arrays.asList(new Object[]{null}));

Is there any other way to remove null references from collections? For example by using HashSets and only use remove(null). Or is it better to implement your own implementation of a Collection which does it itself?
What are the performance benefits of each variation? Could we use the Java High Performance Collections implementation Trove?

• Comments (0)
• Trackbacks (0)