Home Creating a composite Primary Key with a unique string and sequential, corresponding number
Reply: 0

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

user4151
1#
user4151 Published in May 21, 2018, 10:49 pm

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!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310069 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO