3

I am having a single table in my database and I have added the @Id attribute over the field. As strategy I use GenerationType.IDENTITY. This works fine WHEN the database table is not already populated by rows from a SQL script.

How can I manage to get it to work when the table already has some rows in it? Because it doesn't work when I am trying to insert entities from my application when it is pre-populated.

I am using Derby database for this and eclipselink as implementation.

LuckyLuke
  • 47,771
  • 85
  • 270
  • 434

2 Answers2

3

Use sequence generator that gets its ids from a sequence table. Something like this:

@Id
@GeneratedValue(generator = "yourTableIdGenerator")
@GenericGenerator(name = "yourTableIdGenerator", strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator", parameters = {
                    @Parameter(name = "sequence_name", value = "your_table_id_seq"),
                    @Parameter(name = "optimizer", value = "hilo"),
                    @Parameter(name = "initial_value", value = "1000"),
                    @Parameter(name = "increment_size", value = "10") }
                )
@Column(name = "your_table_id", length = 15)
public Long getId() {
    return id;
}

Set the initial_value to a value larger than the maximum id of the rows populated from the script.

From the java 6 ee api: http://download.oracle.com/javaee/6/api/javax/persistence/TableGenerator.html

@TableGenerator(
   name="empGen",
   table="ID_GEN",
   pkColumnName="GEN_KEY",
   valueColumnName="GEN_VALUE",
   pkColumnValue="EMP_ID",
   initialValue = 1000,
   allocationSize=1)
@Id
@GeneratedValue(strategy=TABLE, generator="empGen")
int id;
dcernahoschi
  • 14,968
  • 5
  • 37
  • 59
2

I will assume that your error is because the IDENTITY that you has created for you table starts (by example) in "1" and you have already populated the table with a row with the id "1" . So sure exist a PK Constraint violation. If this is the scenario I can suggest:

  1. Check the number of rows for prepopulate the data. By example 3500.
  2. Create the identity column in a superior number for avoid PK constraint violation, by example the following table starts the identity in 4000:

This should work in Derby DB

CREATE TABLE MAPS    (
    MAP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 4000, INCREMENT BY 1),
    MAP_NAME VARCHAR(24) NOT NULL,
    REGION VARCHAR(26),
    AREA DECIMAL(8,4) NOT NULL,
    PHOTO_FORMAT VARCHAR(26) NOT NULL,
    PICTURE BLOB(102400),
    UNIQUE (MAP_ID, MAP_NAME)
)
Ernesto Campohermoso
  • 7,213
  • 1
  • 40
  • 51
  • Yes, that is the error I get. I don't know if I understood you right: Should I check the count manually or in code? Of can I make this "offset" in JPA/eclipselink – LuckyLuke Nov 14 '11 at 20:46
  • You can check it manually. If you don't know the number of rows you can start the identity in a highest number by example 1000000. If you need to populate data after the table has been created My recommendation is that you use the identity so you insert null on the ID field. If you need to keep the ID of the rows that will be inserted in the database you maybe need a new column – Ernesto Campohermoso Nov 14 '11 at 20:57