The difference between java.sql.preparedstatement and java.sql.statement

This article was transferred from CSDN, and then tidied it up again. Original source: CSDN

The java.sql.statement of one of the main four classes in the API for JDBC (Java database Connectivity,java Connection) requires developers to devote a lot of time and effort. One common problem with using statement to get JDBC access is to enter the date and time stamp in the appropriate format: 2002-02-0520: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:

1 String sql = "SELECT * from people p where p.id =?" and p.name =? " ; 2 preparedstatement PS = conn.preparestatement (sql); 3 ps.setint (1, id); 4 ps.setstring (2, name); 5 ResultSet rs = Ps.executequery ();

PreparedStatement

Another advantage of using PreparedStatement is that strings are not created dynamically. Here is an example of a dynamically created string:

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

This allows the JVM (Javavirtualmachine,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:

1  PackageJstarproject;2 ImportJava.sql.*;3 4  Public classmypreparedstatement {5 6 Private FinalStringdb_driver= "Com.microsoft.JDBC.sqlserver.sqlserverdriver";7 Private FinalString url = "Jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=pubs";8 9  Publicmypreparedstatement ()Ten { One } A  Public voidQuery ()throwssqlexception{ -Connection conn = This. getconnection (); -String strSQL = "Select emp_id from employee WHERE emp_id =?"; thePreparedStatement 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); A } at rs.close (); - pstmt.close (); - conn.close (); -  - } - PrivateConnection getconnection ()throwssqlexception{ in //class. -Connection conn =NULL; to Try { + class. forname (db_driver); -conn = drivermanager.getconnection (URL, "sa", "sa"); the } * Catch(ClassNotFoundException ex) {} $ returnConn;Panax Notoginseng } - //Main the  Public Static voidMain (string[] args)throwsSqlException { +Mypreparedstatement JDBCtest1 =Newmypreparedstatement (); A jdbctest1.query (); the } +}

Example

preparedstatement instead statement? Why always use the preparedstatement replace 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.

Why PreparedStatement is important and how to use them "correctly".

The database has a hard job. They constantly read SQL queries from many clients and query the data as efficiently as possible. Processing statements can be a costly operation, but now the database is a good design, so the difficulty is minimized. But these optimizations require the assistance of application developers, and this article shows you how to properly use PreparedStatement to help the database perform these optimizations beautifully.

How does a database execute a statement?

Obviously, don't expect a lot of detail here; We'll just look at some of the more important parts of this article. When a database receives a statement, the database engine first parses the statement and looks at the syntax error. Once the statement is parsed, the database needs to find the most efficient way to execute the statement. The cost of this calculation is very high. The database checks what index (if any) it can help, or whether it can read all of the records in a single table. The database finds the best approach based on these statistics on the data stored in the database. Once a query scheme is developed, it can be executed by the database engine.

CPU is required to generate an access scheme. Ideally, if we send the same statement to the database two times, we expect the database to reuse the first record of the access scheme. This will take less CPU than the second time the scheme is re-generated.

Statement buffering

The database can be adjusted to make the statement buffer. Typically contains some types of statement buffering. The buffer uses the statement itself as a keyword, and the access scheme and corresponding statements are stored in the buffer. This allows the database engine to reuse the access scenarios used by previously executed statements. For example, if we send such a statement to the database "Select a, b from t where C = 2", the computed access scheme is put into the buffer. If we use the same statement later, the database will be able to reuse the previous access scheme, thus saving the CPU.

Note, however, that the entire statement is a keyword. For example, if we later send a statement that is "Selecta,bfrom t where C = 3", then the previous access scheme will not be identified. Because "c=3" and "c=2" are not the same. So, for example:

for (int I = 0; I < 1000; ++i)
{
PreparedStatement PS = conn.preparestatement ("Select a, b from t where C =" + I);
ResultSet rs = Ps.executequery ();
Rs.close ();
Ps.close ();
}

Buffers are not used here. Each loop sends a different SQL statement to the database. Each time the loop recalculates the new access scheme, we waste a lot of CPU cycles in this way. But look at the next fragment:

PreparedStatement PS = conn.preparestatement ("Select, b from Twhere c=?");
for (int I = 0; I < 1000; ++i)
{
Ps.setint (1, I);
ResultSet rs = Ps.executequery ();
Rs.close ();
}
Ps.close ();

This will be much more efficient. The statements sent to the database are parameterized using the @#[email protected]# notation in SQL. This means that each loop is sending the same statement, with different parameters in the "C=?" section. This allows the database to reuse the access scheme of the statement, which is more efficient for the program to run inside the database. This can basically make your program run faster, or allow the database user to use the CPU more.

PreparedStatement and Java EE server

When we use the Java EE server, things get more complicated. Typically, a prepared statement (prepared statement) is associated with a separate database connection. When the connection is closed, the statement is discarded. In general, a rich client application remains at the end of the program after it has a database connection. It uses two methods to create all the statements: eager to create (eagerly) or lazy to create (lazily). Eagerly is said to be created when the program is started. Lazily is said to be created with the use. The urgent method will be delayed when the program starts, but it works well once the program is started. Lazy methods start very quickly, but when the program runs, the pre-prepared statements are created in the first use. This creates an imbalance in performance, knowing that all statements are ready, but the final program will be as fast as the eager method. Which one is best depends on whether you want fast startup or balanced performance.

The problem with a Java EE application is that it can't work like this. It maintains only one connection in the lifetime of a request. This means that when he processes each request, the statement is recreated, not as if the FAT client was created only once, not as effectively as every request was created, and when the Java EE Server gives your program a connection, it is not a real connection, but a wrapper. You can check the name of the connected class by looking at it. It is not a JDBC connection to a database, it is a class created by your server. Typically, if you call a connected Close method, the JDBC driver closes the connection. What we want is that when the Java EE application calls Close, the connection is returned to the connection pool. We do this by designing a proxy JDBC connection class, but it looks like the actual connection. When we invoke any method of this connection, the proxy class will pass the request to the actual connection. However, when we call a method similar to close, we do not invoke the Close method of the actual connection, but simply return the connection to the connection pool and then mark the proxy connection as invalid so that when it is reused by the application, we get an exception.

Packaging is very useful because it helps the Java EE application Server implementations to intelligently add support for pre-prepared statements. When the program calls Connection.preparestatement, a PreparedStatement object is returned by the driver. When the application obtains it, the handle is saved, and when the request completes, close the handle before closing the request. However, after the connection is returned to the connection pool, it is later the same or
When another application is reused, then we theoretically want the same preparedstatement to be returned to the application.

Java EE preparedstatement buffering

The Java EE preparedstatement buffer is implemented by using a buffer from the connection pool manager inside the Java EE server. The Java EE server holds a list of pre-prepared statements for all databases in the connection pool. When a program calls a connected Preparestatement method, the server checks to see if the statement is already there, and if so, the corresponding PreparedStatement Within the buffer, it is returned to the application, and if not, the request is passed to the JDBC driver, and the request/pre-prepared statement object is added to the buffer.

For each connection we need a buffer, as this is the working requirement of the JDBC driver. Any returned preparedstatement are for this connection.

If we want to take advantage of the buffer, use the same rules as before. We need to use the arguments to the query so that they will match one of the buffers already in the buffer. Most application servers allow you to adjust the size of the buffer.

Profile

In summary, for pre-prepared statements, we should use parameterized queries. This allows the database to reuse existing access scenarios, thereby alleviating the burden on the database. Such buffers are scoped to this database, so you can schedule all your applications, using similar parameterized SQL, which will improve the efficiency of such a buffer scheme, because one application can use the statement of another application. This is also the advantage of an application server because the logic to access the database should be centered on the data access layer (or mapping, entity bean, or direct JDBC).

Finally, the proper use of pre-prepared statements also allows you to take advantage of the buffers of the application server's pre-prepared statements. Improves the performance of your application because the application reduces the number of JDBC driver calls by reusing the previously prepared statements. This allows it to compete with the efficiency of the FAT client and removes the disadvantage of not maintaining a long-term connection.

If you use parameterized pre-prepared statements, you can improve the efficiency of your database and your server-side code. These improvements will allow your application to improve performance.

The difference between java.sql.preparedstatement and java.sql.statement