Get inserted row to Oracle with java

Question

I am building a java program to insert data to my oracle database. My problem is that I need to insert into two tables, and to reach unique rows I use in TABLE_A triggers for id before insert get next val in a sequence. But i need the same id for the TABLE_B for connection. ( i cant get getval because what if another user uses the program... ) So I need to reach somehow that when I use executeql(sql) command in return I see what I have submit.

Now I use that I have name and date, so I select the id where name and date is the just inserted. But its not the best because in one day I can insert more names. So now this will not unique. like :

insert into table a ( name,date) val ( 'Ryan','2014.01.01')

id here is autoincremented by sequence

than another sql run:

inert into table_b  ( id,someval) val ( select id from table_a where
name ='Ryan', date='2014.01.01, 23)

so i need something like:

 system.out.println(smtp.executesql(sql).whatIinsertednow())
*than console:* '1 row insered (id,name,date) : ( 1, Ryan, 2014.01.01)

You can get that generated ID value back [like this](http://stackoverflow.com/q/17320205/266304); and you have the rest of the values already? — Alex Poole, Sep 01 '14 at 14:49

score 3 · Accepted Answer · answered Sep 02 '14 at 07:18

PreparedStatement prepareStatement = connection.prepareStatement("insert...",

        new String[] { "your_primary_key_column_name" });

prepareStatement.executeUpdate();

ResultSet generatedKeys = prepareStatement.getGeneratedKeys();
if (null != generatedKeys && generatedKeys.next()) {
     Long primaryKey = generatedKeys.getLong(1);
}

I have found the answer this is perfectly works. I can insert from JAVA and its return with the key.

Full version:

CREATE TABLE STUDENTS
(
   STUDENT_ID   NUMBER NOT NULL PRIMARY KEY,
   NAME         VARCHAR2 (50 BYTE),
   EMAIL        VARCHAR2 (50 BYTE),
   BIRTH_DATE   DATE
);


CREATE SEQUENCE STUDENT_SEQ
   START WITH 0
   MAXVALUE 9999999999999999999999999999
   MINVALUE 0;

And the Java code

String QUERY = "INSERT INTO students "+
               "  VALUES (student_seq.NEXTVAL,"+
               "         'Harry', 'harry@hogwarts.edu', '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);
    }

}

source : http://viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/

score 2 · Answer 2 · answered Sep 01 '14 at 14:58

2

You can accomplish that by using the RETURNING clause in your INSERT statement:

INSERT INTO table_a ( name,date) val ( 'Ryan','2014.01.01') RETURNING id INTO ?

answered Sep 01 '14 at 14:58

gvenzl

1,861
14
22

but how can i get it in java? (JDBC) – SüniÚr Sep 01 '14 at 15:15
1

Have a look at [this solution on SO](http://stackoverflow.com/questions/682539/return-rowid-parameter-from-insert-statement-using-jdbc-connection-to-oracle/881414#881414) – gvenzl Sep 01 '14 at 15:21

Get inserted row to Oracle with java

2 Answers2