Update table with primary key and unique columns using Hibernate and mySQL

Question

I have a table containing four columns:

CREATE TABLE `participants` (
 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(128) NOT NULL,
 `function` VARCHAR(255) NOT NULL,
 `contact` VARCHAR(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE INDEX `name_function_contact` (`name`, `function`, `contact`)
)

From the application I get participants-objects, which might have values for name, functionand contactwhich are already in that exact matter in the database. In this case I want Hibernate to get me the idof that object, otherwise I want to save the object.

Using saveOrUpdate()I just get an:

org.hibernate.exception.ConstraintViolationException: Duplicate entry 'NAME-FUNCTION-CONTACT: NAME' for key 'name_function_contact'

How can I accomplish this? Thanks a lot!

Your application would have to do this itself by first querying the database to pull out any object that matches those 3 fields. If no object is found, then you call save. Otherwise, you set the id on the object with the id you just found, and call save. (I deleted my first comment because I re-read the question and realized you already understood what I typed). — David Fleeman
– David Fleeman, Commented Nov 12, 2013 at 16:27
Your other option is to do an insert and IGNORE the error, but if there are other fields in the object that need updated, then this approach does not work since those additional fields would not be updated. — David Fleeman
– David Fleeman, Commented Nov 12, 2013 at 16:30
Ok. Did it quick n dirty: for (Participants p : participants) { try { session.save(p); } catch (ConstraintViolationException e) { p.setId(((Participants) session.createQuery("FROM Participants WHERE name LIKE '" + p.getName() + "' AND function LIKE '" + p.getFunction() + "' AND contact LIKE '" + p.getContact() + "'").uniqueResult()).getId()); } finally { } } — tommueller
– tommueller, Commented Nov 12, 2013 at 16:52
Don't forget to call session.save(p) again inside your catch {} block. Also, I would recommend using name= and function= and contact= for clarity/performance instead of using LIKE. If this works for you, you might want to post it as an Answer. — David Fleeman
– David Fleeman, Commented Nov 12, 2013 at 18:55

tommueller · Accepted Answer · 2013-11-13 09:53:27Z

1

Since the answers suggested that Hibernate cannot do it on its own (bummer!) I solved it the "native sql" way:

Participants tempParti = ((Participants) session.createQuery("FROM Participants WHERE name = '" + p.getName() + "' AND function = '" + p.getFunction() + "' AND contact = '" + p.getContact() + "'").uniqueResult());
if (tempParti != null) {
    p = tempParti;
} else {
    session.save(p);
}

Works like a charm! Thanks to all of you!

answered Nov 13, 2013 at 9:53

tommueller

2,5263 gold badges33 silver badges50 bronze badges

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

2 Comments

David Fleeman Over a year ago

Look like this will work great as long as the Participants object does not have any additional internal data that needs persisted at the time of the save. Glad you got a concise solution that works!

tommueller Over a year ago

true. But it does not :) Thanks again for all the help!

Slowcoder · Accepted Answer · 2013-11-12 16:29:00Z

0

I am no expert in Hibernate. But from Mysql perspective, you do the following.

use INSERT IGNORE INTO... to add the value in the table. If the number of rows inserted is 0, then you can manually get the ID of the row by a SELECT statement.

EDIT: LAST_INSERT_ID() was wrong here. I have edited the answer.

answered Nov 12, 2013 at 16:29

Slowcoder

2,1303 gold badges17 silver badges21 bronze badges

Collectives™ on Stack Overflow

Update table with primary key and unique columns using Hibernate and mySQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related