由数据库对sql的履行说JDBC的Statement和PreparedStatement51CTO博客 - 凯发娱乐

由数据库对sql的履行说JDBC的Statement和PreparedStatement51CTO博客

2019年03月09日08时55分18秒 | 作者: 又绿 | 标签: 履行,数据库,句子 | 浏览: 174

1.每一种数据库都会尽最大努力对预编译句子供给最大的功用优化.因为预编译句子有或许被重复调用.所以句子在被DB的编译器编译后的履行代码被缓存下来,那么下次调用时只需是相同的预编译句子就不需求编译,只需将参数直接传入编译过的句子履行代码中(相当于一个涵数)就会得到履行.这并不是说只需一个Connection中屡次履行的预编译句子被缓存,而是关于整个DB中,只需预编译的句子语法和缓存中匹配.那么在任何时分就能够不需求再次编译而能够直接履行.
2.PreparedStatement在conn.prepareStatement(sql)时就把sql句子传给它,这样它会在数据库端进行预编译(包括占位符),下次execute或许executeQuery时只需是相同的预编译句子就不需求编译,只需将参数直接传入编译过的句子履行代码中(相当于一个涵数)就会得到履行。其实这并不是说只需一个Connection中屡次履行的预编译句子被缓存,这是PreparedStatement凭借数据库的编译sql句子的原理来完成的优先做法罢了。
  Statement在conn.createStatement()时不传sql句子,而是在execute或许executeQuery时传过去死的sql句子。这样使是相同一操作,而因为每次操作的数据不同所以使整个句子相匹配的时机极小,简直不太或许匹配.   3.运用Statement要给它传死的拼接的sql句子,其实这样做是很不安全的,有发作歹意sql句子注入的风险。比方:
  String sql="select * from t_user where name=zhangs and passwd=zhangs123"
  而我歹意给你注入一个"or 1=1",就成了
  String sql="select * from t_user where name=zhangs and passwd=zhangs123 or 1=1"
  这样,你的暗码就失去了成效。
 
  小结,PreparedStatement在运用上的优势是清楚明了的,当然,它的开支会比Statement达一些,但我觉得功用榜首,任何情况下仍是首选PreparedStatement。    下面是我分别用两个Statement和PreparedStatement写的用来增修改查的操作: /**
* Statement是先用Connection得到一个空的履行器,在履行的时分给它传拼好的死的sql
* @author Administrator
*
*/

public class StatementCRUDtest {

  /**
    * 操作表jdbc_users
    * @param args
    */

