JAVA-JDBC call batch, stored procedure, transaction

I. Batch processing of databases using batch

When you need to send a batch of SQL statement execution to the database, avoid sending execution to the database, and use the JDBC batch mechanism to improve execution efficiency.

1. There are two ways to implement batch processing, the first way:

Statement.addbatch (SQL) List

Execute a batch SQL statement

ExecuteBatch () Method: Execute Batch Command

Clearbatch () Method: Clear Batch Command

Cases:

Connection conn = null;

Statement st = null;

ResultSet rs = null;

try {

conn = Jdbcmanager.getconnection ();

String SQL1 = "INSERT into user (Name,password,email,birthday)

VALUES (' KKK ', ' 123 ', ' [email protected] ', ' 1978-08-08 ');

String sql2 = "Update user set password= ' 123456 ' where id=3";

st = Conn.createstatement ();

St.addbatch (SQL1); Add SQL statements to the batch command

St.addbatch (SQL2); Add SQL statements to the batch command

St.executebatch ();

} finally{

Jdbcmanager.dbclose (CON,ST,RS);

}

Batch processing using Statement.addbatch (SQL):

Pros: You can send multiple different SQL statements to the database.

Disadvantages:

The SQL statement is not precompiled.

When you send more than one statement to a database, but only a different SQL statement, you need to write a number of SQL statements repeatedly. For example:

Insert into User (Name,password) VALUES (' AA ', ' 111 ');

Insert into User (Name,password) VALUES (' BB ', ' 222 ');

Insert into User (Name,password) VALUES (' cc ', ' 333 ');

Insert into User (Name,password) VALUES (' dd ', ' 444 ');

2, the second way to achieve batch processing:

Preparedstatement.addbatch ();

Cases:

conn = Jdbcmanager.getconnection ();//access to tools;

String sql = "INSERT into user (Name,password,email,birthday) VALUES (?,?,?,?)";

st = conn.preparestatement (SQL);//preprocessing SQL statements;

for (int i=0;i<50000;i++) {

St.setstring (1, "AAA" + i);

St.setstring (2, "123" + i);

St.setstring (3, "AAA" + i + "@sina. com");

St.setdate (4,new Date (1980, 10, 10));

St.addbatch ();//Adds a set of parameters to the batch command for this PreparedStatement object.

if (i%1000==0) {

St.executebatch ();

St.clearbatch (); Clears the current list of SQL commands for this Statement object.

}

}

St.executebatch (); Submits a batch of commands to the database for execution, and returns an array of update counts if all commands succeed. The order of the int element that returns the array corresponds to the command in the batch, and the commands in the batch are sorted according to the sequence that is added to the batch

Using Preparedstatement.addbatch () to implement batch processing

Advantage: The post-compilation SQL statement is sent with high execution efficiency.

Disadvantage: You can only apply in batches that have the same SQL statement but different parameters. So this form of batching is often used to bulk insert data in the same table, or to bulk update the data for a table.

Second, get the database auto-generated primary key:

Connection Con=null;

PreparedStatement Ps=null;

con = jdbcmanager.getconnection ();

String sql= "INSERT into users (Name,password) VALUES (?,?)";

try {

PS = con.preparestatement (Sql,statement.return_generated_keys);//Gets the primary key returned;

Ps.setstring (1, "QQ");

Ps.setstring (2, "123");

Ps.executeupdate ();

ResultSet Rs=ps.getgeneratedkeys ();//Returns a result set that holds the result set of the resulting key,

while (Rs.next ()) {

System.out.println (Rs.getobject (1));//The result set has only one value;

}

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}finally{

Jdbcmanager.dbclose (Con, PS, null);

}

Third, JDBC calls the stored procedure

Creation of stored procedures

Create procedure stored procedure name (parameter)

Stored Procedure Body

Write a stored procedure that queries all the information in the student table.

Delimiter $$

CREATE PROCEDURE Myproc1 ()

Begin

select * Form XS;

End $$

delimiter;

Execution: Call Myproc1 ();

Parameters

In: Input parameters

Out: Output parameters

InOut: input and output parameters

Requirements: Write a stored procedure to query a student's information by the number.

Delimiter $$

CREATE PROCEDURE select_student (in XH char (6))

Begin

SELECT * FROM XS where study number =xh;

End $$

delimiter;

Execution: Call select_student (' 081101 ');

Requirements: Write a stored procedure that counts the total number of students

Delimiter $$

CREATE PROCEDURE Count_xs (out number int)

Begin

Select COUNT (*) into number from XS;

End $$

delimiter;

Execution: Call Count_xs (@rs);

Enquiry: Select @rs;

Create procedure stored procedure name (in|out|inout parameter name type ....) );

JDBC calls the save process (creates the body of the stored procedure):

When the value is the input function:

Cases:

Connection Con=null;

CallableStatement Cs=null;

Con=jdbcmanager.getconnection ();

try {

Cs=con.preparecall ("{call PD (?,?)}"); /stored procedure statement;

Cs.setstring (1, "yy");

Cs.setstring (2, "MSN");

Cs.execute ();//execution

SYSTEM.OUT.PRINTLN ("Successful Execution");

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}finally{

Jdbcmanager.dbclose (Con, CS, null);

}

When input and output are simultaneously:

Cases

Connection Con=null;

