Make handling of ID generation work with Oracle [DATAJDBC-350]

[spring-projects-issues]

Dav1dde opened DATAJDBC-350 and commented

Oracle returns oracle.sql.ROWID when no key gets generated.

 
Spring-data-jdbc calls "holder.getKey()" in org.springframework.data.jdbc.core.DefaultDataAccessStrategy#getIdFromHolder which fails with the following Exception:

Caused by: org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]
	at org.springframework.jdbc.support.GeneratedKeyHolder.getKey(GeneratedKeyHolder.java:79) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
	at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.getIdFromHolder(DefaultDataAccessStrategy.java:323) ~[spring-data-jdbc-1.0.5.RELEASE.jar:1.0.5.RELEASE]
	at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:111) ~[spring-data-jdbc-1.0.5.RELEASE.jar:1.0.5.RELEASE]
	at org.springframework.data.jdbc.core.DefaultJdbcInterpreter.interpret(DefaultJdbcInterpreter.java:61) ~[spring-data-jdbc-1.0.5.RELEASE.jar:1.0.5.RELEASE]
	at org.springframework.data.relational.core.conversion.DbAction$Insert.doExecuteWith(DbAction.java:86) ~[spring-data-jdbc-1.0.5.RELEASE.jar:1.0.5.RELEASE]
	at org.springframework.data.relational.core.conversion.DbAction.executeWith(DbAction.java:55) ~[spring-data-jdbc-1.0.5.RELEASE.jar:1.0.5.RELEASE]
	... 47 common frames omitted

 

The Postgres-Fallback would also work for Oracle:

		try {
			// MySQL just returns one value with a special name
			return holder.getKey();
		} catch (InvalidDataAccessApiUsageException e) {
			// Postgres returns a value for each column
			Map<String, Object> keys = holder.getKeys();

			if (keys == null || persistentEntity.getIdProperty() == null) {
				return null;
			}

			return keys.get(persistentEntity.getIdColumn());
		}

---

Issue Links:

• DATAJDBC-256 Run integration tests with Oracle
  ("depends on")

1 votes, 4 watchers

[spring-projects-issues]

Dav1dde commented

There is another issue with Keygeneration and Oracle 12c (git head). Oracle requires you to explicitly set which columns it should return the generated keys from, so instead of calling org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#update(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.support.KeyHolder) we would need to call org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#update(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.support.KeyHolder, java.lang.String[]) with the ID column as "keyColumnNames" (last Argument)

[mipo256]

@schauder @mp911de This issue should be closedю It is not relevant anymore. This is already implemented here: https://github.com/spring-projects/spring-data-relational/blob/main/spring-data-jdbc/src/main/java/org/springframework/data/jdbc/core/convert/IdGeneratingInsertStrategy.java

[schauder]

Basic Id generation worked. Id generation for non capital names will be fixed with #1666