Manually specify the value of a primary key in JPA @GeneratedValue column

Question

I'm having an Entity which has a primary key / id field like the following:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

This works well. I'm using EclipseLink to create the DDL-Schema, and the column is correctly created like so:

`id` bigint(20) NOT NULL AUTO_INCREMENT

However, I've got several entities for which I do want to specify the PK myself (it's a little application that transfers data from an old database to the new one we're building). If I specify the ID for the POJO (using setId(Long id)) and persist it, EclipseLink does not save it (i.e. the record is saved, but the id is auto generated by eclipseLink).

Is there a way to manually specify the value of a column which has a @GeneratedValue ?

Here some thoughts on the issue:

I tried to work around the problem by not using @GeneratedValue at all, but simply manually define the column to be AUTO_INCREMENTed. However this forces me to manually provide an IDs always, since EclipseLink validates the primary key (so it may not be null, zero, or a negative number). The exception message reads that I should specify eclipselink.id_validation, however this does not seem to make any difference (I annotated @PrimaryKey(validation = IdValidation.NONE) but still got the same message).

To clarify: I'm using EclipseLink (2.4.0) as persistence provider and I can't switch away from it (large portions of the project depend on eclipselink specific query hints, annotations, and classes).

---

EDIT (In Response to the answers):

Custom Sequencing: I tried to implement my own sequencing. I tried subclassing DefaultSequence, but EclipseLink will tell me Internal Exception: org.eclipse.persistence.platform.database.MySQLPlatform could not be found. But I've checked: The class is on the classpath.

So I subclassed another class, NativeSequence:

public class MyNativeSequence extends NativeSequence {

    public MyNativeSequence() {
        super();
    }

    public MyNativeSequence(final String name) {
        super(name);
    }


    @Override
    public boolean shouldAlwaysOverrideExistingValue() {
        return false;
    }

    @Override
    public boolean shouldAlwaysOverrideExistingValue(final String seqName) {
        return false;
    }

}

However, what I get is the following:

javax.persistence.RollbackException: Exception [EclipseLink-7197] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.ValidationException
Exception Description: Null or zero primary key encountered in unit of work clone [de.dfv.datenbank.domain.Mitarbeiter[ id=null ]], primary key [null]. Set descriptors IdValidation or the "eclipselink.id-validation" property.
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:102)
    ...
Caused by: Exception [EclipseLink-7197] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.ValidationException
Exception Description: Null or zero primary key encountered in unit of work clone [de.dfv.datenbank.domain.Mitarbeiter[ id=null ]], primary key [null]. Set descriptors IdValidation or the "eclipselink.id-validation" property.
    at org.eclipse.persistence.exceptions.ValidationException.nullPrimaryKeyInUnitOfWorkClone(ValidationException.java:1451)
    ...

(stack trace shortened for clarity). This is the same message which I got before. Shouldn't I subclass NativeSequence? If so, I don't know what to implement for the abstract methods in Sequence or StandardSequence.

It may also be worth noting, that simply subclassing (without overriding any methods) the class works as expected. However, returing false in shouldAlwaysOverrideExistingValue(...) will not generate a single value at all (I stepped through the program and getGeneratedValue() is not called once).

Also, when I insert like 8 entities of a certain kind within a transaction it resulted in 11 records in the database (what the hell?!).

EDIT (2012-09-01): I still do not have a Solution for the problem, Implementing my own sequence did not solve it. What I need is a way to be able to not set an Id explicitly (so it will be auto generated) and to be able to set an Id explicitly (so it will be used for the creation of the record in the database).

I tried to define the column as auto_increment myself and ommit @GeneratedValue, however Validation will kick in and not allow me to save such an entity. If I specify a value != 0 and != zero, mysql will complain for a duplicate primary key.

I'm running out of ideas and options to try. Any? (starting a bounty)

Gymno

