Three objects for database operations in JDBC: Statement; preparedstatement; callablestatement

Statement

1. Create a statement object

After a connection is established to a specific database, the connection can be used to send SQL statements. The statement object is created using the connection method createstatement, as shown in the following code segment:

Connection con = drivermanager. getconnection (URL, "Sunny ","");
Statement stmt = con. createstatement ();

To execute the statement object, the SQL statement sent to the database will be provided to the statement as a parameter:

Resultset rs = stmt.exe cutequery ("select a, B, c from Table2 ");

2. Execute the statement using the statement object

The statement interface provides three methods for executing SQL statements: executequery, executeupdate, and execute. The method used is determined by the content generated by the SQL statement.

The executequery method is used to generate a single result set statement, such as the SELECT statement.

Executeupdate 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.

Execute is used to execute a statement that returns multiple result sets, multiple update counts, or a combination of the two. Because most programmers do not need this advanced feature, this overview will be introduced later in a single section.

All the methods for executing the statement will disable the currently opened result set of the called statement object (if any ). This means that the current resultset object must be processed before the statement object is re-executed.

Note that the preparedstatement interfaces inherited from all methods in the statement interface have their own executequery, executeupdate, and execute methods. The statement object does not contain SQL statements. Therefore, the statement.exe cute method must be provided with SQL statements as parameters. Preparedstatement objects do not provide SQL statements as parameters to these methods because they already contain pre-compiled SQL statements. The callablestatement object inherits the preparedstatement form of these methods. For the preparedstatement or callablestatement versions of these methods, sqlexception is thrown using the query parameter.

3. Statement completion

When the connection is in auto-submit mode, the statements executed in the connection are automatically submitted or restored when the connection is completed. When the statement is executed and all results are returned, the statement is deemed to have been completed. For the executequery method that returns a result set, the statement is completed after all rows of the resultset object are retrieved. For method executeupdate, the statement is completed when it is executed. However, in a few cases where the method execute is called, the statement is completed only after retrieving all result sets or the update count it generates.

Overview

The statement object is used to send SQL statements to the database. There are actually three statement objects, which are used as the package containers for executing SQL statements on a given connection: Statement, preparedstatement (inherited from statement), and callablestatement (inherited from preparedstatement ). They are dedicated to sending specific types of SQL statements: the statement object is used to execute simple SQL statements without parameters; The preparedstatement object is used to execute pre-compiled SQL statements with or without the in parameter; the callablestatement object is used to call stored procedures of the database.

The statement interface provides basic methods for executing statements and obtaining results. The preparedstatement interface adds a method to process in parameters, while the callablestatement interface adds a method to process out parameters.

Some DBMS regard each statement in the stored procedure as an independent statement, while others regard the entire process as a composite statement. When automatic submission is enabled, this difference becomes very important because it affects when to call the commit method. In the previous case, each statement is submitted separately. In the latter case, all statements are submitted simultaneously.

4. Close the statement object

The statement object will be automatically closed by the Java garbage collection program. As a good programming style, you should explicitly close statement objects when they are not required. This will immediately release DBMS resources and help avoid potential memory problems.