The Generic Way To Convert Between Java and PostgreSQL Enums

An enumerated type (enum) is a handy data type that allows us to specify a list of constants to which an object field or database column can be set. 

The beauty of the enums is that we can enforce data integrity by providing the enum constants in a human-readable format. As a result, it’s unsurprising that this data type is natively supported in Java and PostgreSQL.

However, the conversion between Java and PostgreSQL enums doesn’t work out of the box. The JDBC API doesn’t recognize enums as a distinct data type, leaving it up to the JDBC drivers to decide how to deal with the conversion. And, usually, the drivers do nothing about it — the chicken-and-egg problem.

Many solutions help you map between Java and PostgreSQL enums, but most are ORM or JDBC-specific. This means that what is suggested for Spring Data will not work for Quarkus and vice versa.

In this article, I will review a generic way of handling the Java and PostgreSQL enums conversion. This approach works for plain JDBC APIs and popular ORM frameworks such as Spring Data, Hibernate, Quarkus, and Micronaut. Moreover, it’s supported by databases built on PostgreSQL, including Amazon Aurora, Google AlloyDB, and YugabyteDB.

Creating Java Entity Object and Enum

Assume that we have a Java entity object for a pizza order:

public class PizzaOrder {
    private Integer id;

    private OrderStatus status;

    private Timestamp orderTime;

    // getters and setters are omitted
}

The status field of the object is of an enumerated type defined as follows:

public enum OrderStatus {
    Ordered,
    Baking,
    Delivering,
    YummyInMyTummy
}

The application sets the status to Ordered once we order a pizza online. The status changes to Baking as soon as the chef gets to our order. Once the pizza is freshly baked, it is picked up by someone and delivered to our door - the status is then updated to Delivering. In the end, the status is set to YummyInMyTummy meaning that we enjoyed the pizza (hopefully!)

Creating Database Table and Enum

To persist the pizza orders in PostgreSQL, let’s create the following table that is mapped to our PizzaOrder entity class:

CREATE TABLE pizza_order (
   id int PRIMARY KEY,
   status order_status NOT NULL,
   order_time timestamp NOT NULL DEFAULT now()
 );

The table comes with a custom type named order_status. The type is an enum that is defined as follows:

CREATE TYPE order_status AS ENUM(
  'Ordered', 
  'Baking', 
  'Delivering', 
  'YummyInMyTummy');

The type defines constants (statuses) similar to the Java counterpart.

Hitting the Conversion Issue

If we connect to PostgreSQL using psql (or another SQL tool) and execute the following INSERT statement, it will complete successfully:

insert into pizza_order (id, status, order_time) 
values (1, 'Ordered', now());

The statement nicely accepts the order status (the enum data type) in a text representation - Ordered.

After seeing that, we may be tempted to send a Java enum value to PostgreSQL in the String format. If we use the JDBC API directly, the PreparedStatement can look as follows:

 PreparedStatement statement = conn
     .prepareStatement("INSERT INTO pizza_order (id, status, order_time) VALUES(?,?,?)");

 statement.setInt(1, 1);
 statement.setString(2, OrderStatus.Ordered.toString());
 statement.setTimestamp(3, Timestamp.from(Instant.now()));

 statement.executeUpdate();

However, the statement will fail with the following exception:

org.postgresql.util.PSQLException: ERROR: column "status" is of type order_status but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 60
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)

Even though PostgreSQL accepts the enum text representation when an INSERT/UPDATE statement is executed directly via a psql session, it doesn’t support the conversion between the varchar (passed by Java) and our enum type.

One way to fix this for the plain JDBC API is by persisting the Java enum as an object of the java.sql.Types.OTHER type:

 PreparedStatement statement = conn
     .prepareStatement("INSERT INTO pizza_order (id, status, order_time) VALUES(?,?,?)");

 statement.setInt(1, 1);
 statement.setObject(2, OrderStatus.Ordered, java.sql.Types.OTHER);
 statement.setTimestamp(3, Timestamp.from(Instant.now()));

 statement.executeUpdate();

But, as I said earlier, this approach is not generic. While it works for the plain JDBC API, you need to look for another solution if you are on Spring Data, Quarkus, or another ORM.

Casting Types at the Database Level

The database provides a generic solution. 

PostgreSQL supports the cast operator that can perform a conversion between two data types automatically.

So, in our case, all we need to do is to create the following operator:

CREATE CAST (varchar AS order_status) WITH INOUT AS IMPLICIT;

The created operator will map between the varchar type (passed by the JDBC driver) and our database-level order_status enum type. The WITH INOUT AS IMPLICIT clause ensures that the cast will happen transparently and automatically for all the statements using the order_status type.

Testing With Plain JDBC API

After we create that cast operator in PostgreSQL, the earlier JDBC code snippet inserts an order with no issues:

PreparedStatement statement = conn
     .prepareStatement("INSERT INTO pizza_order (id, status, order_time) VALUES(?,?,?)");

 statement.setInt(1, 1);
 statement.setString(2, OrderStatus.Ordered.toString());
 statement.setTimestamp(3, Timestamp.from(Instant.now()));

 statement.executeUpdate();

All we need is to pass the Java enum value as a String, and the driver will send it to PostgreSQL in the varchar representation that will automatically convert the varchar value to the order_status type.

If you read the order back from the database, then you can easily reconstruct the Java-level enum from a String value:

PreparedStatement statement = conn.prepareStatement("SELECT id, status, order_time " +
	"FROM pizza_order WHERE id = ?");
statement.setInt(1, 1);

