Using Oracle sequence to insert log id into 2 tabl

问题:

I am using oracle sequence for inserting log id into tableA as follows,

String SQL_PREP_INSERT = "INSERT INTO tableA (LOG_ID,USER_ID,EXEC_TIME) VALUES"
            + " (logid_seq.nextval, ?, ?)";

Then getting the recently inserted value,

String SQL_PREP_SEL = "SELECT max(LOG_ID) FROM tableA ";

stmt = con.prepareStatement(SQL_PREP_SEL);
stmt.execute();
ResultSet rs = stmt.getResultSet();
if (rs.next()) {
logid = rs.getInt(1);
}

And inserting it into tableB,

String SQL_PREP_INSERT_DETAIL = "INSERT INTO tableB (LOG_ID, RESPONSE_CODE, RESPONSE_MSG) VALUES"
                + " (?, ?)";

        stmt = con.prepareStatement(SQL_PREP_INSERT_DETAIL);
        stmt.setInt(1, logid);
        stmt.setString(2, respCode);
        stmt.setString(3, respMsg);
        stmt.execute();

Is there a way to generate sequence in Java instead of Oracle and insert into both tables at once, instead of selecting from tableA and inserting into tableB?

回答1:

In general, selecting the MAX(log_id) is not going to give you the same value that logid_seq.nextval provided. Assuming that this is a multi-user system, some other user could have inserted another row with a larger log_id value than the row you just inserted before your query is executed.

Assuming that both INSERT statements are run in the same session, the simplest option is probably to use the logid_seq.currval in the second INSERT statement. currval will return the last value of the sequence that was returned to the current session so it will always return the same value that was generated by the nextval call in the first statement.

INSERT INTO tableB (LOG_ID, RESPONSE_CODE, RESPONSE_MSG) 
  VALUES( logid_seq.currval, ?, ? )

Alternatively, you could use the RETURNING clause in your first statement to fetch the sequence value into a local variable and use that in the second INSERT statement. But that is probably more work than simply using the currval.

回答2:

String QUERY = "INSERT INTO students "+
               "  VALUES (student_seq.NEXTVAL,"+
               "         'Harry', '[e-mail]', '31-July-1980')";

// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");

// get database connection from connection string
Connection connection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:sample", "scott", "tiger");

// prepare statement to execute insert query
// note the 2nd argument passed to prepareStatement() method
// pass name of primary key column, in this case student_id is
// generated from sequence
PreparedStatement ps = connection.prepareStatement(QUERY,
        new String[] { "student_id" });

// local variable to hold auto generated student id
Long studentId = null;

// execute the insert statement, if success get the primary key value
if (ps.executeUpdate() > 0) {

    // getGeneratedKeys() returns result set of keys that were auto
    // generated
    // in our case student_id column
    ResultSet generatedKeys = ps.getGeneratedKeys();

    // if resultset has data, get the primary key value
    // of last inserted record
    if (null != generatedKeys && generatedKeys.next()) {

        // voila! we got student id which was generated from sequence
        studentId = generatedKeys.getLong(1);
    }

}