JDBC PreparedStatement 实例

The “PreparedStatement” interface is extended “Statement”, with extra feature to send a pre-compiled SQL statement with parameters. For create, insert, update or delete statement, uses “PreparedStatement.executeUpdate(sql)“; select query, uses “PreparedStatement.executeQuery(sql)“.

完整代码：
JDBCMySQL.java

package org.hnrsc.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCMySQL {

    private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/test";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "";


    public static void main(String[] args) {
        try{
            //createTable();
            insertRecord();
            batchInsertRecords();
            deleteRecords();

            selectRecords();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }

    /*
     * BUG:
     * INSERT INTO h_user(username) VALUES(?,?)
     * You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?)' at line 1
     *You're calling Statement.executeUpdate(String) with your prepared statement placeholder
     *query, which is incorrect. You should be calling PreparedStatement.executeUpdate().
     *Remove the reference to the query from your call to executeUpdate() and things should
     *work.
     */
    private static void insertRecord() throws SQLException{
        Connection conn = null;
        PreparedStatement prep = null;

        String sql = "INSERT INTO h_user"
            +"(username,password) VALUES"
            +"(?,?)";

        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            prep.setString(1, "Kity");
            prep.setString(2, "woshikity");

            System.out.println(sql);
            //Executes the SQL statement in this PreparedStatement object
            prep.executeUpdate();//preparedStatement.executeUpdate(sql); error

            System.out.println("A Record has been inserted!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(prep!=null){
                prep.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
    }
    private static void createTable() throws SQLException{
        Connection conn = null;
        PreparedStatement prep = null;

        String sql = "CREATE TABLE h_user("
            +"id int auto_increment primary key,"
            +"username varchar(50) NOT NULL,"
            +"password varchar(50) NOT NULL"
            +")";

        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            System.out.println(sql);
            prep.execute();
            System.out.println("Table \"h_user\" is created!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(prep!=null){
                prep.close();
            }
            if(conn!=null){
                conn.close();
            }
        }

    }

    private static void selectRecords() throws SQLException{
        Connection conn = null;
        PreparedStatement prep = null;

        String sql = "SELECT id,username,password FROM h_user where id > ?";
        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            prep.setInt(1, 3);
            System.out.println(sql);

            //execute select SQL statement

            ResultSet rs = prep.executeQuery();
            while(rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                System.out.println("id: "+ id);
                System.out.println("username: "+ username);
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(prep!=null){
                prep.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
    }
    private static void deleteRecords() throws SQLException{
        Connection conn = null;
        PreparedStatement prep = null;

        String sql = "DELETE FROM h_user where id=?";
        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            prep.setInt(1, 3);
            System.out.println(sql);
            prep.executeUpdate();

        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(prep!=null){
                prep.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
    }
    private static void batchInsertRecords() throws SQLException{
        Connection conn = null;
        PreparedStatement prep = null;

        String sql = "INSERT INTO h_user(username,password) values"
            +"(?,?)";

        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);

            conn.setAutoCommit(false);
            prep.setString(1, "Ella");
            prep.setString(2, "elladepassword");
            prep.addBatch();

            prep.setString(1, "Jerry");
            prep.setString(2, "oooooooooo");
            prep.addBatch();
            prep.executeBatch();
            conn.commit();

            System.out.println("Batch Records have been inserted!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(prep!=null){
                prep.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
    }
    private static Connection getDBConnection(){
        Connection conn = null;
        try{
            Class.forName(DB_DRIVER);
        }catch(ClassNotFoundException e){
            System.out.println(e.getMessage());
        }
        try{
            conn = DriverManager.getConnection(DB_CONNECTION,DB_USER, DB_PASSWORD);
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }

        return conn;
    }

}