I am switching from MYSQL to ORACLE.

I have JPA Authentication setup like this:

@Override
    @Transactional(readOnly = true)
    public UserDetails loadUserByUsername(String email) throws UsernameNotFoundException {
        AppUser user = userRepository.findByUseremailIgnoreCase(email);

With MySQL all works fine. But for Oracle, during login using JPA authentication I am getting this exception.

org.springframework.security.authentication.InternalAuthenticationServiceException: Unable to extract JDBC value for position `3`

Followed by these exceptions:

Caused by: org.springframework.orm.jpa.JpaSystemException: Unable to extract JDBC value for position `3`
Caused by: java.sql.SQLException: Invalid conversion requested
Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

Any clue what I am missing and where to debug?

My table structure is as per below:

create table CONTENTPLUSPLUS.app_user (
id NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) NOT NULL,
useremail VARCHAR(150) NOT NULL,
userpassword VARCHAR(150) NOT NULL,
useruuid VARCHAR(50) NOT NULL,
userfirstname VARCHAR(150) NOT NULL,
userlastname VARCHAR(150) NOT NULL,
userenabled NUMBER(1) DEFAULT 0 NOT NULL,
created_by VARCHAR(150) NOT NULL,
created_date VARCHAR(150) NOT NULL,
modified_by VARCHAR(150) NOT NULL,
modified_date VARCHAR(150) NOT NULL,
CONSTRAINT appuser_pk PRIMARY KEY (id), UNIQUE (useremail, useruuid));

create table CONTENTPLUSPLUS.app_role(
id NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) NOT NULL,
name VARCHAR(150) NOT NULL,
CONSTRAINT approle_pk PRIMARY KEY (id),UNIQUE (name));


CREATE TABLE CONTENTPLUSPLUS.app_department (
id NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) NOT NULL,
departmentuuid VARCHAR(150),
departmentheadname varchar(255) NOT NULL,
departmentheademail varchar(255) NOT NULL,
departmentname varchar(255) NOT NULL,
userid NUMBER NOT NULL,
created_by VARCHAR(150) NOT NULL,
created_date VARCHAR(150) NOT NULL,
modified_by VARCHAR(150) NOT NULL,
modified_date VARCHAR(150) NOT NULL,
CONSTRAINT appdepartment_pk PRIMARY KEY (id),UNIQUE (departmentname, departmentuuid));

CREATE TABLE CONTENTPLUSPLUS.app_user_department (
userid NUMBER NOT NULL,
departmentid NUMBER NOT NULL
);

ALTER TABLE CONTENTPLUSPLUS.app_user_department ADD CONSTRAINT FK_AUSERDEPTUSERID FOREIGN KEY (userid) REFERENCES app_user (id);
ALTER TABLE CONTENTPLUSPLUS.app_user_department ADD CONSTRAINT FK_AUSERDEPTDEPTID FOREIGN KEY (departmentid) REFERENCES app_department (id); 
ALTER TABLE CONTENTPLUSPLUS.app_department ADD CONSTRAINT FK_AUSERUSERID FOREIGN KEY (userid) REFERENCES app_user (id);

CREATE TABLE CONTENTPLUSPLUS.app_user_role (
id NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) NOT NULL,
userid NUMBER NOT NULL,
roleid NUMBER NOT NULL,
CONSTRAINT appuserrole_pk PRIMARY KEY (id));

ALTER TABLE CONTENTPLUSPLUS.app_user_role ADD CONSTRAINT FK_AURUSERID FOREIGN KEY (userid) REFERENCES app_user (id);
ALTER TABLE CONTENTPLUSPLUS.app_user_role ADD CONSTRAINT FK_AURROLEID FOREIGN KEY (roleid) REFERENCES app_role (id);

Below is the query which gets fired during the login operation (shows up only for MySQL):

Hibernate: 
    select
        a1_0.id,
        a1_0.created_by,
        a1_0.created_date,
        a1_0.modified_by,
        a1_0.modified_date,
        a1_0.useremail,
        a1_0.userenabled,
        a1_0.userfirstname,
        a1_0.userlastname,
        a1_0.userpassword,
        a1_0.useruuid 
    from
        app_user a1_0 
    where
        upper(a1_0.useremail)=upper(?)
Hibernate: 
    select
        r1_0.userid,
        r1_1.id,
        r1_1.name 
    from
        app_user_role r1_0 
    join
        app_role r1_1 
            on r1_1.id=r1_0.roleid 
    where
        r1_0.userid=?

CodePudding user response：

You map Date in Java with VARCHAR2 in SQL: bad idea. You probably get lucky with the default conversion format of TS and the locale in MySQL: back to my first comment... look at the SQL Office Hours session code...