Insert multiple values

[RicardoPereira]

Hi,

It is possible insert multiple values posted in a form to an database?
I must insert 10 records posted in a form to an table. Something like this:

record1
-------
Value
field1: a
field2: b
..
field6: c

record2
-------

field1: x
field2: y
..
field6: z

...

record10
-------

field1: e
field2: t
..
field6: d

...


How do i insert these 10 records with only one insert?

Thanks
Ricardo

 

[venur]

Hi,

You could use Batch Updates to Insert the values into DB. The advantage is it creates as many insert statements and I will be processed only by the DB. Saves Time, Saves Resoucrese and Easy to Execute the Batch.

Ex: I have set the autoCommit(false) so that you have the choice of committing or not in the event of an exception

import java.sql.*;

/**
*
* User: venu reddy
* Date: Jan 8, 2004
* Time: 1:58:56 PM
* Java: ${filename}
*/
public class BatchProcess {

public static void processUpdateCounts(int[] updateCounts) {
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts >= 0) {
// Successfully executed; the number represents number of affected rows
} else if (updateCounts == Statement.SUCCESS_NO_INFO) {
// Successfully executed; number of affected rows not available
} else if (updateCounts == Statement.EXECUTE_FAILED) {
// Failed to execute
}
}
}

public static void main(String[] args) throws SQLException {
// get the connection from Connection Pool
Connection connection = null;
try {
connection.setAutoCommit(false);
// Create a prepared statement
String sql = &quot;INSERT INTO my_table VALUES(? , ?, ? )&quot;;
PreparedStatement pstmt = connection.prepareStatement(sql);
// Insert 10 rows of data
for (int i = 0; i < 10; i++) {
pstmt.setString(1, &quot;A&quot;
pstmt.setString(2, &quot;B&quot;
pstmt.setString(3, &quot;C&quot;
pstmt.addBatch();
}
// Execute the batch
int[] updateCounts = pstmt.executeBatch();
// All statements were successfully executed.
processUpdateCounts(updateCounts);
// Since there were no errors, commit
connection.commit();
} catch (BatchUpdateException e) {
// Not all of the statements were successfully executed
int[] updateCounts = e.getUpdateCounts();
// Some databases will continue to execute after one fails.
processUpdateCounts(updateCounts);
// Either commit the successfully executed statements or rollback the entire batch
connection.rollback();
} catch (SQLException e) {
}


}
}

Cheers,
Venu

 

[venur]

Hi,

I am not sure does the SQL Server driver supports the Batch Updates. Just make sure that your driver supports it.

Add couple more lines in the above code.. once you have the connection object.

DatabaseMetaData dmd = connection.getMetaData();
if (dmd.supportsBatchUpdates()) {
// Batching is supported
} else {
// Batching is not supported
}

Cheers,
Venu

 

[sedj]

I would suggest using SQL stored procedures (which are written db side) and then executed through Java via the JDBC class CallableStatement.

Consult the JDBC API docs for specifics ...