1

I am developing an application using Java and I have an Oracle database. To store data I am using JDBC. In Oracle database, there is nothing like a Auto Increment. So I have used triggers to get the lastest primary key.

Currently the application has been implemented and I get the saved primary key back. This happens when the connection.setAutoCommit(true) is set. What I would like to know if I set the connection.setAutoCommit(false) is it possible to get the primary key?

The reason why I ask this is because I am facing some concurrency issue with multiple threads where one thread saves the data and and another thread can pick up the saved record. I don't want this to happen. I want the code to be sequentially completed before the other thread views the data. So, I tried to investigate JDBC row lock but nothing like this exists.

This primary key is required for an input into another method within the same Thread.

user3189663
  • 211
  • 4
  • 18
  • why cant you use `oracle sequences` instead of triggers – Sajan Chandran Jun 10 '14 at 08:28
  • How do you persist data to Oracle tables? i.e. do you use a stored procedure or DML statements? – Jacob Jun 10 '14 at 08:29
  • I couldn't remember the name but yes both sequences and triggers are used. – user3189663 Jun 10 '14 at 08:30
  • I use PreparedStatement and SQL query such as SELECT. – user3189663 Jun 10 '14 at 08:31
  • I get the primary key back through SELECT sequenceName.CURRVAL FROM DUAL – user3189663 Jun 10 '14 at 08:34
  • Ok, but this is not the question raised on this forum. – user3189663 Jun 10 '14 at 08:36
  • @user3189663 Have a look at [this](http://stackoverflow.com/a/23777036/599528), perhaps of some help. – Jacob Jun 10 '14 at 08:38
  • The question is whether connection.setAutoCommit(false) would return the a primary key and whether JDBC can lock a row in the database. – user3189663 Jun 10 '14 at 08:40
  • @user3189663 When Oracle provides a straight way to get the primary key in an insert statement using `RETURNING` , why not use it rather than trying to use JDBC method of retrieving a value? Thus you do not have to worry about thread safety. – Jacob Jun 10 '14 at 08:46
  • The problem is that the company that I work for will not allow me to use stored procedure. The full code has been implemented and we are towards the end of the project. SELECT FROM DUAL returns the primary key and therefore JDBC has to be used. – user3189663 Jun 10 '14 at 08:50
  • @user3189663 I understand. Have a look at [this](http://stackoverflow.com/q/17320205/599528) as well. – Jacob Jun 10 '14 at 08:53
  • @user3189663 And [this](http://stackoverflow.com/a/881414/599528) as well. – Jacob Jun 10 '14 at 08:56
  • All these solutions involve stored procedure. I cannot change any code due to being the end of the project. – user3189663 Jun 10 '14 at 09:05
  • Is it possible to lock a particular row after inserting, do some processing on that same record and once processed unlock it so other threads can do other processing? – user3189663 Jun 10 '14 at 09:07
  • 1
    @user3189663 Sequences are outside of transaction control, selecting your primary key using `SELECT sequenceName.CURRVAL FROM DUAL` might give the wrong result if multiple inserts are done at the same time. – Mark Rotteveel Jun 10 '14 at 09:16

2 Answers2

1

The correct way to retrieve your primary key is to use the getGeneratedKeys facility (which can be activated by using the Statement.RETURN_GENERATED_KEYS value with one of the Statement.execute* or Connection.prepareStatement methods.

In most databases this can be used to retrieve the primary key directly. In the case of Oracle however this will allow you to obtain the ROWID, this ROWID can be used to query the table for the inserted row and obtain the primary key.

For example:

stmt.executeUpdate("INSERT INTO theTable(column1) VALUES ('a')",
      Statement.RETURN_GENERATED_KEYS);
ResultSet keys = stmt.getGeneratedKeys();
int primaryKey = -1;
if (keys.next()) {
    try (PreparedStatement keyStatement = 
           connection.prepareStatement("SELECT ID FROM theTable WHERE ROWID = ?")) {
        keyStatement.setRowId(keys.getRowId(1));
        try (ResultSet rs = keyStatement.executeQuery()) {
             primaryKey = rs.getInt(1);
        }
    }
}
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
1

In addition to the solution provided by @Mark Rotteveel, you can specify the exact columns you want values returned from by using Connection.prepareStatement(String, String[]). In this case, if you provide the column name for you primary key, that value will be in the ResultSet returned from Statement.getGeneratedKeys.

Another option is to get the value for the primary key from the sequence and insert as part of your insert statement rather than using a trigger:

select mysequence.nextval from dual
Brett Okken
  • 6,210
  • 1
  • 19
  • 25