Inserting a Row into a Database Table Using a Prepared Statement
If you have a SQL statement that needs to be executed many times but
with different values, a prepared statement can be used to improve
performance. For example, if you have a website that looks up product
information with a product id using the same query each time, a
prepared statement should be used. A prepared statement is a
precompiled SQL statement and its use saves the database from
repeatedly having to compile the SQL statement each time it is
executed.
A query in a prepared statement contains placeholders
(represented by the '?' character) instead of explicit values. You
set values for these placeholders and then execute the prepared
statement.
Here is another example of inserting with a prepared statement that
uses the various setXXX() methods. This example uses the table
created in Creating a MySQL Table to Store Java Types.
try {
// Prepare a statement to insert a record
String sql = "INSERT INTO my_table (col_string) VALUES(?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
// Insert 10 rows
for (int i=0; i<10; i++) {
// Set the value
pstmt.setString(1, "row "+i);
// Insert the row
pstmt.executeUpdate();
}
} catch (SQLException e) {
}
try {
// Prepare a statement to insert a record
String sql = "INSERT INTO mysql_all_table("
+ "col_boolean,"
+ "col_byte,"
+ "col_short,"
+ "col_int,"
+ "col_long,"
+ "col_float,"
+ "col_double,"
+ "col_bigdecimal,"
+ "col_string,"
+ "col_date,"
+ "col_time,"
+ "col_timestamp,"
+ "col_asciistream,"
+ "col_binarystream,"
+ "col_blob) "
+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
// Set the values
pstmt.setBoolean(1, true);
pstmt.setByte(2, (byte)123);
pstmt.setShort(3, (short)123);
pstmt.setInt(4, 123);
pstmt.setLong(5, 123L);
pstmt.setFloat(6, 1.23F);
pstmt.setDouble(7, 1.23D);
pstmt.setBigDecimal(8, new BigDecimal(1.23));
pstmt.setString(9, "a string");
pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
pstmt.setTime(11, new Time(System.currentTimeMillis()));
pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));
// Set the ascii stream
File file = new File("infilename1");
FileInputStream is = new FileInputStream(file);
pstmt.setAsciiStream(13, is, (int)file.length());
// Set the binary stream
file = new File("infilename2");
is = new FileInputStream(file);
pstmt.setBinaryStream(14, is, (int)file.length());
// Set the blob
file = new File("infilename3");
is = new FileInputStream(file);
pstmt.setBinaryStream(15, is, (int)file.length());
// Insert the row
pstmt.executeUpdate();
} catch (SQLException e) {
} catch (FileNotFoundException e) {
}
Nice and neat, thank you!