Hibernate Entity with no Primary Key and nullable fields

Question

I've seen similar cases but not this exact combination of difficulties and any solutions I've seen have not been working. I'm upgrading some library from Spring-data 1.X and Hibernate 4.X to Spring-data 2.X and Hibernate 5.X and I think this is related to one of those two frameworks...likely Hibernate, getting much pickier about IDs between versions, because slapping @Id on multiple fields seemingly used to work. I guess if I need to, could I use Hibernate 4 with Spring-data 2, QueryDSL 4?

1. I have a Hibernate @Entity based on a table with 2 fields.
2. The first field is non-unique, so it can't be the PK.
3. The other field is nullable, so it can't be the PK.
4. I'm using Oracle in production.
5. I'm testing with HSQLDB with sql.syntax_ora=true

Solution #1: Composite key of both fields.
Failure #1: Using both @EmbeddedId/@Embedded and @IdClass solutions, when the nullable field is null Hibernate gives up on the object entirely and returns a simple null instead of an object with a null field.

Solution #2: Select a pseudocolumn instead IE @Column(name = "ROWID")
Failure #2: Hibernate doesn't recognize this is not a real column and tries to select it while adding the table alias, which fails because the field is not found.

I've also tried adding the parentheses "ROWID()" but it does the same thing.

Solution #3: Use a @Formula to try injecting a snippet instead of fake @Column
Failure #3: I think this isn't an allowable combination and just results in an error saying identifier property [] cannot contain formula []

Solution #4: Use a custom Dialect to add "ROWID" as a keyword instead of relying on it being registered as a function
Failure #4: No notable difference from solution #2

I haven't really messed with the JpaRepository being used to .findAll() on this table but in most cases I'm not really sure what it should be. Would something like #1 work better if ID was set to the @IdClass?

I'm at an absolute loss here. It's effectively a legacy database I can't really mess with. I need to override the SQL generated to select the object, function with a partially nullable composite-id, function with a non-unique ID, otherwise use a non-persisted field as id...rowid/rownum/some sort of hash...is there some other option I'm not seeing where this @Entity will work with its apparently UN-IDable combination of fields?

Is there some way to treat the null value in the 2nd column as another value when being used as part of the ID to prevent Failure #1, but still allowing it to be null in the object itself?

        @Bean
        public EntityManagerFactory entityManagerFactory() {
            final LocalContainerEntityManagerFactoryBean bean = new LocalContainerEntityManagerFactoryBean();
            //bean.setJpaDialect(new HibernateJpaDialect());
            bean.setDataSource(dataSource());
            bean.setJpaVendorAdapter(jpaVendorAdapter());
            bean.setPackagesToScan("com.company.core.domain");

            final Properties jpaProperties = new Properties();
            //jpaProperties.setProperty("javax.persistence.schema-generation.database.action", "create");
            jpaProperties.setProperty("hibernate.show_sql", "true");
            jpaProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");

            bean.setJpaProperties(jpaProperties);
            bean.afterPropertiesSet();

            return bean.getObject();
        }
        @Bean
        public DataSource dataSource() {
            final String uuid = UUID.randomUUID().toString();
            return new EmbeddedDatabaseBuilder().addScript("schema.sql").setName(uuid + ";sql.syntax_ora=true;hsqldb.sqllog=3").build();
        }

Answer 1

The best I managed to do here was to tweak my @Entity to include the ROWID pseudo-column. Unfortunately while HSQLDB supports a certain kind of ROWNUM it only works when NOT prefixed with the tableAlias, which Hibernate will always do. And it doesn't support ROWID at all. But ROWID which will work in Oracle won't work in HSQLDB, so the trickery is to manually setup my embedded HSQLDB with a ROWID column set to auto-increment with copious notes that this will Just Work(TM) on Oracle without modifying the model.

Yes, the correct answer is to add a real PK to the table in Oracle. Alternatively using an embedded Oracle (IE dockerized instance via test-containers) would work fine as well, but the licensing isn't particularly amenable...or so I'm told. BUT as hacky as it is to deliberately use a different schema in test than at runtime, it does work and is well-documented in multiple places so no one is surprised by anything.

Or...you know...just don't upgrade Hibernate. This entity snuck through a bug in older versions of Hibernate for YEARS where the Mapper accepted the multiple @Id fields as a composite key but the Loader didn't recognize the null field as part of the key so it allowed the object to rehydrate.