Oracle, JPA and the Mystery of the String That Was Null

This is the story of how Oracle DB was messing up Kotlin’s type system, and what I did to fix it.

The setup

Let’s start by setting the stage, for this particular project I was working with the following stack:

The problem

So I had modeled the following Entity leveraging Kotlin’s data classes¹:

Tests where passing with flying colors, but for some reason we were noticing that the name would sometimes come back as null even thought it was typed as String and not String?.

The analysis

To make things more difficult there where other failures in the communication layer masking the real issue. But we finally figured out what was happening when we notice it was only reproducible under the following conditions:

• The property name was empty
• Not reproducible on tests
• Persisting to OracleDB (instead of embedded H2)

That’s when I discovered:

This is because Oracle internally changes empty string to NULL values. Oracle simply won't let insert an empty string.

So when the data was mapped back to my Person object I ended up with a null value for name. This is probably only possible because Hibernate is using reflection to set the field value in runtime, thus breaking Kotlin’s null safety.

What I did about it

The funny thing about this one is that there is not much you can do about it. There is no magic configuration to tell Oracle how you want to handle empty strings. Yes there are some hacks like changing "" to " " but I’d rather invent a random name for the guy than persisting a whitespace.

The silver lining is that most of the time if you’re not allowing null values you probably don’t want an empty string either. Now YMMV but I know this was true for a person’s name.

In fact you might even want to implement a more strict validation so people can’t be named: “💩”.

Testing

First thing I did was to try to reproduce this using a test. But since I was using @DataJpaTest with H2 embedded DB empty strings where empty strings an nulls where nulls. So the issue was not reproducible.

That’s when I learned that you can tell H2 to behave like an Oracle DB using Oracle Compatibility mode. To achieve this I added the following configuration to my application.yml under test/resources:

1
2
3

spring:
  datasource:
    url: jdbc:h2:mem:testdb;Mode=Oracle

And annotated my test class with:

1
2
3
4

@RunWith(SpringRunner::class)
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class FormRepositoryTest {…}

And voilà, now you have an H2 in memory DB dressed up as Oracle.

Changing the schema

The other thing I realized is that the schema allowed for null values on the name column. I’d been using javax.persistence.schema-generation as a starting point for my schema and I wrongly assumed it would take the hint from the Kotlin type system to prevent null values².

Instead I had to explicitly annotate my Entity:

and manually change my existing schema

1
2
3
4
5

CREATE TABLE Person (
  id NUMBER(19, 0) NOT NULL,
  name VARCHAR2(255 CHAR) NOT NULL,
  PRIMARY KEY (id)
);

The result is that now if somebody tries to persist a Person with an empty name a big fat Exception is thrown. Or at least until I implement proper name validation.