![]() |
The Java Developers Almanac 1.4Order this book from Amazon. |
e259. Inserting a Row into a Database Table Using a Prepared StatementIf 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. 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) { }Here is another example of inserting with a prepared statement that uses the various setXXX() methods. This example uses the table
created in e248 Creating a MySQL Table to Store Java Types.
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) { }
e260. Getting and Inserting Binary Data into an Database Table e261. Updating a Row in a Database Table © 2002 Addison-Wesley. |