How do I make Java & Postgres enums work together for update?

Question

acttype is an enumcs so you can't insert it as a normal string without casting it to an enumcs INSERT INTO dir_act (actcode,actname,acttype,national_code) VALUES (?,?,?::enumcs,?)

Where as for updating I tried with same typecasting as follows, but it does not worked.

update dir_act set actname=?,acttype=?::enumcs,national_code=? where actcode=?

Solution 1

You can avoid the toString() on the Enum by doing this

stmt.setObject(2,AnimalType.DOG,java.sql.Types.OTHER)

Works for JDBC driver PostgreSQL 42.2.5

Solution 2

From JDBC's point-of-view, just treat the PostgreSQL enum like a string.

Quoting blog Convert between Java enums and PostgreSQL enums:

PostgreSQL allows you to create enum types using the following syntax:

CREATE TYPE animal_type AS ENUM('DOG', 'CAT', 'SQUIRREL');

You can now use ‘animal’ as a datatype in your tables, for example:

create table pet (                         
    pet_id        integer         not null,
    pet_type      animal_type     not null,
    name          varchar(20)     not null
);

In Java, you’d have a corresponding enum type:

public enum AnimalType {
    DOG,
    CAT,
    SQUIRREL;
}

Converting between Java and PostgreSQL enums is straightforward. For example, to insert or update an enum field you could use the CAST syntax in your SQL PreparedStatement:

INSERT INTO pet (pet_id, pet_type, name) VALUES (?, CAST(? AS animal_type), ?);

--or

INSERT INTO pet (pet_id, pet_type, name) VALUES (?, ?::animal_type, ?);

Postgres will also let you insert/update an enum just by passing its value as a string.

Whether casting or not, the Java side is the same. You would set the fields like this:

stmt.setInt(1, 1);
stmt.setString(2, AnimalType.DOG.toString());
stmt.setString(3, 'Rex');

Retrieving the enum from a SELECT statement looks like this:

AnimalType.valueOf(stmt.getString("pet_type"));

Take into consideration that enums are case-sensitive, so any case mismatches between your Postgres enums and Java enums will have to be accounted for. Also note that the PostgreSQL enum type is non-standard SQL, and thus not portable.

Solution 3

You have to define implicit conversion in Postgres along with your type definition, like below:

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

Than you can skip the explicit conversion in insert; so it works also with Spring Data JDBC and other libs that do generate insert query for you.

Solution 4

If you're using SpringJDBC, just provide Types.OTHER as explicit type using JdbcTemplate, for example:

String sql = "INSERT INTO dir_act (actcode,actname,acttype,national_code) VALUES (?,?,?,?)";
Object[] arguments = new Object[]{"code", "name", Enum.Type, "nat_code"};
int[] argumentTypes = new int[]{Types.VARCHAR, Types.VARCHAR, Types.OTHER, Types.VARCHAR};

jdbcTemplate.update(sql, arguments, argumentTypes);

View more solutions

Comments

[Ashish]

it has to be acttype=? and in prepared statement cast it to enum

[Ajay Takur]

@ Ashish I did try with your solution but i got org.postgresql.util.PSQLException: ERROR: type "enum" does not exist

[Martin Vysny]

Thank you for excellent explanation. However I believe it is a bug in Postgres JDBC driver: github.com/pgjdbc/pgjdbc/issues/1420

[zolee]

Great answer as this solves it in the DB level hence it works with any framework. Didn't even know about this auto cast feature of postgres before.