Hibernate and Java and Postgres @Enumerated(value=EnumType.STRING) - Caused by: org.postgresql.util.PSQLException: Bad value for type int

Question

I'm getting an exception with the mapping:

@Enumerated(value=EnumType.STRING) 
public AuthenticationVendor getProvider() {
 return this.provider;
}

Here is the enum

public enum AuthenticationVendor {
 LOCALDB, GOOGLE, FACEBOOK
}

The exception seems to indicate hibernate is using the org.hibernate.type.EnumType$OrdinalEnumValueMapper to get the value based on the stack trace:

Caused by: org.postgresql.util.PSQLException: Bad value for type int : LOCALDB
 at org.postgresql.jdbc.PgResultSet.toInt(PgResultSet.java:2831)
 at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2088)
 at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2502)
 at com.zaxxer.hikari.pool.HikariProxyResultSet.getInt(HikariProxyResultSet.java)
 at org.hibernate.type.EnumType$OrdinalEnumValueMapper.getValue(EnumType.java:337)

This is only a problem with Postgres - when I unit test with H2 database it works fine. Here is the table for Postgres (Note that my @Id is indeed a String and works fine):

 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id | character varying(255) | | not null | | extended | |
 created | timestamp without time zone | | | | plain | |
 active | boolean | | not null | | plain | |
 password | character varying(255) | | not null | | extended | |
 provider | character varying(255) | | | | extended | |
 salt | bytea | | | | extended | |
 useremailaddress | character varying(255) | | | | extended | |
 userphonenumber | character varying(255) | | | | extended | |
 username | character varying(255) | | not null | | extended | |

Here is the entire entity:

@Entity
@Table(name = "app_user",
 uniqueConstraints = {
 @UniqueConstraint(columnNames = {"username"})
 }
 )
@NamedQueries(
 @NamedQuery(name = "AppUser.findByUsername", query = "SELECT U FROM AppUser U WHERE U.username = :username")
)
public final class AppUser extends BaseEntity {
 static final SecureRandom sr = new SecureRandom();
 String username;
 @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
 String password;
 @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
 byte[] salt;
 AuthenticationVendor provider;
 String userPhoneNumber;
 String userEmailAddress;
 boolean active;
 public AppUser() {
 super();
 provider = AuthenticationVendor.LOCALDB;
 }
 public static byte[] randomSalt() {
 return new BigInteger(130, sr).toString(32).getBytes();
 }
 public static String hashPassword(byte[] salt, String password)
 throws NoSuchAlgorithmException, InvalidKeySpecException {
 KeySpec spec = new PBEKeySpec(password.toCharArray(), salt, 65536, 128);
 SecretKeyFactory f = SecretKeyFactory.getInstance("PBKDF2WithHmacSHA1");
 byte[] hash = f.generateSecret(spec).getEncoded();
 Base64.Encoder enc = Base64.getEncoder();
 return enc.encodeToString(hash);
 }
 public boolean validatePassword(String maybePassword)
 throws Exception {
 try {
 String pw = hashPassword(this.salt, maybePassword);
 return pw.equals(this.getPassword());
 } catch (Exception e) {
 throw new Exception(e);
 }
 }
 public void changePassword(String newPassword)
 throws Exception {
 try {
 this.salt = randomSalt();
 this.setPassword(hashPassword(this.salt, newPassword));
 } catch (Exception e) {
 throw new Exception(e);
 }
 }
 @NotNull
 public String getUsername() {
 return this.username;
 }
 @NotNull
 public String getPassword() {
 return this.password;
 }
 public byte[] getSalt() {
 return this.salt;
 }
 @Enumerated(value=EnumType.STRING)
 public AuthenticationVendor getProvider() {
 return this.provider;
 }
 public String getUserPhoneNumber() {
 return this.userPhoneNumber;
 }
 public String getUserEmailAddress() {
 return this.userEmailAddress;
 }
 public boolean isActive() {
 return this.active;
 }
 public void setUsername(String username) {
 this.username = username;
 }
 public void setPassword(String password) {
 this.password = password;
 }
 public void setSalt(byte[] salt) {
 this.salt = salt;
 }
 public void setProvider(AuthenticationVendor provider) {
 this.provider = provider;
 }
 public void setUserPhoneNumber(String userPhoneNumber) {
 this.userPhoneNumber = userPhoneNumber;
 }
 public void setUserEmailAddress(String userEmailAddress) {
 this.userEmailAddress = userEmailAddress;
 }
 public void setActive(boolean active) {
 this.active = active;
 }
 public boolean equals(Object o) {
 if (o == this)
 return true;
 if (!(o instanceof AppUser))
 return false;
 final AppUser other = (AppUser) o;
 if (!other.canEqual((Object) this))
 return false;
 final Object this$username = this.getUsername();
 final Object other$username = other.getUsername();
 if (this$username == null ? other$username != null : !this$username.equals(other$username))
 return false;
 final Object this$password = this.getPassword();
 final Object other$password = other.getPassword();
 if (this$password == null ? other$password != null : !this$password.equals(other$password))
 return false;
 if (!Arrays.equals(this.getSalt(), other.getSalt()))
 return false;
 final Object this$provider = this.getProvider();
 final Object other$provider = other.getProvider();
 if (this$provider == null ? other$provider != null : !this$provider.equals(other$provider))
 return false;
 final Object this$userPhoneNumber = this.getUserPhoneNumber();
 final Object other$userPhoneNumber = other.getUserPhoneNumber();
 if (this$userPhoneNumber == null ?
 other$userPhoneNumber != null :
 !this$userPhoneNumber.equals(other$userPhoneNumber))
 return false;
 final Object this$userEmailAddress = this.getUserEmailAddress();
 final Object other$userEmailAddress = other.getUserEmailAddress();
 if (this$userEmailAddress == null ?
 other$userEmailAddress != null :
 !this$userEmailAddress.equals(other$userEmailAddress))
 return false;
 if (this.isActive() != other.isActive())
 return false;
 return true;
 }
 public int hashCode() {
 final int PRIME = 59;
 int result = 1;
 final Object $username = this.getUsername();
 result = result * PRIME + ($username == null ? 43 : $username.hashCode());
 final Object $password = this.getPassword();
 result = result * PRIME + ($password == null ? 43 : $password.hashCode());
 result = result * PRIME + Arrays.hashCode(this.getSalt());
 final Object $provider = this.getProvider();
 result = result * PRIME + ($provider == null ? 43 : $provider.hashCode());
 final Object $userPhoneNumber = this.getUserPhoneNumber();
 result = result * PRIME + ($userPhoneNumber == null ? 43 : $userPhoneNumber.hashCode());
 final Object $userEmailAddress = this.getUserEmailAddress();
 result = result * PRIME + ($userEmailAddress == null ? 43 : $userEmailAddress.hashCode());
 result = result * PRIME + (this.isActive() ? 79 : 97);
 return result;
 }
 protected boolean canEqual(Object other) {
 return other instanceof AppUser;
 }
 public String toString() {
 return "AppUser(username=" + this.getUsername() + ", provider=" + this.getProvider() +
 ", userPhoneNumber=" + this.getUserPhoneNumber() + ", userEmailAddress=" +
 this.getUserEmailAddress() + ", active=" + this.isActive() + ")";
 }
}

