Java Hibernate中批量Insert Ignore的优化及数据校验方案咨询

Great question! When you’ve got 100+ var strings but only 1-2 are missing from your table, looping through each with individual INSERT IGNORE calls is total overkill. Here are two far more efficient approaches to solve this:

This is the most efficient option for your scenario, since you only need to insert a tiny subset. The idea is to first query which of your var values don’t exist in the table, then insert just those.

Step 1: Query for missing vars #

The exact SQL syntax depends on your database, but here’s how to do it with Hibernate for common databases:

For PostgreSQL (using UNNEST):

List<String> allVars = /* Your list of 100+ var strings */;

String findMissingSql = """
    SELECT v 
    FROM UNNEST(:varList) AS v 
    WHERE v NOT IN (SELECT VAR FROM TABLA)
""";

List<String> missingVars = getSession()
    .createSQLQuery(findMissingSql)
    .setParameterList("varList", allVars)
    .list();

For MySQL (using VALUES clause, MySQL 8.0+):

List<String> allVars = /* Your list of 100+ var strings */;

// Build a VALUES clause with placeholders
StringBuilder valuesClause = new StringBuilder("VALUES ");
for (int i = 0; i < allVars.size(); i++) {
    valuesClause.append("(?),");
}
valuesClause.deleteCharAt(valuesClause.length() - 1);

String findMissingSql = String.format("""
    SELECT v 
    FROM (%s) AS temp(v) 
    WHERE v NOT IN (SELECT VAR FROM TABLA)
""", valuesClause.toString());

Query query = getSession().createSQLQuery(findMissingSql);
for (int i = 0; i < allVars.size(); i++) {
    query.setParameter(i, allVars.get(i));
}

List<String> missingVars = query.list();

Step 2: Insert the missing records #

Now that you have only 1-2 values to insert, you can either insert them individually (no big deal here) or use a batch insert:

if (!missingVars.isEmpty()) {
    StringBuilder insertSql = new StringBuilder("INSERT INTO TABLA (ID, VAR) VALUES ");
    List<Object[]> params = new ArrayList<>();
    
    for (String var : missingVars) {
        insertSql.append("(?, ?),");
        params.add(new Object[]{UUID.randomUUID(), var});
    }
    insertSql.deleteCharAt(insertSql.length() - 1);
    
    Query insertQuery = getSession().createSQLQuery(insertSql.toString());
    int paramIndex = 0;
    for (Object[] paramPair : params) {
        insertQuery.setParameter(paramIndex++, paramPair[0]);
        insertQuery.setParameter(paramIndex++, paramPair[1]);
    }
    
    insertQuery.executeUpdate();
}

If you don’t want to add an extra query step, you can send all 100+ records in one batch INSERT IGNORE statement. This is way more efficient than 100+ individual calls, since the database only processes one request (even if most records are ignored).

List<String> allVars = /* Your list of 100+ var strings */;

StringBuilder batchInsertSql = new StringBuilder("INSERT IGNORE INTO TABLA (ID, VAR) VALUES ");
List<Object[]> params = new ArrayList<>();

for (String var : allVars) {
    batchInsertSql.append("(?, ?),");
    params.add(new Object[]{UUID.randomUUID(), var});
}
// Remove the trailing comma
batchInsertSql.deleteCharAt(batchInsertSql.length() - 1);

Query query = getSession().createSQLQuery(batchInsertSql.toString());
int paramIndex = 0;
for (Object[] paramPair : params) {
    query.setParameter(paramIndex++, paramPair[0]);
    query.setParameter(paramIndex++, paramPair[1]);
}

query.executeUpdate();

Key Notes for Both Approaches #

• Index Your VAR Column: Make sure the VAR column has a unique index (or is part of a unique constraint). This ensures that INSERT IGNORE works correctly and speeds up the "missing records" query.
• Database Syntax Differences: Adjust the SQL snippets to match your database (e.g., MySQL vs. PostgreSQL have different ways to handle list inputs).
• Hibernate Batch Settings: For very large lists, you might want to enable Hibernate’s batch processing (set hibernate.jdbc.batch_size in your config), but for 100+ records, it’s usually not necessary with the single batch statement.

内容的提问来源于stack exchange，提问作者user3552178