JDBC初体验（二）

一、SQL注入

1.1 SQL注入原理

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

1.2 防止SQL注入的方法

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

二、PreparedStatement接口

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

PreparedStatement的使用

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

三、增、删、改、查操作代码演示

3.1 User类

package day02.pojo;

/**
 * @desc: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 +
                '}';
    }
}

3.2 JDBC/增删改查类

package day02.IncreaseDeleteChangeQuery;

import day02.pojo.User;

import java.sql.*;

/**
 * @desc:JDBC类、增删改查类
 */

public class UserDao {
    Connection connection =null;
    Statement statement =null;
    PreparedStatement ps = null;
    ResultSet resultSet =null;

    /**
     * 用户登录
     * @param userName 用户名
     * @param userPass 用户密码
     * @return User
     */
    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 {
            // 创建PreparedStatement对象 发送SQL语句并预执行
            ps = connection.prepareStatement(sql);

            //3.1处理参数
            ps.setString(1,userName);
            ps.setString(2,userPass);

            //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;
        //1、获取连接对象
        getConnection();
        //2、编写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());
            //3、执行     增删改的执行方法是excuteUpdate()
            //返回一个整数，表示受影响的行数
            line = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return line;
    }



    /**
     * 增删改查——改
     */
    public int updateUser(User user) {
        int line = 0;
        //1、获取连接对象
        getConnection();
        //2、编写SQL语句
        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());
            //3、执行     增删改的执行方法是excuteUpdate()
            //返回一个整数，表示受影响的行数
            line = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return line;
    }



    /**
     * 增删改查——删
     */
    public  int deleteUser(int id) {
        int line = 0;
        //1、获取连接对象
        getConnection();
        //2、编写SQL语句
        String sql = "delete from user where id = ?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            //3、执行     增删改的执行方法是excuteUpdate()
            //返回一个整数，表示受影响的行数
            line = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return line;
    }





    //↓↓↓以下两部分代码是固定的，不需要改动，所以封装起来
    /**
     * 加载驱动和建立连接
     */
    public Connection getConnection(){
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");

            //2.建立连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?useSSL=false","root","zkz2002513>");
        } 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();
            }
        }
    }

}

3.3 增删改查——增

package day02.IncreaseDeleteChangeQuery;

import day02.pojo.User;

/**
 * @desc:增删改查操作——增加main方法
 */

public class IncreaseMain {
    public static void main(String[] args) {
        UserDao userDao = new UserDao();
        User user = new User();
        user.setUserName("诸葛亮");
        user.setUserPass("9087666");
        user.setRole(3);

        int line = userDao.saveUser(user);
        System.out.println(line > 0 ? "添加成功": "添加失败");
    }
}

3.4 增删改查——删

package day02.IncreaseDeleteChangeQuery;

/**
 * @desc:增删改查操作——删除main方法
 */

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

3.5 增删改查——改

package day02.IncreaseDeleteChangeQuery;

import day02.pojo.User;

import java.util.Scanner;

/**
 * @desc:增删改查操作——改main方法
 */

public class ChangeMain {
    public static void main(String[] args) {
/*        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要修改的用户id：");
        int id = sc.nextInt();
        System.out.println("请输入要修改的用户名：");
        String username = sc.next();
        System.out.println("请输入新的的密码：");
        String password = sc.next();
        System.out.println("请输入角色类型：");
        int role = sc.nextInt();
        User user = new User(id, username, password, role);
        UserDao userDao = new UserDao();*/

        UserDao userDao = new UserDao();
        User user = new User(8,"赵云","24678",2);

        int line = userDao.updateUser(user);
        System.out.println(line > 0 ? "修改成功" : "修改失败");
    }
}

3.6 增删改查——查

package day02.IncreaseDeleteChangeQuery;

import day02.pojo.User;

import java.util.Scanner;

/**
 * @desc:增删改查操作——查询main方法
 */

public class QueryMain {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        UserDao userDao = new UserDao();
        System.out.println("****************************************");
        System.out.println("\t\t\t\t\t用户管理系统");
        System.out.println("****************************************");
        System.out.print("请输入用户名：");
        String userName = sc.next();
        System.out.print("请输入密码：");
        String userPass = sc.next();

        User user = userDao.toLogin(userName,userPass);
        if (user == null) {
            System.out.println("登录失败，用户名或密码错误！");
        } else {
            System.out.println("登录成功，欢迎【" + user.getUserName() + "】！");
        }
    }
}