DB2 是否有“插入或更新”语句？

Question

从我的代码（Java）中，我想确保在我的代码执行后数据库（DB2）中存在一行。

我的代码现在执行 select 如果没有返回结果，它执行 insert 。我真的不喜欢这段代码，因为它让我在多线程环境中运行时遇到并发问题。

我想做的是将这个逻辑放在 DB2 中，而不是放在我的 Java 代码中。 DB2 是否有 insert-or-update 语句？或者我可以使用的任何类似的东西？

例如：

 insertupdate into mytable values ('myid')

另一种方法可能是始终执行插入并捕获“SQL-code -803 主键已经存在”，但如果可能的话，我想避免这种情况。

原文由 Mikael Eriksson 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以使用 Merge 语句，如下所示：

 MERGE INTO USERS t
    USING (VALUES (123, 'John', '[e-mail]'))
    AS v(ID, NAME, EMAIL)
    ON (t.ID = v.ID)
WHEN MATCHED THEN
    UPDATE SET NAME = v.NAME,EMAIL = v.EMAIL
WHEN NOT MATCHED THEN
    INSERT (ID,NAME,EMAIL) VALUES (v.ID,v.NAME,v.EMAIL)

假设您使用的是本 答案 中提到的 DB2 Linux/Unix/Windows (LUW)。

提到的原始问题 Java ，为了方便起见，我编写了一些代码来生成带有参数标记的 SQL 字符串 PreparedStatement 。 （这需要 LUW 9.7 或以上链接 答案 中提到的更新版本。）如果您正在制作原型，经常更改数据库表等，您可能会发现它很有用……因为您将不得不重写更少的代码。

此示例代码：

 final String TABLE = "USERS";
final int NUM_OF_KEYS = 1; //first n columns are keys(PKs)
final String COLUMNS = Arrays.asList(
                    "ID",      //1 only this first 1 column will be PK
                    "NAME",    //2
                    "EMAIL");  //3
String mergreSqlString = getMergeSql(TABLE, COLUMNS, NUM_OF_KEYS);

将生成以下 SQL 语句：

 MERGE INTO USERS t
    USING (VALUES (?,?,?))
    AS v(ID, NAME, EMAIL)
    ON (t.ID = v.ID)
WHEN MATCHED THEN
    UPDATE SET NAME = v.NAME,EMAIL = v.EMAIL
WHEN NOT MATCHED THEN
    INSERT (ID,NAME,EMAIL) VALUES (v.ID,v.NAME,v.EMAIL)

然后在创建 PreparedStatement 时将此 SQL 用作输入参数。