ResultSet resultSet = statement.executeQuery();
resultSet.next();

PizzaOrder order = new PizzaOrder();

order.setId(resultSet.getInt(1));
order.setStatus(OrderStatus.valueOf(resultSet.getString(2)));
order.setOrderTime(resultSet.getTimestamp(3));

Testing With Spring Data

Next, let’s validate the cast operator-based approach with Spring Data. Nowadays, you’re likely to use an ORM rather than the JDBC API directly.

First, we need to label our PizzaOrder entity class with a few JPA and Hibernate annotations:

@Entity
public class PizzaOrder {
    @Id
    private Integer id;

    @Enumerated(EnumType.STRING)
    private OrderStatus status;

    @CreationTimestamp
    private Timestamp orderTime;

    // getters and setters are omitted
}

The @Enumerated(EnumType.STRING) instructs a JPA implementation (usually Hibernate) to pass the enum value as a String to the driver.

Second, we create PizzaOrderRepository and save an entity object using the Spring Data API:

// The repository interface
public interface PizzaOrderRepository extends JpaRepository<PizzaOrder, Integer> {

}

// The service class
@Service
public class PizzaOrderService {
    @Autowired
    PizzaOrderRepository repo;


    @Transactional
    public void addNewOrder(Integer id) {
    	PizzaOrder order = new PizzaOrder();
        order.setId(id);
        order.setStatus(OrderStatus.Ordered);

        repo.save(order);
     }
  
   ...
   // Somewhere in the source code
   pizzaService.addNewOrder(1);  
}

When the pizzaService.addNewOrder(1) method is called somewhere in our source code, the order will be created and persisted successfully to the database. The conversion between the Java and PostgreSQL enums will occur with no issues.

Lastly, if we need to read the order back from the database, we can use the JpaRepository.findById(ID id) method, which recreates the Java enum from its String representation:

PizzaOrder order = repo.findById(orderId).get();

System.out.println("Order status: " + order.getStatus());

Testing With Quarkus

How about Quarkus, which might be your #1 ORM? There is no significant difference from Spring Data as long as Quarkus favours Hibernate as a JPA implementation.

First, we annotate our PizzaOrder entity class with JPA and Hibernate annotations:

@Entity(name = "pizza_order")
public class PizzaOrder {
    @Id
    private Integer id;

    @Enumerated(EnumType.STRING)
    private OrderStatus status;

    @CreationTimestamp
    @Column(name = "order_time")
    private Timestamp orderTime;

    // getters and setters are omitted 
}

Second, we introduce PizzaOrderService that uses the EntityManager instance for database requests:

@ApplicationScoped
public class PizzaOrderService {
    @Inject
    EntityManager entityManager;

    @Transactional
    public void addNewOrder(Integer id) {
        PizzaOrder order = new PizzaOrder();

        order.setId(id);
        order.setStatus(OrderStatus.Ordered);

        entityManager.persist(order);
    }

   ...
   // Somewhere in the source code
   pizzaService.addNewOrder(1);  

When we call the pizzaService.addNewOrder(1) somewhere in our application logic, Quarkus will persist the order successfully, and PostgreSQL will take care of the Java and PostgreSQL enums conversion.

Finally, to read the order back from the database, we can use the following method of the EntityManager that maps the data from the result set to the PizzaOrder entity class (including the enum field):

PizzaOrder order = entityManager.find(PizzaOrder.class, 1);

System.out.println("Order status: " + order.getStatus());  

Testing With Micronaut

Alright, alright, how about Micronaut? I love this framework, and you might favour it as well.

The database-side cast operator is a perfect solution for Micronaut as well. To make things a little different, we won’t use Hibernate for Micronaut. Instead, we’ll rely on Micronaut’s own capabilities by using the micronaut-data-jdbc module:

<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-jdbc</artifactId>
</dependency>

// other dependencies

First, let’s annotate the PizzaOrder entity:

@MappedEntity
public class PizzaOrder {
    @Id
    private Integer id;

    @Enumerated(EnumType.STRING)
    private OrderStatus status;

    private Timestamp orderTime;

   // getters and setters are omitted
}

Next, define PizzaRepository:

@JdbcRepository(dialect = Dialect.POSTGRES)
public interface PizzaRepository extends CrudRepository<PizzaOrder, Integer> {

}

And, then store a pizza order in the database by invoking the following code snippet somewhere in the application logic:

PizzaOrder order = new PizzaOrder();

order.setId(1);
order.setStatus(OrderStatus.Ordered);
order.setOrderTime(Timestamp.from(Instant.now()));

repository.save(order);

As with Spring Data and Quarkus, Micronaut persists the object to PostgreSQL with no issues letting the database handle the conversion between the Java and PostgreSQL enum types.

Finally, whenever we need to read the order back from the database, we can use the following JPA API:

PizzaOrder order = repository.findById(id).get();

System.out.println("Order status: " + order.getStatus());

The findById(ID id) method retrieves the record from the database and recreates the PizzaOrder entity, including the PizzaOrder.status field of the enum type.

Wrapping Up

Nowadays, it’s highly likely that you will use Java enums in your application logic and as a result will need to persist them to a PostgreSQL database. You can use an ORM-specific solution for the conversion between Java and PostgreSQL enums, or you can take advantage of the generic approach based on the cast operator of PostgreSQL.

The cast operator-based approach works for all ORMs, including Spring Data, Hibernate, Quarkus, and Micronaut, as well as popular PostgreSQL-compliant databases like Amazon Aurora, Google AlloyDB, and YugabyteDB.