JDBC中的PreparedStatement对象

• 提取工具类

  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");
  
              //驱动只用加载一次
              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, PreparedStatement ps, ResultSet rs){
          if(rs != null){
              try {
                  rs.close();
              }catch(SQLException e){
                  e.printStackTrace();
              }
          }
          if(ps != null){
              try {
                  ps.close();
              }catch(SQLException e){
                  e.printStackTrace();
              }
          }
          if(conn != null){
              try {
                  conn.close();
              }catch(SQLException e){
                  e.printStackTrace();
              }
          }
      }
  }
  

• insert添加

  public class TestInsert {
      public static void main(String[] args) {
          Connection conn = null;
          PreparedStatement ps = null;
  
          try {
              conn = JdbcUtils.getConnection();
  
              //使用?占位符代替参数
              String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
              ps = conn.prepareStatement(sql);
  
              //给参数手动赋值
              ps.setInt(1,5);
              ps.setString(2,"zhaoliu");
              ps.setString(3,"123456");
              ps.setString(4,"123456@qq.com");
              ps.setDate(5,new java.sql.Date(new Date().getTime()));
  
              //执行
              int i = ps.executeUpdate();
              if(i>0){
                  System.out.println("插入成功");
              }
  
          } catch (SQLException e) {
              e.printStackTrace();
          }finally {
              JdbcUtils.release(conn,ps,null);
          }
      }
  }
  

• delete删除

  public class TestDelete {
      public static void main(String[] args) {
          Connection conn = null;
          PreparedStatement ps = null;
          try {
              conn = JdbcUtils.getConnection();
              String sql = "delete from users where id = ?";
              ps = conn.prepareStatement(sql);
              ps.setInt(1,5);
              int i = ps.executeUpdate();
              if(i>0){
                  System.out.println("删除成功");
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }finally {
              JdbcUtils.release(conn,ps,null);
          }
      }
  }
  

• update修改

  public class TestUpdate {
      public static void main(String[] args) {
          Connection conn = null;
          PreparedStatement ps = null;
          try {
              conn = JdbcUtils.getConnection();
              String sql = "update users set name = ? where id = ?";
              ps = conn.prepareStatement(sql);
              ps.setString(1,"张三");
              ps.setInt(2,1);
              int i = ps.executeUpdate();
              if(i>0){
                  System.out.println("修改成功");
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }finally {
              JdbcUtils.release(conn,ps,null);
          }
      }
  }
  

• select查询

  public class TestSelect {
      public static void main(String[] args) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
  
          try {
              conn = JdbcUtils.getConnection();
              String sql = "select `name`,`password` from users where `id` = ?";
              ps = conn.prepareStatement(sql);
              ps.setInt(1,1);
              rs = ps.executeQuery();
              while (rs.next()){
                  System.out.println("name=" + rs.getString("name"));
                  System.out.println("password=" +rs.getString("password"));
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }finally {
              JdbcUtils.release(conn,ps,rs);
          }
  
      }
  }
  

• 防止SQL注入

  • PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符，假设其中存在转义字符，比如’'就会直接忽略

  public class SQL {
      public static void main(String[] args) {
  
          //正常登录
          login("张三","123456");
          //SQL注入没用
          //login("'' or 1=1","123456");
      }
  
      //登录业务
      public static void login(String username,String password){
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
              conn = JdbcUtils.getConnection();
              String sql = "select * from users where `name` = ? and `password` = ?";
              ps = conn.prepareStatement(sql);
              ps.setString(1,username);
              ps.setString(2,password);
              rs = ps.executeQuery();
              while (rs.next()){
                  System.out.println("name="+rs.getString("name"));
                  System.out.println("password="+rs.getString("password"));
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }finally {
              JdbcUtils.release(conn,ps,rs);
          }
      }
  }