Is there a reason you can't just do a standard import via SQL from Old -> New? Once you have completed that you simply need to adjust the Sequence so that you don't overwrite the values. After-all, its an ORM and it is reading the database. – Marine

The old and the new have completely different schemas and the application is used to transform data from the one model into the other (things like one field for first and last name in the old, two fields in the new, etc.). Of course it would be an alternative option to dump the old database, create a new data script and import these; however, the app is almost complete. Besides, I'd really like to know how to achieve this in general. – Gymno

Since you schemas are entirely different, why is there a problem just doing a "clean" import. Aka: build new entities and relationships and persist them? Where I'm leading you is that I think you're barking up a difficult tree. You can, however, remove the sequence, persist everything manually, then start the sequence. – Marine

@GeneratedValue(strategy = GenerationType.AUTO) did the trick in my case. Migrating the old Data first, the existing keys are kept. – Stodder

Answer 1

This works with eclipselink. It will create a seperate table for the sequence, but that shouldn't pose a problem.

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="id", insertable=true, updatable=true, unique=true, nullable=false)
private Long id;

GenerationType.AUTO will choose the ideal generation strategy. Since the field is specified as insertable and updateable, a TABLE generation strategy will be used. This means eclipselink will generate another table holding the current sequence value and generate the sequence itself instead of delegating it to the database. Since the column is declared insertable, if id is null when persisting, eclipselink will generate the id. Otherwise the existing id will be used.

Ildaile

Answer 2

If you use TABLE sequencing, then EclipseLink will allow you to override the value (or SEQUENCE if your database supports this, MySQL does not).

For IDENTITY, I'm not even sure that MySQL will allow you to supply your own Id, you might want to verify this. In general I would never recommend using IDENTITY as it does not support preallocation.

There are a few issues with allowing IDENTITY to provide the id or not. One is that two different insert SQL will need to be generated depending on the id value, as for IDENTITY the id cannot be in the insert at all. You may wish to log a bug to have IDENTITY support user provided ids.

You should still be able to get it working with your own Sequence subclass, or possibly MySQLPlatform subclass. You would set your MySQLPlatform subclass using the "eclipselink.target-database" persistence unit property.

Potamic

Answer 3

Database-centric solution to your problem:

1. Create an auxiliary, nullable column in your table. It will hold your manually assigned ids:

   CREATE TABLE `test_table`
   (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `manual_id` bigint(20) NULL,
     `some_other_field` varchar(200) NOT NULL,
     PRIMARY KEY(id)
   );
   

2. Map this column to a normal field in your Entity:

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
   @Column(name="manual_id")
   private Integer manualId;
   

3. Create a trigger that sets the table id to the manual assigned id if it is not null:

   DELIMITER //
   CREATE TRIGGER `test_table_bi` BEFORE INSERT ON `test_table`
     FOR EACH ROW 
     BEGIN
       IF NEW.`manual_id` IS NOT NULL THEN 
         SET NEW.`id` = NEW.`manual_id`;
       END IF;
   END;//
   DELIMITER;    
   

4. Always use the manualId when you need to assign a custom id. The trigger will do the magic for you:

   testEntiy.setManualId(300);
   entityManager.persist(testEntity);
   

5. After the database import phase, simple remove the trigger, the auxiliary column and it's mapping.

   DROP TRIGGER `test_table_bi`;
   ALTER TABLE `test_table` DROP COLUMN `manual_id`;
   

---

Warning

If you manually specify an id greater than the current AUTO_INCREMENT value, the next generated id will jump to the value of the manually assigned id plus 1, e.g.:

INSERT INTO `test_table` (manual_id, some_other_field) VALUES (50, 'Something');
INSERT INTO `test_table` (manual_id, some_other_field) VALUES (NULL, 'Something else');
INSERT INTO `test_table` (manual_id, some_other_field) VALUES (90, 'Something 2');
INSERT INTO `test_table` (manual_id, some_other_field) VALUES (NULL, 'Something else 2');
INSERT INTO `test_table` (manual_id, some_other_field) VALUES (40, 'Something 3');
INSERT INTO `test_table` (manual_id, some_other_field) VALUES (NULL, 'Something else 3');

