Oracle DB Datatype for boolean - which to choose for Java Persistence?

This post is older than a year. Consider some information might not be accurate anymore.

If you have to deal with the Oracle DB (11g), Boolean values have no specific datatype yet. Some uses number - a SMALLINT, some uses a character - VARCHAR(1). Number or Character - which to choose? This post illustrates both approaches for the Oracle DB for Java with the major Persistence APIs JDBC and JPA.

JDBC

The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases SQL databases ..

In the interface java.sql.ResultSet.getBoolean(..) method, we can find following javadoc:

If the designated column has a datatype of CHAR or VARCHAR and contains a "0" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 0, a value of false is returned. If the designated column has a datatype of CHAR or VARCHAR and contains a "1" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 1, a value of true is returned.

It seems numeric values (even stored as character) are the preferred way. Please consider, don’t mix things up. Unix or Linux has a exit code 0. A successful command returns a 0. Any other non-zero value can usually be interpreted as error code. What we can say for sure for Boolean values within the JDBC/JPA:

• 0 ⇒ false
• 1 ⇒ true

JPA

The Java Persistence API is the Java API for the management of persistence and object/relational mapping in Java EE and Java SE environments. It provides an object/relational mapping facility for the Java application developer using a Java domain model to manage a relational database.

JSR-317, JSR-338

Number Approach

Finding the article Using the Java Persistence API, for the automatic schema generation Oracle will use for Boolean data types the db type NUMBER(1). The JPA (implementations) will set the correct value for true and false then. A small example

@Entity
public class CreditCard {
    /**
     * Primary Account Number
     */
    @Id
    private String pan;
    private Integer sequenceNumber = 0;
    /**
     * flag if card is on the blacklist
     */
    @Basic
    private boolean blacklisted;
    //some auto-generated getters and setters ..
}

The JPA will take for the Boolean property blacklisted a number datatype for the respective database.

A small JUnit Test

public class CreditCardBooleanTest {
    private EntityManager em;
    @Before
    public void setUp() throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("h2mem");
        em = factory.createEntityManager();
        CreditCard masterCard = new CreditCard();
        masterCard.setPan("MA-PAN-4711");
        masterCard.setBlacklisted(false);
        CreditCard visa = new CreditCard();
        visa.setPan("VISA-PAN-4711");
        visa.setBlacklisted(true);
        em.getTransaction().begin();
        em.persist(masterCard);
        em.persist(visa);
        em.getTransaction().commit();
    }
    @After
    public void tearDown() throws Exception {
        if (em.isOpen()) {
            em.close();
        }
    }
    @Test
    public void testBooleanFlag() {
        // card is not blacklisted = false
        CreditCard creditCard = em.find(CreditCard.class, "MA-PAN-4711");
        assertFalse("Mastercard is not blacklisted", creditCard.isBlacklisted());
        // String jpql = "SELECT c FROM CreditCard c WHERE c.blacklisted = false";
        // does also work, false is 0
        String jpql = "SELECT c FROM CreditCard c WHERE c.blacklisted = 0";
        TypedQuery<CreditCard> query = em.createQuery(jpql, CreditCard.class);
        CreditCard creditCard2 = query.getSingleResult();
        assertEquals("same PAN", creditCard.getPan(), creditCard2.getPan());
        // card is blacklisted = true
        creditCard = em.find(CreditCard.class, "VISA-PAN-4711");
        assertTrue("Visa is blacklisted", creditCard.isBlacklisted());
        // jpql = "SELECT c FROM CreditCard c WHERE c.blacklisted = true";
        // does also work, true is 1
        jpql = "SELECT c FROM CreditCard c WHERE c.blacklisted = 1";
        query = em.createQuery(jpql, CreditCard.class);
        creditCard2 = query.getSingleResult();
        assertEquals("same PAN", creditCard.getPan(), creditCard2.getPan());
    }
}

• The JUnit test runs against the in-memory H2 database
• The JPA objects, i.e. DDL is automatically generated by the JPA
• You can use the boolean values true and false in the JPQL
• You can also use the numeric values 1 and 0 in the JPQL
• As datatype a SMALLINT was taken for the boolean value
• Against the Oracle DB NUMBER(1) is used, you might optimize the datatype by using SMALLINT

Character Approach

Compared to Number a single char takes less disk space. Many thanks to Christian Gohmann for this insight.

The check constraint avoids any illegal values to be inserted.

Custom Hibernate Mapping

Hibernate supports with its Hibernate annotations the mapping to the characters ‘Y’ and ‘N’. Keep in mind, this is a non JPA feature! Add the Maven dependency to your persistence pom.xml to use this feature.

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-annotations</artifactId>
    <version>3.5.6-Final</version>
</dependency>

To map boolean to char use org.hibernate.annotations.Type of "yes_no"

@Entity
public class Offer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String details;
    @Type(type = "yes_no")
    private boolean accepted;
}

A small JUnit test

public class OfferBooleanTest {
    private EntityManager em;
    @Before
    public void setUp() throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("h2mem");
        em = factory.createEntityManager();
        Offer offer = new Offer();
        offer.setAccepted(true);
        offer.setDetails("test offer");
        Offer refusedOffer = new Offer();
        refusedOffer.setDetails("example refused offer");
        em.getTransaction().begin();
        em.persist(offer);
        em.persist(refusedOffer);
        em.getTransaction().commit();
    }
    @After
    public void tearDown() throws Exception {
        if (em.isOpen()) {
            em.close();
        }
    }
    @Test
    public void testBooleanString() {
        String jpql = "SELECT o FROM Offer o WHERE o.accepted='Y'";
        TypedQuery<Offer> query = em.createQuery(jpql, Offer.class);
        Offer wanted = query.getSingleResult();
        assertTrue("offer was accepted", wanted.isAccepted());
        jpql = "SELECT o FROM Offer o WHERE o.accepted=false";
        wanted = em.createQuery(jpql, Offer.class).getSingleResult();
        assertFalse("refused offer found", wanted.isAccepted());
    }
}

• You can use the boolean values
• You can use the char literals ‘Y’ and ‘N’

A screenshot from the SQL result panel of IntelliJ

EclipseLink JPA

EclipseLink has no special feature of that kind, but since JPA 2.1, Converters can perform the task. See also JPA Basic Attribute Converters.

Alternatives

Another workaround is to store the String/character and apply the logic in a transient field/property. JPA will not store this entity property. This approach violates some design principles like Single Responsibility, etc. but should be mentioned for completeness.

// boolean char value from the database
private String acceptance;
private transient boolean accepted;
...
public boolean isAccepted {
    return "Y".equals(acceptance);
}
public void setAccepted(final boolean accepted) {
    this.accepted = accepted;
    this.acceptance = accepted ? "Y" : "N";
}