Statement and preparedstatement callablestatement and session in hibernate

The JDBC driver is optimized based on what features are used. Choosing preparedstatement or statement depends on how you use them.It is best to select statement for SQL statements that are executed only once. On the contrary, it is best to select preparedstatement for Multiple SQL statements.
For preparedstatement plus? Set again. Otherwise, it will be ineffective like statement. If the database uses the preparedstatement statement to execute an SQL statement, it has the following advantages: the database will pre-compile the preparedstatement statement. The next time you execute the same SQL statement, instead of pre-compiling, the database directly uses the database buffer to improve data access efficiency (but try to use it whenever possible? If the SQL statement is executed only once and will not be reused later, it is recommended to use statement.
The first execution consumption of preparedstatement is very high. Its performance is reflected in the subsequent repeated execution.

Callablestatement interface used to execute SQL stored procedures

.PreparedstatementIt is pre-compiled and can greatly improve the efficiency of batch processing. It is also called the JDBC stored procedure.

2. Use the statement object.When only one-time access is performed to the database, the statement object is used for processing. The overhead of the preparedstatement object is greater than that of the statement object, which does not bring additional benefits to one-time operations. 3. Each time statement executes an SQL statement, the relevant database must compile the SQL statement. preparedstatement is pre-compiled, and preparedstatement supports batch processing.

 

Ii. Use the batch function of preparedstatement
When updating a large amount of data, prepare an insert statement is executed multiple times, which may lead to many network connections. to reduce the number of JDBC calls and improve performance, you can use the addbatch () method of preparedstatement to send multiple queries to the database at a time. for example, let's compare the following example.

Example 1: Execute prepared statement multiple times

 

 

1. Preparedstatement PS = conn. preparestatement (
2. "Insert into employees values (?, ?, ?) ");
4. For(N = 0; n <100; n ++ ){
6. PS. setstring (name [N]);
7. PS. setlong (ID [N]);
8. PS. setint (salary [N]);
9. Ps.exe cuteupdate ();
10. }

 

Example 2: Use batch

 

 

1. Preparedstatement PS = conn. preparestatement (
2. "Insert into employees values (?, ?, ?) ");
4. For(N = 0; n <100; n ++ ){
6. PS. setstring (name [N]);
7. PS. setlong (ID [N]);
8. PS. setint (salary [N]);
9. PS. addbatch ();
10. }
11. Ps.exe cutebatch ();

 

 

void	addBatch() Add a set of parameters to thisPreparedStatementObject in the batch processing command.
void	clearParameters() Immediately clear the current parameter value.
boolean	execute() HerePreparedStatementObject. This statement can be any type of SQL statement.
ResultSet	executeQuery() HerePreparedStatementAnd returnsResultSetObject.
int	executeUpdate() HerePreparedStatementObject, which must be a data manipulation language (DML) statement, such INSERT,UPDATEOrDELETEStatement, or an SQL statement without returned content, such as a DDL statement.

 

The above are several common methods of preperstatement.

 

Execute executequery and executeupdate are both SQL statement methods. The differences are as follows:

Method execute:
It is used to execute statements that return multiple result sets, multiple update counts, or a combination of the two.

Method executequery
A statement used to generate a single result set, such as a SELECT statement. This method is used to execute select statements, which are almost the most commonly used SQL statements.

Method executeupdate
It is used to execute insert, update, or delete statements and SQL DDL (Data Definition Language) statements, such as CREATE TABLE and drop table. The insert, update, or delete statement modifies one or more columns in zero or multiple rows of the table. The returned value of executeupdate is an integer indicating the number of affected rows (that is, the update count ). For statements that do not operate on rows such as create table or drop table, the return value of executeupdate is always zero.

 

In example 1, preparedstatement is used to execute the insert statement multiple times. here, 100 insert operations are performed, with a total of 101 network round-trips. one round trip is the pre-storage statement, and the other 100 round trips are executed for each iteration. in Example 2, when the addbatch () method is used in 100 insert operations, only two network round trips are performed. one round trip is the pre-storage statement, and the other is the execution of the batch command. although batch commands use more CPU cycles for databases, performance is improved by reducing network round-trips. remember, the biggest improvement of JDBC performance is to reduce network communication between JDBC drivers and databases.
Note: The maximum batch size of the JDBC driver of volume el 10g is 16383. If addbatch exceeds this limit, an "invalid batch value" (invalid batch value) exception occurs during executebatch. Therefore, if Oracle10g is used, the batch size must be limited before the bug is reduced.

Some people argue that in JDBC applications, if you are already a relatively level developer, you should always replace statement with preparedstatement. That is to say, you should never use statement at any time.

When a database receives a statement, the database engine first parses the statement to view the syntax error. Once the statement is parsed,
The database needs to find the most effective way to execute this statement. this computation costs a lot. the database checks what indexes (if any) can help, or whether it can read all the records in a table. the database finds the best solution based on the statistics on the data stored in the database. once a query scheme is developed, it can be executed by the database engine. the CPU is required to generate the ACCESS scheme. if we send the same statement to the database twice, we expect the database to reuse the ACCESS scheme of the first record. this requires less CPU than the second re-generation scheme. the Statement Buffer database can be adjusted to buffer statements. it usually contains some types of statement buffering.
The buffer uses the statement itself as the key word, and the access scheme and corresponding statements are stored in the buffer. This allows the database engine

Because preparedstatement has many advantages, developers may usually use it, and use the normal statement only when it is completely because of performance or when there is no variable in a row of SQL statements.

Reuse the ACCESS scheme used by previously executed statements. for example, if we send such a statement "select a, B from t where c = 2" to the database, the computed access scheme will be placed in the buffer zone. if we use the same syntax in the future, the database will be able to reuse the previous access scheme, thus saving the CPU. note that the entire statement is a keyword. for example, if the statement we sent later is "select a, B

From t where c = 3 ", the previous access scheme will not be found, because" c = 3 "and" c = 2 "are different.

For example:

For (INT I = 0; I <1000; ++ I)

{

Preparedstatement PS = conn. preparestatement ("select a, B from t

Where c = "+ I );

Resultset rs = ps.exe cutequery ();

Rs. Close ();

PS. Close ();

}

No buffer is used here. each cycle sends a different SQL statement to the database. every cycle recalculates a new access scheme. Using this method will waste a lot of CPU cycles. however, let's look at the next clip:

Preparedstatement PS = conn. preparestatement ("select a, B from t where c

　=? ");

For (INT I = 0; I <1000; ++ I)

{

PS. setint (1, I );

Resultset rs = ps.exe cutequery ();

Rs. Close ();

}

PS. Close ();

This will be much more efficient. The statements sent to the database use @ # in SQL @#? @ # Symbol to parameterize. This means that each loop is sent with the same statement, in "C =? "Some have different parameters. in this way, the access scheme that allows the database to reuse statements is more efficient for the program to run in the database. this basically makes your program run faster, or enables database users to use CPU more. preparedstatement and J2EE Server

The SQL statement sent to the database returns the result through a two-step process. Prepare them first, and then process them. With the statement object, these two phases become a phase for the application. Preparedstatement allows the two steps to be separated. The preparation step occurs when the object is created, and the processing step occurs when the executequery, executeupdate, or execute method is called on the preparedstatement object.

If no parameter tag is added, it does not make sense to split SQL into separate stages. The parameter tag is placed in the application so that it can tell the database that it does not have a specific value during preparation, but it provides a value before processing. In SQL statements, parameter tags are represented by question marks.

By using parameter tags, you may create common SQL statements for specific requests. For example, the following SQL query statement is given:

     SELECT * FROM EMPLOYEE_TABLE WHERE LASTNAME = 'DETTINGER'

This is a specific SQL statement that returns only one value, that is, information about the employee named Dettinger. By adding parameter tags, you can make the statement more flexible:

     SELECT * FROM EMPLOYEE_TABLE WHERE LASTNAME = ?

You can simply set the parameter tag to a value to obtain information about any employee in the table.

Since the previous statement example can go through only one preparation phase and then use different parameter values for repeated processing, preparedstatement can provide higher performance than statement.

Note:To support the use of statements of the local JDBC driver, you must use preparedstatement.

The preparestatement method is used to create a new preparedstatement object. Unlike the createstatement method, you must provide an SQL statement when creating a preparedstatement object. At that time, SQL statements were pre-compiled for use. For example, if a connection object named conn already exists, the following example creates a preparedstatement object and prepares the SQL statement to be processed in the database.

     PreparedStatement ps = conn.prepareStatement("SELECT * FROM EMPLOYEE_TABLE                                                  WHERE LASTNAME = ?");

The same as the createstatement method, the preparestatement method is used to provide support for the specified resultset feature. The preparestatement method also has variants that can use automatically generated keys. The following are examples of calling a valid preparestatement method:

Each parameter tag must be set to a value before the preparedstatement object can be processed. The preparedstatement object provides many methods for setting parameters. The format of all these methods is set <type>, where <type> is a Java data type. Examples of these methods include setint, setlong, setstring, settimestamp, setnull, and setblob. Almost all of these methods have two parameters:

• The first parameter is the index of this parameter in the statement. The parameter tag has a number starting from 1.
• The second parameter is the value set for the first parameter. Several set <type> methods have additional parameters, such as the length parameter on setbinarystream.

 3: The following methods are available in hibernate to obtain the connection and Session:

First, obtain sessionfactoryutils. getsession (getsessionfactory (), true) and session in hibernate. Then pass

Sessionfactoryutils. getdatasource (getsessionfactory (). getconnection () to obtain the connection. Or use session. Connection (), but this method has expired.

Then it is the same as JDBC.

However, Hibernate also has dedicated sessions for database operations.

1. A user can correspond to multiple sessions, but not necessarily to a connection (several sessions may share a connection

Connection refers to the (physical) Connection established between the user and the database.
2. session refers to the data transmitted over this physical connection, or session is a logical connection.
3, session> = connection