Will wield the results:

+----+-----------+------------------+
| id | manual_id | some_other_field |
+----+-----------+------------------+
| 50 |        50 | Something        |
| 51 |      NULL | Something else   |
| 90 |        90 | Something 2      |
| 91 |      NULL | Something else 2 |
| 40 |        40 | Something 3      |
| 92 |      NULL | Something else 3 |
+----+-----------+------------------+

To avoid problems it is highly recommended to set the AUTO_INCREMENT column to start with a number greater than all of the existing ids in your previous database, e.g.:

ALTER TABLE `test_table` AUTO_INCREMENT = 100000;

Titus

This was excellent and helpful. We've implemented this solution on a mySql DB with great success. – Snuggle

Glad it was useful. It has been a while since I wrote this answer. Generally between ORM specific tools (Sequence, SessionCustomizer) and database hacks, the second tends to get things done faster and with less unknown consequences – Titus

Answer 4

I might be missing something obvious, but why not just define another Entity with the same @Table(name=".....") annotation, with the same fields, but make the id not generated? Then you can use that Entity for the code that copies data from the old DB to the new, and the one with the generated Id can be used for normal creates that require id generation.

I can't tell you if it works with EclipseLink, but we're using Hibernate here and it doesn't seem to mind it.

Matusow

Answer 5

Using GenerationType.SEQUENCE with PostgreSQL and EclipseLink worked for me.

1) Change

@GeneratedValue(strategy = GenerationType.IDENTITY) 

by

@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="step_id_seq")
@SequenceGenerator(name="step_id_seq", sequenceName="step_id_seq")

Now, you can call sequence using NativeQuery:

return ((Vector<Integer>) em.createNativeQuery("select nextval('step_id_seq')::int").getSingleResult()).get(0);

and set the returned Id to your Entity before call EntityManager.persist() method.

Hope it's not too late!

Fabyola

Answer 6

Look for Custom Id Generator

http://blog.anorakgirl.co.uk/2009/01/custom-hibernate-sequence-generator-for-id-field/

maybe this could help.

Pianissimo

I'm sorry, but this is Hibernate specific (special annotations and classes from hibernate are being used). I'm using EclipseLink (and I can't switch away from it, since large portions of the project are using eclipselink specific annotations and classes). – Gymno

i think EclipseLink also has something called CustomSequencing. – Pianissimo

thanks for the correct link, however, custom sequencing seems not to work. Can't imagine that this is such a rare use case! – Gymno

@Gymno This should work: post your code. There's no reason a custom sequence won't do the trick. – Marine

Answer 7

My way (MySql) is deactivate GeneratedValue:

@Id
//@GeneratedValue
@Column(unique = true, nullable = false, columnDefinition ="BINARY(16)")
private UUID id;

And add in Entity:

@PrePersist
protected void onCreation() {
    if (id == null) setId(UUID.randomUUID());
}

Now in my code I can do (on service for example):

String clientID = env.getParam("id");
Entity entity = entityRepository.findFirstById(UUID.fromString(clientID));
//is new?
if (entity==null){
  entity = new Entity();
  entity.setId(UUID.fromString(clientID));//set cumstom ID here
}

Entity entityNew = entityRepository.save(entity); //insert
if (entityNew.getId().equals(entity.getId()) ){
  Log.i("OK!")
}

👌

Marsupial

my id is an int, not a uuid. – Gymno

ok.use whatever you want. you can generate an int, a long or bananas – Marsupial

Answer 8

In my case,

I commented out @GeneratedValue annotation line from the @Id field of my Entity

and that let me to set the primary key in the object that I'm persisting

Unharness

but doesn't let you not set it. this I experimented with myself as stated in the question. – Gymno

why do you define it as PK if you don't want to have value for it ?!! – Unharness