Convert between Java enums and PostgreSQL enums
PostgreSQL allows you to create enum types using the following syntax:
1 | CREATE TYPE animal_type AS ENUM('DOG', 'CAT', 'SQUIRREL'); |
You can now use ‘animal’ as a datatype in your tables, for example:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | INSERT INTO pet (pet_id, pet_type, name) VALUES (?, CAST(? AS animal_type), ?);--orINSERT 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:
1 2 3 | stmt.setInt(1, 1);stmt.setString(2, AnimalType.DOG.toString());stmt.setString(3, 'Rex'); |
Retrieving the enum from a SELECT statement looks like this:
1 | 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.
Also, FYI, to view the set of values in a given Postgres enum type, you can use the following SQL query:
1 2 | SELECT enumlabel FROM pg_enum WHERE enumtypid = 'your_enum'::regtype ORDER BY oid; |
While this looks great for regular Java, I’m using JPA and was relying upon EntityManager simply persisting my entities but it throws exception:
@Entity
@Table(name=”myentity”)
public class MyEntity {
@Id
@SequenceGenerator(name = “entitySequence”, sequenceName = “myentity_seq”, allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = “entitySequence”)
private int id;
private int age;
@Enumerated(EnumType.STRING)
@Column(name=”gender_type”, columnDefinition=”GENDER_ENUM”)
private GenderType gender;
… /* getters and setters */
}
**********************************
@Test
public void test1_Create() {
MyEntity myentity = new MyEntity();
myentity.setAge(20);
myentity.setGender(GenderType.Male);
try {
EntityManager em = JpaUtils.create();
em.getTransaction().begin();
em.persist(myentity);
em.getTransaction().commit();
…
}
**********************************
Caused by: org.postgresql.util.PSQLException: ERROR: column “gender_type” is of type gender_enum but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 57
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
**********************************
JPA v2.0
PG v9.1
PG JDBC 9.1-902
Java 6
Eclipse Juno
Any ideas?
Thx
-Rob