PreparedStatement对象 可以防止SQL注入，效率更好。1、新增package com.sw.kuangshen.lesson03; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.util.Date; import java.sql.PreparedStatement; import java.sql.SQLException; /** * @Author suaxi * @Date 2020/11/9 10:12 */ public class TestInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement pstm = null; try { conn = JdbcUtils.getConnection(); //PreparedStatement与Statement的区别 //使用？占位符代替参数 String sql = "insert into users(id,name,passwd,email,birthday) values(?,?,?,?,?)"; pstm = conn.prepareStatement(sql); //预编译SQl，先编译sql但不执行 //手动给参数赋值 pstm.setInt(1,5); pstm.setString(2,"liubo"); pstm.setString(3,"12345"); pstm.setString(4,"liubo@qq.com"); //注：sql.Date 数据库使用 java.sql.Date() // util.Date Java new Date().getTime() 获得当前时间戳 pstm.setDate(5,new java.sql.Date(new Date().getTime())); //执行 int i = pstm.executeUpdate(); if(i>0){ System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pstm,null); } } } 2、删除package com.sw.kuangshen.lesson03; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Date; /** * @Author suaxi * @Date 2020/11/9 10:28 */ public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement pstm = null; try { conn = JdbcUtils.getConnection(); //PreparedStatement与Statement的区别 //使用？占位符代替参数 String sql = "delete from users where id=?"; pstm = conn.prepareStatement(sql); //预编译SQl，先编译sql但不执行 //手动给参数赋值 pstm.setInt(1,5); //执行 int i = pstm.executeUpdate(); if(i>0){ System.out.println("删除成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pstm,null); } } } 3、修改package com.sw.kuangshen.lesson03; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Date; /** * @Author suaxi * @Date 2020/11/9 10:28 */ public class TestUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement pstm = null; try { conn = JdbcUtils.getConnection(); //PreparedStatement与Statement的区别 //使用？占位符代替参数 String sql = "update users set `name`=? where id=?"; pstm = conn.prepareStatement(sql); //预编译SQl，先编译sql但不执行 //手动给参数赋值 pstm.setString(1,"孙笑川"); pstm.setInt(2,2); //执行 int i = pstm.executeUpdate(); if(i>0){ System.out.println("更新成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pstm,null); } } } 4、查询package com.sw.kuangshen.lesson03; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @Author suaxi * @Date 2020/11/9 10:34 */ public class TestSelect { public static void main(String[] args) { Connection conn = null; PreparedStatement pstm = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "select * from users where id=?"; pstm = conn.prepareStatement(sql); //预编译 pstm.setInt(1,2); //传递参数 rs = pstm.executeQuery(); //执行 if (rs.next()){ System.out.println(rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pstm,rs); } } } 5、防止SQL注入package com.sw.kuangshen.lesson03; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.*; /** * @Author suaxi * @Date 2020/11/9 9:57 */ public class SQlzhuru { public static void main(String[] args) { //login("dd","12345"); //正常登录 login("''or 1=1","12345"); } //登录 public static void login(String username,String passwd){ Connection conn = null; PreparedStatement pstm = null; ResultSet rs =null; try { conn = JdbcUtils.getConnection(); //PreparedStatement防止SQL注入的本质，把传递进来的参数当作字符 //假设其中存在转义字符，例如 ' 会被直接转义 String sql = "select * from users where `name`=? and passwd=?"; pstm = conn.prepareStatement(sql); pstm.setString(1,username); pstm.setString(2,passwd); rs = pstm.executeQuery(); while (rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("passwd")); System.out.println("================="); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pstm,rs); } } }

statement对象 statement对象jdbc中的statement对象用于向数据库发送SQL语句，例如：想完成对数据库的增删查改，只需通过这个对象向数据库发送增删查改语句即可。Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的resultSet对象。CRUD操作 --create使用executeUpdate(String sql)方法完成数据添加操作Statement st = conn.createStatement(); String sql = "insert into user(...) values(...)"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("数据插入成功！"); }CRUD操作 --delete使用executeUpdate(String sql)方法完成数据删除操作Statement st = conn.createStatement(); String sql = "delete from user where id=1"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("删除成功！"); }CRUD操作 --update使用executeUpdate(String sql)方法完成数据修改操作Statement st = conn.createStatement(); String sql = "update user set name='孙笑川' where id =1; int num = st.executeUpdate(sql); if(num>0){ System.out.println("修改成功！"); }CRUD操作 --read使用executeQuery(String sql)方法完成数据查询操作Statement st = conn.createStatement(); String sql = "select * from user; ResultSet rs = st.executeQuery(sql); while(re.next*()){ //根据获取列的数据类型，分别调用rs相应的方法映射到java对象中 }代码实现1、提取工具类package com.sw.kuangshen.utils; import java.io.InputStream; import java.sql.*; import java.util.Properties; /** * @Author suaxi * @Date 2020/11/8 21:06 */ public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static{ try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1、驱动只用加载一次 Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } //释放资源 public static void release(Connection conn, Statement st, ResultSet rs){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } 2、编写增删改的方法，都是用executeUpdate新增package com.sw.kuangshen; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @Author suaxi * @Date 2020/11/8 21:16 */ public class TestInsert { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获得SQL的执行对象 String sql ="INSERT INTO users(id,`name`,`passwd`,`email`,`birthday`)" + "VALUES(4,'dd','12345','dd@qq.com','1994-01-01')"; int i = st.executeUpdate(sql); if(i>0){ System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } } 删除package com.sw.kuangshen; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @Author suaxi * @Date 2020/11/8 21:16 */ public class TestDelete { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获得SQL的执行对象 String sql ="DELETE FROM users WHERE id=1"; int i = st.executeUpdate(sql); if(i>0){ System.out.println("删除成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } } 修改package com.sw.kuangshen; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @Author suaxi * @Date 2020/11/8 21:16 */ public class TestUpdate { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获得SQL的执行对象 String sql ="UPDATE users SET `name`='sunxiaochuan' WHERE id=2"; int i = st.executeUpdate(sql); if(i>0){ System.out.println("更新成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } } 3、查询 executeQuerypackage com.sw.kuangshen; import com.sw.kuangshen.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @Author suaxi * @Date 2020/11/8 21:45 */ public class TestSelect { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs =null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "select * from users where id=2"; rs = st.executeQuery(sql); //查询完毕会返回一个结果集 while (rs.next()){ System.out.println(rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }

第一个JDBC程序 创建测试数据库CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcStudy; CREATE TABLE users( id INT PRIMARY KEY, name VARCHAR(40), passwd VARCHAR(40), email VARCHAR(40), birthday DATE ); INSERT INTO users(id,name,passwd,email,birthday) VALUES(1,'aa','12345','aa@qq.com','1991-01-01'), (2,'bb','12345','bb@qq.com','1992-01-01'), (3,'cc','12345','cc@qq.com','1993-01-01');1、创建一个普通项目2、导入数据库驱动3、编写测试代码package com.sw.kuangshen; import java.sql.*; /** * @Author suaxi * @Date 2020/11/7 10:58 */ public class jdbcStudyDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.用户信息和url //useUnicode=true&characterEncoding=utf8&useSSL=true //支持中文编码 设置字符集为utf-8 使用安全的连接 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username = "root"; String password = "123456"; //3.连接成功，数据库对象 Connection connection = DriverManager.getConnection(url, username, password); //4.执行SQL的对象 Statement Statement statement = connection.createStatement(); //5.执行SQL的对象去执行SQL语句 String sql = "select * from users"; ResultSet resultSet = statement.executeQuery(sql); //返回的结果集，结果集里封装了查询出来的所有结果、 while (resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("name")); System.out.println("pwd="+resultSet.getObject("passwd")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birth="+resultSet.getObject("birthday")); System.out.println("========================="); } //6.释放连接(先开放的资源后释放) resultSet.close(); statement.close(); connection.close(); } } 步骤总结：1、加载驱动2、连接数据库DriverManager3、获取执行SQL的对象 Statement4、获得返回的结果集5、释放连接DriverManager//DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动 Connection connection = DriverManager.getConnection(url, username, password); //connection 代表数据库 //数据库设置自动提交 //事务提交 //事务回滚 connection.rollback(); connection.commit(); connection.setAutoCommit();URLString url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; //mysql默认3306 //协议://主机地址:端口号/数据库名?参数1&参数2&参数3 //oracle默认1521 //jdbc:oracle:thin:@localhost:1521:sidStatement 执行SQL的对象 PrepareStatement执行SQL的对象String sql = "select * from users"; //编写SQL statement.executeQuery(); //查询操作返回resultSet statement.execute(); //执行任何SQL（其中包含一个判断是什么类型SQL的过程） statement.executeUpdate(); //更新、插入、删除都是用用这个，返回一个受影响的行数ResultSet 查询结果集：封装了所有的查询结果获得指定的数据类型resultSet.getObject(); //在不知道类型的情况下使用 //如果知道列的类型就使用指定的类型 resultSet.getString(); resultSet.getInt(); resultSet.getFloat(); resultSet.getDate(); ...遍历，指针resultSet.beforeFirst(); //移动到最前面 resultSet.afterLast(); //移动到最后面 resultSet.next(); //移动到下一个数据 resultSet.previous(); //移动到前一行 resultSet.absolute(row); //移动到指定行释放资源//释放连接(先开放的资源后释放) resultSet.close(); statement.close(); connection.close();

Java Web用户角色与权限的管理 以SSM框架为例后台管理系统——给用户添加角色流程分析与设计1.流程分析注：图片来源itheima的ssm课件2.UserController //给用户添加角色 @RequestMapping("/addRoleToUser.do") public String addRoleToUser(@RequestParam(name = "userId",required = true) Integer userId,@RequestParam(name = "ids",required = true) Integer[] roleIds){ //单个用户可以对应多个角色，所以采用数组存userService.findOtherRoles(id)查询出来的角色id userService.addRoleToUser(userId,roleIds); return "redirect:findAll.do"; } //查询用户以及用户可以添加的角色 @RequestMapping("/findUserByIdAndAllRole.do") public ModelAndView findUserByIdAndAllRole(@RequestParam(name = "id",required = true) Integer id){ ModelAndView mv = new ModelAndView(); //1.根据用户id查询用户 Userinfo userinfo = userService.findById(id); //2.根据用户id查询可以添加的角色 List<Role> roles = userService.findOtherRoles(id); mv.addObject("user",userinfo); mv.addObject("roleList",roles); mv.setViewName("user-role-add"); return mv; }3.UserService接口void addRoleToUser(Integer userId, Integer[] roleIds);4.UserServiceImpl //给用户添加角色 @Override public void addRoleToUser(Integer userId, Integer[] roleIds) { for (Integer roleId : roleIds) { //（单个用户可以对应多个角色）给用户添加角色时，从数组中遍历取出角色id userDao.addRoleToUser(userId,roleId); } } //根据用户id查询可以添加的角色 @Override public List<Role> findOtherRoles(Integer id) { return userDao.findOtherRoles(id); }5.UserDao//查询用户可以添加的角色@Select("select * from role where id not in(select roleId from users_role where userId=#{id})") List<Role> findOtherRoles(Integer id); @Insert("insert into users_role(userId,roleId) values(#{userId},#{roleId})") //当传入mybatis的参数有多个时，必须写@Param void addRoleToUser(@Param("userId") Integer userId,@Param("roleId") Integer roleId);注：添加权限与添加角色同理

SpringSecurity报错问题 项目一启动在控制台就会先报错Access is denied，原因如下：spring-security.xml里配置了使用默认登陆页面在index.jsp里面做了页面跳转到主页web.xml里配置的默认主页是index，需要先由SpringSecurity进行用户鉴权，所以出现了这个原因在web.xml里把原先的index改成login，问题解决