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;}@Overridepublic 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 ? "删除成功" : "删除失败");}
}