Spring Data JDBC: DataRetrievalFailureException : Unable to cast [oracle.sql.ROWID] to [java.lang.Number]

Question

I am new to Spring Data JDBC, and I am struggling to create a simple Dto and get it persisted on the DB.

I am using Spring-Boot 2.1.1.RELEASE and and Oracle 12 Database.

UserDto

@Table(value="USERS_T")
public class UserDto extends PersistableDto {
    @Id
    @Column(value="USR_USERNAME")
    private String userName;

    @Column(value="USR_FIRSTNAME")
    private String firstName;

    @Column(value="USR_LASTNAME")
    private String lastName; 
.....
}

UserDao

@Repository
public interface UserDao extends CrudRepository<UserDto, String> {

    @Query("SELECT * FROM USERS_T u WHERE u.USR_USERNAME = :userName")
    UserDto findByUserName(@Param("userName") String userName);
}

and I am simply trying to persist it on the DB like this

public String createUser() {
    UserDto userDto = new UserDto().setUserName("mapss@sapot.wrong.email.pt").setPassword("superpass").setUserType("Guest").setActive(true);
    logger.info(String.format("Creating user: " + userDto));

    userDto.setNew(true);
    UserDto persistedUser = userDao.save(userDto);

    logger.info(String.format("Persisted user: " + persistedUser));
    return "Ending of create user operation";
}

I am getting this exception.

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.3.RELEASE.jar:5.1.3.RELEASE]
        at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.getIdFromHolder(DefaultDataAccessStrategy.java:323) ~[spring-data-jdbc-1.0.3.RELEASE.jar:1.0.3.RELEASE]

I believe that this somehow is related with the fact that the @Id is a String.

Can someone help me understand what am I doing wrong? Why this behavior. On the spec I do not see and restriction to the type of the Id. Is this and Oracle integration issue? How can i fix this?

thank you all for your help.

I tried to add an Id of type Long the issue remains. This means the exception has nothing to do with the @Id type but probably and issue between SpringData and Oracle Database — mpssantos, Jan 17 '19 at 16:07
I noticed that on the Database the records on this table have the following ROWID: 'AACVejAAJAAAAFOAAA', 'AACVejAAJAAAAFOAAB', 'AACVejAAJAAAAFOAAC' Bug the GeneratedKeyHolder wich implements KeyHolder is expecting that the Key is a number. https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/support/GeneratedKeyHolder.html What does this mean? Is an incompatibility of Spring and Oracle 12? — mpssantos, Jan 17 '19 at 16:21
It seems you have not defined a numeric primary in your table. Define a primary key, say Id of type number in your table and then update your entity class. Since you do not have primary Key , oracle may be using the ROWID — Gagan, Jan 17 '19 at 17:08
Actually i did: CREATE TABLE USERS_T ( USR_ID NUMBER(19) DEFAULT USERS_ID_SEQ.nextval NOT NULL,.......,CONSTRAINT USERS_T_PK PRIMARY KEY (USR_ID) ); — mpssantos, Jan 17 '19 at 17:26
The version of oracle driver that I am using is: ojdbc8 18.3.0.0.0. Eventually it can be related. — mpssantos, Jan 18 '19 at 08:18

score 2 · Accepted Answer · answered Jan 18 '19 at 12:45

Unfortunately, Oracle is not yet fully supported. There is an issue open for creating integration tests for Oracle and the accompanying PR is already fixing some issues, but certainly not all.

The main problem here is that Oracle does some interesting stuff regarding generated key generation. I see the following options

a) Don't use key generation on the database side. DATAJDBC-282 makes this more comfortable. But it is so far only in the SNAPSHOT release.

b) Don't use Oracle. We currently test with MySql, Postgres, H2, HSQLDB and MariaDb

c) Take a look at the PR mentioned above to see if you can patch it enough to work.

I'm aware that these options aren't very satisfying. The challenge is that it is really hard for an Open Source project to do integration tests with Oracle, since even downloading a legal Oracle JDBC driver from a public CI build is a nightmare, let alone a database.

A coworker sent me this image when we were discussing the situation:

But we don't give up, proper support will be added.

score 1 · Answer 2 · answered Feb 04 '19 at 17:14

I suffered the same problem, while the PR is being included in the next Spring Data JDBC release we can use the following workaround with Spring AOP, It's not "perfect" but enough for us until the underlying problem is solved:

@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;        
    }       
}

score 0 · Answer 3 · answered Jan 17 '19 at 18:35

0

I think you need to declare the USR_ID field and corresponding sequence in your entity

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "XXXX")
@SequenceGenerator(sequenceName = "YYYY", allocationSize = 1, name = "XXXX")
Long USR_ID;

answered Jan 17 '19 at 18:35

Gagan

298
1
11

1

The question is about Spring Data JDBC and not JPA. – Jens Schauder Jan 18 '19 at 06:12
1

Am I the first guy trying to use Spring Data JDBC with an Oracle Database? I am starting to have a felling that yes. – mpssantos Jan 18 '19 at 07:49

Spring Data JDBC: DataRetrievalFailureException : Unable to cast [oracle.sql.ROWID] to [java.lang.Number]

3 Answers3

Linked

Related