CODEWITHSUNDEEP
javamysqlhibernate
tommueller
tommueller

Reputation: 2496

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

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!

Upvotes: 0

Views: 1114

Answers (2)

tommueller
tommueller

Reputation: 2496

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!

Upvotes: 1

Slowcoder
Slowcoder

Reputation: 2120

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.

Upvotes: 0

Related Questions