下面是代码，它可能会写得更干净，但我相信它的文档足够好，可以按原样理解。

     /**
     * Creates a String representation of DB2 SQL Merge Statement.<br>
     * The returned SQL should be used with {@link java.sql.PreparedStatement}.<br>
     * This Merge Statement will perform <b>update</b> if it matches already existing record,
     * else <b>insert</b> will be performed.<br>
     * The matching will be done on first n columns.(Where n is numberOfKeys)<br>
     * This means that the DB Table Identifiers(PKs) have to be at the start of the provided columns List.<br>
     * Example of matching:<br>
     * columns = {"id", "key", "name"}; numberOfKey = 2 (first 2 columns are used in matching condition)<br>
     * Matching condition will look like: <br>
     * preparedStatementSetValue.id = table.id <b>AND</b> preparedStatementSetValue.key = table.key<br>
     * <b>Please note the order of List of columns is important because:</b>
     * <ul>
     *     <li>First numberOfKeys columns are used for matching.</li>
     *     <li>When setting values of PreparedStatement, they have to be filled in same order as they are supplied to this method.</li>
     * </ul>
     * @param table name of table the returned Merge statement will be created for
     * @param columns ordered list of columns to be merged, starting with identifiers(Primary Keys), must be >=1
     * @param numberOfKeys number of first n columns that will be used for matching
     * @return a new String representing DB2 SQL Merge Statement, or null if some problem occurs
     * @see <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-merge">IBM DB2 Merge Statement</a>
     */
    public static String getMergeSql(String table, List<String> columns, int numberOfKeys) {
        if (numberOfKeys < 1 || //Need at least 1 key to match on
                Objects.isNull(columns) ||
                columns.isEmpty() ||
                numberOfKeys > columns.size() || //Cannot match on more columns than were provided
                Objects.isNull(table) ||
                table.isEmpty()) {
            return null; //Input validation failed.
        }

        return "MERGE INTO " + table +
                " t " + // table reference (DO NOT CHANGE - ref. is used in getMatchingOrUpdateString)
                "USING (VALUES " +
                getPsValues(columns.size()) + // question marks for prepared statement parameters
                ") AS v" + // table reference (DO NOT CHANGE - ref. is used in getMatchingOrUpdateString)
                getColumns(columns, "") + //all columns separated by ','
                " ON (" +
                getMatchingOrUpdateString(columns, numberOfKeys, true) + //get matching String
                ") WHEN MATCHED THEN UPDATE SET " +
                //Update Statement
                getMatchingOrUpdateString(columns, numberOfKeys, false) +//get update String
                " WHEN NOT MATCHED THEN INSERT " +
                //Insert Statement
                getColumns(columns, "") + //all columns separated by ,
                " VALUES " +
                getColumns(columns, "v."); //all columns with 'v.' prefix, separated by ','
    }

    /**
     * Builds a new String that can have one of two formats, example :<br>
     * if isMatching == true :<br>
     * mt.column[0] = v.column[0], mt.column[1] = v.column[1],..., mt.column[n-1] = v.column[n-1] <br>
     * if isMatching == false :<br>
     * column[n] = v.column[n], column[n+1] = v.column[n+1],..., column[columns.size()-1] = v.column[columns.size()-1]
     * @param columns list of columns from which this string is build
     * @param n denotes how many columns are keys
     * @param isMatching determines format of the returned string
     * @return a new String constructed from input arguments
     */
    private static String getMatchingOrUpdateString(List<String> columns, int n, boolean isMatching) {
        StringBuilder result = new StringBuilder();

        //matching string starts at 0 and ends at n-1 -> i.e., appends all key columns (first n columns)
        //update string starts at n and appends the rest of columns -> i.e., appends all columns except of key columns
        int startIndex = isMatching ? 0 : n;
        int endIndex = isMatching ? n : columns.size();
        for (int i = startIndex ; i < endIndex; i++) {//loop over desired columns and create the string
            if (isMatching) result.append("t.");
            result.append(columns.get(i));
            result.append(" = ");
            result.append("v.");
            result.append(columns.get(i));
            result.append(",");
        }
        replaceLastCommaWithString(result, "");// delete last comma

        return result.toString();
    }

    /**
     * Builds String like (?,?,...,?) the number of ? is determined by the parameter n
     * @param n determines the number of '?'
     * @return a new String like '(?,?,...,?)'
     */
    private static String getPsValues(int n) {
        StringBuilder result = new StringBuilder("(");
        for (int i = 0; i < n; i++) {
            result.append("?,");
        }
        replaceLastCommaWithString(result, ")"); // replace last string with closing bracket
        return result.toString();
    }

    /**
     * Builds String like ([columnPrefix]columns[0], [columnPrefix]columns[1], ... [columnPrefix]columns[n])
     * The columnPrefix is either empty, or a table name from which selection is made e.g., "v."
     * @param columns list of column names
     * @param columnPrefix prefix for each column name
     * @return a new String constructed from columns argument
     */
    private static String getColumns(List<String> columns, String columnPrefix) {
        String delimiter = "," + columnPrefix;
        return  "(" + columnPrefix +
                String.join(delimiter, columns) +
                ")";
    }

    private static void replaceLastCommaWithString(StringBuilder sb, String replacement) {
        int idx = sb.lastIndexOf(",");
        if (idx != -1) { // avoid IndexOutOfBounds exception (if "," wasn't found)
            sb.replace(idx, ++idx, replacement);
        }
    }

希望你会发现它和我一样有用。

原文由 Vlado Lovis 发布，翻译遵循 CC BY-SA 4.0 许可协议