Enum - ReWrite or Cast Expression Error

Support
,
#1

Probably a nube quesiotn, but after 4 hours of this I’m furious (I’m primarily a dba). I’m trying to setup a new project with jmix and postgresql. I’ve made the connections but I cannot define a working datatype for my enum’s. I’ve followed the instructions on the jmix udemy course and documentation but I will always receive the same error:

PSQLException: ERROR: column "status_staffing" is of type status_staffing but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 40

The enum is mapped somewhat correctly, as it displays data correctly. It is reading my database inputs, but I cannot update an input at all.

To set this up, I used the entity creator and created “Enumeration” of type “string” setup my values and id and then added the attribute to my table’s jpa entity .java file as a “Attribute Type” of “ENUM” and a “Type” of “RecipientStatusStaffing” and set my column.

The sql used to setup the column’s data type is “create type status_staffing as enum (‘need_staff’, ‘staffed’, ‘discharged’);” so if I’m following the documentation correctly this should all connect at this point.

Any help on what I’m doing or interpreting wrong would be appreciated.

Thank you,
Oran

Enum File - RecipientStatusStaffing.java

package com.company.alirem.entity;

import io.jmix.core.metamodel.datatype.EnumClass;

import org.springframework.lang.Nullable;


public enum RecipientStatusStaffing implements EnumClass<String> {

    NEED_STAFF("need_staff"),
    STAFFED("staffed"),
    DISCHARGED("discharged");

    private final String id;

    RecipientStatusStaffing(String id) {
        this.id = id;
    }

    public String getId() {
        return id;
    }

    @Nullable
    public static RecipientStatusStaffing fromId(String id) {
        for (RecipientStatusStaffing at : RecipientStatusStaffing.values()) {
            if (at.getId().equals(id)) {
                return at;
            }
        }
        return null;
    }
}

Table’s JPA File - Recipient.java

package com.company.alirem.entity;

import io.jmix.core.entity.annotation.JmixGeneratedValue;
import io.jmix.core.metamodel.annotation.JmixEntity;
import io.jmix.data.DdlGeneration;
import jakarta.persistence.*;

import java.util.UUID;

@DdlGeneration(value = DdlGeneration.DbScriptGenerationMode.DISABLED,
        unmappedColumns = {"pets", "smoking", "staff_gender_needs", "status_admission", "hours_wanted"})
@JmixEntity
@Table(name = "recipient", indexes = {
        @Index(name = "IDX_RECIPIENT_CM", columnList = "uuid")
})
@Entity
public class Recipient {
    @JmixGeneratedValue
    @Column(name = "uuid", nullable = false)
    @Id
    private UUID id;

    @JoinColumn(name = "ref_cm", unique = true, referencedColumnName = "uuid")
    @ManyToOne(fetch = FetchType.LAZY)
    private Cm refCm;

    @Column(name = "caretap_id", length = 8)
    private String caretap;

    @Column(name = "caretap_password", length = 16)
    private String caretapPassword;

    @Column(name = "caretap_username", length = 32)
    private String caretapUsername;

    @Column(name = "generalnote", length = 100)
    private String generalnote;

    @Column(name = "pmi", length = 8)
    private String pmi;

    @JoinColumn(name = "ref_user", unique = true)
    @ManyToOne(fetch = FetchType.LAZY)
    private User refUser;

    @Column(name = "url_caretap", length = 50)
    private String urlCaretap;

    @Column(name = "url_googledrive", length = 100)
    private String urlGoogledrive;

    @Column(name = "status_staffing")
    private String statusStaffing;

    public RecipientStatusStaffing getStatusStaffing() {
        return statusStaffing == null ? null : RecipientStatusStaffing.fromId(statusStaffing);
    }

    public void setStatusStaffing(RecipientStatusStaffing statusStaffing) {
        this.statusStaffing = statusStaffing == null ? null : statusStaffing.getId();
    }

    public Cm getRefCm() {
        return refCm;
    }

    public void setRefCm(Cm refCm) {
        this.refCm = refCm;
    }

    public String getUrlGoogledrive() {
        return urlGoogledrive;
    }

    public void setUrlGoogledrive(String urlGoogledrive) {
        this.urlGoogledrive = urlGoogledrive;
    }

    public String getUrlCaretap() {
        return urlCaretap;
    }

    public void setUrlCaretap(String urlCaretap) {
        this.urlCaretap = urlCaretap;
    }

    public User getRefUser() {
        return refUser;
    }

    public void setRefUser(User refUser) {
        this.refUser = refUser;
    }

    public String getPmi() {
        return pmi;
    }

    public void setPmi(String pmi) {
        this.pmi = pmi;
    }

    public String getGeneralnote() {
        return generalnote;
    }

    public void setGeneralnote(String generalnote) {
        this.generalnote = generalnote;
    }

    public String getCaretapUsername() {
        return caretapUsername;
    }

    public void setCaretapUsername(String caretapUsername) {
        this.caretapUsername = caretapUsername;
    }

    public String getCaretapPassword() {
        return caretapPassword;
    }

    public void setCaretapPassword(String caretapPassword) {
        this.caretapPassword = caretapPassword;
    }

    public String getCaretap() {
        return caretap;
    }

    public void setCaretap(String caretap) {
        this.caretap = caretap;
    }

    public UUID getId() {
        return id;
    }

    public void setId(UUID id) {
        this.id = id;
    }
}

Like I said I’m furious not understanding this and getting nowhere for a whole day. Any help is appreciated.

Thank you,
Oran

#3

Hi Oran,

This PostgreSQL enum type requires additional conversion to the regular character type used by Jmix when you define an enum in your application data model.

But it seems there is an easy solution described here - you just define an additional connection parameter stringtype=unspecified. So your database connection should look like this:
image

In my experiment, the standard Jmix enum with String ids works well with the Postgres enum.

Regards,
Konstantin

1 Like
#5

Thank you so much, this worked instantly and as a DBA this parameter makes me feel better knowing the db is directly handling the string parameters (because I know how to set those correctly lol). For anyone who finds this in the future, the other answer I got working was casting types, a complete breakdown of that can be found here Convert Between Java and PostreSQL Enums .

Thank you so much, words cannot describe how annoyed I was working on this yesterday.

1 Like