Java Statement detailed usage

Statement detailed usage in reprint]java

One, the introduction of statement
1,statement is an important way for Java to perform database operations by sending SQL statements to the database, based on the database connection already established. A statement object that executes a simple SQL statement without parameters.
2, there are actually three kinds of Statement objects that act as the containment for executing SQL statements on a given connection:
(1), Statement,
(2), PreparedStatement (which inherits from Statement) and
(3), CallableStatement (it inherits from PreparedStatement).

They are all dedicated to sending specific types of SQL statements:
(1), the Statement object is used to execute a simple SQL statement without parameters;

(2), the PreparedStatement object is used to execute a precompiled SQL statement with or without an in parameter;

The 3,callablestatement object is used to perform a call to a stored procedure on a database.

The 4,statement interface provides a basic way to execute statements and obtain results.

5 PreparedStatement interface adds a method for handling in parameters;

Instead, CallableStatement adds a method for handling out parameters.

1. Create a Statement object

After you establish a connection to a specific database, you can use the connection to send the SQL statement. The Statement object is created with the Connection method Createstatement, as shown in the following code snippet:

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

In order to execute the Statement object, the SQL statement sent to the database will be supplied as a parameter to the Statement method:

ResultSet rs = stmt.executequery ("Select a, B, C from Table2");

2. Execute the statement using the Statement object

The Statement interface provides three ways to execute SQL statements: ExecuteQuery, Executeupdate, and execute. Which method to use is determined by the content produced by the SQL statement.

The ExecuteQuery method uses statements that produce a single result set, such as a SELECT statement.

The Executeupdate method is used to execute INSERT, UPDATE, or DELETE statements, as well as SQL DDL (data definition language) statements, such as CREATE table and DROP table. The effect of an INSERT, UPDATE, or DELETE statement is to modify one or more columns in 0 or more rows in a table. The return value of Executeupdate is an integer that indicates the number of rows affected (that is, the update count). For statements that do not manipulate rows such as CREATE table or DROP table, the return value of executeupdate is always zero.

The Execute method executes a statement that returns multiple result sets, multiple update counts, or a combination of the two. Because most programmers do not require this advanced functionality, this overview is described later in a separate section.

All methods that execute the statement will close the currently open result set of the called Statement object, if present. This means that the processing of the current ResultSet object needs to be completed before the Statement object is re-executed.

It should be noted that the PreparedStatement interface that inherits all the methods in the Statement interface has its own executeQuery, Executeupdate, and Execute methods. The Statement object itself does not contain SQL statements, so the Statement.execute method must be provided with an SQL statement as a parameter. The PreparedStatement object does not provide SQL statements as parameters to these methods, because they already contain precompiled SQL statements. The CallableStatement object inherits the PreparedStatement form of these methods. For PreparedStatement or callablestatement versions of these methods, using query parameters will throw SQLException.

3. Statement completion

When the connection is in autocommit mode, the statements executed are automatically committed or restored when the statement is completed. Statement is considered complete when it has been executed and all results are returned. For the ExecuteQuery method that returns a result set, the statement completes when all rows of the ResultSet object are retrieved. For method Executeupdate, the statement is complete when it executes. However, in the case of a few call method execute, the statement is completed after retrieving all result sets or the update count that it generates.

statement-related Overview The statement object is used to send SQL statements to the database. There are actually three Statement objects, all of which act as an containment for executing SQL statements on a given connection: Statement, PreparedStatement (which inherits from Statement), and CallableStatement (it PreparedStatement inherited). They are all dedicated to sending a specific type of SQL statement: The Statement object is used to execute a simple SQL statement without parameters; The PreparedStatement object is used to execute a precompiled SQL statement with or without an in parameter; The CallableStatement object is used to Row calls to the database stored procedure.

The Statement interface provides a basic way to execute statements and obtain results. The PreparedStatement interface adds a method to handle the in parameter, and CallableStatement adds a method to handle the out parameter.

Some DBMS treats each statement in a stored procedure as a separate statement, while others treat the entire procedure as a compound statement. When Autocommit is enabled, this difference becomes very important because it affects when the commit method is called. In the former case, each statement is submitted separately; In the latter case, all statements are committed at the same time.

4. Close the Statement object

The Statement object will be automatically shut down by the Java garbage collection program. As a good programming style, you should explicitly close them when you don't need Statement objects. This frees the DBMS resources immediately, helping to avoid potential memory problems.

Java Statement detailed usage