e259. inserting a row into a database table using a prepared statement.txt

这里面包含了一百多个JAVA源文件

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. 

    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) {
    }

 Related Examples