  public static void main(String[] args) {
    User u=new User();
    u.setId(45);
    u.setName("statement");
    u.setPasswd("yf123");
    u.setPhone("13821930");
    u.setEmail("yf@163.com");
    //insert(u);
    
    //delete(2);
    
    //reset(u);
    
    System.out.println(getById(45));
  }
  /**增*/
  public static void insert(User user){
    Connection conn=null;
    Statement stmt=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      stmt=conn.createStatement();//Statement创立时就是一个空的履行器
      /**在execute或许executeQuery时履行死的sql句子*/
      /**这只能是拼好的字符串,而不能动态的传参数,并且在数据库中每次必定穿的是不同的sql句子,因而每次都要解析编译*/
      stmt.execute("insert into jdbc_users values ("+user.getId()+","+user.getName()+","+user.getPasswd()+","+user.getPhone()+","+user.getEmail()+")");
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(stmt!=null){try{stmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
  }
    
  /**删*/
  public static void delete(Integer id){
    Connection conn=null;
    Statement stmt=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      stmt=conn.createStatement();//Statement创立时就是一个空的履行器
      /**在execute或许executeQuery时履行死的sql句子*/
      /**这只能是拼好的字符串,而不能动态的传参数,并且在数据库中每次必定穿的是不同的sql句子,因而每次都要解析编译*/
      stmt.execute("delete from jdbc_users where id="+id);
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(stmt!=null){try{stmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
  }
    
  /**改*/
  public static void reset(User user){
    Connection conn=null;
    Statement stmt=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      stmt=conn.createStatement();//Statement创立时就是一个空的履行器
      /**在execute或许executeQuery时履行死的sql句子*/
      /**这只能是拼好的字符串,而不能动态的传参数,并且在数据库中每次必定穿的是不同的sql句子,因而每次都要解析编译*/
      String sql="update jdbc_users set name="+user.getName()+",passwd="+user.getPasswd()+",phone="+user.getPhone()+",email="+user.getEmail()+" where id="+user.getId();
      System.out.println(sql);
      stmt.execute(sql);
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(stmt!=null){try{stmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
  }
    
  /**查*/
  public static User getById(Integer id){
    Connection conn=null;
    Statement stmt=null;
    ResultSet rs=null;
    User u=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      stmt=conn.createStatement();//Statement创立时就是一个空的履行器
      /**在execute或许executeQuery时履行死的sql句子*/
      /**这只能是拼好的字符串,而不能动态的传参数,并且在数据库中每次必定穿的是不同的sql句子,因而每次都要解析编译*/
      String sql="select * from jdbc_users where id="+id;
      System.out.println(sql);
      rs=stmt.executeQuery(sql);
      while(rs.next()){
        u=new User();
        u.setId(rs.getInt("id"));
        u.setName(rs.getString("name"));
        u.setPhone(rs.getString("phone"));
        u.setPasswd(rs.getString("passwd"));
        u.setEmail(rs.getString("email"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(stmt!=null){try{stmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
    return u;
  }
}
/**
* PreparedStatement是在创立pstm的时分就给它传一个动态的sql,参数是经过pstm设置的。履行时,只需求空履行一下就能够.
* @author Administrator
*
*/

public class PreparedStatementCRUDtest {

  /**
    * 操作表jdbc_users
    * @param args
    */

  public static void main(String[] args) {
    User u=new User();
    u.setId(21);
    u.setName("statement");
    u.setPasswd("yf123");
    u.setPhone("13821930");
    u.setEmail("yf@163.com");
    //insert(u);
    
    //delete(42);
    
    //reset(u);
    
    System.out.println(getById(21));
  }
  /**增*/
  public static void insert(User user){
    Connection conn=null;
    PreparedStatement pstmt=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      String sql="insert into jdbc_users values(?,?,?,?,?)";
      pstmt=conn.prepareStatement(sql);//PreparedStatement创立时就传过去一个sql句子,这样就能够预编译
      /**然后设置sql中好占位符的值,这里是动态的传参数*/
      pstmt.setInt(1, user.getId());
      pstmt.setString(2, user.getName());
      pstmt.setString(3, user.getPasswd());
      pstmt.setString(4, user.getPhone());
      pstmt.setString(5, user.getEmail());
      /**设置好后,就全封装到pstm里了,只需空履行就能够了*/
      pstmt.execute();
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(pstmt!=null){try{pstmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
  }
    
  /**删*/
  public static void delete(Integer id){
    Connection conn=null;
    PreparedStatement pstmt=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      String sql="delete from jdbc_users where id=?";
      /**PreparedStatement创立时就传过去一个sql句子,这样就能够预编译*/
      pstmt=conn.prepareStatement(sql);
      /**然后设置sql中好占位符的值,这里是动态的传参数*/
      pstmt.setInt(1, id);
      /**设置好后,就全封装到pstm里了,只需空履行就能够了*/
      pstmt.execute();
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(pstmt!=null){try{pstmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
  }
    
  /**改*/
  public static void reset(User u){
    Connection conn=null;
    PreparedStatement pstmt=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      String sql="update jdbc_users set name=?,passwd=?,phone=?,email=? where id=?";
      /**PreparedStatement创立时就传过去一个sql句子,这样就能够预编译*/
      pstmt=conn.prepareStatement(sql);
      /**然后设置sql中好占位符的值,这里是动态的传参数*/
      pstmt.setString(1, u.getName());
      pstmt.setString(2, u.getPasswd());
      pstmt.setString(3, u.getPhone());
      pstmt.setString(4, u.getEmail());
      pstmt.setInt(5, u.getId());
      /**设置好后,就全封装到pstm里了,只需空履行就能够了*/
      pstmt.execute();
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(pstmt!=null){try{pstmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
  }
    
  /**查*/
  public static User getById(Integer id){
    Connection conn=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    User u=null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL10", "SCOTT", "yf123");
      String sql="select * from jdbc_users where id=?";
      pstmt=conn.prepareStatement(sql);//Statement创立时就是一个空的履行器
      /**在execute或许executeQuery时履行死的sql句子*/
      /**这只能是拼好的字符串,而不能动态的传参数,并且在数据库中每次必定穿的是不同的sql句子,因而每次都要解析编译*/
      pstmt.setInt(1, id);
      rs=pstmt.executeQuery();
      while(rs.next()){
        u=new User();
        u.setId(rs.getInt("id"));
        u.setName(rs.getString("name"));
        u.setPhone(rs.getString("phone"));
        u.setPasswd(rs.getString("passwd"));
        u.setEmail(rs.getString("email"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(pstmt!=null){try{pstmt.close();}catch(Exception e){}}
      if(conn!=null){try{conn.close();}catch(Exception e){}}
    }
    return u;
  }
}
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章