Creating a composite Primary Key with a unique string and sequential, corresponding number

Question

I am new to Hibernate, and am struggling with a way to create a composite Primary Key that uses both a unique string an auto-incrementing (sequentially, according to the unique string) long. I have found some resources here and here, but am still having trouble.

I am using Java 8, Hibernate 5, Spring Boot 1.5, and Spring JDBC 5 and MySQL connector 6, with MySQL 5.7

Problem

I am trying to create an Primary Key for an entity that maps to a ticketing table. I want the Primary Key to be something like PROJ-1 which I have seen in a lot of places and I like the convention.

Ideally, I would like there to be two parts to the PK. I want a unique, project code like PROJ and a ticket number that increments with that project code, each ticket should be sequential: PROJ-1 PROJ-2 PROJ-3 but if I make another project code like TICK then it should start from one: TICK-1 TICK-2 and so on. Instead of having entries like PROJ-1 PROJ-2 PROJ-3 TICK-4 and TICK-5.

I am not sure how to accomplish this with hibernate.

The code I have so far is this.

Entity

UPDATE 2 Made the generated TicketIdentifier unique.

@Entity
@Table(name = "support_ticket")
public class SupportTicket implements Serializable {
    private TicketIdentifier id;
    ... other irrelevant properties

    @EmbeddedId
    @AttributeOverride(name = "id", column = @Column(unique = true, nullable = false))
    public TicketIdentifier getId() {
        return id;
    }

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

    ... other irrelevant getters and setters
}

Embeddable Class

UPDATE Changed ticket number to a non-unique value, per the comments.

UPDATE 2 Made project non-unique, and did it from the ticket-side

@Embeddable
public class TicketIdentifier implements Serializable {
    String projectId;
    Long ticketNum;

    public TicketIdentifier() {}

    public TicketIdentifier(String projectId) {
        this.projectId = projectId;
    }

    @Column(name = "project", nullable = false, length = 10)
    public String getProjectId() {
        return projectId;
    }

    public void setProjectId(String projectId) {
        this.projectId = projectId;
    }

    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "ticket", nullable = false)
    public Long getTicketNum() {
        return ticketNum;
    }

    public void setTicketNum(Long ticketNum) {
        this.ticketNum = ticketNum;
    }
    ... implementing of hashcode and equals
}

Table DDL

CREATE TABLE support_ticket
(
  project         VARCHAR(10)  NOT NULL,
  ticket          BIGINT       NOT NULL,
  ... irrelevant stuff
  PRIMARY KEY (project, ticket),
  CONSTRAINT UK_6qwbkx66syjgp0jcxn16vkqkd
)

But as you can see the ticket is merely a bigint instead of looking more like bigint auto_increment not sure what I am doing wrong here. This is very close to what I think I would like. Except the ticket should auto-increment (as detailed in the "problem" section), and I don't know how (or if this is the right place) to put a dash between the project and ticket column combination.

Additionally, in the below screenshot, I am also unable to do two things: automatically increment the value of ticket and, re-use the same project code. I can see in the DDL that the latter is because project has to be unique, but I want it to be a unique combination only.

UPDATE

I was able to get the combination unique (I think), but am still unable automatically increment the value of ticket the way I want.

Any suggestions on how to do this in Java and/or Hibernate combos would be much appreciated. Thank you ahead of time!

Answer 1

One way to achieve this is to use a custom generation strategy for your primary key. (Note that @GeneratedValue can only be used with @Id annotation)

To implement a custom generation strategy you need to create a class that implements the IdentifierGenerator interface or extends one of the existing generators, for example, SequenceStyleGenerator. Then you can define it using @GenericGenerator annotation:

@Id
@Column(name = "ticket", nullable = false, insertable = false, updatable = false)
@GenericGenerator(
    name = "custom-sequence",
    strategy = "your.package.CustomGenerator"
)
@GeneratedValue(generator = "custom-sequence")
public String getTicketNum() {
    return ticketNum;
}

To demonstrate by example, I've modified your SupportTicket entity in the following way:

@Entity
@Table(name = "support_ticket")
public class SupportTicket implements Serializable {

    private String projectId;
    private String ticketNum;

    public SupportTicket() {
    }

    public SupportTicket(String projectId) {
        this.projectId = projectId;
    }

    @Column(name = "project", unique = true, nullable = false, length = 10)
    public String getProjectId() {
        return projectId;
    }

    public void setProjectId(String projectId) {
        this.projectId = projectId;
    }

    @Id
    @Column(name = "ticket", nullable = false, insertable = false, updatable = false)
    @GenericGenerator(
            name = "custom-sequence",
            strategy = "your.package.CustomGenerator"
    )
    @GeneratedValue(generator = "custom-sequence")
    public String getTicketNum() {
        return ticketNum;
    }

    public void setTicketNum(String ticketNum) {
        this.ticketNum = ticketNum;
    }
}

The ticket column now is a primary key that will hold values like PROJ-1 and TICK-1 generated by the CustomGenerator. So I've changed the column type to VARCHAR.

The last step is to implement the CustomGenerator. As you are using MySQL, and it doesn't have sequences, you have to emulate them.

public class CustomGenerator implements IdentifierGenerator {

    @Override
    public synchronized Serializable generate(SessionImplementor session, Object obj) {
        if (obj instanceof SupportTicket) {
            String sequenceName = ((SupportTicket) obj).getProjectId();
            Serializable result = null;
            try {
                Connection c = session.connection();
                Statement s = c.createStatement();
                s.execute("CREATE TABLE IF NOT EXISTS sequences\n" +
                        "     (\n" +
                        "         name VARCHAR(70) NOT NULL UNIQUE,\n" +
                        "         next INT NOT NULL\n" +
                        "     );");
                s.execute("INSERT INTO sequences (name, next)\n" +
                        "VALUES ('" + sequenceName + "', @current := 1)\n" +
                        "ON DUPLICATE KEY UPDATE \n" +
                        "next = @current := next + 1");
                ResultSet resultSet = s.executeQuery("SELECT @current");
                if (resultSet.next()) {
                    int nextValue = resultSet.getInt(1);
                    result = sequenceName + "-" + nextValue;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return result;
        } else {
            return null;
        }
    }
}

This custom generator will create a sequences table where each row corresponds to a specific projectId and use it to increment the ticketNum field.

UPDATE: It is also possible to create an entity for the sequences table and use the hibernate session to manage it:

@Override
public synchronized Serializable generate(SessionImplementor session, Object obj) {
    if (obj instanceof SupportTicket) {
        Session s = (Session) session;
        String sequenceName = ((SupportTicket) obj).getProjectId();
        Sequences sequences = (Sequences) s.get(Sequences.class, sequenceName);
        long next = 1;
        if (sequences == null) {
            sequences = new Sequences(sequenceName, next);
        } else {
            next = sequences.getNext() + 1;
            sequences.setNext(next);
        }
        s.saveOrUpdate(sequences);
        return sequenceName + "-" + next;
    } else {
        return null;
    }
}

Don't forget to add spring.jpa.properties.hibernate.hbm2ddl.auto=update to your properties to force hibernate to create a new table if it doesn't exist.