JDBC初体验（二）——增、删、改、查

本课目标

理解SQL注入的概念

掌握 PreparedStatement 接口的使用

熟练使用JDBC完成数据库的增、删、改、查操作

SQL注入

注入原理：利用现有应用程序，将（恶意的）SQL命令注入到后台数据库引擎执行能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句

防止SQL注入的方法：

• 过滤用户输入的数据库中是否包含非法字符
• 分步校验，先使用用户名来查询用户，如果找到了，在比较密码
• 使用 PreparedStatement 接口

PreparedStatement接口是 Statement 的子接口，可以使用该接口来替换 Statement 接口

?PreparedStatement的使用

• 使用 Connection 对象的preparedStatement(String sql):即创建它时就让它与一条SQL语句绑定
• 编写SQL语句时，如果存在参数，使用“？” 作为数据占位符
• 调用PreparedStatement 的 setXXX()系列方法为占位符设置值，索引从1开始
• 调用 executeUpdate() 或 executeQuery() 方法，但要注意，调用没有参数的方法

PreparedStatement编程模板

使用JDBC完成数据添加的操作模板

使用JDBC完成数据修改的操作模板

使用JDBC完成数据删除的操作模板

代码演示

• User 类

public class User {
    private int id;
    private String userName;
    private String userPass;
    private int role;

    public User(int id, String userName, String userPass, int role) {
        this.id = id;
        this.userName = userName;
        this.userPass = userPass;
        this.role = role;
    }

    public User() {
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPass() {
        return userPass;
    }

    public void setUserPass(String userPass) {
        this.userPass = userPass;
    }

    public int getRole() {
        return role;
    }

    public void setRole(int role) {
        this.role = role;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", userPass='" + userPass + '\'' +
                ", role=" + role +
                '}';
    }
}

• 方法类

public class UserDo {
    Connection connection = null;
    Statement statement = null;
    PreparedStatement ps = null;
    ResultSet resultSet = null;
    /**
     * 用户登录——查找
     * 用户名
     * 密码
     */
    public User tologin(String userName, String userPass){
        //1.获取连接对象
        getConnection();
        //2.编写SQL语句
        String sql = "SELECT ID,USERNAME,ROLE FROM USER WHERE USERNAME = ? AND USERPASS = ?";
        System.out.println("要执行的SQL语句：" + sql);
        //3.创建statement对象
        User user = null;
        try {
            //statement = connection.createStatement();
            //创建PreparedStatement对象 发送SQL语句并执行
            ps = connection.prepareStatement(sql);
            //3.1处理参数
            ps.setString(1,userName);
            ps.setString(2,userPass);
            //resultSet = statement.executeQuery(sql);
            //4.执行并解析结果
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt(1));
                user.setUserName(resultSet.getString(2));
                user.setRole(resultSet.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeResource();
        }
        return user;
    }

    public User login(String userName,String userPass){
        //1.获取连接对象
        getConnection();
        //2.编写SQL语句
        String sql = "select ID,USERNAME,ROLE from user where userName = '"+userName+"' and userPass = '"+userPass+"'";
        System.out.println("要执行的SQL语句是：" + sql);
        //3.创建statement对象
        User user = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            //4.解析结果
            while (resultSet.next()){
                user = new User();
                user.setId(resultSet.getInt(1));
                user.setUserName(resultSet.getString(2));
                user.setRole(resultSet.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeResource();
        }
        return user;
    }

    public User login2(String userName,String userPass){
        //1.获取连接对象
        getConnection();
        //2.编写SQL语句
        String sql = "select ID,USERNAME,USERPASS,ROLE from user where userName = '"+userName+"'";
        System.out.println("要执行的SQL语句是：" + sql);
        //3.创建statement对象
        User user = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            //4.解析结果
            while (resultSet.next()){
                user = new User();
                user.setId(resultSet.getInt(1));
                user.setUserName(resultSet.getString(2));
                user.setUserPass(resultSet.getString(3));
                user.setRole(resultSet.getInt(4));
            }
            if(userPass.equals(user.getUserPass())){
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeResource();
        }
        return null;
    }

    /**
     * 增加用户
     */
    public int saveUser(User user){
        int line = 0;
        //获取连接对象
        getConnection();
        //sql语句
        String sql = "INSERT INTO USER VALUES(DEFAULT,?,?,?)";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getUserPass());
            ps.setInt(3,user.getRole());

            //执行  增删改的执行方法是executeUpdate()
            //返回受影响的行数
            line = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeResource();
        }
        return line;
    }


    /**
     * 修改用户
     */
    public int updateUser(User user){
        int line = 0;
        getConnection();
        String sql = "UPDATE USER SET USERNAME=?,USERPASS=?,ROLE=? WHERE ID=?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getUserPass());
            ps.setInt(3,user.getRole());
            ps.setInt(4,user.getId());

            line = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeResource();
        }
        return line;
    }


    /**
     * 删除用户
     */
    public int deleteUser(int id){
        int line = 0;
        getConnection();
        String sql = "delete from user where id =?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            line = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeResource();
        }
        return line;
    }

    /**
     * 获取连接对象
     * @return
     */
    public Connection getConnection(){
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.建立连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?useSSL=false","root","123456");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭资源
     */
    public void closeResource(){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ps!=null){
            try {
                ps.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();
            }
        }
    }
}

• main 方法

查——登录

public class TestQuery {
    
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        UserDo userDo = new UserDo();
        //验证登录
        System.out.print("请输入用户名：");
        String userName = sc.nextLine();
        System.out.print("请输入密码：");
        String userPass = sc.nextLine();
        User user = userDo.tologin(userName,userPass);
        if (user != null) {
            System.out.println("登录成功！欢迎您【" + user.getUserName() + "】！");
        }else {
            System.out.println("登录失败！用户名或密码错误！");
        }
    }
}

增加

public class TestInsert {
    public static void main(String[] args) {
        UserDo userDo = new UserDo();
        User user = new User();
        user.setUserName("yanlingji");
        user.setUserPass("666666");
        user.setRole(3);

        int line = userDo.saveUser(user);
        System.out.println(line > 0 ? "插入成功" : "插入失败");
    }
}

修改

public class TestUpdate {
    public static void main(String[] args) {
        UserDo userDo =new UserDo();
        User user = new User(13,"yanlingji","888888",1);
        int line = userDo.updateUser(user);
        System.out.println(line > 0 ? "修改成功" : "修改失败");
    }
}

删除

public class TestDelete {
    public static void main(String[] args) {
        UserDo userDo = new UserDo();
        int line = userDo.deleteUser(9);
        System.out.println(line > 0 ? "删除成功" : "删除失败");
    }
}