Ways to Save Enums in Database

public enum Suit { Spade, Heart, Diamond, Club }

Suit theSuit = Suit.Heart;

szQuery = "INSERT INTO Customers (Name, Suit) " +
          "VALUES ('Ian Boyd', %s)".format(theSuit.name());

Suit theSuit = Suit.valueOf(reader["Suit"]);

Name          Suit
------------  ----
Kylie Guénin  2
Ian Boyd      1

Name          Suit
------------  -------
Kylie Guénin  Diamond
Ian Boyd      Heart

public enum Suit { Unknown, Heart, Club, Diamond, Spade }

public enum Suit { 
      Unknown = 4,
      Heart = 1,
      Club = 3,
      Diamond = 2,
      Spade = 0 }

SELECT Suit FROM Cards
ORDER BY SuitID; --where SuitID is integer value(4,1,3,2,0)

Suit
------
Spade
Heart
Diamond
Club
Unknown

SELECT Suit FROM Cards
ORDER BY CASE SuitID OF
    WHEN 4 THEN 0 --Unknown first
    WHEN 1 THEN 1 --Heart
    WHEN 3 THEN 2 --Club
    WHEN 2 THEN 3 --Diamond
    WHEN 0 THEN 4 --Spade
    ELSE 999 END

SELECT Suit FROM Cards
ORDER BY Suit; --where Suit is an enum name

Suit
-------
Club
Diamond
Heart
Spade
Unknown

SELECT Suit FROM Cards
ORDER BY CASE Suit OF
    WHEN 'Unknown' THEN 0
    WHEN 'Heart'   THEN 1
    WHEN 'Club'    THEN 2
    WHEN 'Diamond' THEN 3
    WHEN 'Space'   THEN 4
    ELSE 999 END

Suit	SuitID	Rank	Color
Unknown	4	0	NULL
Heart	1	1	Red
Club	3	2	Black
Diamond	2	3	Red
Spade	0	4	Black

how to save enum value to DB with Hibernate?

You can add following enumeration, to indicate you want the String representation to be persisted :

@Enumerated(EnumType.STRING)
private ApartmentState apartmentState;

Store ENUM value into database

In order to store the actual enumeration value in the database, you can do two things.

One, as suggested by @PetarBivolarski, modify the method convertToDatabaseColumn in AttributeConverter and return value.name() instead of value.getStatus(). But please, be aware that in addition you will need to update the convertToEntityAttribute as well to take into account that change:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter
public class BusinessCustomersStatusAttributeConverter
    implements AttributeConverter<BusinessCustomersStatus, String> {

  public String convertToDatabaseColumn( BusinessCustomersStatus value ) {
    if ( value == null ) {
      return null;
    }

    return value.name();
  }

  public BusinessCustomersStatus convertToEntityAttribute( String value ) {
    if ( value == null ) {
      return null;
    }

    return BusinessCustomersStatus.valueOf( value );
  }

}

If you think about it, a more straightforward solution will be just keep the status field as @Enumerated:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
@Entity
@Table(name = "business_customers")
public class BusinessCustomers {

    //...

    @Enumerated(EnumType.STRING)
    @Column(name = "status", length = 20)
    private BusinessCustomersStatus status;

    //...
}

It is in addition more according to the rest of your code.

Regarding your second problem, the application is returning "status":"ACTIVE" because in BusinessCustomersFullDTO you are defining the status field as String and this field receives the result of the mapping process performed by @Mapstruct and BusinessCustomersMapper.

To solve that issue, as I suggested you previously, you can modify your Mapper to handle the desired custom conversion:

import org.mapstruct.Mapper;
import org.mapstruct.Mapping;
import org.mapstruct.Named;
import org.merchant.config.BaseMapperConfig;
import org.merchant.database.entity.BusinessCustomers;
import org.merchant.database.service.businesscustomers.BusinessCustomersStatus;
import org.merchant.dto.businesscustomers.BusinessCustomersFullDTO;

@Mapper(config = BaseMapperConfig.class)
public interface BusinessCustomersMapper {

    @Mapping(source = "status", target = "status", qualifiedByName = "businessCustomersToDTOStatus")
    BusinessCustomersFullDTO toFullDTO(BusinessCustomers businessCustomers);

