5, use preparatory statements
The PreparedStatement object is a precompiled SQL statement. DBMS does not have to compile the PREPAREDSTATEMENT's SQL statement directly. So instead of Statement objects generally shorten the execution time. Although the PareDStatement object can be used for SQL statements without parameters, but in most occasions is the SQL statement for with parameters. The usage is as follows:
String strupdates = "Update coffees set sales =? Where cool_name like?";
PreparedStatement Updates = Con.PrepareStatement (STRUPDATESALES);
UpdatesaS.setint (1, 75); // pay the first question mark for the SQL statement above
UpdateSaSaS.setString (2, "colorbian"); // pay the second question mark for the SQL statement above
UpdateSales.executeUpdate (); // Perform Update Coffee Set Sales = 75 Where Cof_name Like 'Colombian'
Therefore, the SQL statement that can be used to construct a plurality of band parameters can be achieved with this object system to simplify the encoding.
6, return value of the executeUpdate method
EXECUTEQUERY Returns a RESULTSET object, but EXECUTEUPDATE returns an integer value indicating that the number of rows that have been updated in the table
If the return value of ExecuteUpdate is 0 indicates that the following two situations: (1) The statement executed is an update statement that does not affect any row; (2) Execute a DLL statement.
7, use transactions
A transaction is a set of statements (one or more statements) performed as a unit, so they are either executed, or they are not executed. To make two or more statements
A transaction is to disable automatic submission mode, specific method con.sertAutoCommit (false); once the automatic submission mode is disabled, there is no SQL statement will be submitted.
Unless you explicitly call the Con.commit () method, implement the transaction.
Use transactions to maintain data integrity. Call the Rollback method to cancel a transaction, returning any of the modified data to the previous value. If you are executing a transaction
A SQLEXCEPTION is obtained when one or more statements. You should call the ROLLBACK method to cancel the transaction, starting the entire transaction from the head. Generally you want to call in the CatCH block
ROLLBACK to avoid possible data that may use incorrect.
8, retrieve warning
SQLWARNING object is a subclass of SQLException, which is used to process database access warnings. Just like an exception, warning does not terminate the execution of the program; they just remind
User - some unpredictable things happen. Connection object, Statement object (including PreparedStatement and CallableStatement object)
Or ResultSet objects will report warnings. These classes have a getWarnings method, and this method must be called for the first warning reported to the invoke object. in case
GetWarnings returns a warning to call the SQLWARNING method getnextwarning to get other warnings. Automatically perform a statement clearing the previous statement
Warning, so warning will not be overlap. But this also shows that the warning of the extracted statement report must be performed before the next statement is performed.
9, stored procedure
The stored procedure is a logical unit consisting of a set of SQL statements for performing specific tasks. The stored procedure is used to encapsulate a set of operations or queries executed on the database server.
The stored procedure can be compiled, and then different parameters and results are used, these parameters and results can be any combination of input, output, and input / output parameters. 1) Create a stored procedure with SQL statement
As shown below, the method is the same as other DDL statements:
String createprocedure = "create procedure show_suppliers"
"AS" "Select Suppliers.suP_Name, Coffee Q_Name"
"from sales, coffees" "where support.sup_id = coffeees.sup_id"
"ORDER BY SUP_NAME";
Statement Stmt = con.createstatement ();
Stmt.executeUpdate (createprocedure);
2) Call the stored procedure from the JDBC
The first step is to create a CallableStatement object. The callablestatement object contains a call for the stored procedure; but it does not include a stored procedure.
itself. Then use the connection CON to create a call to the stored procedure. The part of the lacerans is the syntax of the stored procedure. When the driver encounters
When "{Call show_suppliers}", it converts this escape syntax into a local SQL used by the database to call the stored procedure behind the CALL.
As follows:
CallableStatement Cs = Con.PrepareCall ("{call show_suppliers});
ResultSet RS = cs.executeQuery ();
Note that the method for performing the CS is ExecuteQuery because the stored procedure called the CS contains a query, and a result set is generated. If stored procedures
Contains an update or a DLL statement, then use the ExecuteUpdate method. But sometimes a stored procedure contains multiple SQL statements, so it is not just
A result set, not just an update count or generate a combination of some result sets and update counts. This will have multiple result sets, and the Execute method should be used.
To perform CallableStatement. The CallableStatement class is a subclass of PreparedStatement, so the callablestatement object is
The PreparedStatement object has an input parameter. In addition, the CallableStatement object can also be used with output parameters or input / output parameters.
SUN's sample programs can be downloaded and read in http://java.sun.com/products/jdbc/codeexamples_3.0.zip.
