1

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?

rkosegi
  • 14,165
  • 5
  • 50
  • 83
danieln
  • 4,795
  • 10
  • 42
  • 64
  • How do you use Java for this? Do you use plain JDBC, Hibernate, ...? –  Sep 11 '12 at 07:59
  • Personally, I would go for the second option. Generating your own id means you are replicating behaviour that is already implemented in MySQL. You are in essence duplicating code, which violates the DRY (don't repeat yourself) principle. – Gustav Bertram Sep 11 '12 at 08:13
  • Is there any major reason why you want to store the single object in two tables? Is it because of normalization? Could you add an example? – Gustav Bertram Sep 11 '12 at 08:15
  • @GustavBertram I want to separate them because this object will now contain a map, the map will be inserted to the new table. – danieln Sep 11 '12 at 08:35

5 Answers5

1

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.
Community
  • 1
  • 1
uldall
  • 2,458
  • 1
  • 17
  • 31
  • The problem is that I'm using two different buffers that run on two different threads to insert the two objects to the DB. – danieln Sep 11 '12 at 08:37
0

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();

Community
  • 1
  • 1
Andreas Fester
  • 36,091
  • 7
  • 95
  • 123
  • The problem is that I'm using two different buffers that run on two different threads to insert the two objects to the DB. – danieln Sep 11 '12 at 08:33
  • And they are not synchronized? Means, you have one buffer containing the parent objects and one buffer containing the child objects, and each of the buffers is handled by a separate thread? – Andreas Fester Sep 11 '12 at 08:49
0

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.

Yair Zaslavsky
  • 4,091
  • 4
  • 20
  • 27
0

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.

Gustav Bertram
  • 14,591
  • 3
  • 40
  • 65
0

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197