0

I have a table (not created by me and it cannot be modified by me either) with ~30 columns with a surrogate primary key from a sequence. From these 30 columns, only 3 are not nullable (let's call them col1, col2 and col3) and using these 3 and other 6 nullable (col4 ... col9) columns I have an unique index. How can I map this with Hibernate since it doesn't allow null values at @Id attributes nor @EmbeddedId? What is the best workaround recommended considering I'm not allowed to change any database property?

Consider I want to implement a crud rest api that should check if that entity is already present to update data, otherwise, insert it inside the same method. Passing (1,2,3) values (col1,col2,col3) to my query should result into a successful insert with an empty table. After that, if I try to insert (1,2,3,4) values (col1,col2,col3,col4) and the forth value is from a column indexed by previous unique index, it should insert another row, resulting into:

seqPK col1(notNull) col2(notNull) col3(notNull) col4(null)   col25(null) ...
1     1             2             3             null         null        ...
2     1             2             3             4            null        ...

Because (1,2,3,null(x6)) is different from (1,2,3,4,null(x5)). But if I try to insert (1,2,3,25) values (col1,col2,col3,col25) and the forth value is from a non indexed column, it should update the first inserted row like this:

seqPK col1 col2 col3 col4(null) col25(null) ...
1     1    2    3    null       25          ...
2     1    2    3    4          null        ...

I'm planning using a @Query at my findById(passing all 9 arguments) and always forcing them to be not null (@NotNull), thus forcing uniqueness from their attributes without using any @Id functionalities hibernate could me provide. Is there a better approach?

rado
  • 5,720
  • 5
  • 29
  • 51
  • Hi. This is not clear. What does "map this" mean--map what to what? Why not use the PK as id? What is the point of telling us that you can do those inserts? What do you mean, "minimal"? What is the unsimplified version? Do you understand in SQL when columns are "unique" with nulls allowed it means that 2 such subrows with any nulls are considered different & can be in a table even if they are the same when treating null=null? Do you actually want multiple such rows? Such a subrow holding nulls does not identify a particular row of a table. And you can't have FKs to them. – philipxy Aug 06 '18 at 17:38
  • 1. "map this" means correctly create a Java entity from table 2. I cannot use PK as id because it's a surrogate key from a sequence, it doesn't add any info on how a row can be unique, only guarantees it after it was checked for uniqueness. 3. The point is to show a example what my hibernate entity should accept before being inserted into database 4. Minimal means with not every other column filled. The unsimplified version would be filling all the columns 5. About your last point, I agree with you how poor was this database design. Unfortunately, I'm not allowed to change it. – rado Aug 06 '18 at 17:45
  • "map this" comes from Object-relational mapping, as pointed out as a tag – rado Aug 06 '18 at 17:46
  • 1
    I know what map means, I mean that you are not clear about what you are trying to do. – philipxy Aug 06 '18 at 17:50
  • Thanks for your patience. If you have any other doubt about what I want, feel free to ask – rado Aug 06 '18 at 17:51
  • You can't have or want a hibernate id formed from nullable columns, because nullable rows although "unique" per an SQL keyword are not unique in the sense of identifying, ie being an id or as the target of a FK. That is why we use surrogates--to identify an entity/event that cannot be distinguished from others just by the data we are otherwise recording about it. Explain how you expect to use such a set of columns. Please address the id issues of my last comment. Please read & act on [mcve]. PS Please edit clarifications into your post, not comments. – philipxy Aug 06 '18 at 18:03

0 Answers0