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; } } |
|