    @Named("busineessCustomersToDTOStatus")
    public static String businessCustomersToDTOStatus(final BusinessCustomersStatus status) {
        if (status == null) {
            return null;
        }

        return status.getStatus();
    }
}

If you do not prefer this solution, perhaps you can take a different approach: it will consist in the following. The idea is modifying the Jackson serialization and deserialization behavior of BusinessCustomersFullDTO. In fact, in your use case only is necessary to modify the serialization logic.

First, define the status field in BusinessCustomersFullDTO in terms of BusinessCustomersStatus as well:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class BusinessCustomersFullDTO {

    private long id;

    private String name;

    private String businessType;

    private BusinessCustomersStatus status;

    private String description;

    private String country;

    private String address1;
}

To complete the solution, please, perform the following changes in the BusinessCustomersStatus enum:

public enum BusinessCustomersStatus {
    A("active"),
    O("onboarding"),
    NV("not_verified"),
    V("verified"),
    S("suspended"),
    I("inactive");

    private String status;

    BusinessCustomersStatus(String status)
    {
        this.status = status;
    }

    // Define the status field as the enum representation by using @JsonValue
    @JsonValue
    public String getStatus() {
        return status;
    }

    // Use the fromStatus method as @JsonCreator
    @JsonCreator
    public static BusinessCustomersStatus fromStatus(String status) {
        if (StringUtils.isEmpty(status)) {
            return null;
        }

        switch (status) {
            case "active": {
                return A;
            }

            case "onboarding": {
                return O;
            }

            case "not_verified": {
                return NV;
            }

            case "verified": {
                return V;
            }

            case "suspended": {
                return S;
            }

            case "inactive": {
                return I;
            }

            default: {
                throw new UnsupportedOperationException(
                        String.format("Unkhown status: '%s'", status)
                );
            }
        }
    }
}

Note the inclusion of the @JsonValue and @JsonCreator annotations: the later is using for deserialization which seems unnecessary to me in your application, but just in case.

Please, see the relevant documentation of the provided Jackson annotations.

Not save an enum value in the database, why?

If your fields rol and life are Enums you have to declare them as Enums with @Enumerated. There are two options. Default will store the ordinal number. Or you can choose to use the string name to store in the DB. That's the better option in terms of maintainability:

@Enumerated(EnumType.STRING)
@Column(name="rol")
Rol rol;

@Enumerated(EnumType.STRING)
@Column(name = "life")
Life life;

Two remarks:

1. When the database field has the same name as the attribute you can omit @Column. And if the table has the same name as the entity this is also true for the @Table annotatoin.

2. Read more about Enums and JPA and if you really should use it in one of my articles: https://72.services/de/should-you-use-enums-with-jpa/

How to save enum field in the database room?

You can make a convert to each enum, like this:

class Converters {

     @TypeConverter
     fun toHealth(value: String) = enumValueOf<Health>(value)

     @TypeConverter
     fun fromHealth(value: Health) = value.name
}

Or if you prefer store it as SQL integer, you can use ordinal too:

class Converters {

    @TypeConverter
    fun toHealth(value: Int) = enumValues<Health>()[value]

    @TypeConverter
    fun fromHealth(value: Health) = value.ordinal
}

Unfortunatally, there is no way to use generics Enum<T> to accomplish this since unbound generics will raise an error Cannot use unbound generics in Type Converters.

Android Room team could seriously add an annotation and a generator for Enums to their kapt compiler.

Finally, annotate a database class, entity class, dao class, dao method, dao method parameter or entity field class with this:

@TypeConverters(Converters::class)

Storing enum values in database

There is no definite design rule (that I know of), but I prefer approach #1.

1. Is the approach I prefer. It's simple, and enums are usually compact enough that I start remembers what the numbers mean.
2. It's more readable, but can get in the way of refactoring or renaming your enumeration values when you want to. You lose some freedom of your code. All of the sudden you need to get a DBA involved (depending on where/how you work) just to change an enumeration value, or suffer with it. Parsing an enum has some performance impact as well since things like Locale come into play, but probably negligible.
3. What problem does that solve? You still have unreadable numbers in a table somewhere, unless you want to add the overhead of a join. But sometimes, this is the correct answer too depending on how the data is used.

