Hi,

I have the following problem with enumerated values and ordering, and can not seem to find an answer. I am trying to use the JPA annotations and queries only.

Hibernate entity manager 3.3.1.GA

I have these two classes:


@Entity
@Table(name="SEVERITY")
public class Severity implements Serializable, Comparable<Severity>{

public enum ImfLevel{
INFO, WARN, SEVERE, CRITICAL;
}

private String severitytext;

@Id
private String severity;

@Enumerated(EnumType.STRING)
@Column(name="SEVERITY", nullable = false, updatable = false)
private ImfLevel level;

@OneToMany(mappedBy="severity")
private Set<Event> events;
.....
}


and

@Entity
@Table(name="EVENT")
public class Event implements Serializable, EventIf {

@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
private long eventid;

@Column(name = "EVENTTIME")
private Timestamp eventtime;


@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="SEVERITY")
private Severity severity;
. . . .
}

I am trying to query for events (and subset of events), and order them by severity, as defined in the enumerated type.

When I build the following query string I get a list ordered alphabetically.

"from Event event order by event.severity", ok so that seems reasonable.

Now I tried: "from Event event order by event.severity.level", but no luck, still ordered alphabetically. I also tried changing the EnumType to ORDINAL, but that did not change anything.

I have even tried:
"from Event event order by event.severity" and implemented Comparable on Severity, ordering it by level. . . . but still no luck.

Please point me to someplace that can point me in the right direction.

Thanks,

Shannon

I think the problem comes from the fact that the enum values are stored as Strings in the database table. In the end, HQL or the Criteria API just produces plain old SQL, so you have to be able to imagine writing a SQL query that will return results in the order you want. Since they are just plain text in the database, alphabetical is the best ordering it can offer.

I'd recommend storing Integer values in the database instead, so you can easily build a query to return Events in the desired order.

I think you are correct. I changed the severity class to be:

@Entity
@Table(name="SEVERITY")
public class Severity implements Serializable{

public enum Level{
INFO, WARN, SEVERE, CRITICAL;
}

private String severitytext;

@Id
private String severity;

@Enumerated(EnumType.ORDINAL)
@Column(name="LEVELID", nullable = false, updatable = false)
private Level level;
......

}

Then I put the ordinals (0-3) into the database and it appears to work as expected.

THANKS!!

I think you are correct. I changed the severity class to be:

@Entity
@Table(name="SEVERITY")
public class Severity implements Serializable{

public enum Level{
INFO, WARN, SEVERE, CRITICAL;
}

private String severitytext;

@Id
private String severity;

@Enumerated(EnumType.ORDINAL)
@Column(name="LEVELID", nullable = false, updatable = false)
private Level level;
......

}

Then I put the ordinals (0-3) into the database and it appears to work as expected.

THANKS!!