How to optimize sequence retrieval in Oracle and Java

From: Marko Hantula <marko_at_coriolisnet.com>
Date: 9 Oct 2001 11:06:38 -0700
Message-ID: <d1c2ab88.0110091006.7795d98e@posting.google.com>

Let's say I have a table & sequence like these :

CREATE TABLE student(id NUMBER PRIMARY KEY, name VARCHAR2(50)); CREATE SEQUENCE student_sequence START WITH 1 INCREMENT BY 1;

Now let's say I have a Java method that creates a bunch of Student objects at once as follows :

public Student[] createStudents(Connection       Conn,
                                String[]         NameArray) throws

Exception
{
  Student[] Students = new Student[NameArray.length];   long[] laIds = new long[NameArray.length];

// Create ids

  for(int iIndex = 0; iIndex < NameArray.length; iIndex++)   {
    Statement Stmt = Conn.createStatement();     ResultSet Set = Stmt.executeQuery("SELECT student_sequence.NEXTVAL FROM dual");

    while(Set.next())
      laIds[iIndex] = Set.getLong(1);

    Set.close();
    Stmt.close();
  }

// Create student objects

  PreparedStatement Stmt = Conn.prepareStatement("INSERT INTO student_table VALUES (?, ?)");

  for(int iIndex = 0; iIndex < NameArray.length; iIndex++)   {
    // Add a batch to the statement

    Stmt.setLong(1, laIds[iIndex]);
    Stmt.setString(2, NameArray[iIndex]);
    Stmt.addBatch();

     // Create object
     Students[iIndex] = new Student(laIds[iIndex],
                                    NameArray[iIndex]);

  }

// Execute batch & close

  Stmt.executeBatch();
  Stmt.close();

  return Students;
}

Now my question is, if the size of the student name array is large (e.g. 1000), how do I optimize the first part so that I would get all the ids at once in a batch operation, like I do in the insert part.
The implementation above basically does N separate select statements from the database (across network) which is not too efficient.

What would be the best way to optimize this code, should I possibly use a stored procedure that would return an array of ids or is there a better way to do this? I cannot use a bigger increment value in the sequence since the size of the array is not constant. Also, I need the ids because I have to return an array of Student objects, so I cannot use the sequence in the insert statement itself.

Thanks,
Marko Received on Tue Oct 09 2001 - 13:06:38 CDT