CallableStatement Cs=null;

Con=jdbcmanager.getconnection ();

try {

Cs=con.preparecall ("{Call Pcall (?,?)}");

Cs.setint (1, 10);

Cs.registeroutparameter (2,types.char);//Get the registration type;

Cs.execute ();//execution

System.out.println (cs.getstring (2));//Gets the second string type parameter value;

Cs.execute ();

SYSTEM.OUT.PRINTLN ("Successful Execution");

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}finally{

Jdbcmanager.dbclose (Con, CS, null);

}

Iv. use of the transaction

A transaction is a logical set of actions that make up the units of this set of operations, either all succeed or all are unsuccessful.

For example: a--b transfer, corresponding to the following two SQL statements

Update from account set money=money+100 where name= ' B ';

Update from account set money=money-100 where name= ' a ';

Database open Transaction Command

Start Transaction Open transaction

Rollback Rolling back transactions

Commit COMMIT Transaction

When the JDBC program obtains a connection object to the database, by default the Connection object submits the SQL statement sent on it to the database automatically. To turn off this default submission method and have multiple SQL execute in a single transaction, use the following statement:

JDBC Control TRANSACTION Statements

Connection.setautocommit (FALSE); Start transaction

Connection.rollback (); Rollback

Connection.commit (); Commit

Creating a JDBC transaction consists of the following steps

1. Set up transactions to be submitted as non-automatic commits:

Conn.setautocommit (FALSE);

2. Place the code that needs to add the transaction into the Try,catch block.

3. Add the commit operation of the transaction within the try block, indicating that the operation has no exception and commits the transaction.

Conn.commit ();

4. Add a ROLLBACK transaction within the CATCH block to indicate an exception to the operation and to revoke the transaction:

Conn.rollback ();

5. Set the transaction submission method to Auto-commit:

Conn.setautocommit (TRUE);

Characteristics of the transaction (ACID)

Atomicity (atomicity)
Atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.

Consistency (consistency)
A transaction must transform the database from one consistent state to another consistent state.

Isolation (Isolation)
The isolation of transactions is when multiple users concurrently access the database, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

Persistence (Durability)
Persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.

The isolation of the transaction:

Dirty read:

Refers to a transaction that reads uncommitted data from another transaction.

This is very dangerous, assuming a to B transfer 100 yuan, the corresponding SQL statement is as follows

1.update account set money=money+100 while name= ' B ';

2.update account set money=money-100 while Name= ' a ';

When the 1th SQL is executed, and the 2nd is not executed (A is not committed), if at this point B queries its own account, you will find that they have more than 100 dollars. If A and B go back, B will lose 100 yuan.

Non-repeatable READ:

Reading a row of data from a table within a transaction is different from reading the results multiple times.

For example, the bank wants to query a account balance, the first query a account is 200 yuan, at this time a to account deposited 100 yuan and submitted, the bank then made a query, at this time a account is 300 yuan. Bank two queries inconsistent, may be very confused, do not know which query is accurate.

The difference between dirty reads is that dirty reads are dirty data that was not committed by the previous transaction, and non-repeatable reads are re-reading the data that was submitted by the previous transaction.

Many people think this situation is right, need not be confused, of course, the latter will prevail. We can consider such a situation, such as banking procedures need to output the results of the query to the computer screen and write to the file, the result in a transaction for the destination output, the two queries are inconsistent, resulting in inconsistent results in the file and screen, the bank staff do not know which to prevail.

Virtual Read (Phantom Read)

Refers to the data that is inserted into another transaction within a transaction, causing inconsistencies in the read and backward reads.

If the deposit of 100 yuan is not submitted, then the bank to do the report statistics account of all users of the total amount of 500 yuan, and then C submitted, then the bank again to find accounts of 600 yuan, resulting in a virtual reading will also make the bank at a loss, in the end to whichever.

The database has a total of four isolation levels defined:

Serializable: Can avoid dirty reading, non-repeatable reading, the occurrence of false reading. (serialization) (serialized)

REPEATABLE READ: Can avoid dirty read, non-repeatable read situation occurs. (repeatable reading)

Read Committed: Prevents dirty reads from occurring (Read committed).

Read UNCOMMITTED: lowest level, none of the above is guaranteed. (Read not submitted)

Set TRANSACTION ISOLATION level sets transaction isolation levels

SELECT @ @tx_isolation Query the current transaction isolation level

Cases:

Connection Con=null;

PreparedStatement St=null;

String sql1= "INSERT into users (Name,password) VALUES (' bbbbb ', ' bbbb ')";

String sql2= "Delete from users where id=9";

try {

Con=dbmanager.getconnection ();

Con.setautocommit (false);//Turn autocommit into manual

St=con.preparestatement (SQL1);

St.executeupdate ();

System.out.println ("The first statement succeeded");

St=con.preparestatement (SQL2);

St.executeupdate ();

System.out.println ("The second statement succeeded");

Con.commit ();

} catch (Exception e) {

try {

Con.rollback ();//the rollback occurs unexpectedly;

} catch (SQLException E1) {

TODO auto-generated Catch block

E1.printstacktrace ();

}

E.printstacktrace ();

}finally{

try {

Con.setautocommit (TRUE);

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

Dbmanager.closedb (Con, St, null);

}

JAVA-JDBC call batch, stored procedure, transaction