Mysql – How to get the auto-increment primary key value in MySQL using Hibernate

auto-incrementhibernateMySQL

I'm using Hibernate to access MySQL, and I have a table with an auto-increment primary key.

Everytime I insert a row into the table I don't need to specify the primary key. But after I insert a new row, how can I get the relative primary key immediately using hibernate?

Or I can just use jdbc to do this?

Best Answer

When you save the hibernate entity, the id property will be populated for you. So if you have

MyThing thing = new MyThing();
...

// save the transient instance.
dao.save(thing);

// after the session flushes, thing.getId() should return the id.

I actually almost always do an assertNotNull on the id of a persisted entity in my tests to make sure the save worked.

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Mysql – Reorder / reset auto increment primary key

Even though this question seems to be quite old, will post an answer for someone who reaches in here searching.

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

If the column is used as a foreign key in other tables, make sure you use ON UPDATE CASCADE instead of the default ON UPDATE NO ACTION for the foreign key relationship in those tables.

Further, in order to reset the AUTO_INCREMENT count, you can immediately issue the following statement.

ALTER TABLE `users` AUTO_INCREMENT = 1;

For MySQLs it will reset the value to MAX(id) + 1.

Best Answer

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Mysql – Reorder / reset auto increment primary key

Related Topic