The authors of the Spring library went through all the trouble of writing several handy database interface classes, such as NamedParameterJdbcTemplate, with the intent of making writing literal SQL queries in Java feel just a little less terrible. One very useful function provided by the NamedParameterJdbcTemplate (hereafter referred to as NPJT and not to be confused with a Meyers-Briggs personality type) is the ability to retrieve the values auto-generated by your table after a successful insert. For example, if your table schema is designed to generate an ID for each new row, you may want to know what ID was generated so you can use that value in successive Java logic or SQL calls.
NPJT’s update method has a signature that meets this need, works for most people, and is incredibly simple:
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);String myUpdateSQL = “INSERT blah blah blah”;
SqlParameterSource myParamSource = …;
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(myUpdateSQL, myParamSource, keyHolder, new String[] { “ID” });int id = keyHolder.getKey() != null ? keyHolder.getKey().intValue() ? 0;
You simply pass in your SQL statement, your param source, a KeyHolder object, and an array containing the column names that have auto-generated values. Thank you, Spring, for making our lives so much easier.
Unfortunately, this is where things get a little hairy if you’re using an Oracle database. Attempting to use this method signature with an Oracle DB generates the cryptic and altogether unhelpful “operation not allowed” SQL error. This is because the Oracle JDBC driver does not support the Statement#getGeneratedKeys() method which Spring employs.
The solution is to use a CallableStatement instead of a PreparedStatement, and to dress up our SQL queries in such a way that Oracle knows we are expecting to read a return value.
Assume we have a table called my_family with columns ID, name, favorite_color, and age. We would modify our INSERT statements as such:
INSERT INTO my_family (name, favorite_color, age) VALUES (‘Sally’, ‘red’, ‘8’);
This now becomes:
BEGIN INSERT INTO my_family (name, favorite_color, age) VALUES (‘Sally’, ‘red’, ‘8’) RETURNING ‘id’ INTO ? END;
Note: you can not use RETURNING INTO with MERGE statements.
Ideally, we would like to be able to use Spring’s NPJT since it’s simple, concise, and well-documented. Additionally, using an already existing library class reduces potential refactoring and code complications with other team members.
In that spirit, our solution will be to extend NPJT and override its update method with our own. This allows us to use our custom class exactly as if it were NPJT.
First, we’ll create our own class that extends NamedParameterJdbcTemplate:
public class OracleJdbcTemplate extends NamedParameterJdbcTemplate {
}
Extending NamedParameterJdbcTemplate gives us access to some of its protected methods, reducing the amount of code we have to rewrite and allowing us to continue to let Spring do most of the work.
The main difference between our method and Spring’s method is we need to wrap any insert statements that expect an out parameter with BEGIN …. RETURNING … END, and we have to use the subclass CallableStatement as opposed to the PreparedStatement class. This means we also have to rewrite Spring’s getPreparedStatementCreator as our own getCallableStatementCreator.
private CallableStatementCreator getCallableStatementCreator(String sql, SqlParameterSource paramSource, String[] outParamNames) {
ParsedSql parsedSql = this.getParsedSql(sql);
String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
List declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
Map<String, Object> params = new HashMap<>();
for (SqlParameter param : declaredParameters) {
params.put(param.getName(), paramSource.getValue(param.getName()));
}
for (String outParam : outParamNames) {
declaredParameters.add(new SqlOutParameter(outParam, Types.NUMERIC));
}
CallableStatementCreatorFactory cscf = new CallableStatementCreatorFactory(sqlToUse, declaredParameters);
return cscf.newCallableStatementCreator(params);}
The bolded lines above are the only lines we had to write ourselves. Everything else is just a copy of NamedParameterJdbcTemplate’s getPreparedStatementCreator.
(Notice that we specified the out parameter type as Types.NUMERIC. Take a look at java.sql.Types for all of the types available to fit your needs. If you have multiple columns that each return different data types, you will want to add another method parameter to indicate the type of each out parameter).
We have one other helper method to write, and that is the method we’ll use to wrap our SQL statements in the necessary BEGIN … RETURNING … END syntax. This will allow us to still write basic INSERT statements, allowing our Java class to worry about the correct SQL syntax instead of us.
private String prepareSql(String sql, String[] keyColumnNames) {
if (keyColumnNames.length == 0) {
return sql;
}
Iterator<String> i$ = Arrays.asList(keyColumnNames).iterator();
StringBuilder a = new StringBuilder();
StringBuilder b = new StringBuilder();
while (i$.hasNext()) {
String keyColumn = i$.next();
a.append(keyColumn);
b.append(“?”);
if (i$.hasNext()) {
a.append(“, “);
b.append(“, “);
}
}
return “BEGIN “ + sql + “ RETURNING “ + a.toString() + “ INTO “ + b.toString() + “; END;”;}
And finally, the pièce de résistance, our very own update method:
@Override
public int update(String sql, SqlParameterSource paramSource, final KeyHolder generatedKeyHolder, final String[] keyColumnNames) throws DataAccessException {
String preparedSql = this.prepareSql(sql, keyColumnNames);
return this.getJdbcOperations().execute(getCallableStatementCreator(preparedSql, paramSource, keyColumnNames),
new CallableStatementCallback() {
public Integer doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException {
int rows = callableStatement.executeUpdate();
List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
generatedKeys.clear();
int numParams = callableStatement.getParameterMetaData().getParameterCount();
for (int n = numParams – keyColumnNames.length, i = n; i < numParams; i++) {
try {
Object key = callableStatement.getObject(i + 1);
generatedKeys.add(Collections.singletonMap(keyColumnNames[i – n], key));
} catch (SQLException e) {
// log the exception
}
}
return rows;
}
});}
The very astute will notice that this is almost identical to the existing update method (with the exception of the use of CallableStatements instead of PreparedStatements) and our roundabout way of getting the values of the auto-generated columns.
Now we can use our class just as we would use NamedParameterJdbcTemplate.
OracleJdbcTemplate template = new OracleJdbcTemplate(dataSource);String myUpdateSQL = “INSERT blah blah blah”;
SqlParameterSource myParamSource = …;
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(myUpdateSQL, myParamSource, keyHolder, new String[] { “ID” });int id = keyHolder.getKey() != null ? keyHolder.getKey().intValue ? 0;
And there we are! Clean, effective, and elegant. Feel free to add even more helper or utility methods to the new class to DRY up or abstract your code a little bit more.
Contact Us
Ready to achieve your vision? We're here to help.
We'd love to start a conversation. Fill out the form and we'll connect you with the right person.
Searching for a new career?
View job openings
