I got a twitter request to show using text blocks with JDBC. Here you go:
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
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:
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();
}
}
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:
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:
Which approach do you like better?