How to Query Using an Enum Parameter Mapped as Ordinal Using JPA and Hibernate

How to query using an Enum parameter mapped as ORDINAL using JPA and Hibernate

As you set your enum as ordinal, then in query you should use ordinal. Example;

@Transactional
public List<Shop> findByType(String type) {
    return sessionFactory.getCurrentSession().createQuery("from Shop where type=" + ShopType.valueOf(type).ordinal()).list();
}

If you change @Enumerated(EnumType.STRING), then your query will look like;

@Transactional
public List<Shop> findByType(String type) {
    return sessionFactory.getCurrentSession().createQuery("from Shop where type=" + ShopType.valueOf(type).name()).list();
}

ShopType.valueOf(type), This will work only if string type is same as enum name.
Also if your label is same as enum name, then you don't need label.
ShopType.VANS.name()is equals"VANS" and name() method is final, you can be sure that can't be overridden.

Hibernate mapping between PostgreSQL enum and Java enum

HQL

Aliasing correctly and using the qualified property name was the first part of the solution.

<query name="getAllMoves">
    <![CDATA[
        from Move as move
        where move.directionToMove = :direction
    ]]>
</query>

Hibernate mapping

@Enumerated(EnumType.STRING) still didn't work, so a custom UserType was necessary. The key was to correctly override nullSafeSet like in this answer https://stackoverflow.com/a/7614642/1090474 and similar implementations from the web.

@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
    if (value == null) {
        st.setNull(index, Types.VARCHAR);
    }
    else {
        st.setObject(index, ((Enum) value).name(), Types.OTHER);
    }
}

Detour

implements ParameterizedType wasn't cooperating:

org.hibernate.MappingException: type is not parameterized: full.path.to.PGEnumUserType

so I wasn't able to annotate the enum property like this:

@Type(type = "full.path.to.PGEnumUserType",
        parameters = {
                @Parameter(name = "enumClass", value = "full.path.to.Move$Direction")
        }
)

Instead, I declared the class like so:

public class PGEnumUserType<E extends Enum<E>> implements UserType

with a constructor:

public PGEnumUserType(Class<E> enumClass) {
    this.enumClass = enumClass;
}

which, unfortunately, means any other enum property similarly mapped will need a class like this:

public class HibernateDirectionUserType extends PGEnumUserType<Direction> {
    public HibernateDirectionUserType() {
        super(Direction.class);
    }
}

Annotation

Annotate the property and you're done.

@Column(name = "directiontomove", nullable = false)
@Type(type = "full.path.to.HibernateDirectionUserType")
private Direction directionToMove;

Other notes

• EnhancedUserType and the three methods it wants implemented

  public String objectToSQLString(Object value)
  public String toXMLString(Object value)
  public String objectToSQLString(Object value)
  

  didn't make any difference I could see, so I stuck with implements UserType.

• Depending on how you're using the class, it might not be strictly necessary to make it postgres-specific by overriding nullSafeGet in the way the two linked solutions did.
• If you're willing to give up the postgres enum, you can make the column text and the original code will work without extra work.

Map enum in JPA with fixed values?

For versions earlier than JPA 2.1, JPA provides only two ways to deal with enums, by their name or by their ordinal. And the standard JPA doesn't support custom types. So:

• If you want to do custom type conversions, you'll have to use a provider extension (with Hibernate UserType, EclipseLink Converter, etc). (the second solution). ~or~
• You'll have to use the @PrePersist and @PostLoad trick (the first solution). ~or~
• Annotate getter and setter taking and returning the int value ~or~
• Use an integer attribute at the entity level and perform a translation in getters and setters.

I'll illustrate the latest option (this is a basic implementation, tweak it as required):

@Entity
@Table(name = "AUTHORITY_")
public class Authority implements Serializable {

    public enum Right {
        READ(100), WRITE(200), EDITOR (300);

        private int value;

        Right(int value) { this.value = value; }    

        public int getValue() { return value; }

