"Java" PreparedStatement VS Statement

When created: 

Statement Statement = Conn.createstatement ();
PreparedStatement prestatement = conn.preparestatement (sql);

When executing:   

ResultSet rSet = statement.executequery (sql);
ResultSet PSet = Prestatement.executequery ();

As can be seen, PreparedStatement has a precompiled process, has been bound to SQL, and no matter how many times after execution, will not go to compile,

statement different, if the execution is changeable, then the corresponding will compile how many times sql, so from this point of view, prestatement efficiency will be higher than statement.

Import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.statement;public class Jdbctest {        public static void Main (string[] args) throws Exception {        //1 Load Database driver     & nbsp;   class.forname ("Com.mysql.jdbc.Driver");                &NBSP;//2 Get database connection         string url = "Jdbc:mysql://127.0.0.1:3306/test? Useunicode=true&characterencoding=utf-8 ";        string user =" root ";         string password = "root";        connection conn = Drivermanager.getconnection (URL, user, password);                &NBSP;//3 Create a statement        string sql = "Select Id,loginname,emaiL from user where id= ";        string tempsql;         int count = 1000;        long time = System.currenttimemillis ();        for (int i=0; i<count; i++) {             statement Statement = conn.createstatement ();            tempsql=sql+ (int) (Math.random () * +);           & nbsp ResultSet RSet = statement.executequery (tempsql);             Statement.close ();        }        system.out.println ( "Statement Cost:" + (System.currenttimemillis ()-time));                  string psql = "Select Id,loginname, Email from user where id=? ";         time = System.currenttimemillis ();          for (int i = 0; i < count; i++) {        &nb sp;    int id= (int) (Math.random () * 100);              PreparedStatement prestatement = Conn.preparestatement (psql);            prestatement.setlong (1, new Long (ID));              ResultSet pSet = prestatement.executequery ();             prestatement.close ();          }          system.out.println (" Prestatement Cost: "+ (System.currenttimemillis ()-time));          conn.close ();    }    }

　　The above code is executed repeatedly,

Statement cost:95 prestatement cost:90

Statement cost:100 prestatement cost:89

Statement cost:92 prestatement cost:86

Of course, this will also be related to database support, http://lucaslee.iteye.com/blog/49292 this post has a description

Although there is no more detailed testing of the various databases, but the higher the database development version, the database support for Prestatement will be better,

So overall, verifying prestatement efficiency is more efficient than Statement.

2> Security Issues

This is not much to say, prestatement is precompiled, so it can effectively prevent SQL injection and other issues

So prestatement's security is higher than Statement.

Readability and maintainability of 3> code
This is not much to say, you look at the old code will be deep experience

Prestatement a better one.

1. PreparedStatement interface Inherits Statement, preparedstatement instance contains compiled SQL statement, so it executes faster than Statement object.

2. As a subclass of Statement, PreparedStatement inherits all the functions of Statement. Three different methods

Execute, ExecuteQuery, and executeupdate have been changed so that they no longer require parameters

3, in the JDBC application, if you are already a slight level developer, you should always replace with PreparedStatement

Statement. In other words, do not use Statement at any time.

This is based on the following reasons:

One. The readability and maintainability of the code.

While replacing Statement with PreparedStatement will make the code a few more lines, this code is both readable and maintainable. is much higher than the code used directly with statement:

Stmt.executeupdate ("INSERT into Tb_name (COL1,COL2,COL2,COL4) VALUES ('" +var1+ "', '" +var2+ "'," +var3+ ", '" +var4+ "')") ;//stmt is a Statement object instance

perstmt = Con.preparestatement ("INSERT into Tb_name (COL1,COL2,COL2,COL4) VALUES (?,?,?,?)");

Perstmt.setstring (1,VAR1);

Perstmt.setstring (2,VAR2);

Perstmt.setstring (3,VAR3);

Perstmt.setstring (4,VAR4);

Perstmt.executeupdate (); Prestmt is an PreparedStatement object instance

I don't need to say more about the first method. Don't say other people to read your code, it is your own time to read, will feel sad.

PreparedStatement the best possible performance improvements.

Statements are cached by the compiler-compiled execution code of the DB, and the next call will not need to compile as long as it is the same precompiled statement, as long as the parameters are passed directly into the compiled statement execution code (the equivalent of a culvert number) to be executed. This is not to say that only a precompiled statement that executes multiple times in a Connection is cached, but for the entire DB, as long as the precompiled statement syntax matches the cache. At any time, it is possible to execute directly without having to compile again. In statement statements, even the same operation, and because the data for each operation is different so that the chances of matching the entire statement are minimal, it is almost impossible to match. Like what:

Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 22 ');

Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 23 ');

Even though the same operation but because the data content is not the same, so the entire statement itself does not match, there is no meaning of the cached statement. The fact is that no database executes code caches after the normal statement is compiled.

Of course not so precompiled statements are bound to be cached, the database itself will use a strategy, such as the use of frequency and other factors to determine when the existing precompiled results are not cached. To save more space to store the new precompiled statement.

Three. The most important point is that it greatly improves security.

Even so far, some people don't even know the basic semantics of SQL syntax.

String sql = "SELECT * from Tb_name where name= '" +varname+ "' and passwd= '" +varpasswd+ "'";

If we pass [' or ' 1 ' = ' 1] in as VARPASSWD. User name feel free to see what will become?

SELECT * from tb_name = ' random ' and passwd = ' or ' 1 ' = ' 1 ';

Because ' 1 ' = ' 1 ' is sure to be true, so can any pass validation. Moreover:

The [';d ROP table tb_name;] passed in as VARPASSWD:

SELECT * from tb_name = ' casual ' and passwd = ';d rop table tb_name; Some databases will not make you successful, but there are a number of databases that can make these statements executable.

And if you use a precompiled statement. Any content you pass in will not have any matching relationship with the original statement.

As long as you use precompiled statements all the time, you don't have to worry about the incoming data.

And if the use of ordinary statement, there may be to drop,; And so do a very elaborate judgment and worry.

"Java" PreparedStatement VS Statement