JDBC Пакетная вставка обновления MySQL Oracle

Автор оригинала: Pankaj Kumar.

Сегодня мы рассмотрим примеры пакетной вставки и обновления JDBC в базах данных MySQL и Oracle. Иногда нам нужно выполнять массовые запросы аналогичного рода для базы данных. Например, загрузка данных из CSV-файлов в таблицы реляционной базы данных.

Как мы знаем, у нас есть возможность использовать Оператор или PreparedStatement для выполнения запросов. Кроме того, JDBC предоставляет функцию пакетной обработки, с помощью которой мы можем выполнять основную часть запросов к базе данных за один раз.

Пакет JDBC

Пакетные инструкции JDBC обрабатываются с помощью методов Statement и PreparedStatement | addBatch() и executeBatch () . Этот учебник предназначен для предоставления подробной информации о примере пакетной вставки JDBC для баз данных MySQL и Oracle.

Мы рассмотрим различные программы, поэтому у нас есть проект со структурой, как показано на рисунке ниже.

Обратите внимание, что у меня есть JDBC-файлы драйверов MySQL и Oracle DB в пути сборки проекта, чтобы мы могли запускать наше приложение как в MySQL, так и в Oracle DB.

Давайте сначала создадим простую таблицу для наших тестовых программ. Мы выполним основную часть запросов на вставку 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;

Мы прочитаем сведения о конфигурации базы данных из файла свойств, чтобы переключение с одной базы данных на другую было быстрым и легким.

#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

Прежде чем мы перейдем к фактическому примеру пакетной вставки JDBC для вставки массовых данных в таблицу сотрудников, давайте напишем простой служебный класс для подключения к базе данных.

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;
	}
}

Теперь давайте рассмотрим другой подход, который мы можем использовать для примера пакетной вставки JDBC.

Используйте инструкцию для выполнения одного запроса за раз.
Используйте инструкцию для выполнения одного запроса за раз.
Используйте PreparedStatement для выполнения одного запроса за раз.
Используйте PreparedStatement для выполнения одного запроса за раз.
Этот подход аналогичен использованию инструкции, но подготовленная инструкция обеспечивает преимущества в производительности и позволяет избежать атак с использованием SQL-инъекций.
Использование пакетного API для массовой обработки.
Использование пакетного API для массовой обработки.
Мы обрабатываем 10 000 записей с размером пакета 1000 записей. Как только размер пакета будет достигнут, мы его выполним и продолжим обработку оставшихся запросов.
Использование API пакетной обработки PreparedStatement для массовых запросов.
Использование API пакетной обработки PreparedStatement для массовых запросов.

Давайте посмотрим, как наши программы работают с базой данных MySQL, я несколько раз выполнял их отдельно, и в таблице ниже приведены результаты.

Затраченное время (мс)	8256	7129	8130	7019

Затраченное время (мс)

8256

7129

8130

7019

Когда я посмотрел на время отклика, я не был уверен, правильно ли это, потому что я ожидал некоторых хороших улучшений времени отклика при пакетной обработке. Поэтому я поискал в Интернете какое-нибудь объяснение и обнаружил, что по умолчанию пакетная обработка MySQL работает аналогично работе без пакета.

Чтобы получить реальные преимущества пакетной обработки в MySQL, нам нужно передать rewriteBatchedStatements как TRUE при создании подключения к БД. Посмотрите на URL-адрес MySQL выше в файле db.properties для этого.

С помощью rewriteBatchedStatements как true в приведенной ниже таблице указано время отклика для тех же программ.

Затраченное время (мс)	5676	3716	5570	394

Затраченное время (мс)

5676

3716

5570

394

Как вы можете видеть, пакетная обработка PreparedStatement выполняется очень быстро, когда верно значение rewriteBatchedStatements. Поэтому, если у вас много пакетной обработки, вам следует использовать эту функцию для более быстрой обработки.

Пакетная вставка Oracle

Когда я выполнял вышеуказанные программы для базы данных Oracle, результаты соответствовали результатам обработки MySQL, и пакетная обработка PreparedStatement была намного быстрее, чем любой другой подход.

Исключения пакетной обработки JDBC

Давайте посмотрим, как поведут себя пакетные программы в случае, если один из запросов вызовет исключения.

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();
			}
		}
	}

}

Когда я выполнил вышеуказанную программу для базы данных 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)

При выполнении той же программы для базы данных 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)

Но строки до исключения были успешно вставлены в базу данных. Хотя в исключении четко указано, в чем заключается ошибка, но оно не сообщает нам, какой запрос вызывает проблему. Поэтому либо мы проверяем данные перед добавлением их для пакетной обработки, либо мы должны использовать управление транзакциями JDBC, чтобы убедиться, что все или ни одна из записей не вставляются в случае исключений.

Та же программа с управлением транзакциями JDBC выглядит следующим образом.

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();
			}
		}
	}

}

Как вы можете видеть, я откатываю транзакцию, если возникнет какое-либо исключение SQL. Если пакетная обработка прошла успешно, я явно фиксирую транзакцию.

Резюме

Это все для примера обновления пакетной вставки JDBC, обязательно поэкспериментируйте со своими данными, чтобы получить оптимальное значение размера пакета для массовых запросов. Одним из ограничений пакетной обработки JDBC является то, что мы не можем выполнять различные типы запросов в пакете.

Пакет JDBC

Пакетная вставка Oracle

Исключения пакетной обработки JDBC

Резюме

Читайте ещё по теме: