0

I am trying to insert in a oracle view using JPA. JPA generates below SQL statement-

insert into home_view (id, email, first_name, last_name) values (default, ?, ?, ?)

And when it executes query, Oracle DB throws below error-

ORA-32575: Explicit column default is not supported for modifying views

As per my understanding above query can not work with view because we can not insert into a view if its underlying table column is IDENTITY column.

Do we have any option in hibernate which helps exclusion of identity column while saving object to DB?

Note- I do not want to use raw SQL statement to insert my object.

I used GenerationType.SEQUENCE strategy and it is working fine, but only problem is I have to provide sequence name. I do not want to provide hard coded sequence name in Java Entity.

Thanks for your help!

pranav tiwari
  • 1
  • 2
  • remove column name id and default from the query, that should work – nbk Jan 19 '23 at 16:23
  • JPA is generating the query, I have no control. I am looking if JPA has any option to exclude identity column from Entity while generating query. – pranav tiwari Jan 19 '23 at 16:28
  • Does this answer your question? [what is the use of annotations @Id and @GeneratedValue(strategy = GenerationType.IDENTITY)? Why the generationtype is identity?](https://stackoverflow.com/questions/20603638/what-is-the-use-of-annotations-id-and-generatedvaluestrategy-generationtype) – nbk Jan 19 '23 at 16:43
  • Yes, It(Sequence Generation Startegy) works. But unfortunately I can not use this. – pranav tiwari Jan 19 '23 at 17:03
  • This works with other providers - I'm surprised Hibernate tries to insert 'default' when it doesn't need to pass in anything. You might be able to write your own behavior as described here https://dnikiforov.wordpress.com/2015/02/14/oracle-12c-identity-and-popular-orms/ or try another provider. Alternatively, a different generation strategy would be better. Preallocation is generally better performance for batch inserts, and using something like a UUID generator means no fetching it from the DB at all. – Chris Jan 20 '23 at 00:00

2 Answers2

0

Use:

@Id @GeneratedValue(strategy=IDENTITY)
Long id;

UPDATE: For a view on Oracle, use:

  • @Id @Generated in Hibernate 6.2 (soon to be released), or
  • the SelectGenerator, in either Hibernate 5 or 6.

But note that you will have to identify a @NaturalId of your entity, since otherwise Hibernate has no way of locating the just-inserted row to retrieve the generated id.

In your case, the natural id is the email address, I suppose.

Gavin King
  • 3,182
  • 1
  • 13
  • 11
  • This does not work with view(In Oracle DB). Getting below exception. 'ORA-32575: Explicit column default is not supported for modifying views' – pranav tiwari Jan 19 '23 at 17:02
  • Wait, so you're saying that your problem is a limitation of Oracle, and nothing at all to do with Hibernate? – Gavin King Jan 19 '23 at 17:04
  • Perhaps if you can first figure out what *SQL* works for inserting into this view, *then* we can help you figure out how to generate that SQL using Hibernate. – Gavin King Jan 19 '23 at 17:10
  • Yes, It can be limitation of Oracle which does not allow me to insert into a view. And I have no control over that. Do we have any way in JPA using which we can tell JPA to not to include the primary key column while generating insert statement? – pranav tiwari Jan 19 '23 at 17:17
  • insert into home_view(email, first_name, last_name) values ('abc@gmail.com','abc','xyz'); The above SQL works for inserting into view. – pranav tiwari Jan 19 '23 at 17:19
0

Oracle only supports identity columns as of version 12. You didn't mention which version you are using, but I guess this is the reason for the Oracle error. Either way, Oracle just recently stopped supporting versions older than 19c, so either you update or you have to use a different generation strategy i.e. sequence, table or uuid.

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58