Empty value for boolean

Question

In a spring boot 3 application, I use an Oracle database.

I have a field of type

char(1).

In db there is 0, 1 and no value.

In java

@Column(name = "privatePhone", columnDefinition = "boolean default false")
private boolean privatePhone = false;

I got an sql exception

at oracle.jdbc.driver.CharCommonAccessor.getBoolean(CharCommonAccessor.java:207) ~[ojdbc11-21.7.0.0.jar:21.6.0.0.0]

When i check

CharCommonAccessor 

class of Oracle driver

boolean getBoolean(int var1) throws SQLException {
    String var2 = this.getString(var1);
    if (var2 != null && !var2.trim().equals("0") && var2.trim().compareToIgnoreCase("f") != 0 && var2.trim().compareToIgnoreCase("false") != 0 && var2.trim().compareToIgnoreCase("n") != 0 && var2.trim().compareToIgnoreCase("no") != 0) {
        if (!var2.trim().equals("1") && var2.trim().compareToIgnoreCase("t") != 0 && var2.trim().compareToIgnoreCase("true") != 0 && var2.trim().compareToIgnoreCase("y") != 0 && var2.trim().compareToIgnoreCase("yes") != 0) {
            throw (SQLException)DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 59).fillInStackTrace();
            } else {
                return true;
            }
        } else {
            return false;
        }
    }

var2 value is empty

how to manage that

Answer 1

Exception you are facing tells that the empty value stored in your char(1) column does not equal either 0/f/false/n/no, or 1/t/true/y/yes.

As soon as on Java side you have primitive boolean field you should adjust your Oracle schema to enable only 1/0 (or t/f etc.) values, e.g.

ALTER TABLE your_table ADD private_phone char(1) DEFAULT '0' CONSTRAINT private_phone_constraint CHECK (private_phone IN ('0', '1'));

Alternatively, if you deal with legacy schema and empty values are already there then you need to implement custom field/column converter, e.g.:

@Converter
public class BooleanConverter implements AttributeConverter<Boolean, String {
    @Override
    public String convertToDatabaseColumn(Boolean value) {
        if (value == null {
            return "";
        }
        return Boolean.TRUE.equals(value) ? "1" : "0";
    }

    @Override
    public Boolean convertToEntityAttribute(String value) {
        if (value == null) {
            return null;
        }
        return "1".equals(value);
    }
}

Then in your entity you should refactor the field as

@Convert(converter = BooleanConverter.class)
private Boolean privatePhone = false;

P.S. See How do I insert the null value in a Boolean attribute when using Hibernate?