Connect to the MySQL database using JDBC-Typical Case Analysis (7)-insert employee information in batches, jdbcmysql

Connect to the MySQL database using JDBC-Typical Case Analysis (7)-insert employee information in batches, jdbcmysql

Reprinted Please note:Zhang Qi's CSDN blog-blog channel-CSDN. NET

Problem:

Insert 100 pieces of data into the Emp table in batches. The columns for inserting data are empno, ename, and sal. the data corresponding to these three fields is automatically generated by the empno column through the series emp_seq, the data in the ename column is composed of string "name" + number of cycles I, and sal data is composed of random integers less than 10000.

Solution:

Insert a piece of data into the database every cycle and access the database frequently, resulting in low efficiency.

The batch processing API provided in java. When you frequently operate databases, you can use JDBC batch processing to improve program efficiency. The main features of batch processing are as follows:

1. Use the same Connection resource to send multiple SQL statements at a time for execution.

2. Improve the throughput between applications and databases and shorten the DB Response Time

3. Compared with the SQL Execution Method One by one, the larger the data volume to be processed, the more obvious the advantage of Batch Processing

The core code for batch processing is as follows:

Step 1: Create a sequence emp_seq in Mysql DATA

In MysqlIn the database, the sequence name is emp_ SQL. The starting value of the sequence is 1 and the step is 1. the SQL statement is as follows:

CREATE TABLE emp_seq ( seq VARCHAR(8));INSERT INTO emp_seq VALUES('0');UPDATE emp_seq SET seq = LAST_INSERT_ID(seq+1);SELECT LAST_INSERT_ID();

Step 2: Prepare the basic code for JDBC operations on the database

First, create a new class Batch, create a new batchAdd method in the class, then prepare the database Connection object, operate the Statement object of the SQL Statement, and SET transaction management. Finally, handle exceptions, the Code is as follows:

Package Account; import java. SQL. connection; import java. SQL. SQLException; import java. SQL. statement; import java. util. random; import dao. connectionSource; public class Batch {public static void main (String [] args) {} public void batchAdd () {Connection con = null; Statement stmt = null; String SQL = null; try {con = ConnectionSource. getConnection (); stmt = con. createStatement (); // close automatic submission con. setAutoCommit (false); // submit con. Commit ();} catch (SQLException e) {System. out. println ("database access exception! "); Throw new RuntimeException (e);} finally {try {if (stmt! = Null) {stmt. close () ;}if (con! = Null) {con. close () ;}} catch (SQLException e) {System. out. println ("an exception occurred when releasing the resource! ");}}}}

Procedure Iii. Batch insert data into the Emp table

Use the statement addBatch method and executeBatch method to insert data into the Emp table in batches. The Code is as follows:

 

Package Account; import java. SQL. connection; import java. SQL. SQLException; import java. SQL. statement; import java. util. random; import dao. connectionSource; public class Batch {public static void main (String [] args) {} public void batchAdd () {Connection con = null; Statement stmt = null; String SQL = null; try {con = ConnectionSource. getConnection (); stmt = con. createStatement (); // close automatic submission con. setAutoCommit (false); for (int I = 0; I <100; I ++) {// SQL statement for data insertion SQL = "insert into emp (empno, ename, sal) values (" + "emp_seq.nextval, 'name "+ I +" ', "+ new Random (). nextInt (10000) + ")"; // Add the SQL statement to the Batch stmt. addBatch (SQL) ;}// execute the batch processing stmt.exe cuteBatch (); // submit con. commit ();} catch (SQLException e) {System. out. println ("database access exception! "); Throw new RuntimeException (e);} finally {try {if (stmt! = Null) {stmt. close () ;}if (con! = Null) {con. close () ;}} catch (SQLException e) {System. out. println ("an exception occurred when releasing the resource! ");}}}}

Step 4: test whether data is successfully inserted in batches

public static void main(String [] args){Batch batch=new Batch();batch.batchAdd();}

Before running:

After running:

The result is a bit problematic. The above Code contains the function of orcle,

emp_seq.nextval

Not found yetTo a suitable similar solution that can run, if you use orcle as the database, then there will be no problem.

If you find a friend who is suitable for a good method, you can comment on the solution and send it to us for communication.

Wait for the next highlights ~