Java PreparedStatement with Oracle efficiency?

Question

I'm working on an application that will pull data from a remote Web service & then push it to Oracle database.

Now, the data is event based so it could be an INSERT/UPDATE or DELETE. For this purpose, I have created a single object of preparedStatement & then based on type of event the code creates a String & assigns it to prepared statement.

Now, with each retrieved event the table name on oracle may changes & hence the query, so after retrieving each event, a String is created based on table on which operation is done & then being passed to preparedStatement object & executed.

BUT, I've 2 issues with this -

1. I guess, I'm not using prepared statement efficiently, since each time the query changes not sure if Db caching mechanism for preparedStatement will help much in this case.

2. Also, all my statements are either INSERT, UPDATE or DELETE, so I'm using pstmt.executeUpdate(), now it returns the number of rows affected by operation. But, I'm getting "maximum open cursor extended error" ... I read through many threads & since my statement does not returns resultset, & closing preparedstatement after each operation is not efficient, not sure how should I handle this error. I can increase the open cursor count in DB but that will not a application fix as this may only delay the error until a bad scenario is encountered.

getEvents()

for ( i = 0 -> lastevent)
{

  if (event == condition1)
  {
    Process.condition1(arg1, arg2)
  }
  else if (event == condition2)
  {
    Process.condition2(arg1, arg2)
  }
  .
  .
  .
}

pstmt.close();
connection.close();

Process Class

{
  condition1(arg1, arg2)
  {
    sqlstatement = "INSERT INTO Table1 (column1, column2, column3,...) VALUES (?, ?, ?);"

pstmt = connection.prepareStatement(sqlstatement);

pstmt.setString(1, value1);

pstmt.setInt(2, value2);
.
.
.

pstmt.executeUpdate();

connection.commit();

return;

}

condition2(arg1, arg2)
  {
    sqlstatement = "INSERT INTO Table2 (column1, column2, column3,...) VALUES (?, ?, ?);"

pstmt = connection.prepareStatement(sqlstatement);

pstmt.setString(1, value1);

pstmt.setInt(2, value2);
.
.
.

pstmt.executeUpdate();

connection.commit();

return;    

}
}

Sorry, I guess, above may give some idea on how the whole process is being done ... I didn't put the actual code coz it is very much distributed among various classes & it gets data for positional parameters from remote service. But above is the summarized form of how it is being done.

Also, one thought may be is to take similar type of events in a list & then process it but the way my business requirement is & the manner in which remote service provides data, it is more prone to errors.

Also, there are 130+ columns in some tables on oracle & the code will be processing 150+ events every 15 minutes.

Answer 1

It is not possible to "assign a query-string to a prepared statement". A prepared statement is created with a query-string as is shown in your code: each call to connection.prepareStatement(sqlstatement) creates a new prepared statement. This is also why the error "too many open cursors" occurs: the previously created pstmt is never closed.

To re-use the prepared statements, instantiate the Process class and give it an init method (and corresponding close method) that is called once to setup the prepared statements that might be used (and are closed when work is done):

PreparedStatement insertTable1;
PreparedStatement insertTable2;

void init(Connection c) {
    insertTable1 = c.prepareStatement(INSERT_TABLE1_QUERY);
    insertTable2 = c.prepareStatement(INSERT_TABLE2_QUERY);
}
void close() {
    insertTable1.close();
    insertTable2.close();
}
void condition1(Connection c, arg1, arg2) {
    insertTable1.setString(1, value1);
    insertTable1.setInt(2, value2);
    insertTable1.executeUpdate();
    c.commit();
}
void condition2(Connection c, arg1, arg2) {
    insertTable2.setString(1, value1);
    insertTable2.setInt(2, value2);
    insertTable2.executeUpdate();
    c.commit();
}