PreparedStatement：

package com.shellway.jdbc;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JDBCTools {    /**     * 執行 SQL 語句, 使用 PreparedStatement     * @param sql     * @param args: 填寫 SQL 占位符的可變參數     */    public static void update(String sql, Object ... args){        Connection connection = null;        PreparedStatement preparedStatement = null;                try {            connection = JDBCTools.getConnection();            preparedStatement = connection.prepareStatement(sql);                        for(int i = 0; i < args.length; i++){                preparedStatement.setObject(i + 1, args[i]);            }                        preparedStatement.executeUpdate();                    } catch (Exception e) {            e.printStackTrace();        } finally{            JDBCTools.releaseDB(null, preparedStatement, connection);        }    }        /**     * 執行 SQL 的方法     *      * @param sql: insert, update 或 delete。 而不包含 select     */    public static void update(String sql) {        Connection connection = null;        Statement statement = null;        try {            // 1. 獲取數據庫連接            connection = getConnection();            // 2. 調用 Connection 對象的 createStatement() 方法獲取 Statement 對象            statement = connection.createStatement();            // 4. 發送 SQL 語句: 調用 Statement 對象的 executeUpdate(sql) 方法            statement.executeUpdate(sql);        } catch (Exception e) {            e.printStackTrace();        } finally {            // 5. 關閉數據庫資源: 由里向外關閉.            releaseDB(null, statement, connection);        }    }    /**     * 釋放數據庫資源的方法     *      * @param resultSet     * @param statement     * @param connection     */    public static void releaseDB(ResultSet resultSet, Statement statement,            Connection connection) {        if (resultSet != null) {            try {                resultSet.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (statement != null) {            try {                statement.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (connection != null) {            try {                connection.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    /**     * 獲取數據庫連接的方法     */    public static Connection getConnection() throws IOException,            ClassNotFoundException, SQLException {        // 0. 讀取 jdbc.properties        /**         * 1). 屬性文件對應 Java 中的 Properties 類 2). 可以使用類加載器加載 bin 目錄(類路徑下)的文件         */        Properties properties = new Properties();        InputStream inStream = ReviewTest.class.getClassLoader()                .getResourceAsStream("jdbc.properties");        properties.load(inStream);        // 1. 準備獲取連接的 4 個字符串: user, passWord, jdbcUrl, driverClass        String user = properties.getProperty("user");        String password = properties.getProperty("password");        String jdbcUrl = properties.getProperty("jdbcUrl");        String driverClass = properties.getProperty("driverClass");        // 2. 加載驅動: Class.forName(driverClass)        Class.forName(driverClass);        // 3. 調用        // DriverManager.getConnection(jdbcUrl, user, password)        // 獲取數據庫連接        Connection connection = DriverManager.getConnection(jdbcUrl, user,                password);        return connection;    }}

    /**     * 使用 PreparedStatement 將有效的解決 SQL 注入問題.     */    @Test    public void testSQLInjection2() {        String username = "a' OR PASSWORD = ";        String password = " OR '1'='1";        String sql = "SELECT * FROM users WHERE username = ? "                + "AND password = ?";        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = JDBCTools.getConnection();            preparedStatement = connection.prepareStatement(sql);            preparedStatement.setString(1, username);            preparedStatement.setString(2, password);            resultSet = preparedStatement.executeQuery();            if (resultSet.next()) {                System.out.println("登錄成功!");            } else {                System.out.println("用戶名和密碼不匹配或用戶名不存在. ");            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCTools.releaseDB(resultSet, preparedStatement, connection);        }    }    /**     * SQL 注入.     */    @Test    public void testSQLInjection() {        String username = "a' OR PASSWORD = ";        String password = " OR '1'='1";        String sql = "SELECT * FROM users WHERE username = '" + username                + "' AND " + "password = '" + password + "'";        System.out.println(sql);        Connection connection = null;        Statement statement = null;        ResultSet resultSet = null;        try {            connection = JDBCTools.getConnection();            statement = connection.createStatement();            resultSet = statement.executeQuery(sql);            if (resultSet.next()) {                System.out.println("登錄成功!");            } else {                System.out.println("用戶名和密碼不匹配或用戶名不存在. ");            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCTools.releaseDB(resultSet, statement, connection);        }    }

練習：

1)請輸入考生的詳細信息

Type:

IDCard:

ExamCard:

StudentName:

Location:

Grade:

提示：信息錄入成功!

2).在 eclipse 中建立 java 程序：輸入身份證號或準考證號可以查詢到學生的基本信息。結果如下：

    @Test    public void testGetStudent() {        // 1. 得到查詢的類型        int searchType = getSearchTypeFromConsole();        // 2. 具體查詢學生信息        Student student = searchStudent(searchType);        // 3. 打印學生信息        printStudent(student);    }    /**     * 打印學生信息: 若學生存在則打印其具體信息. 若不存在: 打印查無此人.     *      * @param student     */    private void printStudent(Student student) {        if (student != null) {            System.out.println(student);        } else {            System.out.println("查無此人!");        }    }    /**     * 具體查詢學生信息的. 返回一個 Student 對象. 若不存在, 則返回 null     *      * @param searchType     *            : 1 或 2.     * @return     */    private Student searchStudent(int searchType) {        String sql = "SELECT flowid, type, idcard, examcard,"                + "studentname, location, grade " + "FROM examstudent "                + "WHERE ";        Scanner scanner = new Scanner(System.in);        // 1. 根據輸入的 searchType, 提示用戶輸入信息:        // 1.1 若 searchType 為 1, 提示: 請輸入身份證號. 若為 2 提示: 請輸入準考證號        // 2. 根據 searchType 確定 SQL        if (searchType == 1) {            System.out.print("請輸入準考證號:");            String examCard = scanner.next();            sql = sql + "examcard = '" + examCard + "'";        } else {            System.out.print("請輸入身份證號:");            String examCard = scanner.next();            sql = sql + "idcard = '" + examCard + "'";        }        // 3. 執行查詢        Student student = getStudent(sql);        // 4. 若存在查詢結果, 把查詢結果封裝為一個 Student 對象        return student;    }    /**     * 根據傳入的 SQL 返回 Student 對象     *      * @param sql     * @return     */    private Student getStudent(String sql) {        Student stu = null;        Connection connection = null;        Statement statement = null;        ResultSet resultSet = null;        try {            connection = JDBCTools.getConnection();            statement = connection.createStatement();            resultSet = statement.executeQuery(sql);            if (resultSet.next()) {                stu = new Student(resultSet.getInt(1), resultSet.getInt(2),                        resultSet.getString(3), resultSet.getString(4),                        resultSet.getString(5), resultSet.getString(6),                        resultSet.getInt(7));            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCTools.releaseDB(resultSet, statement, connection);        }        return stu;    }    /**     * 從控制臺讀入一個整數, 確定要查詢的類型     *      * @return: 1. 用身份證查詢. 2. 用準考證號查詢 其他的無效. 并提示請用戶重新輸入.     */    private int getSearchTypeFromConsole() {        System.out.print("請輸入查詢類型: 1. 用身份證查詢. 2. 用準考證號查詢 ");        Scanner scanner = new Scanner(System.in);        int type = scanner.nextInt();        if (type != 1 && type != 2) {            System.out.println("輸入有誤請重新輸入!");            throw new RuntimeException();        }        return type;    }    @Test    public void testAddNewStudent() {        Student student = getStudentFromConsole();        addNewStudent2(student);    }    /**     * 從控制臺輸入學生的信息     */    private Student getStudentFromConsole() {        Scanner scanner = new Scanner(System.in);        Student student = new Student();        System.out.print("FlowId:");        student.setFlowId(scanner.nextInt());        System.out.print("Type: ");        student.setType(scanner.nextInt());        System.out.print("IdCard:");        student.setIdCard(scanner.next());        System.out.print("ExamCard:");        student.setExamCard(scanner.next());        System.out.print("StudentName:");        student.setStudentName(scanner.next());        System.out.print("Location:");        student.setLocation(scanner.next());        System.out.print("Grade:");        student.setGrade(scanner.nextInt());        return student;    }    public void addNewStudent2(Student student) {        String sql = "INSERT INTO examstudent(flowid, type, idcard, "                + "examcard, studentname, location, grade) "                + "VALUES(?,?,?,?,?,?,?)";        JDBCTools.update(sql, student.getFlowId(), student.getType(),                student.getIdCard(), student.getExamCard(),                student.getStudentName(), student.getLocation(),                student.getGrade());    }    public void addNewStudent(Student student) {        // 1. 準備一條 SQL 語句:        String sql = "INSERT INTO examstudent VALUES(" + student.getFlowId()                + "," + student.getType() + ",'" + student.getIdCard() + "','"                + student.getExamCard() + "','" + student.getStudentName()                + "','" + student.getLocation() + "'," + student.getGrade()                + ")";        System.out.println(sql);        // 2. 調用 JDBCTools 類的 update(sql) 方法執行插入操作.        JDBCTools.update(sql);    }}

在練習中要注意的問題：

1、在執行這一語句ps = conn.prepareStatement(sql);的時候，如果提示要強制轉換成prepareStatement類型，則不要轉換，只需要把包導入就行：import java.sql.PreparedStatement;2、遇到插入日期語句的時候，比如：ps.setDate(3, new Date(new java.util.Date().getTime()));前面的new Date（）是要聲明為java.sql包中的，里面的new Date（）則要聲明為java.util包中的。在使用Preparement的時候，最好在主程序中寫函數然后調用工具類JDBCTools.java中的更新方法：

public void addStudent(Student stu) throws Exception {    String sql = "insert into examstudent values(?,?,?,?,?,?,?)";    JDBCTools.testUpdate(sql, stu.getFlowID(), stu.getType(),            stu.getIdCard(), stu.getExamCard(), stu.getStudentName(),            stu.getLocation(), stu.getGrade());    }

在 工具類JDBCTools.java中寫更新方法，傳入 sql 和 可變參數args

public static void testUpdate(String sql, Object... args) throws Exception {    Connection conn = null;    PreparedStatement ps = null;    try {        conn = JDBCTools.getConnection();        ps = conn.prepareStatement(sql);        for (int i = 0; i < args.length; i++) {            ps.setObject(i + 1, args[i]);        }        System.out.println(sql);        ps.executeUpdate();    } catch (Exception e) {        e.printStackTrace();    } finally {        JDBCTools.release(null, ps, conn);    }}

下面寫個簡單而小巧的PrepareStatement例子：

    @Test    public void testPreparedStatement() {        Connection connection = null;        PreparedStatement preparedStatement = null;        try {            connection = JDBCTools.getConnection();            String sql = "INSERT INTO customers (name, email, birth) "                    + "VALUES(?,?,?)";            preparedStatement = connection.prepareStatement(sql);            preparedStatement.setString(1, "shellway");            preparedStatement.setString(2, "[email protected]");            preparedStatement.setDate(3,                    new Date(new java.util.Date().getTime()));            preparedStatement.executeUpdate();        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCTools.releaseDB(null, preparedStatement, connection);        }    }