A detailed explanation of the usage of PreparedStatement

The java.sql.statement of one of the main four classes in the API of JDBC (Java database Connectivity,java DB connection) requires developers to devote a great deal of time and effort. One common problem with using statement to obtain JDBC access is to enter the appropriate format date and time stamp: 2002-02-05 20:56 or 02/05/02 8:56 pm.

By using java.sql.preparedstatement, this problem can be solved automatically. A PreparedStatement is obtained from the Java.sql.connection object and the supplied SQL string, which contains a question mark (?), which indicates the position of the variable, then provides the value of the variable, and finally executes the statement, for example:

Stringsql = "SELECT * from people p where p.id =? and p.name =? ";
PreparedStatement PS = connection.preparestatement (SQL);
Ps.setint (1,id);
Ps.setstring (2,name);
ResultSet rs = Ps.executequery ();
Another advantage of using PreparedStatement is that the string is not dynamically created. The following is an example of a dynamically created string:

Stringsql = "SELECT * from people p where p.i =" +id;

This allows the JVM (javavirtual Machine,java virtual machines) and drive/Database cache statements and strings and improve performance.

PreparedStatement also provides database-independent sex. The fewer SQL statements are displayed, the smaller the database dependencies of a potential SQL statement.

Because PreparedStatement has many advantages, developers may often use it, using the usual statement only if it is solely for performance reasons or when there are no variables in a row of SQL statements.

An example of a complete preparedstatement:

Package jstarproject;
Import java.sql.*;

public class Mypreparedstatement {

Private final String db_driver= "Com.microsoft.jdbc.sqlserver.sqlserverdriver";
Private final String url = "Jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=pubs";

  public mypreparedstatement ()  
  {
  }
  public  void query ()  throws sqlexception{
    connection conn =  this.getconnection ();
    string strsql =  "select emp_id from employee  where emp_id = ? ";
    preparedstatement pstmt = conn.preparestatement (strSQL);
    pstmt.setstring (1, "pma42628m");
    resultset rs = pstmt.executequery ();

while (Rs.next ()) {
String fname = rs.getstring ("emp_id");
System.out.println ("The fname is" + fname);
}
Rs.close ();
Pstmt.close ();
Conn.close ();

}
Private Connection getconnection () throws sqlexception{
Class.
Connection conn = null;
try {
Class.forName (Db_driver);
conn = drivermanager.getconnection (URL, "sa", "sa");
}
catch (ClassNotFoundException ex) {}
Return conn;
}
Main
public static void Main (string[] args) throws SqlException {
Mypreparedstatement jdbctest1 = new Mypreparedstatement ();
Jdbctest1.query ();
}
}


Why always use PreparedStatement instead of statement? Why should you always use PreparedStatement instead of statement?


In JDBC applications, if you are already a slightly horizontal developer, you should always replace statement with PreparedStatement. That is to say, do not use statement at all times.
Based on the following reasons:
I. Readability and maintainability of the code.
While using PreparedStatement instead of statement can make the code a few more lines, such code is both readable and maintainable. It's a lot higher than the code that uses statement directly:

Stmt.executeupdate ("INSERT into Tb_name (COL1,COL2,COL2,COL4) VALUES (' +var1+" ', ' +var2+ ', ' +var3+ ', ' "+var4+ ')");

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 ();

Needless to say, for the first method. Don't say someone else is going to read your code, and you'll feel sad when you read it yourself for a while.

Two. PreparedStatement can improve performance to the greatest extent possible.
Each database will do its best to provide maximum performance optimization for precompiled statements. Because precompiled statements can be repeatedly called. So the statement is cached after the compiled code of the DB compiler, so that the next call, as long as the same precompiled statement does not need to compile, It is executed as long as the parameters are passed directly into the compiled statement execution code (equivalent to a number of numbers). This is not to say that only a precompiled statement that executes multiple times in a connection is cached, but for the entire DB, Whenever the precompiled statement syntax matches the cache. Then you can execute it without recompiling at any time. In statement statements, even the same operation, and because of the different data for each operation, the chances of matching the entire statement are very small and almost impossible to match. For example:
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 22 ');
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 23 ');
Even if the same operation but because the data content is different, so the whole statement itself can not match, do not have the meaning of cached statements. The fact is that no database is cached for the execution code compiled by the normal statement.

Of course not. The precompiled statements are bound to be cached, and the database itself uses a strategy, such as frequency, to determine when the existing precompiled results will no longer be cached. To save more space to store the new precompiled statements.

Three. The most important point is a significant increase in security.

Even so far, there are people who don't even know the basic semantics of the SQL syntax.
String sql = "SELECT * from Tb_name where name= '" +varname+ "' and passwd= '" +varpasswd+ "";
If we enter [' or ' 1 ' = ' 1] as a varpasswd. User name casually, see what will become?

SELECT * from tb_name = ' random ' and passwd = ' or ' 1 ' = ' 1 ';
Because ' 1 ' = ' 1 ' is definitely set up, so can any pass validation. What's worse:
Put [';d rop table tb_name;] Passed in as a varpasswd, then:
SELECT * from tb_name = ' random ' and passwd = ';d rop table tb_name; some databases won't make you successful, but there are a lot of databases that can get these statements executed.

And if you use precompiled statements. Anything you pass in will not have any matching relationship with the original statement. As long as you use precompiled statements, you don't have to do anything about the incoming data. And if you use ordinary statement, you might want to make a few decisions and worry about the drop.