JDBC批处理插入更新MySQL Oracle

最新推荐文章于 2024-04-23 10:32:10 发布

cunchi4221

最新推荐文章于 2024-04-23 10:32:10 发布

阅读量705

点赞数

文章标签：数据库 mysql java jdbc sql

原文链接：https://www.journaldev.com/2494/jdbc-batch-insert-update-mysql-oracle

版权

Today we will look into JDBC Batch insert and update examples in MySQL and Oracle databases. Sometimes we need to run bulk queries of a similar kind for a database. For example, loading data from CSV files to relational database tables.

今天，我们将研究MySQL和MySQL数据库中的JDBC Batch插入和更新示例。有时我们需要对数据库运行类似类型的批量查询。例如，将数据从CSV文件加载到关系数据库表。

As we know that we have option to use Statement or PreparedStatement to execute queries. Apart from that JDBC provides Batch Processing feature through which we can execute the bulk of queries in one go for a database.

众所周知，我们可以选择使用Statement或PreparedStatement来执行查询。除此之外， JDBC提供了批处理功能，通过该功能，我们可以一次性执行数据库的大量查询。

JDBC批处理 (JDBC Batch)

JDBC batch statements are processed through Statement and PreparedStatement addBatch() and executeBatch() methods. This tutorial is aimed to provide details about JDBC Batch insert example for MySQL and Oracle database.

JDBC批处理语句通过Statement和PreparedStatement addBatch()和executeBatch()方法进行处理。本教程旨在提供有关MySQL和Oracle数据库的JDBC Batch插入示例的详细信息。

We will look into different programs so we have a project with the structure as below image.

我们将研究不同的程序，因此我们有一个结构如下图的项目。

Notice that I have MySQL and Oracle DB JDBC Driver jars in the project build path so that we can run our application across MySQL and Oracle DB both.

注意，我在项目构建路径中有MySQL和Oracle DB JDBC Driver jar，以便我们可以在MySQL和Oracle DB两者上运行我们的应用程序。

Let’s first create a simple table for our test programs. We will run the bulk of JDBC insert queries and look at the performance with different approaches.

让我们首先为我们的测试程序创建一个简单的表。我们将运行大量的JDBC插入查询，并使用不同的方法查看性能。

--Oracle DB
CREATE TABLE Employee (
  empId NUMBER NOT NULL,
  name varchar2(10) DEFAULT NULL,
  PRIMARY KEY (empId)
);

