How to set the column order of a composite primary key using JPA/Hibernate

Question

I'm having trouble with the ordering of the columns in my composite primary key. I have a table that contains the following:

@Embeddable
public class MessageInfo implements Serializable {

    private byte loc;
    private long epochtime;

    @Column(name = "loc")
    public byte getLoc() {
        return loc;
    }    

    @Column(name = "epochtime")
    public long getEpochtime() {
        return epochtime;
    }
}

It is used in this mapping:

@MappedSuperclass
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public abstract class AbstractMessage implements Message {

    private MessageInfo info;
    private int blah;

    @EmbeddedId
    public MessageInfo getInfo() {
        return info;
    }
}

When I subclass AbstractMessage with a concrete @Table class hibernate creates the database and table with no errors. The problem is that hibernate is generating the composite primary key with the columns in the reverse order of what I would like.

CREATE TABLE  `mydb`.`concrete_table` (
  `epochtime` bigint(20) NOT NULL,
  `loc` tinyint(4) NOT NULL,
  `blah` smallint(6) DEFAULT NULL,
  `foo` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`epochtime`,`loc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want the primary key to be

PRIMARY KEY (`loc`,`epochtime`)

Since I know that I will have a maximum of 10 loc's, but many epochtimes for each loc.

Any help would be appreciated =)

Militant

Answer 1

I really don't think there is a way to do this. All I can do is suggest you use the SQL create statement you have (change it to have the correct order) and run it manually in production.

In tests let Hibernate do its thing.

Koa

Yep, that's what we're doing now. I was hoping that Hibernate might have some facility for changing the default. Thanks for taking the time to respond. – Militant

Answer 2

There is a way to do it. How hibernate chooses to order a set of columns for a primary key is alphabetical by your object names defined.

So for e.g. if you declare your objects like this:

private byte loc;
private long epochtime;

You'll get as you are getting now:

(`epochtime`,`loc`)

But if you rename them for e.g.:

private byte aloc;
private long epochtime;

It would generate it as:

(`aloc`, `epochtime`)

As a comes before e.

That's what I found out when I wanted my clustered index to be in the specific order. I know it is irritating but it's the only way I could find so that I won't have to change my schema manually.

Salerno

I have another experience, where a column date precedes company in a primary key. Even if I let Hibernate regenerate the entire database scheme. – Mccrae

any soution for this? Is there any way to reorder the sequence? – Counterintelligence

Answer 3

In 2024 using Hibernate 6.4.4.Final, you can simply define a unique index on your entity with your primary key fields in the order of your choice. Hibernate then uses this sequence for its primary key constraint when creating the table.

Example:

@Entity
@Table(indexes = {
        @Index(name = "ix_composite_key_entity_second_id_first_id", columnList = "secondId, firstId", unique = true)
})
public class CompositeKeyEntity {

    @Embeddable
    @Data
    public static class Id implements Serializable {
        private UUID firstId;
        private UUID secondId;
    }

    @EmbeddedId
    private final CompositeKeyEntity.Id id = new CompositeKeyEntity.Id();

}

DDL created by Hibernate:

    create table "composite_key_entity" (
        "first_id" uuid not null,
        "second_id" uuid not null,
        primary key ("second_id", "first_id")
    )

Chaldron