05. JDBC programming-processing Stored Procedures & amp; Transaction

05. Processing Stored Procedure & Transaction for JDBC Programming
I. Stored Procedure Processing

1. Stored ProcedureSimilar to a function, a piece of code is encapsulated. To execute this code, you can call this stored procedure. In the encapsulated statement body, you can use if/else, case, while and other control structures (parameters, control structures, loops) to program SQL.

(1) SQL syntaxA. view the existing Stored procedure show procedure status \ G B. create Stored procedure delimiter $ // modify statement Terminator create procedure stored procedure name (parameter list) begin encapsulate SQL statement end $ c. delete the name of the stored procedure drop procedure; d. call a stored procedure name () $ call a stored procedure name (parameter) $

(2) Stored Procedure instanceCreate a stored procedure in advance on the MySQL terminal-computing 1 + 2 + 3 +... + 100 and create procedure pro (nam char (10), n smallint) begin declare I int; declare s int; set I = 1; set s = 0; while I <= n do set s = s + I; set I = I + 1; end while; insert into test (name, age, score) values (nam, 0, s); end $ Note: ◇ declare declares a variable ◇ set sets the value of the variable ◇ select output/view a variable or column ◇ stored procedure name only uses a single character

2. JDBC processing stored proceduresThe CallableStatement object provides a standard method for calling stored procedures for all DBMS systems. Stored procedures are stored in the database. The call to a stored procedure is the content contained in the CallableStatement object. This call is written using a code-changing syntax. There are two forms: one with result parameters and the other without result parameters. The result parameter is an output (OUT) parameter, which is the return value of the stored process. The two types can contain variable input (IN parameter), output (OUT parameter), or input and output (INOUT parameter) parameters. The question mark is used as a placeholder for the parameter. The CallableStatement object is created using the Connection method prepareCall. the application code is as follows:

CallableStatement callStmt = conn. prepareCall ("call pro (?,?) ");
// Call the prepareCall method of the Connection object to obtain the CallableStatement object callStmt. setString (1, "pro ");
// Assign nam = 'pro' callStmt. setInt (2,100) to the first placeholder parameter );
// Assign n = 100 callStmt.exe cute () to the second placeholder ();
// Execute the SQL statement

3. Source Code practice (1) create a stored procedureThe stored procedure calculates the sum of 1 + 2 + 3 +... + 100 and inserts the sum result into the table as the score value.

(2) Compile a JDBC database application to call the Stored Procedure

Import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException;/* MySQL database programming * instance (6): JDBC batch processing Stored Procedure */public class JDBC_Procedure {public static void main (String [] args) {// 0. string url = "jdbc: mysql: // localhost: 3306/jdbc_test_db"; // Database URL (unique resource identification identifier) String DBusername = "root "; // database username String DBpasswd = "111111"; // Database Password // 1. load the database Driver and register the Driver to the Dr Try {Class. forName ("com. mysql. jdbc. driver ");} catch (ClassNotFoundException e) {e. printStackTrace ();} // 2. connect to the database through the database URL Connection conn = null; CallableStatement callStmt = null; try {// 3. obtain the Connection object conn = DriverManager. getConnection (url, DBusername, DBpasswd); // 4. obtain the CallableStatement object callStmt = conn for executing the Stored Procedure SQL statement. prepareCall ("call pro (?,?) "); CallStmt. setString (1, "pro"); // assign nam = 'pro' callStmt to the first placeholder parameter. setInt (2,100); // set the value to n0000100callstmt.exe cute (); // Execute SQL statement} catch (SQLException e) {e. printStackTrace ();} // 5. release JDBC resources if (callStmt! = Null) // close the Declaration {try {callStmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) // close the connection {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

Running result


2. Transaction Processing 1. Transaction)A transaction refers to a group of operations, either successfully executed or not executed. Things have four major ACID properties: atomicity, isolation, consistency, and durability. (1) Atomicity (Atomicity): atoms mean the smallest particle, or things that cannot be further divided. The principle that database transactions cannot be further divided is atomicity. (2) Consistency: the total amount of data still matches before and after a transaction occurs. (3) isolation: before all operations are completed, other sessions cannot see the intermediate change process. (4) Durability: the impact of a transaction cannot be undone, in addition to errors, the transaction cannot be undone, but only through "compensatory"

2. Usage of thingsStep 1: Enable the transaction statement start transaction; Step 2: Execute SQL operations, that is, common SQL operations;
Step 3: commit or cancel a transaction. The transaction ends (the next time you use the transaction, you must re-enable it) commit; or rollback. Note: Some statements may cause implicit commit of the transaction, for example, repeat start transaction. In addition, select the innodb Engine when creating the table.

3. JDBC processing stored procedures
(1) Because JDBC processes transactions, transactions are automatically submitted by default. Therefore, before executing an SQL statement, we first call setAutoCommit () of the Connection object and pass in the false parameter. Connection conn = DriverManager. getConnection (url, DBusername, DBpasswd); conn. setAutoCommit (false );
(2) Add the SQL statement to be processed, and call the commit method of the Connection object to submit the transaction so that the SQL statement takes effect.
Statement stmt = conn. createStatement (); stmt = conn. createStatement ();
Stmt. addBatch ("insert into test (name, age, score) values ('hahaha', 10, 33 )");
Stmt.exe cuteBatch (); // Execute SQL statements in batches
Conn. commit (); // submit a transaction
Conn. setAutoCommit (true );

4. Source Code practice

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException; import java. SQL. statement; public class JDBC_Transaction {/* MySQL database programming * instance (7): JDBC batch processing Transaction */public static void main (String [] args) {// 0. string url = "jdbc: mysql: // localhost: 3306/jdbc_test_db"; // Database URL (unique resource identification identifier) String DBusername = "root "; // database username String DBpasswd = "896013"; // Database Password // 1. load the database Driver and register the Driver In DriverManager, try {Class. forName ("com. mysql. jdbc. driver ");} catch (ClassNotFoundException e) {e. printStackTrace ();} // 2. connect to the database through the database URL Connection conn = null; Statement stmt = null; try {// 3. obtain the Connection object conn = DriverManager. getConnection (url, DBusername, DBpasswd); // 4. conn is not automatically executed when SQL statements are set. setAutoCommit (false); // 5. get the Statement object stmt = conn. createStatement (); stmt. addBatch ("insert into test (name, age, score) val Ues ('hahaha', 10, 33) "); stmt. addBatch ("insert into test (name, age, score) values ('heiheihei',)"); stmt. addBatch ("insert into test (name, age, score) values ('xixi',)" );stmt.exe cuteBatch (); // run the SQL statement conn in batches. commit (); // submit transaction conn. setAutoCommit (true);} catch (SQLException e) {e. printStackTrace ();} // 5. release JDBC resource if (stmt! = Null) // close the Declaration {try {stmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) // close the connection {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

  Running result: (1) comment the transaction commit statement// Conn. commit (); // conn. setAutoCommit (true );
(2) execute the conn. commit () Result