java攻城狮之路--复习JDBC(PrepareStatement)

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, "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);        }    }