1

I have a MySQL table which uses JPA Table Generator strategy to retrieve the next available primary key value in the application code.

But there is a special case related to this table, in which I need to insert a new record to this table from a stored procedure. That means the stored procedure has to retrieve the next available PK value for this table before inserting the record.

Although I know how to retrieve the next available PK value using the table generator strategy, I am not sure how to retrieve the next available PK value from a stored procedure.

Do I just have to use a 

SELECT current value + 1 FROM (Table Generator Table) 
WHERE (table generator record for this table) FOR UPDATE;

and then 

UPDATE (Table Generator Table) SET current value = current value + 1
WHERE (table generator record for this table)

?

Is there any better approach?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Manjula
  • 4,961
  • 3
  • 28
  • 41
  • One idea is to leave the the PK column unspecified for the INSERT statement, let the database fill in the missing value via autoincrement, and optionally retrieve the value it came up with using the RETURNING clause (or equivalent MYSQL method). – Thilo Dec 10 '15 at 08:54
  • The problem in using autoincrement is, this application is used for both Oracle and MySQL. We use a different ORM files to map the generator strategy when we use the application in either Oracle or MySQL. Therefore we have to add many if conditions etc and customize the code, if we use AUTOINCREMENT for MySQL, but use sequence strategy for Oracle. – Manjula Dec 10 '15 at 08:59
  • I see. Table Generator requires an independent second transaction, though, to obtain the sequence number, right? Not sure if that is possible to do from within a MySQL stored procedure. – Thilo Dec 10 '15 at 09:43
  • Or, you could go ahead and do this within the same transaction. Would still work, but potentially cause concurrency issues (everyone needs to wait for your transaction to complete before they can get the next number). – Thilo Dec 10 '15 at 09:46
  • 1
    Also, if you are on Oracle 12, you can finally let the database handle it as well: http://stackoverflow.com/a/18153440/14955 – Thilo Dec 10 '15 at 09:48
  • Thanks for the info. It is good to have that kind of feature in Oracle also. It seems some JPA providers uses a separate connection to retrieve the sequence value from the table, according to the documentation. Possibility of concurrency issues is a problem. But I think it should be rare for my use case, but still possible. – Manjula Dec 10 '15 at 10:03

0 Answers0