001package com.nimbusds.infinispan.persistence.sql;
002
003
004import org.jooq.*;
005
006import java.util.Collection;
007import java.util.HashMap;
008import java.util.List;
009import java.util.Map;
010import java.util.function.BiConsumer;
011import java.util.stream.Collectors;
012
013import static org.jooq.impl.DSL.*;
014
015
016/**
017 * SQL query composition utilities.
018 */
019public class SQLQueryUtils {
020
021
022        /**
023         * Creates an "upsert" query for the specified SQL dialect.
024         *
025         * @param table      The table.
026         * @param sqlRecord  The SQL record to update / insert.
027         * @param sqlDialect The SQL dialect.
028         * @param sql        The DSL context.
029         *
030         * @return The query, ready for execution.
031         */
032        public static Query createUpsert(final Table<?> table,
033                                         final SQLRecord sqlRecord,
034                                         final SQLDialect sqlDialect,
035                                         final DSLContext sql) {
036
037                if (SQLDialect.POSTGRES_9_5.equals(sqlDialect)) {
038
039                        // Use INSERT with ON CONFLICT
040                        // https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/insert-statement/insert-on-duplicate-key/
041                        return sql.insertInto(table)
042                                .columns(sqlRecord.getFields().keySet())
043                                .values(sqlRecord.getFields().values())
044                                .onConflict(sqlRecord.getKeyColumns())
045                                .doUpdate()
046                                .set(removeKeyFields(sqlRecord.getFields(), sqlRecord.getKeyColumns()));
047
048                } else if (SQLDialect.ORACLE.family().equals(sqlDialect.family())) {
049
050                        // Compose with help of assistant for jOOQ 3.20
051
052                        // Define alias for the DUAL table (subquery source table)
053                        final String dualAlias = "src";
054
055                        // Build the subquery using DUAL table and alias the fields
056                        Table<?> dualSubquery = sql.select(
057                                sqlRecord.getFields().entrySet().stream()
058                                        .map(entry -> val(entry.getValue()).as(entry.getKey().getName())) // Ensure column name is correct
059                                        .collect(Collectors.toList())
060                                )
061                                .from(table("dual"))
062                                .asTable(dualAlias);  // Alias the subquery as "src"
063
064                        // Build the ON condition
065                        Condition onCondition = sqlRecord.getKeyColumns().stream()
066                                .map(f -> {
067                                        @SuppressWarnings("unchecked")
068                                        var targetField = (Field<Object>) f;
069                                        // Use proper alias and field name for the subquery
070                                        return targetField.eq(field(name(dualAlias, f.getUnqualifiedName().last()), f.getDataType()));
071                                })
072                                .reduce(Condition::and)
073                                .orElseThrow(() -> new IllegalArgumentException("No key fields provided"));
074
075                        // Build the UPDATE set
076                        Map<Field<?>, Field<?>> updateSet = sqlRecord.getFields().entrySet().stream()
077                                .filter(e -> ! sqlRecord.getKeyColumns().contains(e.getKey()))
078                                .collect(Collectors.toMap(
079                                        Map.Entry::getKey,
080                                        e -> field(name(dualAlias, e.getKey().getUnqualifiedName().last()), e.getKey().getDataType())
081                                ));
082
083                        // Build the VALUES part for the INSERT clause
084                        Collection<Field<?>> insertValues = sqlRecord.getFields().keySet().stream()
085                                .map(f -> field(name(dualAlias, f.getUnqualifiedName().last()), f.getDataType()))
086                                .collect(Collectors.toList());
087
088                        // Construct the final MERGE statement
089                        return sql.mergeInto(table)
090                                .using(dualSubquery)
091                                .on(onCondition)
092                                .whenMatchedThenUpdate()
093                                .set(updateSet)
094                                .whenNotMatchedThenInsert(sqlRecord.getFields().keySet())
095                                .values(insertValues);
096                } else {
097                        // Use H2 style MERGE, JOOQ will modify it for the particular database
098                        // http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/merge-statement/
099                        return sql.mergeInto(table, sqlRecord.getFields().keySet())
100                                .key(sqlRecord.getKeyColumns())
101                                .values(sqlRecord.getFields().values());
102                }
103        }
104
105
106        private static Map<Field<?>,?> removeKeyFields(final Map<Field<?>,?> fields, final List<Field<?>> keyCols) {
107
108                Map<Field<?>, Object> filtered = new HashMap<>();
109
110                fields.forEach((BiConsumer<Field<?>, Object>) (field, o) -> {
111                        if (! keyCols.contains(field)) {
112                                filtered.put(field, o); // Non-key field
113                        }
114                });
115
116                return filtered;
117        }
118
119
120        private SQLQueryUtils() {}
121}