Insert specific ID with hibernate on postgresql database

Question

Java hibernate and Postgresql

Table:

CREATE TABLE players
(
  id serial NOT NULL,
  ....
)

HBM:

<class name="Player" table="players">
        <id name="id" column="id">
            <generator class="identity" />
        </id>
...

I want to be able to insert a specific ID in some cases as I'm importing data from another data source and need to maintain ID consistency.

When inserting with hibernate even when I set the ID of the object it still inserts without using the ID that I've specified.

Are there settings that I can change to support specifying the ID on insert?

I can't answer the question, but I did want to alert you to a problem I've seen people report a few times -- if you insert rows with explicit values rather than letting them default from the sequence associated with the serial column, the sequence does not know about those values, and you can get duplicate key errors if you don't have a strategy for avoiding that. — kgrittn
– kgrittn, Commented Apr 12, 2012 at 21:19

JayC · Accepted Answer · 2012-04-12 21:22:53Z

1

Note: I realize OP is primarily and specifically asking about Hibernate, which I'll admit, I have no experience with. I just felt the need to discuss why this doesn't seem advisable to me with ANY Object Relational Model.

I'm not sure that using an ORM to directly import data is such a good idea. Tf you are importing data and the IDs of the data imported are in the range of the yet-to-be-generated, you should eventually get duplicates (or primary key issues if set up that way). Postgresql creates separate sequence objects to maintain sequences which should be unaffected by the tables that use them. Any solution using an ORM should also address that issue as well. Looks like that can be manipulated through Postgresql evaluation of setval (see http://www.postgresql.org/docs/9.1/static/functions-sequence.html)

If you still need to use an ORM for this, I'd be tempted to create some data "landing" tables, generate classes for them with your ORM tools, and then write some SQL function that patches your live tables with the data you just uploaded. There may be another, better way, but I'd like to see it.

answered Apr 12, 2012 at 21:22

JayC

7,1612 gold badges30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JayC Over a year ago

How is this an insult? I just thought this was a bad idea. I've had to implement plenty of bad ideas.

JayC Over a year ago

Well, maybe "bad idea" is too strong... My point was it likely needs something in addition.

Will Shaver Over a year ago

And perhaps it does, perhaps doing it via sql statements would be more effective.

Collectives™ on Stack Overflow

Insert specific ID with hibernate on postgresql database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related