JDBC3Statement#getGeneratedKeys is required by Spring JDBC Template

[maciejmiklas]

Describe the bug
Spring JDBC Template relays on JDBC3Statement#getGeneratedKeys(), which has been a case for years. In this commit: 712a8a5, the method has been changed, and now it throws an exception, which breaks the transaction.

To Reproduce
insert data into DB using: NamedParameterJdbcTemplate#update(....); This is the code from org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate:

@Override
	public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
			throws DataAccessException {

		Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
		logger.debug("Executing SQL update and returning generated keys");

		return updateCount(execute(psc, ps -> {
			int rows = ps.executeUpdate();
			List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
			generatedKeys.clear();
			ResultSet keys = ps.getGeneratedKeys();
			if (keys != null) {
				try {
					RowMapperResultSetExtractor<Map<String, Object>> rse =
							new RowMapperResultSetExtractor<>(getColumnMapRowMapper(), 1);

Expected behavior
JDBC3Statement#getGeneratedKeys() should not throw an Exception but return null instead.

Logs

Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: PreparedStatementCallback; 
SQL [insert into ....))]; 
not implemented by SQLite JDBC driver; nested exception is java.sql.SQLFeatureNotSupportedException: not implemented by SQLite JDBC driver
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:96) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1577) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:669) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:993) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:359) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:343) ~[spring-jdbc-5.3.30.jar:5.3.30]
	at de.bayern.stmelf.wartung.service.MaintenanceService.saveMaintenance(MaintenanceService.java:94) ~[classes/:?]

Environment (please complete the following information):

• Spring Framework

Additional context
None

[gotson]

Expected behavior
JDBC3Statement#getGeneratedKeys() should not throw an Exception but return null instead.

This is not what the javadoc says:

Throws:
SQLException - if a database access error occurs or this method is called on a closed Statement
SQLFeatureNotSupportedException - if the JDBC driver does not support this method

[maciejmiklas]

Maybe the default implementation could throw an exception, but we could pass a parameter that would change it to return null instead?

[gotson]

It seems Spring has the wrong assumption about jdbc and should either check whether the driver supports this function, or try to catch the exception.

[maciejmiklas]

Catching an Exception with every update is inefficient. Spring is here an industry standard, you can adopt, or officially state, that JDBC Template is not supported anymore.

[gotson]

Catching an Exception with every update is inefficient. Spring is here an industry standard, you can adopt, or officially state, that JDBC Template is not supported anymore.

That's turning the problem around. The driver follows jdbc, and it seems spring doesn't. You could ask on the spring data repo what they think of this maybe?

[maciejmiklas]

Is there a way to find out whether #getGeneratedKeys() is supported? If so, I could create a case that they should query it before executing this method.

[gotson]

Is there a way to find out whether #getGeneratedKeys() is supported? If so, I could create a case that they should query it before executing this method.

Yes, with https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#supportsGetGeneratedKeys--

Ideally Spring should check this and adjust its behaviour in jdbc template.

[maciejmiklas]

I've created PR: spring-projects/spring-framework#31463

[maciejmiklas]

I've tired and it was rejected, as you can see there are different interpretations of javadoc: According to the JDBC API the Statement#getGeneratedKeys() method should either return an empty ResultSet or throw an SQLFeatureNotSupportedException exception.