0

I have searched on the web before posting this here, but I'm in an important project and I don't have any more time to waste with this. Well, here's the deal:

My tables in the database(SqlServer) are created automatically by Hibernate. So, I have an Entity and this entity was mapped before with Id annotation, like this:

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private int id;

Hibernate created a table dbo.Parcela inside sqlServer, but we had to change the way we generate the id, Because sometimes we receive the id number and we want that id saved on our database. So our Entity now is like this:

@Id
private Integer id;

Things work fine when we run the program for the first time, but we have some customers that already have their databases created with old Id mapping and we cannot create a new table, with the new Id mapping. So when I'm trying to insert a new record I get this message:

SEVERE: Cannot insert explicit value for identity column
in table 'Parcela' when IDENTITY_INSERT is set to OFF.

Any help would be appreciate.

Thanks

Gijs
  • 5,201
  • 1
  • 27
  • 42
Rafael Paz
  • 497
  • 7
  • 22
  • Hi, I think Hibernate can't update this column now. I think you have to turn on the constraint : SET IDENTITY_INSERT dbo.Parcela ON – fego Nov 22 '12 at 13:00
  • In the applications I worked on, whenever we made changes like these we added a script to alter the DB Schema. You should add a script that alters your table (specifically, your id column), in such a way that the script is run whenever someone sets up your new version. This will bring their databases in the correct state. If they already have the column updated, the script won't do any harm. – Raul Rene Nov 22 '12 at 13:39

2 Answers2

1

So you want your surrogate keys generated by the database, except when they were already generated by the customer. How are you going to avoid collisions, if the database wants to set id=12345, but a customer-imported entry with that id already exists?

The short answer to your question is: don't do this. I don't want to go into the old natural key vs surrogate key debate, this has been done already for example here. And google "codd surrogate keys" to learn how to properly use them. All i want to say is: if you use surrogate keys, then have your database generate them, and treat everything from outside as additional lookup key. That's the sane way.

The long answer is: if you really want to do this, and if you really know what you're doing, you can implement your own IdGenerator class. In JPA for example, you could annotate your id:

@Id
@GenericGenerator(name = "customId", strategy = "com.example.CustomIdGenerator", parameters = { @Parameter(name = "sequence", value = "SEQ_IDGENERATOR") })
@GeneratedValue(generator = "customId")
@Column(name = "ID", unique = true, nullable = false)
private Integer id;

Your customIdGenerator would then extend SequenceGenerator:

public class CustomIdGenerator extends SequenceGenerator {

  public Serializable generate(SessionImplementor session, Object obj) {
    // return o.getId() if obj.getId() is not null
    // newId = SEQ_IDGENERATOR.nextval
    // while (newId is already in db) newId = SEQ_IDGENERATOR.nextval
    // return newId
  }
}

and your database would provide SEQ_IDGENERATOR. Id would no longer be an autogenerated field but simply

create table foo( id integer not null primary key, ...);

But again: you don't want to do this. You want your surrogate keys to be irrelevant to the outside world and handled by the database.

Community
  • 1
  • 1
wallenborn
  • 4,158
  • 23
  • 39
0

How did you have Hibernate create the schema for the DB? If you used hbm2ddl perhaps adding

<property name="hibernate.hbm2ddl.auto" value="update"/>

to your persistence.xml or setting hbm2ddl.auto to "update" may have Hibernate automatically update the db schema on redeploy, having it fix the insertion problem.

Of course it won't help you in cases when you try inserting an already existing id, but i guess you know it :)

Raibaz
  • 9,280
  • 10
  • 44
  • 65