JDBC, MySQL: Placing calculated(?) values in a loop.

[javaQQ]

The Sun JDBC Tutorial shows how to place PreparedStatement values in a loop, in this manner:

The statement below works. But I need to place all the values into a loop

String registerSoldiersStr =
" INSERT INTO Soldiers ( " +
" name, " +
" rank, " +
" serial_number, " +
" ) " +
" SELECT " +
" name, " + // name,
" ?, " + // rank,
" CONCAT( SourceTable.group_number, ' - ', LPAD( ?,3,\"0\" ) ) " // serial_number,
" FROM SourceTable " +
" WHERE some_id = ? "
;


PreparedStatement registerSoldiers = con.prepareStatement ( registerSoldiersStr );
for( int i = 0; i < sources; i++ ) {
registerSoldiers.setString( 1, (String)((Vector)sourceDataVector.elementAt(i)).elementAt(0) ); //
registerSoldiers.setString( 2, (String)((Vector)sourceDataVector.elementAt(i)).elementAt(1) ); //

int rs = registerSoldiers.executeUpdate();
}

How would I set all of the values above in a loop, as below?

...
" SELECT " +
" ?, " + // name,
" ?, " + // rank,
" ? // serial_number,
" FROM SourceTable " +
" WHERE some_id = ? "
;


PreparedStatement registerSoldiers = con.prepareStatement ( registerSoldiersStr );
for( int i = 0; i < sources; i++ ) {
registerSoldiers.setString( 1, ~then "name" value~ ); //
registerSoldiers.setString( 2, (String)((Vector)sourceDataVector.elementAt(i)).elementAt(1) ); //
registerSoldiers.setString( 3, (String)((Vector)sourceDataVector.elementAt(i)).elementAt(2) ); //
registerSoldiers.setString( 4, ~then concatenated value~); //

int rs = registerSoldiers.executeUpdate();
}


Many thanks in advance.

[dlorde]

Am I missing something in the first example? - there seem to be 3 variables ('?') and only 2 'setXxx(...)' calls...

The JavaDocs aren't clear whether the PreparedStatement implementation should do a simple text substitution, or enforce the parameters to represent field values only, but the intent is obviously that they are field values. Since drivers are likely to take advantage of database facilities for statement reuse, it seems unwise to expect to be able to use parameters for the other statement elements (does it actually work?)

AIUI, the point of a PreparedStatement is to repeat the same statement with varying parameters. The effect you require (if I understand it correctly), of more dynamic statements (i.e. fully mutable statements), can be achieved by using a different PreparedStatement string each time. Of course, you have to code this yourself, but it's simple string substitution. ISTM you probably lose the potential efficiency advantages of PreparedStatements, but at least you have the auto parameter formatting...


Faith may be defined as an illogical belief in the occurrence of the improbable...