Insert in oracle with Spring data JDBC CrudRepository using manual PK (not autogenerated)

Question

I'm getting the following error inserting in Oracle DB with Spring Data JDBC v1.0.4 (not JPA):

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.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.getIdFromHolder(DefaultDataAccessStrategy.java:323) ~[spring-data-jdbc-1.0.4.RELEASE.jar:1.0.4.RELEASE]
    at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:111) ~[spring-data-jdbc-1.0.4.RELEASE.jar:1.0.4.RELEASE]
    at org.springframework.data.jdbc.core.DefaultJdbcInterpreter.interpret(DefaultJdbcInterpreter.java:73) ~[spring-data-jdbc-1.0.4.RELEASE.jar:1.0.4.RELEASE]
    at org.springframework.data.relational.core.conversion.DbAction$InsertRoot.doExecuteWith(DbAction.java:110) ~[spring-data-jdbc-1.0.4.RELEASE.jar:1.0.4.RELEASE]
    at org.springframework.data.relational.core.conversion.DbAction.executeWith(DbAction.java:55) ~[spring-data-jdbc-1.0.4.RELEASE.jar:1.0.4.RELEASE]

It seems that the default implementation expects always an autogenerated key, but my table's primary key is a String.

The entity to persist:

@Table("USERS")
public class User implements Persistable<String> {

    @Transient
    @JsonIgnore
    private boolean newRow = false;

    @Id
    @Column("ID_USER")
    private String userId;

    @Column("NAME")
    private String name;    

    @Override
    @JsonIgnore
    public String getId() {
        return userId;
    }

    public void setNew(boolean newRow) {
        this.newRow = newRow;
    }

    @Override
    @JsonIgnore
    public boolean isNew() {
        return newRow;
    }

}

The repository:

@Repository
public interface UserRepository extends CrudRepository<User, String>, PagingAndSortingRepository<User, String> {

}

The call to the repository:

public User create(User user) throws QOException {
    user.setNew(true);
    return userRepository.save(user);
}

The exception is thrown in the last line of insert method in org.springframework.data.jdbc.core.DefaultDataAccessStrategy:

operations.update( //
        sql(domainType).getInsert(parameters.keySet()), //
        parameterSource, //
        holder //
);
// Next line is the problem
return getIdFromHolder(holder, persistentEntity);

The problem is that KeyHolder interface has a method getKey that returns a Number and Oracle returns as generated KEY the ROWID... However the entity PK is not generated, the ID is set before the insert.

I cannot understand what is wrong with that code, any help is welcome.

score 1 · Answer 1 · answered Feb 03 '19 at 12:07

I didn't find the root problem, but I've added a workaround using Spring AOP that silents the exception and returns the proper entity instance.

@Around("execution(public * my-app-pacakage.repository.*.save(..))")
public Object aspectController(ProceedingJoinPoint jp) throws Throwable {
    try {
        return jp.proceed();
    } catch (DbActionExecutionException e) {
        if (e.getCause() instanceof DataRetrievalFailureException) {
            return jp.getArgs()[0];
        }
        return e;
    } catch(Throwable e) {
        throw e;        
    }       
}

If anyone give me a better solution I'll change the correct answer, until then, this code just works.

Insert in oracle with Spring data JDBC CrudRepository using manual PK (not autogenerated)

1 Answers1