I want to migrate a database from MySQL to Postgres. However I would like to keep the same user ids from a user tabel for instance, so what I am doing it:



Which will result in update statements instead of insert statements and errors...

If I use "save" it works. But I want a stable system that will automatically detect whether to update or insert.

If I accept that it is impossible to have the same ids, I want to make sure at least the user is unique..so I added a unique constraint on the username.

I also tried without the .setID and instead added an equals() method in the User class that returns true if the username is the same.

But it still tried to add a user twice instead of doing nothing if such a username already exists - and I get an error due to the unique constraint on the database level.

The thing is, that f I need to use a paging mechanism as I can not retrieve all results at once, due to the memory. And it might also happen that new users register while the database is being migrated. So I need to cope with the situation where a user already exists in the database.

Therefore, y goal is that already migrated user data is ignored and only new user data is being migrated if the programme is called a second time.


I thought it was a simple and straightforward thing to do with Hibernate..... :-(

The "saveOrUpdate()" method using the "unsaved-value" property in hibernate mapping file. for ex:

<id name="id" type="long" column="uid" unsaved-value="null">
<generator class="hiho">
</id>

If you don't specify the property, default is "null". So if the "id" property is null, hibernate will insert the record into the table(call "save()" method automatically). Otherwise, hibernate will call "update()" method.

So if the "id" property isn't null. Hibernate will call "update()" method automatically to update the table. This is the root cause.

The solution is that don't assign "id" to user bean. Let it(the "id" property of the user bean) to null and generate it by the "generator class".