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

17,717

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);
Share:
17,717

Related videos on Youtube

Ajay Takur
Author by

Ajay Takur

I'm passionate about developing, my aim is to work with great client thinkers, in a great tech team, making great products. I also play an active part in the developer community - something I want to keep doing. I love exploring new tools and technologies, as well as working with the established and well-practiced. I have a keen interest in sharing knowledge, so outside of work did contribute to various forums and collaborate with tech persons across the globe. I currently work mostly with Spring, Python, Angular JS, Spring Boot, Struts, Hibernate, MongoDB, PostgresDB, OracleDB, Windows Development, IOS Development, Android development. I Do pet projects. Push them to GitHub with an elegant readme. Follow all the best practices. Do not write messy code just because it's your pet project. Create a detailed portfolio out of these projects. I Contribute to open source projects. Even little things like improving a sentence in the documentation or reporting a new bug are very important for me. Network!!!! I Go to tech meet-ups, events, conferences. Actively participate in online tech communities such as SOF

Updated on October 20, 2022

Comments

  • Ajay Takur
    Ajay Takur 3 months

    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=?
    
    • Ashish about 6 years
      it has to be acttype=? and in prepared statement cast it to enum
    • Ajay Takur
      Ajay Takur about 6 years
      @ Ashish I did try with your solution but i got org.postgresql.util.PSQLException: ERROR: type "enum" does not exist
  • Martin Vysny almost 4 years
    Thank you for excellent explanation. However I believe it is a bug in Postgres JDBC driver: github.com/pgjdbc/pgjdbc/issues/1420
  • zolee
    zolee 11 months
    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.