java中的mysql insert 避免直接用insert语句

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.

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 Creating a MySQL Table to Store Java Types.

COPY

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