The PreparedStatement usage of the JDBC (Java database Connectivity,java connection) API of one of the main four classes of java.sql.statement requires developers to devote a lot of time and effort. One common problem with using statement for JDBC Access is to enter the date and time stamp in the appropriate format: 2002-02-05 20:56 or 02/05/02 8:56 pm.
By using java.sql.preparedstatement, this problem can be resolved automatically. A PreparedStatement is obtained from the Java.sql.connection object and the provided SQL string, which contains a question mark (?), which indicates the position of the variable, and 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 strings are not created dynamically. Here 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 machine) and drive/Database cache statements and strings and improve performance.
PreparedStatement also provides database independence. The fewer SQL that is displayed, the smaller the database dependency on the underlying SQL statement.
Because PreparedStatement has a number of advantages, it is common for developers to use it only if the usual statement is used purely 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 should I always use preparedstatement instead of statement? Why should I always use preparedstatement instead of statement?
In a JDBC application, if you are already a slightly level developer, you should always replace statement with PreparedStatement. That is, do not use statement at any time.
This is based on the following reasons:
I. Readability and maintainability of the code.
Although using PreparedStatement instead of statement will make the code a few more lines, this code can be used in terms of readability and maintainability. It is much higher than the code that directly uses statement:
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 tell anyone else to read your code, it will be sad if you read it yourself for some time.
Two. PreparedStatement the maximum possible performance improvement.
Each database will do its best to provide maximum performance optimizations for precompiled statements. Because the precompiled statement is likely to be called repeatedly. So the execution code that is compiled by the compiler of the DB is cached, so the next call will not need to be compiled as long as it is the same precompiled statement. As long as the parameters are passed directly into the compiled statement execution code (equivalent to a culvert number) it will be executed. This is not to say that only one connection of the precompiled statements executed more than once is cached, but for the entire DB, As long as the precompiled statement syntax matches the cache. At any time, it can be executed without having to compile again. In statement statements, even though the same operation, the chances of matching the entire statement are minimal because of the different data for each operation, which is almost unlikely to match. For example:
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 22 ');
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 23 ');
Even though the same operation is not the same as the data content, the entire statement itself does not match, there is no meaning of the cached statement. The fact is that there is no database that executes code caches after the normal statement is compiled.
Of course not. So the precompiled statements are bound to be cached, and the database itself uses a strategy, such as frequency, to determine when the pre-compiled results are no longer cached. To save more space to store new precompiled statements.
Three. The most important point is a significant increase in 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 you can pass any validation. What's more:
Put [';d rop table tb_name;] Incoming in as VARPASSWD:
SELECT * from tb_name = ' random ' and passwd = ';d rop table tb_name; some databases are not going to make you successful, but there are many databases that can make these statements executable.
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 all the time, you don't have to worry about the incoming data. If you use ordinary statement, you may want to make a decision on the drop,;
A few of the reasons above are not enough to make you use PreparedStatement at all times?
Interview questions-How to prevent SQL injection, using PreparedStatement pre-compilation, the incoming content will not have any matching relationship with the original statement, to prevent the injection method
