Java: Writing SQL Statements

Question

I'm writing a one-time Java program to add a bunch of rows in a CSV file to a MySQL database. Are there any Java classes/toolkits to help with this? Something that will escape necessary characters, etc? (eg prepared statements)

Or should I just write the statements myself, like this:

result += String.format(
   "INSERT INTO node (type, language, title) VALUES (%s, %s, %s)", 
    node.get("type"), node.get("language"), node.get("title")
);

Answer 1

You might want to check out DbUnit. It has a bunch of tools for manipulating databases from XML and flat files.

Answer 2

If you're using JDBC, use a PreparedStatement. This class will save you the trouble of escaping your inputs manually.

The code will look basically like this (totally from memory -- hope I didn't overlook something):

String sql = "INSERT INTO node (type, language, title) VALUES (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
try
{
    pstmt.setString(1, node.get("type"));
    pstmt.setString(2, node.get("language"));
    pstmt.setString(3, node.get("title"));
    pstmt.executeUpdate();
}
finally
{
    pstmt.close(); 
}

Answer 3

See this section in the tutorial on Using Prepared Statements:

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.

...

Looking at these examples, you might wonder why you would choose to use a PreparedStatement object with parameters instead of just a simple statement, since the simple statement involves fewer steps. If you were going to update the SALES column only once or twice, then there would be no need to use an SQL statement with input parameters. If you will be updating often, on the other hand, it might be much easier to use a PreparedStatement object, especially in situations where you can use a for loop or while loop to set a parameter to a succession of values.

Oddly this tutorial doesn't seem to mention that using a PreparedStatement also gives you the benefit of having special characters automatically escaped, that it helps prevent SQL injection, etc. - but those are the main benefits.

Answer 4

This might be considered a bit of a sledgehammer approach, but you might want to considering using spring for your SQL calls, then the above becomes as simple as:

getSimpleJdbcTemplate().update(
"INSERT INTO node (type, language, title) VALUES (?, ?, ?)", 
node.get("type"), 
node.get("language"), 
node.get("title"));

This has the advantage of using JDBC prepared statments under the hood, so you won't end up in trouble if the title includes quotes or other characters that would otherwise have to be escaped, while letting Spring handle all the connection, prepared statement and transaction (if needed at all) complexity.

For more see: SimpleJdbcTemplate from the Spring Framework