Spring Data Jpa如何實(shí)現(xiàn)批量插入或更新
更新時(shí)間：2024年12月09日 11:52:38 作者：點(diǎn)滴1993
文章總結(jié)：本文分享了四種Spring Data JPA批量插入或更新的方法,包括BatchConsumer、QueryParameterBuilder、KeyValue和SqlUtil,旨在為開發(fā)者提供實(shí)用的參考
Spring Data Jpa批量插入或更新
1. BatchConsumer
package com.demo.common.hibernate.batch;
import com.demo.common.hibernate.querydsl.QueryParameterBuilder;
/**
* 批量數(shù)據(jù)消費(fèi)者接口，用于設(shè)置 SQL 參數(shù)并執(zhí)行操作。
*
* @param <T> 記錄類型的泛型
* @author xm.z
*/
@FunctionalInterface
public interface BatchConsumer<T> {
/**
* 設(shè)置 SQL 參數(shù)并執(zhí)行操作。
*
* @param builder 參數(shù)構(gòu)建對象
* @param record 要處理的記錄
*/
void accept(QueryParameterBuilder builder, T record);
}2. QueryParameterBuilder
package com.demo.common.hibernate.querydsl;
import lombok.AccessLevel;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.jpa.TypedParameterValue;
import org.hibernate.type.*;
import org.springframework.util.Assert;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;
/**
* QueryParameterBuilder
* <p>
* A utility class for building parameters for query.
*
* @author xm.z
*/
@Slf4j
@Getter
public class QueryParameterBuilder {
/**
* The native query object to be used for parameter setting
*/
private final Query nativeQuery;
/**
* The counter for parameter position
*/
@Getter(value = AccessLevel.NONE)
private final AtomicInteger position;
/**
* The current date and time when the QueryParameterBuilder instance is created
*/
private final LocalDateTime now;
/**
* Private constructor to initialize QueryParameterBuilder
*/
private QueryParameterBuilder(Query nativeQuery, AtomicInteger position) {
this.nativeQuery = nativeQuery;
this.position = position;
this.now = LocalDateTime.now();
}
/**
* Retrieves the current position of the parameter.
*
* @return The current position of the parameter.
*/
public Integer obtainCurrentPosition() {
return position.get();
}
/**
* Create an instance of QueryParameterBuilder.
*
* @param nativeQuery The native query object
* @param position The parameter position counter
* @return QueryParameterBuilder instance
*/
public static QueryParameterBuilder create(Query nativeQuery, AtomicInteger position) {
Assert.notNull(nativeQuery, "Native query must not be null");
Assert.notNull(position, "Position must not be null");
return new QueryParameterBuilder(nativeQuery, position);
}
/**
* Set a parameter of type Long.
*
* @param value The Long value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(Long value) {
return this.setParameter(StandardBasicTypes.LONG, value);
}
/**
* Set a parameter of type Integer.
*
* @param value The Integer value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(Integer value) {
return this.setParameter(StandardBasicTypes.INTEGER, value);
}
/**
* Set a parameter of type BigDecimal.
*
* @param value The BigDecimal value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(BigDecimal value) {
return this.setParameter(StandardBasicTypes.BIG_DECIMAL, value);
}
/**
* Set a parameter of type String.
*
* @param value The String value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(String value) {
return this.setParameter(StandardBasicTypes.STRING, value);
}
/**
* Set a parameter of type Boolean.
*
* @param value The Boolean value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(Boolean value) {
return this.setParameter(StandardBasicTypes.BOOLEAN, value);
}
/**
* Set a parameter of type Date.
*
* @param value The Date value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(Date value) {
return this.setParameter(StandardBasicTypes.DATE, value);
}
/**
* Set a parameter of type LocalDate.
*
* @param value The LocalDate value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(LocalDate value) {
return this.setParameter(LocalDateType.INSTANCE, value);
}
/**
* Set a parameter of type LocalTime.
*
* @param value The LocalTime value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(LocalTime value) {
return this.setParameter(LocalTimeType.INSTANCE, value);
}
/**
* Set a parameter of type LocalDateTime.
*
* @param value The LocalDateTime value for the parameter
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(LocalDateTime value) {
return this.setParameter(LocalDateTimeType.INSTANCE, value);
}
/**
* Add or include a query condition to the native query object and set the parameter value.
*
* @param type The parameter type
* @param value The parameter value
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(Type type, Object value) {
return this.setParameter(position.getAndIncrement(), type, value);
}
/**
* Add or include a query condition to the native query object and set the parameter value at the specified position.
*
* @param position The position of the parameter in the query
* @param type The parameter type
* @param value The parameter value
* @return The current QueryParameterBuilder instance
*/
public QueryParameterBuilder setParameter(int position, Type type, Object value) {
TypedParameterValue typedParameterValue = new TypedParameterValue(type, value);
if (log.isDebugEnabled()) {
log.debug("Setting parameter at position {}: {}", position, typedParameterValue);
}
nativeQuery.setParameter(position, typedParameterValue);
return this;
}
}3. KeyValue
package com.demo.common.model;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
* 用于表示鍵值對的通用類
*
* @param <K> 鍵的類型
* @param <V> 值的類型
* @author xm.z
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class KeyValue<K, V> implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 鍵
*/
@Schema(title = "鍵")
private K key;
/**
* 值
*/
@Schema(title = "值")
private V value;
}4. SqlUtil
package com.demo.common.hibernate.util;
import com.demo.common.hibernate.batch.BatchConsumer;
import com.demo.common.hibernate.querydsl.QueryParameterBuilder;
import com.demo.common.model.KeyValue;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.core.collection.CollUtil;
import lombok.extern.slf4j.Slf4j;
import org.jetbrains.annotations.NotNull;
import org.springframework.lang.NonNull;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
/**
* SqlUtil
*
* @author xm.z
*/
@Slf4j
@SuppressWarnings("all")
public class SqlUtil {
/**
* Default batch insert size.
*/
public static final int DEFAULT_BATCH_SIZE = 100;
/**
* Private constructor.
*/
private SqlUtil() {
}
/**
* Batch insert records into the database.
*
* @param tableFields The table fields information
* @param records The list of records to be inserted
* @param consumer The consumer function interface for customizing the insert behavior
* @param <T> The type of records
* @return The number of records successfully inserted
*/
public static <T> int batchInsert(@NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
@NonNull List<T> records, @NonNull BatchConsumer<? super T> consumer) {
return batchInsert(DEFAULT_BATCH_SIZE, tableFields, records, consumer);
}
/**
* Perform batch insert operation with the specified batch size.
*
* @param batchSize the size of each batch for insertion
* @param tableFields the key-value pair representing the table fields
* @param records the list of records to be inserted
* @param consumer the batch consumer for processing each batch of records
* @param <T> the type of records
* @return the total number of records successfully inserted
*/
public static <T> int batchInsert(int batchSize, @NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
@NonNull List<T> records, @NonNull BatchConsumer<? super T> consumer) {
EntityManager entityManager = SpringUtil.getBean(EntityManager.class);
return batchExecuteUpdate(batchSize, entityManager, tableFields, null, records, consumer);
}
/**
* Batch insert records into the database.
*
* @param entityManager The entity manager
* @param tableFields The table fields information
* @param records The list of records to be inserted
* @param consumer The consumer function interface for customizing the insert behavior
* @param <T> The type of records
* @return The number of records successfully inserted
*/
public static <T> int batchInsert(EntityManager entityManager,
@NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
@NonNull List<T> records, @NonNull BatchConsumer<? super T> consumer) {
return batchExecuteUpdate(DEFAULT_BATCH_SIZE, entityManager, tableFields, null, records, consumer);
}
/**
* Executes batch insert or update operations on the database using native SQL with a default batch size.
*
* @param tableFields key-value pair representing the table name and its fields
* @param updateFields set of fields to be updated if a record with matching primary key exists
* @param records the list of records to be inserted or updated
* @param consumer functional interface for accepting batch consumer operations
* @param <T> the type of the records to be inserted or updated
* @return the total number of rows affected by the batch operation
*/
public static <T> int batchInsertOrUpdate(@NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
@NonNull LinkedHashSet<String> updateFields,
@NonNull List<T> records, @NonNull BatchConsumer<? super T> consumer) {
return batchInsertOrUpdate(DEFAULT_BATCH_SIZE, tableFields, updateFields, records, consumer);
}
/**
* Executes batch insert or update operations on the database using native SQL with a parameterized batch size.
*
* @param batchSize the size of each batch for insertion
* @param tableFields key-value pair representing the table name and its fields
* @param updateFields set of fields to be updated if a record with matching primary key exists
* @param records the list of records to be inserted or updated
* @param consumer functional interface for accepting batch consumer operations
* @param <T> the type of the records to be inserted or updated
* @return the total number of rows affected by the batch operation
*/
public static <T> int batchInsertOrUpdate(int batchSize, @NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
@NonNull LinkedHashSet<String> updateFields,
@NonNull List<T> records, @NonNull BatchConsumer<? super T> consumer) {
EntityManager entityManager = SpringUtil.getBean(EntityManager.class);
return batchExecuteUpdate(batchSize, entityManager, tableFields, updateFields, records, consumer);
}
/**
* Executes batch insert or update operations on the database using native SQL with a default batch size.
*
* @param entityManager The entity manager
* @param tableFields key-value pair representing the table name and its fields
* @param updateFields set of fields to be updated if a record with matching primary key exists
* @param records the list of records to be inserted or updated
* @param consumer functional interface for accepting batch consumer operations
* @param <T> the type of the records to be inserted or updated
* @return the total number of rows affected by the batch operation
*/
public static <T> int batchInsertOrUpdate(EntityManager entityManager,
@NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
@NonNull LinkedHashSet<String> updateFields,
@NonNull List<T> records, @NonNull BatchConsumer<? super T> consumer) {
return batchExecuteUpdate(DEFAULT_BATCH_SIZE, entityManager, tableFields, updateFields, records, consumer);
}
/**
* Executes batch updates on the database using native SQL with a parameterized batch size.
*
* @param batchSize the size of each batch for inserting records
* @param entityManager the entity manager for creating and executing queries
* @param tableFields key-value pair representing the table name and its fields
* @param updateFields set of fields to be updated if a record with matching primary key exists (optional)
* @param records the list of records to be inserted
* @param consumer functional interface for accepting batch consumer operations
* @param <T> the type of the records to be inserted
* @return the total number of rows affected by the batch operation
*/
private static <T> int batchExecuteUpdate(int batchSize, EntityManager entityManager,
@NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
@Nullable LinkedHashSet<String> updateFields,
@NonNull List<T> records, @NonNull BatchConsumer<? super T> consumer) {
if (records.isEmpty()) {
log.debug("No records to process. The records list is empty.");
return 0;
}
Assert.notNull(entityManager, "The entity manager must not be null.");
Assert.isTrue(batchSize > 0 && batchSize < 500, "The batch size must be between 1 and 500.");
AtomicInteger totalRows = new AtomicInteger(0);
// Split the records into batches based on the specified batch size
List<List<T>> recordBatches = CollUtil.split(records, batchSize);
for (List<T> batchRecords : recordBatches) {
AtomicInteger position = new AtomicInteger(1);
// Generate the appropriate SQL statement for the batch
String preparedStatementSql = CollUtil.isEmpty(updateFields) ?
generateBatchInsertSql(tableFields, batchRecords.size()) :
generateBatchInsertOrUpdateSql(tableFields, updateFields, batchRecords.size());
// Create a Query instance for executing native SQL statements
Query nativeQuery = entityManager.createNativeQuery(preparedStatementSql);
// Create a parameter builder instance using QueryParameterBuilder
QueryParameterBuilder parameterBuilder = QueryParameterBuilder.create(nativeQuery, position);
for (T record : batchRecords) {
// Set parameters for the prepared statement
consumer.accept(parameterBuilder, record);
}
// Execute the SQL statement and accumulate the affected rows
totalRows.addAndGet(nativeQuery.executeUpdate());
}
// Return the total number of affected rows
return totalRows.get();
}
/**
* Generate batch insert SQL statement.
*
* <p>
* This method generates an SQL statement for batch insertion into a specified table with the provided fields.
* Example SQL statement:
* <pre>
* {@code INSERT INTO TABLE_NAME ( field_1, field_2 ) VALUES ( value_1, value_2 ), (value_3, value_4); }
* </pre>
* </p>
*
* @param tableFields The key-value pair representing the table name and its associated field set
* @param batchSize The batch size for insertion
* @return The batch insert SQL statement
*/
private static String generateBatchInsertSql(@NonNull KeyValue<String, LinkedHashSet<String>> tableFields, int batchSize) {
String preparedStatementSql = generateInsertStatement(tableFields.getKey(), tableFields.getValue(), batchSize);
if (log.isDebugEnabled()) {
log.debug("[Batch Insert] Prepared {} records SQL: {}", batchSize, preparedStatementSql);
}
return preparedStatementSql;
}
/**
* Generates SQL statement for batch insert with on duplicate key update.
*
* @param tableFields Key-value pair representing table name and its corresponding fields.
* @param updateFields Fields to be updated in case of duplicate key.
* @param batchSize Number of records to be inserted in a single batch.
* @return SQL statement for batch insert with on duplicate key update.
* @throws IllegalArgumentException if updateFields collection is empty.
*/
private static String generateBatchInsertOrUpdateSql(@NonNull KeyValue<String, LinkedHashSet<String>> tableFields,
LinkedHashSet<String> updateFields, int batchSize) {
Assert.notEmpty(updateFields, "Update field collection cannot be empty.");
// Generate the insert statement
String insertStatement = generateInsertStatement(tableFields.getKey(), tableFields.getValue(), batchSize);
// Initialize StringBuilder with initial capacity
StringBuilder builder = new StringBuilder(insertStatement.length() + 100);
// Append insert statement
builder.append(insertStatement).append(" ON DUPLICATE KEY UPDATE ");
// Append update clause
String updateClause = updateFields.stream()
.map(updateField -> updateField + " = VALUES(" + updateField + ")")
.collect(Collectors.joining(", "));
builder.append(updateClause);
String preparedStatementSql = builder.toString();
if (log.isDebugEnabled()) {
log.debug("[Batch Insert On Duplicate Key Update] Prepared {} records SQL: {}", batchSize, preparedStatementSql);
}
return preparedStatementSql;
}
@NotNull
private static String generateInsertStatement(@NonNull String tableName, @NonNull LinkedHashSet<String> fields, int batchSize) {
Assert.hasText(tableName, "Table name cannot be empty.");
Assert.notNull(fields, "Field collection cannot be empty.");
// Set a reasonable initial capacity
StringBuilder builder = new StringBuilder(fields.size() * 100);
// Concatenate field names
String fieldNames = String.join(", ", fields);
String intoTemplate = String.format("INSERT INTO %s (%s) VALUES ", tableName, fieldNames);
// Generate placeholders
String placeholders = "(" + String.join(", ", Collections.nCopies(fields.size(), "?")) + ")";
// Construct the insert statement
builder.append(intoTemplate);
for (int i = 0; i < batchSize; i++) {
if (i > 0) {
builder.append(", ");
}
builder.append(placeholders);
}
return builder.toString();
}
}總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
Java全面細(xì)致講解Cookie與Session及kaptcha驗(yàn)證碼的使用
web開發(fā)階段我們主要是瀏覽器和服務(wù)器之間來進(jìn)行交互。瀏覽器和服務(wù)器之間的交互就像人和人之間進(jìn)行交流一樣，但是對于機(jī)器來說，在一次請求之間只是會(huì)攜帶著本次請求的數(shù)據(jù)的，但是可能多次請求之間是會(huì)有聯(lián)系的，所以提供了會(huì)話機(jī)制2022-06-06
這篇文章主要介紹了springboot配置文件中屬性變量引用方式@@解讀，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
Java開發(fā)中常用的 Websocket 技術(shù)參考
WebSocket 使得客戶端和服務(wù)器之間的數(shù)據(jù)交換變得更加簡單，允許服務(wù)端主動(dòng)向客戶端推送數(shù)據(jù)，當(dāng)然也支持客戶端發(fā)送數(shù)據(jù)到服務(wù)端。2020-09-09
jdk中動(dòng)態(tài)代理異常處理分析：UndeclaredThrowableException
最近在工作中遇到了報(bào)UndeclaredThrowableException的錯(cuò)誤，通過查找相關(guān)的資料，終于解決了，所以這篇文章主要給大家介紹了關(guān)于jdk中動(dòng)態(tài)代理異常處理分析：UndeclaredThrowableException的相關(guān)資料，需要的朋友可以參考下2018-04-04
Springdata-JPA是對JPA使用的封裝,Querydsl-JPA也是基于各種ORM之上的一個(gè)通用查詢框架,使用它的API類庫可以寫出Java代碼的sql,下面就來介紹一下JPA之QueryDSL-JPA使用指南2023-11-11
這篇文章主要介紹了SpringBoot底層原理,包括配置優(yōu)先級(jí)的配置方式給大家講解的非常詳細(xì)，需要的朋友可以參考下2024-03-03
大部分的游戲數(shù)據(jù)庫都是使用mysql ,所以今天今天大概聊一下對數(shù)據(jù)庫的優(yōu)化原則問題，都是基于InnoDB 引擎，希望你能在遇到同樣的問題時(shí)能解決問題2021-07-07
總結(jié)：通過File->Settings->Editor->FileAndCodeTemplates,創(chuàng)建一個(gè)自定義的XML文件模板,并命名為XMLFile.xml,后綴名為xml,模板內(nèi)容可自定義,并啟用實(shí)時(shí)模板功能,然后在文件夾中右鍵New,即可找到并創(chuàng)建XML文件2025-11-11
這篇文章主要介紹了Eclipse常用快捷鍵大全,較為詳細(xì)的針對eclipse中各種應(yīng)用中使用快捷鍵進(jìn)行了分類總結(jié),具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-11-11
今天小編就為大家分享一篇在java中http請求帶cookie的例子，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2019-08-08