【PreparedStatement】对数据库增删改查

PreparedStatement概述及使用

PreparedStatement使用步骤

• 第一步
  编写带有？占位符的SQL语句

• 第二步
  使用Connection的prepareStatement( )方法和包含了？占位符的SQL字符串创建PreparedStatement对象并对SQL语句进行预编译

• 第三步
  使用PreparedStatement的setXXX(index , value)方法传入实参取代之前的？占位符

• 第四步
  使用PreparedStatement的execute( )、 executeUpdate( ) 、 executeQuery( )方法执行 SQL 语句

  使用PreparedStatement实现对学生表的增删改查，代码如下：

1.查询学生表里的内容：

  public static void main(String[] args) {
    select();
  }
  public static void select(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
   	  //加载驱动
      Class.forName("com.mysql.jdbc.Driver");
      //建立连接
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test0221", "root", "root");
      String sql = "select * from student";
      //创建preparedStatement
      preparedStatement = connection.prepareStatement(sql);
      //执行SQL
      resultSet = preparedStatement.executeQuery();
      while (resultSet.next()){
        int id = resultSet.getInt(1);
        String name = resultSet.getString(2);
        Student student = new Student(id, name);
        System.out.println(student);
      }
    }
    catch (Exception e){
      System.out.println(e);
    }
    //关闭资源
    finally {
      if (connection!=null){
        try {
          connection.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
      if (preparedStatement!=null){
        try {
          preparedStatement.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
      if (resultSet!=null){
        try {
          resultSet.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
    }
  }

结果如下：

2.向学生表内添加学生

  public static void main(String[] args) {
    insert();
    select();
  }
    public static void insert(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test0221", "root", "root");
      String sql = "insert into student values (?,?)";
      preparedStatement = connection.prepareStatement(sql);
      int id = 6;
      String name = "kk";
      preparedStatement.setInt(1, id);
      preparedStatement.setString(2, name);
      preparedStatement.executeUpdate();
    }
    catch (Exception e){
      System.out.println(e);
    }
    finally {
      if (connection!=null){
        try {
          connection.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
      if (preparedStatement!=null){
        try {
          preparedStatement.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
    }
  }

得到结果如下：

3.对学生表内的内容进行修改

  public static void main(String[] args) {
    update();
    select();
  }
    public static void update(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test0221", "root", "root");
      String sql = "update student set studentname = ? where studentname = ?";
      preparedStatement = connection.prepareStatement(sql);
      String name1 = "kk";
      String name = "bibi";
      preparedStatement.setString(1, name);
      preparedStatement.setString(2, name1);
      preparedStatement.executeUpdate();
    }
    catch (Exception e){
      System.out.println(e);
    }
    finally {
      if (connection!=null){
        try {
          connection.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
      if (preparedStatement!=null){
        try {
          preparedStatement.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
    }
  }

得到结果如下：

4.删除学生表中id=6的学生信息：

  public static void main(String[] args) {
    delete();
    select();
  }
    public static void delete(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test0221", "root", "root");
      String sql = "delete from student where studentid=?";
      preparedStatement = connection.prepareStatement(sql);
      int id = 6;
      preparedStatement.setInt(1, id);
      preparedStatement.executeUpdate();
    }
    catch (Exception e){
      System.out.println(e);
    }
    finally {
      if (connection!=null){
        try {
          connection.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
      if (preparedStatement!=null){
        try {
          preparedStatement.close();
        }
        catch (Exception e){
          System.out.println(e);
        }
      }
    }
  }

得到结果如下：