I have a method for getting users from a database with JDBC:
public List<User> getUser(int userId) {
String sql = \"SELECT id, name FROM users WHERE id = ?\";
List<User> users = new ArrayList<User>();
try {
Connection con = DriverManager.getConnection(myConnectionURL);
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, userId);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
users.add(new User(rs.getInt(\"id\"), rs.getString(\"name\")));
}
rs.close();
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
How should I use Java 7 try-with-resources to improve this code?
I have tried with the code below, but it uses many try
, and doesn\'t improve the readability much. Should I use try-with-resources
in another way?
public List<User> getUser(int userId) {
String sql = \"SELECT id, name FROM users WHERE id = ?\";
List<User> users = new ArrayList<>();
try {
try (Connection con = DriverManager.getConnection(myConnectionURL);
PreparedStatement ps = con.prepareStatement(sql);) {
ps.setInt(1, userId);
try (ResultSet rs = ps.executeQuery();) {
while(rs.next()) {
users.add(new User(rs.getInt(\"id\"), rs.getString(\"name\")));
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
I realize this was long ago answered but want to suggest an additional approach that avoids the nested try-with-resources double block.
public List<User> getUser(int userId) {
try (Connection con = DriverManager.getConnection(myConnectionURL);
PreparedStatement ps = createPreparedStatement(con, userId);
ResultSet rs = ps.executeQuery()) {
// process the resultset here, all resources will be cleaned up
} catch (SQLException e) {
e.printStackTrace();
}
}
private PreparedStatement createPreparedStatement(Connection con, int userId) throws SQLException {
String sql = \"SELECT id, username FROM users WHERE id = ?\";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, userId);
return ps;
}
There\'s no need for the outer try in your example, so you can at least go down from 3 to 2, and also you don\'t need closing ;
at the end of the resource list. The advantage of using two try blocks is that all of your code is present up front so you don\'t have to refer to a separate method:
public List<User> getUser(int userId) {
String sql = \"SELECT id, username FROM users WHERE id = ?\";
List<User> users = new ArrayList<>();
try (Connection con = DriverManager.getConnection(myConnectionURL);
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, userId);
try (ResultSet rs = ps.executeQuery()) {
while(rs.next()) {
users.add(new User(rs.getInt(\"id\"), rs.getString(\"name\")));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
Here is a concise way using lambdas and JDK 8 Supplier to fit everything in the outer try:
try (Connection con = DriverManager.getConnection(JDBC_URL, prop);
PreparedStatement stmt = ((Supplier<PreparedStatement>)() -> {
try {
PreparedStatement s = con.prepareStatement(
\"SELECT userid, name, features FROM users WHERE userid = ?\");
s.setInt(1, userid);
return s;
} catch (SQLException e) { throw new RuntimeException(e); }
}).get();
ResultSet resultSet = stmt.executeQuery()) {
}
What about creating an additional wrapper class?
package com.naveen.research.sql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public abstract class PreparedStatementWrapper implements AutoCloseable {
protected PreparedStatement stat;
public PreparedStatementWrapper(Connection con, String query, Object ... params) throws SQLException {
this.stat = con.prepareStatement(query);
this.prepareStatement(params);
}
protected abstract void prepareStatement(Object ... params) throws SQLException;
public ResultSet executeQuery() throws SQLException {
return this.stat.executeQuery();
}
public int executeUpdate() throws SQLException {
return this.stat.executeUpdate();
}
@Override
public void close() {
try {
this.stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Then in the calling class you can implement prepareStatement method as:
try (Connection con = DriverManager.getConnection(JDBC_URL, prop);
PreparedStatementWrapper stat = new PreparedStatementWrapper(con, query,
new Object[] { 123L, \"TEST\" }) {
@Override
protected void prepareStatement(Object... params) throws SQLException {
stat.setLong(1, Long.class.cast(params[0]));
stat.setString(2, String.valueOf(params[1]));
}
};
ResultSet rs = stat.executeQuery();) {
while (rs.next())
System.out.println(String.format(\"%s, %s\", rs.getString(2), rs.getString(1)));
} catch (SQLException e) {
e.printStackTrace();
}