CODEWITHSUNDEEP
javamysqlhibernateduplicates
David
David

Reputation: 1101

Hibernate insert twice with same PK

I have a one-table-per-subclass setup. I have a Model class which is mapped with the CarModelDesc table. This table has 3 columns : (modelDescId, makeId, model).

We are working on a new i18n system so I made a table catalog_model_i18n that has 3 column (model_id, locale, model, display_name). DisplayName is new, but catalog_model_i18n.model will replace the column CarModelDesc .model later on. These are the columns definition in my model : 

@Id
@GeneratedValue
@Column(name = Cols.modelId)
private int modelId = -1;
@Column(name = Cols.makeId)
private Integer makeId = null;
@Column(name = Cols.model)
private String model = null;

@ElementCollection
@MapKeyClass(value = java.util.Locale.class)
@MapKeyColumn(name = "locale")
@CollectionTable(name = "catalog_model_i18n", joinColumns = @JoinColumn(name = "model_id"))
@Column(name = "model")
private Map<Locale, String> models = new HashMap<>();

@ElementCollection
@MapKeyClass(value = java.util.Locale.class)
@MapKeyColumn(name = "locale")
@CollectionTable(name = "catalog_model_i18n", joinColumns = @JoinColumn(name = "model_id"))
@Column(name = "display_name")
private Map<Locale, String> displayNames = new HashMap<>();

Each line in the table catalog_model_i18n is a translated entry. The primary key is based on the model_is, locale combination. When I try to save a model into the database, hibernates gives me a Duplicate entry exception.

I enabled logging to see these queries being performed : 

3 Query insert into sm360_webauto.CarModelDesc (makeId, model) values (45, 'modele fr')
3 Query insert into catalog_model_i18n (model_id, locale, display_name) values (884, 'fr', 'nom')
3 Query insert into catalog_model_i18n (model_id, locale, display_name) values (884, 'en', 'name')
3 Query insert into catalog_model_i18n (model_id, locale, model) values (884, 'fr', 'modele fr')
3 Query insert into catalog_model_i18n (model_id, locale, model) values (884, 'en', 'modele en')
3 Query rollback

What I expect hibernate to do in this context is whether to insert once, or to update like so : 

3 Query insert into sm360_webauto.CarModelDesc (makeId, model) values (45, 'modele fr')
3 Query insert into catalog_model_i18n (model_id, locale, model, display_name) values (884, 'fr', 'modele fr', 'nom')
3 Query insert into catalog_model_i18n (model_id, locale, model,  display_name) values (884, 'en', 'modele en', 'name')

Or : 

3 Query insert into sm360_webauto.CarModelDesc (makeId, model) values (45, 'modele fr')
3 Query insert into catalog_model_i18n (model_id, locale, display_name) values (884, 'fr', 'nom')
3 Query insert into catalog_model_i18n (model_id, locale, display_name) values (884, 'en', 'name')
3 Query update catalog_model_i18n SET model_id=884 , locale='fr' , model='modele fr'
3 Query update catalog_model_i18n SET model_id=884 , locale='en' , model='modele en'

Is there anyway I can tell hibernate on how to do it properly? Possibly without having to write SQL code into my model.

Thanks

*UPDATE : *

The problem is that hibernate do not know theses two variables in my Model class link to the same table. Therefore, it performs two insert statements, but it is wrong.

Until now this setup was working because I only had one column per i18n table which never caused a problem. But with 2 columns, hibernate does two insert request and a duplicate entry exception is raised. I still don't have a solution.

Upvotes: 2

Views: 1979

Answers (1)

David
David

Reputation: 1101

I believe there should be a better way to do this but since MySQL support NO DUPLICATE KEY, we could override the SQL insert done by Hibernate to make this work. Here is the modified declaration in my model : 

@SQLInsert(sql="INSERT INTO catalog_model_i18n(model_id, locale, name) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name)")
@ElementCollection
@MapKeyClass(value = java.util.Locale.class)
@MapKeyColumn(name = "locale")
@CollectionTable(name = "catalog_model_i18n", joinColumns = @JoinColumn(name = "model_id"))
@Column(name = "name")
private Map<Locale, String> names = new HashMap<>();

@SQLInsert(sql="INSERT INTO catalog_model_i18n (model_id, locale, display_name) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE display_name = VALUES(display_name)")
@ElementCollection
@MapKeyClass(value = java.util.Locale.class)
@MapKeyColumn(name = "locale")
@CollectionTable(name = "catalog_model_i18n", joinColumns = @JoinColumn(name = "model_id"))
@Column(name = "display_name")
private Map<Locale, String> displayNames = new HashMap<>();

Upvotes: 2

Related Questions