prepareStatement的使用

1.1prepareStatement解决sql注入的问题

//演示sql注入的安全问题
    public static void main(String [] args) throws Exception{
        Scanner scanner=new Scanner(System.in); //Scanner类有没有讲过。
        System.out.print("请输入账号:");
        String username = scanner.nextLine();
        System.out.print("请输入密码:");
        String password = scanner.nextLine(); //你输入的账号和密码 nextLine() 可以输入空格 回车任认为结束  next()输入空格后认为输入结束。
        boolean b = sqlSafe02(username, password);
    }

    private static boolean sqlSafe(String name,String password) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai", "root", "root");

        //使用PrepareStatement 这里的? 是占位符。
        String sql="select * from t_user where username=? and password=?";
        PreparedStatement ps = conn.prepareStatement(sql);//预编译sql
        //为占位符赋值。根据占位符的类型使用不同的方法来赋值
        ps.setString(1,name); //1表示第一个占位符  name:表示第一个占位符的值
        ps.setString(2,password);

        //执行sql语句
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            System.out.println("登录成功");
            return true;
        }
        System.out.println("登录失败");
        return false;
    }

2.prepareStatement完成增删改查

 /*添加记录*/
    @Test
    public void addDemo01() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
        String user="root";
        String pwd ="root";
        Connection connection = DriverManager.getConnection(url, user, pwd);
        String sql = "insert into student values(null,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,"张三");
        ps.setInt(2,16);
        ps.setString(3,"北京");
        ps.executeUpdate();
        ps.close();
    }

/*删除记录*/
    @Test
    public void deleteDemo01() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
        String user="root";
        String pwd ="root";
        Connection connection = DriverManager.getConnection(url, user, pwd);
        String sql = "delete from student where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,3);
        ps.executeUpdate();
        ps.close();
    }

/*修改记录*/
    @Test
    public void updateDemo01() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
        String user="root";
        String pwd = "root";
        Connection connection = DriverManager.getConnection(url, user, pwd);
        String sql = "update student set address=? where id=26";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,"北京");
        ps.executeUpdate();
        ps.close();
    }
 /*查询记录*/
    @Test
    public void searchDemo01() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
        String user="root";
        String pwd = "root";
        Connection connection = DriverManager.getConnection(url, user, pwd);
        String sql="select * from student where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,7);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            String name = rs.getString("name");
            int age = rs.getInt("age");
            String address = rs.getString("address");
            System.out.println(name+"\t"+age+"\t"+address);
        }
        rs.close();
    }