0

I'm trying to insert the user object to Oracle with Hibernate. Object is loaded with values entered in the user registration form.

id is @GeneratedValue
pass is @Transient

enter image description here

These are the properties of User and UserType classes:

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(length = 50, nullable = false)
    @NotBlank(message = "Boş bırakılamaz.")
    @Size(min=2, max = 50)
    private String firstName;

    @Column(length = 50, nullable = false)
    @NotBlank(message = "Boş bırakılamaz.")
    @Size(min=2, max = 50)
    private String lastName;

    @Column(length = 50, nullable = false)
    @NotBlank(message = "Boş bırakılamaz.")
    @Size(min=2, max = 50)
    private String userName;

    @Column(columnDefinition = "char(128)")
    private String passHash;

    @Column(columnDefinition = "char(32)")
    private String salt;

    @ManyToOne
    @NotNull(message = "Boş bırakılamaz.")
    private UserType userType;

    @Transient
    @NotBlank(message = "Boş bırakılamaz.")
    @Size(min=4)
    private String pass;
}
@Entity
public class UserType {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(length = 50, nullable = false)
    @NotBlank(message = "Boş bırakılamaz.")
    private String name;
}

This is the Oracle DDL:

create table DH_USER
(
    ID          NUMBER(19) generated as identity
        primary key,
    FIRSTNAME   VARCHAR2(50 char) not null,
    LASTNAME    VARCHAR2(50 char) not null,
    PASSHASH    CHAR(128),
    SALT        CHAR(32),
    USERNAME    VARCHAR2(50 char) not null,
    USERTYPE_ID NUMBER(19)        not null
        constraint FKO3DS41MXQLO527MM8H8J7F0FL
            references DH_USERTYPE
)
create table DH_USERTYPE
(
    ID   NUMBER(19) generated as identity
        primary key,
    NAME VARCHAR2(50 char) not null
)

After adding logging.level.org.hibernate.SQL=DEBUG and logging.level.org.hibernate.type=TRACE to application.properties file, critical part of the log is now like this:

2020-12-08 15:41:59.256  INFO 6676 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet        : Completed initialization in 1 ms
2020-12-08 15:41:59.356 DEBUG 6676 --- [nio-8080-exec-2] org.hibernate.SQL                        : select usertype0_.id as id1_1_0_, usertype0_.name as name2_1_0_ from DH_UserType usertype0_ where usertype0_.id=?
Hibernate: select usertype0_.id as id1_1_0_, usertype0_.name as name2_1_0_ from DH_UserType usertype0_ where usertype0_.id=?
2020-12-08 15:41:59.379 TRACE 6676 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [2]
2020-12-08 15:41:59.481 TRACE 6676 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([name2_1_0_] : [VARCHAR]) - [Yönetici]
2020-12-08 15:41:59.830 DEBUG 6676 --- [nio-8080-exec-2] org.hibernate.SQL                        : insert into DH_User (id, firstName, lastName, passHash, salt, userName, userType_id) values (default, ?, ?, ?, ?, ?, ?)
Hibernate: insert into DH_User (id, firstName, lastName, passHash, salt, userName, userType_id) values (default, ?, ?, ?, ?, ?, ?)
2020-12-08 15:41:59.834  WARN 6676 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 17068, SQLState: 99999
2020-12-08 15:41:59.834 ERROR 6676 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Invalid arguments in call
2020-12-08 15:41:59.863 ERROR 6676 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement] with root cause

Why does Hibernate add the id field into the insert statement?
id field is "generated as identity" so doesn't need to be involved in the query.
And why does it try to insert "default" into id column? It should use null instead of default, as null is the value of user.id at that point in the code.

DH_User (id, firstName, lastName, passHash, salt, userName, userType_id) values (default, ?, ?, ?, ?, ?, ?)
10
  • Can you share the table schema? If you are using auto generated id then change the Entity to include @GeneratedValue(strategy = GenerationType.IDENTITY). Also check this post stackoverflow.com/questions/65130850/… Commented Dec 8, 2020 at 11:50
  • It's annotated exactly as you suggested. I'm adding the User class to the question body. Commented Dec 8, 2020 at 11:54
  • What is the version of JDK and ORACLE? Commented Dec 8, 2020 at 12:07
  • JDK version is 8, Oracle version is 18c Commented Dec 8, 2020 at 12:09
  • Can you try to change the driver version to 19.3.0.0? Also, enable the hibernate logs to print the SQL parameters. For testing disable the userType association and see if it still throws the same exception. Commented Dec 8, 2020 at 12:20

3 Answers 3

1

Oracle should support the DEFAULT syntax, I've seen it being used like here: https://www.techrepublic.com/article/oracle-tip-how-to-use-default-values-with-database-columns/

Maybe there are different editions of Oracle and yours does not have support for this? Or maybe you need to use GENERATED BY DEFAULT AS IDENTITY? Anyway, you can override this by subclassing org.hibernate.dialect.identity.Oracle12cIdentityColumnSupport and org.hibernate.dialect.Oracle12cDialect which then has to return that subclass instance in getIdentityColumnSupport().

In a custom Oracle12cIdentityColumnSupport class you can also use null for getIdentityInsertString instead of default, but then make sure you also change getIdentityColumnString to use generated by default on null as identity.

By the way, I would recommend using sequences whenever possible for performance reasons. The use of sequences and sequence caching allows Hibernate to defer/reorder and batch inserts which will improve performance drastically when inserting a lot.

Sign up to request clarification or add additional context in comments.

5 Comments

I get "Invalid arguments in call" error if I use GenerationType.IDENTITY. Only GenerationType.SEQUENCE worked so far. I don't want to use SEQUENCE because I can't insert anything manually when using SEQUENCE id columns. But IDENTITY never worked for me even if I tried everything. I will look at org.hibernate.dialect.identity.Oracle12cIdentityColumnSupport as you suggested.
What do you mean you can't insert manually with a sequence? You can write insert statements just like hibernate does e.g. INSERT INTO DH_USERTYPE VALUES (SEQ_USERTYPE.NEXTVAL, 'type1');
How can I know the sequence name that Hibernate creates?
Ok, I see, I need to set that as well.
You are in control of this. You can configure the sequence name.
0

Changing the annotation of id field as

@GeneratedValue(strategy = GenerationType.SEQUENCE)

worked. But I don't understand why.
And I don't want to use a sequence. I want to use an auto-generated ID and get a value back post insertion.
I need an annotation resulting exactly:

GENERATED BY DEFAULT ON NULL AS IDENTITY

I tried to use the columnDefinition. You can see that "not null" is appended automatically even I use nullable = true.
enter image description here

2 Comments

pls post the Hibernate dialect you are using.
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
0

As Christian suggested, I tried Oracle12cIdentityColumnSupport method and successfully changed Hibernate's way of generating IDENTITY ddl. But I got the same error, Invalid arguments in call :(
Seems like the only way for me is GenerationType.SEQUENCE
AFAIK, generated by default on null as identity is the least restrictive way of defining id columns and allowing null id's while inserting.

enter image description here

2 Comments

Yes but he says "WE8ISO8859..." charsets are causing problems. Mine is AL32UTF8.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.