EDIT:
Chris in the comments had a good point: If you do go down the numeric approach, you should explicitly assign values so you can re-order them as well. For example:

public enum Foo
{
     Bar = 1,
     Baz = 2,
     Cat = 9,
     //Etc...
}

How to save enum in database as string

I had this problem as far as I remember and honestly I don't know why didn't MS add this feature (NH can do it like since always..).

Any ways, what I usually did is use const strings classes like:

public static class MyEnum
{
    public const string Foo = "Foo";
    public const string Bar = "Bar";
}

public class Client
{

    public string MyVal { get; set; }

    public Client()
    {
        MyVal = MyEnum.Bar;
    }

}

Cons - as simple as can be.

Downsides - you loose type checking (though it could be enforced programmatically).

---

So this time I tried to think of something more ambitious. So I took the concept described by Brian (which has some downsides when e.g. a given enum is used widely across the domain). And well.. I got the following working:

A base component class to store the values:

[ComplexType]
public class DbEnum<TEnum>
{
    public string _ { get; set; }

    public DbEnum()
    {
        _ = default(TEnum).ToString();
    }

    protected DbEnum(TEnum value)
    {
        _ = value.ToString();
    }

    public TEnum ToEnum()
    {
        return _.ToEnum<TEnum>();
    }

    public static implicit operator DbEnum<TEnum>(TEnum value)
    {
        return new DbEnum<TEnum>(value);
    }

    public static implicit operator TEnum(DbEnum<TEnum> value)
    {
        return value.ToEnum();
    }
}

... which would be basically sufficient.. except EF doesn't support generic types...

This means for every enum you have to have something like...

public enum PrivacyLevel
{
    Public,
    Friends,
    Private
}

public class PrivacyLevelEnum : DbEnum<PrivacyLevel>
{
    public PrivacyLevelEnum() : this(default (PrivacyLevel))
    {      
    }

    public PrivacyLevelEnum(PrivacyLevel value) : base(value)
    {
    }

    public static implicit operator PrivacyLevelEnum(PrivacyLevel value)
    {
        return new PrivacyLevelEnum(value);
    }

    public static implicit operator PrivacyLevel(PrivacyLevelEnum value)
    {
        return value.ToEnum();
    }
}

Which gives you some boiler-plate that could be easily generated e.g. using T4 templates.

Which finally ends you up with using:

public class CalendarEntry : Entity
{

    public virtual PrivacyLevelEnum PrivacyLevel { get; set; } = new PrivacyLevelEnum();

}

But since you have implicit conversion in place, class declarations are the only ones to be aware of the helper types.

Best way to store enum values in database - String or Int

Both have advantages. If you store them by their Integer value, then you must be careful editing your enumeration later on in your project. If somehow the integer values for the enumeration elements get redefined, all your data will be corrupted. But it will be the fastest/smallest datatype to use.

If you use the string representation you'll not have the redefined value problem for as long as you don't change the name of the elements in the enumeration. However strings take up more space in your database and are probably a bit more costly in use.

At the end, there's no definitive winner I guess.

Edit

Using the Integer value might be the best way. You can overcome the 'redefined value' problem for the enumeration elements, by setting the value for each element yourself. Indeed a good suggestion from Kevin.

How to insert all enums into database?

From the above mentioned details what I understood is that we have to insert all the enums present in the java file to database.

The possible answer I think would be to iterate over all the enums and insert each of them to database.

EnumSet.allOf(EJobTrackingType.class)
            .forEach(eJob -> ejobRepository.saveAnFlush(eJob));

This is not the actual implementation though but it gives idea how we can proceed.

We can have a class mapping to the values that has to be stored marked as @Entity.
Populate that class with values in Enums with the help of some adapter.
And the entity class can be saved to database.

We can have some attribute to specify an insert or update has to be made. So that we can have some filter during the forEach of Enums.

This job can be ran on the start up of application where a new class which will handle this job can be annotated with @Configuration. If there are too many rows to be updated/inserted there might be a possibility of running this job in a separate thread.

I think this helps.. These are few things I thought of..

---

Related Topics