JDBC is great.
But there seems to be a useful feature missing:
"Named placeholders for SQL parameters"
PreparedStatement provides a precompiled Statement that can be executed multiple times. The statement can be created by calling the factory method on the java.sql.Connection class:
public PreparedStatement prepareStatement(String sql)
throws SQLException;
This method takes an SQL String as an argument, which can contain (?) place holders, for example:
"SELECT name FROM users WHERE id = ?"
You can change the value of the place holders, by parameter index using methods:
public void setString(int parameterIndex, String theString)
throws SQLException;
public void setLong(int parameterIndex, long theLong)
throws SQLException;
...
Very nice indeed.
But shouldn't it be easier if we (additionally) could use NAMED placeholders like this:
"SELECT name FROM users WHERE id = ?{ID}"
And set the place holders values like this:
public void setString(String parameterName, String theString)
throws SQLException;
public void setLong(String parameterName, int theLong)
throws SQLException;
...
Using indexes is error prone. You often need to re-count which index number is used for what parameter. Especially when you're refactoring, bug-fixing or implementing new functionality. Also, when using parameter indexes, you sometimes need to duplicate parameter values, which makes it even less readable.
In my projects, I prefer using my own helper class for adding functionality to Prepared Statements.
Some sample code:
public class PreparedStatementTemplate {
private enum ParameterType {LONG, STRING}
private final Pattern placeHolderPattern = Pattern.compile(
"(\\?\\{([A-Za-z0-9]*)\\})"
);
private final PreparedStatement preparedStatement;
private List<String> placeHolders;
public PreparedStatementTemplate(
Connection connection, String sql) throws SQLException {
this.preparedStatement = connection.prepareStatement(
extractPlaceHolders(sql)
);
}
private String extractPlaceHolders(String sql) {
List<String> placeHolders = new LinkedList<String>();
Matcher matcher = placeHolderPattern.matcher(sql);
while (matcher.find()) {
placeHolders.add(matcher.group(2));
}
this.placeHolders = placeHolders;
return matcher.replaceAll("?");
}
public void setString(String key, String value) throws SQLException {
setParameterValue(key, value, ParameterType.STRING);
}
public void setLong(String key, long value) throws SQLException {
setParameterValue(key, value, ParameterType.LONG);
}
private void setParameterValue(
String key, Object value, ParameterType type) throws SQLException {
for (Integer index : findPlaceHolderIndexes(key)) {
if (type == ParameterType.LONG) {
preparedStatement.setLong(index, (Long) value);
} else if (type == ParameterType.STRING) {
preparedStatement.setString(index, (String) value);
} else {
throw new SQLException("unknown parameter type: " + type);
}
}
}
public List<Integer> findPlaceHolderIndexes(String key) {
List<Integer> indexes = new LinkedList<Integer>();
int n = 1;
for (String placeHolder : placeHolders) {
if (key.equalsIgnoreCase(placeHolder)) {
indexes.add(n);
}
n++;
}
return indexes;
}
public ResultSet executeQuery() throws SQLException {
return preparedStatement.executeQuery();
}
public int executeUpdate() throws SQLException {
return preparedStatement.executeUpdate();
}
public void close() throws SQLException {
preparedStatement.close();
}
}
You can use it like this:
PreparedStatementTemplate template = new PreparedStatementTemplate(
connection, "SELECT name FROM users WHERE id = ?{ID}"
);
try {
template.setLong("ID", userId);
ResultSet rs = template.executeQuery();
try {
while (rs.next()) {
System.out.println(rs.getString("NAME"));
}
} finally {
rs.close();
}
} finally {
template.close();
}
Why isn't this feature embedded in the standard JDBC? It would make life much easier!
More information about JDBC Prepared Statements can be found here:
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
http://www.tutorialspoint.com/jdbc/jdbc-statements.htm