Category Archives: JDBC

Making sample JDBC code easier

Posted on by
Reply

I’m writing a lot of small JDBC examples for our upcoming practice tests book. I got tired of writing the same code over and over so I wrote this helper class. It can:

  • Drop the table if it already there (because I run the same examples many times)
  • Run a varargs of SQL statements (to create tables and insert data)
  • Spit out the contents of a table (to confirm what happened)


import java.sql.*;
public class DerbyUtil {

    public static void dropTablesIfExist(String url, String... tables) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String table : tables) {
                try (PreparedStatement ps = conn.prepareStatement("DROP TABLE " + table)) {
                    ps.executeUpdate();
                } catch (SQLException e) {
                    // ignore exceptions on drop since table might not exist
                }
            }
        }
    }

    public static void run(String url, String... sqlStatements) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String stmt : sqlStatements) {
                try (PreparedStatement ps = conn.prepareStatement(stmt)) {
                    ps.executeUpdate();
                }
            }
        }
    }

    public static void outputTable(String url, String tableName) throws SQLException {
        String sql = "SELECT * FROM " + tableName;
        try (var conn = DriverManager.getConnection(url);
             var ps = conn.prepareStatement(sql);
             var rs = ps.executeQuery()) {

            var metadata = rs.getMetaData();
            int numColumns = metadata.getColumnCount();

            while (rs.next()) {
                outputRow(rs, numColumns);
            }
        }
    }

    private static void outputRow(ResultSet rs, int numColumns) throws SQLException {
        for (int i = 1; i <= numColumns; i++) {
            System.out.print(rs.getString(i));
            System.out.print('\t');
        }
        System.out.println();
    }
}

try-with-resources and jdbc without sql injection

Posted on by
2

As I was on Oracle’s JDBC tutorial page, I noticed it was using a Statement rather than a PreparedStatement. I grumbled to myself about how this is teaching people to develop using SQL Injection and decided to Google for an example so I could tweet about it.

I was looking for an example of using try-with-resources automatic resource management using PreparedStatements. I searched on google for “try with resources jdbc”. This didn’t go well. I found a lot more of the same example including one from Martjin and Ben whom I respect. It is even that anyone’s example is bad. It is just oversimplified and implies that using createStatement is more common than using prepareStatement.

I then searched for “try with resources preparedstatement” to be more specific about it and found:

  • Informit does use a PreparedStatement but one without any binding variables.  Which means as an example, it isn’t much better.
  • JavaCodeGeeks does the same.
  • Someone on StackOverflow asked how best to do it and got an answer involving a nested try.  Which does work, but the nested try seem less readable than it needs to be.

I propose:


public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = createPreparedStatement(conn);  ResultSet rs = stmt.executeQuery()) {

while (rs.next()) {

list.add(rs.getString("name"));

}

}

return list;

}

private PreparedStatement createPreparedStatement(Connection conn) throws SQLException {

PreparedStatement ps = conn.prepareStatement(SQL);

ps.setString(1,  "test");

return ps;

}

The StackOverflow post proposes:

public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = conn.prepareStatement(SQL)) {

stmt.setString(1, "test");

try (ResultSet rs = stmt.executeQuery()) {

while (rs.next()) {

list.add(rs.getString("name"));

}

}

}

return list;

}

The StackOverflow answer is shorter.  I think the reason I like mine better is that is is easier to template out so the JDBC plumbing is only in a superclass.  Leaving us with

public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = createPreparedStatement(conn); ResultSet rs = stmt.executeQuery()) {

return processResultSet(rs);

}

return list;

}

The subclass then has two methods to implement:

  1. PreparedStatement createPreparedStatement(Connection conn)
  2. T processResultSet(ResultSet rs)  [templated to return type of subclass’ choosing]

Which approach do you like better?