MySQL——PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更高。

1. 增

public class TestInsert {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnection();

            // 区别
            // 使用问号占位符代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";

            ps = conn.prepareStatement(sql);  // 预编译SQl，先写sql，然后不执行

            // 手动给参数赋值
            ps.setInt(1,5);
            ps.setString(2,"haha");
            ps.setString(3,"55555");
            ps.setString(4,"5555@qq.com");
            // 注意点：sql.Date：数据库用      util.Date：Java用    Date().getTime()：获得时间戳   java.sql.Date()：转换
            ps.setDate(5, new java.sql.Date(new Date().getTime()));

            // 执行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            jdbcUtils.release(conn,ps,null);
        }
    }
}

2. 删

public class TestDelete {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnection();

            // 区别
            // 使用问号占位符代替参数
            String sql = "delete from users where id = ?";

            ps = conn.prepareStatement(sql);  // 预编译SQl，先写sql，然后不执行

            // 手动给参数赋值
            ps.setInt(1,5);

            // 执行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            jdbcUtils.release(conn,ps,null);
        }
    }
}

3. 改

public class TestUpdate {
    public static void main(String[] args) throws SQLException {


        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnection();

            // 区别
            // 使用问号占位符代替参数
            String sql = "update users set `NAME`=? where id=?";

            ps = conn.prepareStatement(sql);  // 预编译SQl，先写sql，然后不执行

            // 手动给参数赋值
            ps.setString(1,"hahaha");
            ps.setInt(2,5);

            // 执行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("修改成功！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            jdbcUtils.release(conn,ps,null);
        }
    }
}

4. 查

public class TestSelect {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnection();

            String sql = "select * from users where id=?";  // 编写SQL

            ps = conn.prepareStatement(sql);  // 预编译

            ps.setInt(1,4);  // 传递参数

            rs = ps.executeQuery();  // 执行

            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            jdbcUtils.release(conn,ps,rs);
        }
    }
}

5. 防止SQL注入

public class 防止SQL注入 {
    public static void main(String[] args) throws SQLException {

        login("chenyang8","123456");  //正常登录
        //login(" ' 'or 1=1","123456");  // 技巧

    }

    // 登录业务
    public static void login(String username, String password) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtils.getConnection();
            // PreparedStatement 防止SQl注入的本质，把传递进来的参数当作字符
            // 假设其中存在转义字符，比如说 ' 会被直接转义
            String sql = "select * from users where `NAME`=? and `PASSWORD`=?";

            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery();  //查询完毕会返回一个结果集
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            jdbcUtils.release(conn,st,rs);
        }
    }
}