• Michal: Could you paste the whole entity code? you maybe need you annotation on the attribute, not the getter.
• mikeb: I have tried it on the attribute and on the getter, same result. I did add the entity
• Mạnh Quyết Nguyễn: Do you config by xml or annotation only?
• mikeb: Annotations only
• devlopp: Can you see this solution stackoverflow.com/questions/30060609/…

Answer 1

You need to define a enum type in Postgresql:

create type authentication_vendor as enum ('LOCALDB', 'GOOGLE', 'FACEBOOK');

And to define in your table the provider to authentication_vendor type:

 Column | Type | Collation | Nullable |
 id | character varying(255) | | not null |
 created | timestamp without time zone | | |
 active | boolean | | not null |
 password | character varying(255) | | not null |
 provider | authentication_vendor | | |
 salt | bytea | | |
 useremailaddress | character varying(255) | | |
 userphonenumber | character varying(255) | | |
 username | character varying(255) | | not null |

In your entity you need to define the type on class level and at column level:

@Entity
@Table(name = "app_user",
 uniqueConstraints = {
 @UniqueConstraint(columnNames = {"username"})
 }
 )
@NamedQueries(
 @NamedQuery(name = "AppUser.findByUsername", query = "SELECT U FROM AppUser U WHERE U.username = :username")
)
@TypeDef(name = "pgsql_enum", typeClass = PostgreSQLEnumType.class)
public final class AppUser extends BaseEntity {
 static final SecureRandom sr = new SecureRandom();
 String username;
 @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
 String password;
 @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
 byte[] salt;
 
 @Enumerated(EnumType.STRING)
 @Type(type = "pgsql_enum")
 AuthenticationVendor provider;
 String userPhoneNumber;
 String userEmailAddress;
 boolean active;
 // constructors
 // getters and setters
}