nested preparedstatement ?

[WebGoat]

nested preparedstatement ? is it possible ?

first ,i want to do a query in a table . then taking each result of that big resultset i want to insert some record into another table.


How could i do it ? is not it will be a nested ? so, i am in problem.

 

[sedj]

pseudo code :

Connection conn1 = new Connection();
Connection conn2;

ResultSet rs = conn1.doQuery();

while (rs.next()) {
conn2 = new Connection();
PreparedStatement ps = conn2.prepare();
ps.execute();
ps.close()
conn2.close();
}


--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[WebGoat]

Excellent ! i can make code now.
thank you

 

[ishnid]

There shouldn't be any need to prepare the second statement for each insert - that kind of defeats one of the main purpose sof PreparedStatements:

// prepare both statments
PreparedStatement firstStatement = con.prepareStatement( "SELECT field1, field2 FROM some_table WHERE some_identifier=?" );
PreparedStatement secondStatement = con.prepareStatement( "INSERT INTO some_other_table VALUES(?,?)" );

// execute the SELECT query (the oneToGet variable is just an example)
int oneToGet = 10;
firstStatement.setInt( 1, oneToGet );
ResultSet rs = firstStatement.executeQuery();

// for each row retrieved
while( rs.next() ) {

   // load values into the INSERT statement and execute
   secondStatement.setInt( 1, rs.getInt( 1 ) );
   secondStatement.setString( 2, rs.getString( 2 ) );
   secondStatement.execute();
}

 

[stefanwagner]

That should be done by the database:

ps = con.prepareStatement( "
INSERT INTO t2 (c1, c2) VALUES AS
   SELECT f1, f2 
   FROM t1 
   WHERE f3=?"
);

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[sedj]

Its a much safer approach to use one statement per conn call.

All applications that use heavily db transactions should really be running through a pool. As for the statement caching, any db worth its salt will be caching statement executing plans anyway, so there is not a lot of point in "preparing" the statement in that way once off anyway.

Its better to prepare a new statement, to make sure the JDBC driver has cleaned out any globals, and let the db handle the caching IMO.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[WebGoat]

>its a much safer approach to use one statement per conn call.
i see. i want to be safe