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();
}
}