智能建站设计,做贸易进出口要什么网站平台,搜索引擎优化名词解释,免费可以看污app秋葵本课目标
理解SQL注入的概念
掌握 PreparedStatement 接口的使用
熟练使用JDBC完成数据库的增、删、改、查操作
SQL注入
注入原理#xff1a;利用现有应用程序#xff0c;将#xff08;恶意的#xff09;SQL命令注入到后台数据库引擎执行能力#xff0c;它可以通过在…本课目标
理解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?useSSLfalse,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 ? 删除成功 : 删除失败);}
}
