Category Archives: JDBC

text blocks with JDBC

Posted on by
Reply

I got a twitter request to show using text blocks with JDBC. Here you go:

1
2
3
4
5
run(conn, """
               CREATE TABLE names (
                  id INTEGER PRIMARY KEY,
                  species_id integer REFERENCES exhibits (id),
                  name VARCHAR(255))""");

The key is what isn’t here. No sprint concatentation. No escaping. Just clear text

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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

1
2
3
4
5
6
7
8
9
10
11
12
13
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?