        public static Right parse(int id) {
            Right right = null; // Default
            for (Right item : Right.values()) {
                if (item.getValue()==id) {
                    right = item;
                    break;
                }
            }
            return right;
        }

    };

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "AUTHORITY_ID")
    private Long id;

    @Column(name = "RIGHT_ID")
    private int rightId;

    public Right getRight () {
        return Right.parse(this.rightId);
    }

    public void setRight(Right right) {
        this.rightId = right.getValue();
    }

}

How to map custom enumerated integer ordinals with hibernate?

You could define your own UserType which defines how Hibernate should map those enums.

Note that the ordinal defines the index of the enum value and thus FAILED would have the ordinal 2. To map the enum using its properties your need a UserType implementation.

Some links:

• https://community.jboss.org/wiki/UserTypeForPersistingAnEnumWithAVARCHARColumn
• http://javadata.blogspot.de/2011/07/hibernate-and-enum-handling.html (look at the "Paramterized Enumeration in Hibernate" section)

Mapping enum to string in hibernate

Yes, is possible. It should be:

@Enumerated(EnumType.STRING)
@Column(name = "category_type")
private CategoryType categoryType;

Query enum in hibernate throws DataException: Bad value for type int : t

I have figured out the problem. In our case the production server has multiple schemas and a database update failed on one of them, resulting in a field having a different data type then its hibernate mapping.

In case anyone gets here looking for an answer to the same error, try the following:

• For enums, verify that the datatype in the db is integer for mapping @Enumerated(EnumType.ORDINAL) (or varchar for EnumType.STRING)
• Verify that all other data types of that table match the hibernate mapping
• Verify that the values inside the db do not exceed the number of items in the enum
• Verify data in all schemas (if you are using multiple)
• If you are experiencing this error on one server and not on another, check for the following differences: jdk version, hibernate-core version, database driver version. If they are all the same, try dumping the database from one server to another

Mapping enum to a table with hibernate annotation

Hibernate is kind of terrible at Enums. It's a strange failing of an otherwise pretty good ORM. The "easiest" way to get around it is to declare your Enum a custom hibernate type. Fortunately, Hibernate wrote an example implementation which you can crib verbatim into your app:

http://www.hibernate.org/265.html

They even include instructions on how to use it. This is the pattern I use whenever I end up with the need to persist enums.

Hibernate Search/Lucene range query with enum field doesn't return any results

Enums are indexes as strings by default, so the range you asked for is actually "every skill level whose name is alphabetically after the word 'ADVANCED'".

Thus I'd expect this to match everything instead; maybe you forgot to reindex your data after you changed your mapping?

Regardless... If you want to index your enums as their ordinal, implement a custom bridge:

public class EnumAsIntegerBridge implements MetadataProvidingFieldBridge {
    @Override
    public void configureFieldMetadata(String name, FieldMetadataBuilder builder) {
        builder.field( name, FieldType.INTEGER );
    }

    @Override
    public void set(String name, Object value, Document document, LuceneOptions luceneOptions) {
        Enum<?> enum = (Enum<?>) value;

        if ( enum != null ) {
            luceneOptions.addNumericFieldToDocument( name, enum.ordinal(), document );
        }
    }
}

Then use it in your mapping:

public class UserSkill {
    @Enumerated
    @Field(bridge = @FieldBridge(impl = EnumAsIntegerBridge.class))
    private UserSkillLevel level;
}

public class User {
    @IndexedEmbedded
    private Set<UserSkill> skills = new HashSet<>();
}

And pass the ordinal instead of the enum to the query:

var q = queryBuilder.range().onField("skills.level").above(UserSkillLevel.ADVANCED.ordinal()).createQuery();

Spring Boot JPA native query for enum values

You cannot use enums and SQL. You have to pass the parameter as String:

@Repository
public interface MealRepository extends JpaRepository<Meal, Long> {

    @Query(value ="select * from meal m where m.meal_type = ?1", nativeQuery = true)
    List<Meal> findMealByType(String mealType);

---

Related Topics