--MySQL DB
CREATE TABLE `Employee` (
  `empId` int(10) unsigned NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We will read the Database configuration details from the property file so that switching from one database to another is quick and easy.

我们将从属性文件中读取数据库配置详细信息，以便快速而轻松地从一个数据库切换到另一个数据库。

db.properties

#mysql DB properties
DB_DRIVER_CLASS=com.mysql.jdbc.Driver
DB_URL=jdbc:mysql://localhost:3306/UserDB
#DB_URL=jdbc:mysql://localhost:3306/UserDB?rewriteBatchedStatements=true
DB_USERNAME=pankaj
DB_PASSWORD=pankaj123

#Oracle DB Properties
#DB_DRIVER_CLASS=oracle.jdbc.driver.OracleDriver
#DB_URL=jdbc:oracle:thin:@localhost:1871:UserDB
#DB_USERNAME=scott
#DB_PASSWORD=tiger

Before we move into actual JDBC batch insert example to insert bulk data into the Employee table, let’s write a simple utility class to get the database connection.

在进入实际的JDBC批处理插入示例以将批量数据插入Employee表之前，让我们编写一个简单的实用程序类来获取数据库连接。

DBConnection.java

package com.journaldev.jdbc.batch;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnection {

	public static Connection getConnection() {
		Properties props = new Properties();
		FileInputStream fis = null;
		Connection con = null;
		try {
			fis = new FileInputStream("db.properties");
			props.load(fis);

			// load the Driver Class
			Class.forName(props.getProperty("DB_DRIVER_CLASS"));

			// create the connection now
			con = DriverManager.getConnection(props.getProperty("DB_URL"),
					props.getProperty("DB_USERNAME"),
					props.getProperty("DB_PASSWORD"));
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
}

Now let’s look at the different approach we can take for JDBC batch insert example.

现在让我们看一下可以为JDBC批处理插入示例使用的不同方法。

Use Statement to execute one query at a time.

JDBCStatement.java

package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatement {

	public static void main(String[] args) {
		
		Connection con = null;
		Statement stmt = null;
		
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			
			long start = System.currentTimeMillis();
			for(int i =0; i<10000;i++){
				String query = "insert into Employee values ("+i+",'Name"+i+"')";
				stmt.execute(query);
			}
			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

使用Statement一次执行一个查询。

JDBCStatement.java

Use PreparedStatement to execute one query at a time.

JDBCPreparedStatement.java

package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPreparedStatement {

	public static void main(String[] args) {
		
		Connection con = null;
		PreparedStatement ps = null;
		String query = "insert into Employee (empId, name) values (?,?)";
		try {
			con = DBConnection.getConnection();
			ps = con.prepareStatement(query);
			
			long start = System.currentTimeMillis();
			for(int i =0; i<10000;i++){
				ps.setInt(1, i);
				ps.setString(2, "Name"+i);
				ps.executeUpdate();
			}
			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

This approach is similar to using Statement but PreparedStatement provides performance benefits and avoids SQL injection attacks.

JDBCPreparedStatement.java

这种方法类似于使用Statement，但是PreparedStatement提供了性能优势并避免了SQL注入攻击。

Using Statement Batch API for bulk processing.

JDBCStatementBatch.java

package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementBatch {

	public static void main(String[] args) {
		
		Connection con = null;
		Statement stmt = null;
		
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			
			long start = System.currentTimeMillis();
			for(int i =0; i<10000;i++){
				String query = "insert into Employee values ("+i+",'Name"+i+"')";
				stmt.addBatch(query);
				
				//execute and commit batch of 1000 queries
				if(i%1000 ==0) stmt.executeBatch();
			}
			//commit remaining queries in the batch
			stmt.executeBatch();
			
			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

We are processing 10,000 records with a batch size of 1000 records. Once the batch size reaches, we are executing it and continue processing remaining queries.

JDBCStatementBatch.java

我们正在处理10,000条记录，批量大小为1000条记录。达到批处理大小后，我们将执行它并继续处理剩余的查询。

Using PreparedStatement Batch Processing API for bulk queries.

JDBCPreparedStatementBatch.java

package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPreparedStatementBatch {

	public static void main(String[] args) {
		
		Connection con = null;
		PreparedStatement ps = null;
		String query = "insert into Employee (empId, name) values (?,?)";
		try {
			con = DBConnection.getConnection();
			ps = con.prepareStatement(query);
			
			long start = System.currentTimeMillis();
			for(int i =0; i<10000;i++){
				ps.setInt(1, i);
				ps.setString(2, "Name"+i);
				
				ps.addBatch();
				
				if(i%1000 == 0) ps.executeBatch();
			}
			ps.executeBatch();
			
			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

使用PreparedStatement批处理API进行批量查询。

JDBCPreparedStatementBatch.java

Let’s see how our programs work with MySQL database, I have executed them separately multiple times and below table contains the results.

让我们看看我们的程序如何与MySQL数据库一起使用，我已经分别执行了多次，下表包含结果。

MySQL DB	Statement	PreparedStatement	Statement Batch	PreparedStatement Batch
Time Taken (ms)	8256	8130	7129	7019

MySQL数据库	声明	准备声明	报表批处理	PreparedStatement批处理
所需时间（毫秒）	8256	8130	7129	7019

When I looked at the response time, I was not sure whether it’s right because I was expecting some good response time improvements with Batch Processing. So I looked online for some explanation and found out that by default MySQL batch processing works in a similar way like running without batch.

当我查看响应时间时，我不确定是否正确，因为我期望批处理可以改善一些响应时间。因此，我在网上寻找了一些解释，并发现默认情况下，MySQL批处理的工作方式类似于无批处理。

To get the actual benefits of Batch Processing in MySQL, we need to pass rewriteBatchedStatements as TRUE while creating the DB connection. Look at the MySQL URL above in db.properties file for this.

为了获得MySQL中批处理的实际好处，我们需要在创建数据库连接时将rewriteBatchedStatements传递为TRUE。在db.properties文件中查看上面MySQL URL。

With rewriteBatchedStatements as true, below table provides the response time for the same programs.

在rewriteBatchedStatements为true ，下表提供了相同程序的响应时间。

MySQL DB	Statement	PreparedStatement	Statement Batch	PreparedStatement Batch
Time Taken (ms)	5676	5570	3716	394

MySQL数据库	声明	准备声明	报表批处理	PreparedStatement批处理
所需时间（毫秒）	5676	5570	3716	394

As you can see that PreparedStatement Batch Processing is very fast when rewriteBatchedStatements is true. So if you have a lot of batch processing involved, you should use this feature for faster processing.

如您所见，rewriteBatchedStatements为true时，PreparedStatement批处理非常快。因此，如果涉及大量批处理，则应使用此功能来加快处理速度。

Oracle批处理插入 (Oracle Batch Insert)

When I executed above programs for Oracle database, the results were in line with MySQL processing results and PreparedStatement Batch processing was much faster than any other approach.

当我针对Oracle数据库执行上述程序时，结果与MySQL处理结果一致，并且PreparedStatement Batch处理比任何其他方法都快得多。

JDBC批处理异常 (JDBC Batch Processing Exceptions)

Let’s see how batch programs behave in case one of the queries throw exceptions.

让我们看看在查询之一抛出异常的情况下批处理程序的行为。

JDBCBatchExceptions.java

package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class JDBCBatchExceptions {

	public static void main(String[] args) {
		
		Connection con = null;
		PreparedStatement ps = null;
		String query = "insert into Employee (empId, name) values (?,?)";
		try {
			con = DBConnection.getConnection();
			
			ps = con.prepareStatement(query);
			
			String name1 = "Pankaj";
			String name2="Pankaj Kumar"; //longer than column length
			String name3="Kumar";
			
			ps.setInt(1, 1);
			ps.setString(2, name1);
			ps.addBatch();
			
			ps.setInt(1, 2);
			ps.setString(2, name2);
			ps.addBatch();
			
			ps.setInt(1, 3);
			ps.setString(2, name3);
			ps.addBatch();
			
			int[] results = ps.executeBatch();
			
			System.out.println(Arrays.toString(results));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

When I executed the above program for MySQL database, I got below exception and none of the records were inserted in the table.

当我为MySQL数据库执行上述程序时，出现以下异常，并且没有任何记录插入表中。

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'name' at row 2
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1008)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:908)
	at com.journaldev.jdbc.batch.JDBCBatchExceptions.main(JDBCBatchExceptions.java:37)

When executed the same program for Oracle database, I got below exception.

当对Oracle数据库执行相同的程序时，出现以下异常。

java.sql.BatchUpdateException: ORA-12899: value too large for column "SCOTT"."EMPLOYEE"."NAME" (actual: 12, maximum: 10)

	at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10070)
	at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:213)
	at com.journaldev.jdbc.batch.JDBCBatchExceptions.main(JDBCBatchExceptions.java:38)

But the rows before exception were inserted into the database successfully. Although the exception clearly says what the error is but it doesn’t tell us which query is causing the issue. So either we validate the data before adding them for batch processing or we should use JDBC Transaction Management to make sure all or none of the records are getting inserted in case of exceptions.

但是异常之前的行已成功插入数据库。尽管该异常清楚地说明了错误是什么，但它并没有告诉我们哪个查询引起了该问题。因此，要么在添加数据进行批处理之前验证数据，要么使用JDBC事务管理以确保在发生异常的情况下插入所有记录或不插入任何记录。

Same program with JDBC transaction management looks like below.

带有JDBC事务管理的相同程序如下所示。

JDBCBatchExceptions.java

package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class JDBCBatchExceptions {

	public static void main(String[] args) {
		
		Connection con = null;
		PreparedStatement ps = null;
		String query = "insert into Employee (empId, name) values (?,?)";
		try {
			con = DBConnection.getConnection();
			con.setAutoCommit(false);
			
			ps = con.prepareStatement(query);
			
			String name1 = "Pankaj";
			String name2="Pankaj Kumar"; //longer than column length
			String name3="Kumar";
			
			ps.setInt(1, 1);
			ps.setString(2, name1);
			ps.addBatch();
			
			ps.setInt(1, 2);
			ps.setString(2, name2);
			ps.addBatch();
			
			ps.setInt(1, 3);
			ps.setString(2, name3);
			ps.addBatch();
			
			int[] results = ps.executeBatch();
			
			con.commit();
			System.out.println(Arrays.toString(results));
			
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

As you can see that I am rolling back the transaction if any SQL exception comes. If the batch processing is successful, I am explicitly committing the transaction.

如您所见，如果出现任何SQL异常，我将回滚事务。如果批处理成功，则我将明确提交事务。

摘要 (Summary)

That’s all for JDBC Batch insert update example, make sure to experiment with your data to get the optimal value of batch size for bulk queries. One of the limitations of JDBC batch processing is that we can’t execute different type of queries in the batch.

这就是JDBC批处理插入更新示例的全部内容，请确保对您的数据进行试验以获得批量查询的最佳批处理大小值。 JDBC批处理的局限性之一是我们不能在批处理中执行不同类型的查询。

GitHub Repository. GitHub Repository下载JDBC示例项目。

翻译自: https://www.journaldev.com/2494/jdbc-batch-insert-update-mysql-oracle

cunchi4221

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JDBC批处理插入更新MySQL Oracle

Today we will look into JDBC Batch insert and update examples in MySQL and Oracle databases. Sometimes we need to run bulk queries of a similar kind for a database. For example, loading data from CSV ...
复制链接

扫一扫