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 {    /**     * 執(zhí)行 SQL 語(yǔ)句, 使用 PreparedStatement     * @param sql     * @param args: 填寫 SQL 占位符的可變參數(shù)     */    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);        }    }        /**     * 執(zhí)行 SQL 的方法     *      * @param sql: insert, update 或 delete。 而不包含 select     */    public static void update(String sql) {        Connection connection = null;        Statement statement = null;        try {            // 1. 獲取數(shù)據(jù)庫(kù)連接            connection = getConnection();            // 2. 調(diào)用 Connection 對(duì)象的 createStatement() 方法獲取 Statement 對(duì)象            statement = connection.createStatement();            // 4. 發(fā)送 SQL 語(yǔ)句: 調(diào)用 Statement 對(duì)象的 executeUpdate(sql) 方法            statement.executeUpdate(sql);        } catch (Exception e) {            e.printStackTrace();        } finally {            // 5. 關(guān)閉數(shù)據(jù)庫(kù)資源: 由里向外關(guān)閉.            releaseDB(null, statement, connection);        }    }    /**     * 釋放數(shù)據(jù)庫(kù)資源的方法     *      * @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();            }        }    }    /**     * 獲取數(shù)據(jù)庫(kù)連接的方法     */    public static Connection getConnection() throws IOException,            ClassNotFoundException, SQLException {        // 0. 讀取 jdbc.properties        /**         * 1). 屬性文件對(duì)應(yīng) Java 中的 Properties 類 2). 可以使用類加載器加載 bin 目錄(類路徑下)的文件         */        Properties properties = new Properties();        InputStream inStream = ReviewTest.class.getClassLoader()                .getResourceAsStream("jdbc.properties");        properties.load(inStream);        // 1. 準(zhǔn)備獲取連接的 4 個(gè)字符串: 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. 加載驅(qū)動(dòng): Class.forName(driverClass)        Class.forName(driverClass);        // 3. 調(diào)用        // DriverManager.getConnection(jdbcUrl, user, password)        // 獲取數(shù)據(jù)庫(kù)連接        Connection connection = DriverManager.getConnection(jdbcUrl, user,                password);        return connection;    }}

    /**     * 使用 PreparedStatement 將有效的解決 SQL 注入問(wèn)題.     */    @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);        }    }

練習(xí)：

1)請(qǐng)輸入考生的詳細(xì)信息

Type:

IDCard:

ExamCard:

StudentName:

Location:

Grade:

提示：信息錄入成功!

2).在 eclipse 中建立 java 程序：輸入身份證號(hào)或準(zhǔn)考證號(hào)可以查詢到學(xué)生的基本信息。結(jié)果如下：

    @Test    public void testGetStudent() {        // 1. 得到查詢的類型        int searchType = getSearchTypeFromConsole();        // 2. 具體查詢學(xué)生信息        Student student = searchStudent(searchType);        // 3. 打印學(xué)生信息        printStudent(student);    }    /**     * 打印學(xué)生信息: 若學(xué)生存在則打印其具體信息. 若不存在: 打印查無(wú)此人.     *      * @param student     */    private void printStudent(Student student) {        if (student != null) {            System.out.println(student);        } else {            System.out.println("查無(wú)此人!");        }    }    /**     * 具體查詢學(xué)生信息的. 返回一個(gè) Student 對(duì)象. 若不存在, 則返回 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. 根據(jù)輸入的 searchType, 提示用戶輸入信息:        // 1.1 若 searchType 為 1, 提示: 請(qǐng)輸入身份證號(hào). 若為 2 提示: 請(qǐng)輸入準(zhǔn)考證號(hào)        // 2. 根據(jù) searchType 確定 SQL        if (searchType == 1) {            System.out.print("請(qǐng)輸入準(zhǔn)考證號(hào):");            String examCard = scanner.next();            sql = sql + "examcard = '" + examCard + "'";        } else {            System.out.print("請(qǐng)輸入身份證號(hào):");            String examCard = scanner.next();            sql = sql + "idcard = '" + examCard + "'";        }        // 3. 執(zhí)行查詢        Student student = getStudent(sql);        // 4. 若存在查詢結(jié)果, 把查詢結(jié)果封裝為一個(gè) Student 對(duì)象        return student;    }    /**     * 根據(jù)傳入的 SQL 返回 Student 對(duì)象     *      * @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;    }    /**     * 從控制臺(tái)讀入一個(gè)整數(shù), 確定要查詢的類型     *      * @return: 1. 用身份證查詢. 2. 用準(zhǔn)考證號(hào)查詢 其他的無(wú)效. 并提示請(qǐng)用戶重新輸入.     */    private int getSearchTypeFromConsole() {        System.out.print("請(qǐng)輸入查詢類型: 1. 用身份證查詢. 2. 用準(zhǔn)考證號(hào)查詢 ");        Scanner scanner = new Scanner(System.in);        int type = scanner.nextInt();        if (type != 1 && type != 2) {            System.out.println("輸入有誤請(qǐng)重新輸入!");            throw new RuntimeException();        }        return type;    }    @Test    public void testAddNewStudent() {        Student student = getStudentFromConsole();        addNewStudent2(student);    }    /**     * 從控制臺(tái)輸入學(xué)生的信息     */    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. 準(zhǔn)備一條 SQL 語(yǔ)句:        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. 調(diào)用 JDBCTools 類的 update(sql) 方法執(zhí)行插入操作.        JDBCTools.update(sql);    }}

在練習(xí)中要注意的問(wèn)題：

1、在執(zhí)行這一語(yǔ)句ps = conn.prepareStatement(sql);的時(shí)候，如果提示要強(qiáng)制轉(zhuǎn)換成prepareStatement類型，則不要轉(zhuǎn)換，只需要把包導(dǎo)入就行：import java.sql.PreparedStatement;2、遇到插入日期語(yǔ)句的時(shí)候，比如：ps.setDate(3, new Date(new java.util.Date().getTime()));前面的new Date（）是要聲明為java.sql包中的，里面的new Date（）則要聲明為java.util包中的。在使用Preparement的時(shí)候，最好在主程序中寫函數(shù)然后調(diào)用工具類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 和 可變參數(shù)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);    }}

下面寫個(gè)簡(jiǎn)單而小巧的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, "shellway@163.com");            preparedStatement.setDate(3,                    new Date(new java.util.Date().getTime()));            preparedStatement.executeUpdate();        } catch (Exception e) {            e.printStackTrace();        } finally {            JDBCTools.releaseDB(null, preparedStatement, connection);        }    }