How to create composite primary key with one auto generated value?

Question

I am trying to map entity with my existing database table which is having two primary keys.

Out of two keys, one primary key is auto generated.

I am using `Spring Boot`, `JPA`, `Hibernate` and `MySQL`. I have used `@IdClass` to map with the composite primary class (with public constructor, setter/getters, same property names, equals and hash code methods).

`org.springframework.data.repository.CrudRepository` save method to save the entity.

Code snippet below.

@Entity
@Table(name = "data")
@IdClass(DataKey.class)
public class DeviceData {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private BigInteger id;
    @Column(name="name")
    private String name;
    @Id
    @Column(name="device_id")
    private int deviceId;
    getters/setters
}
public class DataKey implements Serializable {
    private static final long serialVersionUID = 1L;
    private BigInteger id;
    private int deviceId;
    //setter/getters
    public DataKey() {
    }
    public DataKey(BigInteger id, int deviceId) {
        this.id = id;
        this.deviceId = deviceId;
    }
    public int hashCode() {
        return Objects.hash(id, deviceId);
    }
    public boolean equals(Object obj) {
        if (obj == this)
            return true;
        if (!(obj instanceof DataKey))
            return false;
        DataKey dk = (DataKey) obj;
        return dk.id.equals(this.id) && dk.deviceId == (this.deviceId);}
}
I am using org.springframework.data.repository.CrudRepository save method for persisting the entity.

DeviceData data=new DeviceData();

data.setName("device1"); data.setDeviceId("1123");

dao.save(data); //dao extending crudrepository interface.

But I am getting below errors :

    org.springframework.orm.jpa.JpaSystemException: Could not set field 
    value [POST_INSERT_INDICATOR] value by reflection.[class DataKey.id]
    setter of DataKey.id; nested exception is
    org.hibernate.PropertyAccessException: Could not set field value
    [POST_INSERT_INDICATOR] value by reflection : [class class DataKey.id]
    setter of class DataKey.id.

Caused by: java.lang.IllegalArgumentException: Can not set java.math.BigInteger field DataKey.id to org.hibernate.id.IdentifierGeneratorHelper$2

Do you need a new id every time you create an object or do you want to control it by yourself? — kaba713, Jul 18 '18 at 07:02
`@Id` does not need to match datbase PK: See https://stackoverflow.com/a/46370549/1356423 — Alan Hay, Jul 18 '18 at 09:12
@AlanHay Thanks for your replay.Yes It has to match with db pk,else there is issue while reading the data from db.For some entries we are setting the id(same id for multiple rows with different deviceid) and device id manually.So,while fetching all the device id's ,hibernate returning all the rows with same device id. — user2247791, Jul 18 '18 at 18:02
I am running into the exact same problem. Anyone know if this is a known issue by the Hibernate dev team? — RedTailedHawk, Jul 01 '21 at 21:36

score 1 · Answer 1 · answered Nov 28 '20 at 06:40

I have achieved this by creating a one orderId class with parameters -- Mysql
@Getter
@Setter
public class OrderId implements Serializable {

    private int oId;
    private int customerId;
    private int productId;

    @Override
    public boolean equals(Object o) {
        if (this == o)
            return true;
        if (o == null || getClass() != o.getClass())
            return false;
        OrderId orderId = (OrderId) o;
        return oId == orderId.oId && customerId == orderId.customerId && productId == orderId.productId;
    }

    @Override
    public int hashCode() {
        return Objects.hash(oId, customerId, productId);

    }

the class OrderData with orderid as auto generated field in mysql
@Entity
@Table(name = "OrderData")
@Getter
@Setter
@NoArgsConstructor
@IdClass(OrderId.class)
public class OrderData{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int oId;
    @Id
    private int customerId;
    @Id
    private int productId;
    private int quanity;
    private String paymentMode;
    private String orderStatus;

}

Is this true? What does the `with parameters` mean? – Jin Kwon Dec 22 '22 at 08:28 — Jin Kwon, Dec 22 '22 at 08:28

RedTailedHawk · Answer 2 · 2021-07-01T23:51:33.680

I was able to solve it using a sequence generator.

    @Id
    @SequenceGenerator(name="my_seq", sequenceName="my_db_table_seq", allocationSize=1)
    @GeneratedValue(generator = "my_seq")
    @Column(name = "id", updatable = false, nullable = false)
    private BigInteger id

In my case I'm using Postgres, and my tables that have SERIAL as a column data type also have a corresponding entry in the pg_catalog.pg_sequences table (e.g. my_db_table_seq in my example above).

If you don't specify a particular named sequence generator (and just use @GeneratedValue(strategy = GenerationType.SEQUENCE) on your @Id column), it looks like Hibernate defaults to one called hibernate_sequence, and I have seen some suggestions to run the db command CREATE SEQUENCE hibernate_sequence START 1 to get that created in the database. But this would use the same sequence for every table that inserts rows via Hibernate, so probably not a good idea. Best to use the sequence that gets created in the database for that table and then be explicit with the @SequenceGenerator annotation.

There does seem to be a problem using @GeneratedValue(strategy = GenerationType.IDENTITY) in a composite key on an entity that has @IdClass, so would be nice if Hibernate could support this.

But looks like the sequence generator is a valid workaround, so that's what I'm going with.

score 0 · Answer 3 · answered Jul 18 '18 at 05:06

0

I had also tried the same in one of my projects but eventually I found out that it is not possible to auto generate one of the key attributes(in case of composite primary key) in JPA. So, I did it separately by invoking the sequence with the help of query.

answered Jul 18 '18 at 05:06

codeLover

2,571
1
11
27

Thanks for your reply.How did u generate the sequence?Could you please explain.I suspect ,sequence may get duplicates in concurrent requests. – user2247791 Jul 18 '18 at 06:42
You need to create a sequence in database and then use native query to fetch the next value. Like: select nextval('my_sequence'); The next value fetched can be set in your id attribute – codeLover Jul 18 '18 at 07:10
Of course it is possible to generate one component of a multi-field PK – Jul 18 '18 at 07:47
@BillyFrost Thanks for your reply.Could you please explain how can I implement that. – user2247791 Jul 18 '18 at 16:31
@user2247791 When you update your question with your actual code then there is basis for comment. – Jul 18 '18 at 17:09

How to create composite primary key with one auto generated value?

3 Answers3