CODEWITHSUNDEEP
javamysqljdbcauto-increment
danieln
danieln

Reputation: 4973

MySQL - Foreign key and auto-generated ID

In my application I have a component that receive objects from another components and insert them to MySQL DB. Currently I'm buffering the objects and once in a while (few seconds) the objects are inserted to the DB using a batch (using JDBC, not hibernate).

I would like to break this objects to 2 objects, then two buffers, and finally insert them to 2 different tables.

My first thought was to use MySQL auto generated ID to tie the two sub-objects together in the table (as foreign key).

My problem is - how will I know the auto-generated ID for the 'father' object when I insert the 'child' object?

My ideas are:

  1. Generate my own ID before splitting the object and send the ID to the DB myself, without using MySQL auto-generated ID.
  2. Use stored procedure that will insert the first object, use MySQL's LAST_INSERT_ID();

What do you think?

Upvotes: 1

Views: 2663

Answers (5)

Mark Rotteveel
Mark Rotteveel

Reputation: 108954

The standard interface with JDBC to retrieve generated keys is to use the getGeneratedKeys() method on the statement. See Example 21.8. Connector/J: Retrieving AUTO_INCREMENT column values using from the MySQL site

Upvotes: 0

Gustav Bertram
Gustav Bertram

Reputation: 14901

Your essential problem is that you need information from the database for one object before you can store the other object.

Let's say you split your compound object into two smaller objects, namely Place and Map.

If you must have two buffers to store Place and Map, then I see three ways of doing so:

  1. Put only Place in the store-buffer. When Place is stored, and the PK has been read from the database, ONLY THEN place Map in the other store-buffer.

  2. Put Place and Map in their own store-buffers. When a Map is about to be stored, check it's associated Place, to see if the PK has been generated yet. If it has, then store the Map. If it hasn't, then skip that Map and check the next Map.

  3. Put Place in the store-buffer. When it gets stored, read the PK, and immediately store the Map also. This obviously does not use two store-buffers.

Look at how other objects are stored in your application. Certainly they don't generate their own IDs before they are stored in the database. Certainly you don't try to put them in a store-buffer before you have all the information necessary to store them. My recommended course of action is therefore option 1 or 3.

Upvotes: 0

Andreas Fester
Andreas Fester

Reputation: 36630

Maybe this helps (you can use LAST_INSERT_ID() also in plain SQL, not only inside procedures),see also http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html:

INSERT INTO foo (auto,text) VALUES(NULL,'text');         # generate ID by inserting NULL
INSERT INTO foo2 (id,text)  VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

Given that the OP runs the INSERTs on separate threads, the IDs need to be preallocated - you can use a sequence to safely select unique IDs (see also MySQL information functions):

  • Create a table to hold the sequence counter and initialize it:

    mysql> CREATE TABLE sequence (id INT NOT NULL);

    mysql> INSERT INTO sequence VALUES (0);

  • Use the table to generate sequence numbers like this:

    mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

    mysql> SELECT LAST_INSERT_ID();

Upvotes: 0

Yair Zaslavsky
Yair Zaslavsky

Reputation: 4137

Both approaches are valid.
The java approach is more DB agnostic, and might be easier for you to switch database or to support multiple DB vendors.

Upvotes: 0

uldall
uldall

Reputation: 2558

One solution would be to insert the two records in the same transaction as done in this question. JDBC: foreign key on PK created in same transaction

That is:

  1. Initialize the transaction.
  2. Do the first insert of the father.
  3. Retrieve the generated father ID.
  4. Do the second insert of the child using the father ID.
  5. Commit the transaction.

Upvotes: 1

Related Questions