Mapping Java Enum in Postgresql with Spring Data JDBC

Question

I want to be able to map a enum declared in java to an enum created in postgres. For example having the following:

CREATE TYPE EYE_COLOR AS ENUM ('BROWN', 'BLUE', 'GREEN');
CREATE TABLE PERSON (
 ID INT PRIMARY KEY AUTO_INCREMENT,
 NAME NVARCHAR2(128) NOT NULL,
 EYE EYE_COLOR
);

In java I have something like this:

public enum EyeColor {
 BROWN,
 BLUE,
 GREEN
}
public class Person {
 @Id
 Long id;
 String name;
 EyeColor eye;
}

When I try to do save something to the database like this:

personRepository.save(new Person("test", EyeColor.BROWN))

the sql log seems fine:

Executing prepared SQL statement [INSERT INTO person (name, eye) VALUES (?, ?)]
 o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 1, parameter value [test], value class [java.lang.String], SQL type 12
 o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 2, parameter value [BROWN], value class [java.lang.String], SQL type 12

but inside the actual database field I get an exception text:

org.h2.jdbc.JdbcSQLException: General error: "java.lang.RuntimeException: type=25" [50000-196] 
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) 
at org.h2.message.DbException.get(DbException.java:168) 
at org.h2.message.DbException.convert(DbException.java:295) 
at org.h2.message.DbException.toSQLException(DbException.java:268) 
at org.h2.message.TraceObject.logAndConvert(TraceObject.java:352) 
at org.h2.jdbc.JdbcResultSetMetaData.getColumnClassName(JdbcResultSetMetaData.java:376) 
at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getObject(RemoteResultSetImpl.java:1269) 
at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getCurrentRow(RemoteResultSetImpl.java:1249) 
at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getObjects(RemoteResultSetImpl.java:1229) 
at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:498) 
 at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:346) 
at sun.rmi.transport.Transport1ドル.run(Transport.java:200) 
at sun.rmi.transport.Transport1ドル.run(Transport.java:197) 
at java.security.AccessController.doPrivileged(Native Method) 
at sun.rmi.transport.Transport.serviceCall(Transport.java:196) 
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568) 
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826) 
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run0ドル(TCPTransport.java:683) 
at java.security.AccessController.doPrivileged(Native Method) 
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) 
at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.RuntimeException: type=25

If I replace the EYE_COLOR type to NVARCHAR2(128) in the postgres column definition everything works fine. An actual "BROWN" value is inserted.

Any ideas how this should be implemented?

LE: Forgot to add my spring database driver:

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:file:path/to/file;DB_CLOSE_DELAY=-1;MODE=PostgreSQL;AUTO_SERVER=TRUE

I also tried using the ENUM definition like they say in the H2 driver specifications directly inside the column definition, but is still didn't work:

CREATE TABLE PERSON (
 ID INT PRIMARY KEY AUTO_INCREMENT,
 NAME NVARCHAR2(128) NOT NULL,
 EYE ENUM ('BROWN', 'BLUE', 'GREEN')
);

• Jens Schauder: It does write the exception into the field? That is weird.
• Jens Schauder: The stacktrace says h2 all over the place, but your tag says postgresql. So which is it?
• Razvan S: I edited the answer and added my driver details
• Jens Schauder: jira.spring.io/browse/DATAJDBC-239 seems to be related. Especially since says treating it as a String is fine: stackoverflow.com/questions/40356750/…
• Razvan S: Thanks for the quick answer, for the time being we'll use the string solution.

Answer 1

We solved this problem in different ways.

The first way - use flag stringtype=unspecified (see https://jdbc.postgresql.org/documentation/94/connect.html)

jdbc:postgresql://${PG_HOSTNAME}/${PG_DB_NAME}?stringtype=unspecified

More info see this https://jdbc.postgresql.org/documentation/94/connect.html

The second way - use operator functions to automatically transform between enums and varchar. We will have to do this for each enum data type example for your enum eye_color.

create or replace function
 eye_color_with_character_varying_equals(_a eye_color, _b character varying)
 returns boolean as
 $$
 select _a = _b::eye_color
 $$
 language sql
 immutable strict;
create operator = (
 leftarg = eye_color,
 rightarg = character varying,
 procedure = eye_color_with_character_varying_equals,
 negator = !=,
 hashes,
 merges
 );
create cast ( character varying as eye_color) with inout as assignment;

The third way - use @Query for special syntax.

Answer 2

As mentioned in https://stackoverflow.com/a/55146310 OTHER type can be used instead of VARCHAR for enums.

For Spring Data JDBC it can be achieved with a custom converter.

@Configuration
public class CustomJdbcConfiguration extends AbstractJdbcConfiguration {
 @Override
 @Nonnull
 protected List<?> userConverters() {
 return List.of(new MyEnumConverter());
 }
 @WritingConverter
 public static class MyEnumConverter implements Converter<MyEnum, JdbcValue> {
 @Override
 public JdbcValue convert(@Nonnull MyEnum source) {
 return JdbcValue.of(source, JDBCType.OTHER);
 }
 }
}

• VariabileAleatoria: I'm using this solution

Answer 3

Currently there is no special support for Postgres enums in Spring Data JDBC.

But as described here: https://stackoverflow.com/a/40356977/66686 Postgres enums would need some special SQL syntax to get manipulated.

This allows for the following possible solutions of the problem:

1. use a VARCHAR instead of an ENUM in the database
2. use a @Query annotated method using the special syntax for the enum. This is only feasible if your aggregate consists only of a single entity.

• gstackoverflow: Is it not github.com/spring-projects/spring-data-jdbc/issues/629 ?