org.springframework.jdbc.support.KeyHolder

@Override
protected Object doExecute() throws JdbcDataException {
    final String sql = this.sqlBuilder.toString();
    if (returnKey) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcBuilder.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql.toString(), 1);
                int i = 1;
                for (ValueElement object : valueElements) {
                    ps.setObject(i, object.getValue());
                    i++;
                }
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey() != null ? keyHolder.getKey() : primaryKey;
    } else {
        return this.jdbcBuilder.update(sql, new ValueSetter(LOBHANDLER, this.valueElements));
    }
}

public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder) throws DataAccessException {
    int rows = jdbcTemplate.update(psc, generatedKeyHolder);
    getCacheInstance().clear();
    return rows;
}

@Override
public MailTemplatePo save(MailTemplatePo mailTemplatePo) {
    String sql = "insert into sys_mail_template(mail_code, mail_type, data_sources, run_sql, mail_temp, mail_markdown, " + "descritpion, to_emails, subject, template_name, single_result, valid_ind, creator_code, " + "updater_code, create_time, update_time) value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    log.debug("sql:{}", sql);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, mailTemplatePo.getMailCode());
            ps.setString(2, mailTemplatePo.getMailType());
            ps.setString(3, mailTemplatePo.getDataSources());
            ps.setString(4, mailTemplatePo.getRunSql());
            ps.setString(5, mailTemplatePo.getMailTemp());
            ps.setString(6, mailTemplatePo.getMailMarkdown());
            ps.setString(7, mailTemplatePo.getDescritpion());
            ps.setString(8, mailTemplatePo.getToEmails());
            ps.setString(9, mailTemplatePo.getSubject());
            ps.setString(10, mailTemplatePo.getTemplateName());
            ps.setBoolean(11, mailTemplatePo.getSingleResult());
            // ps.setBoolean(12, mailTemplatePo.getAttachment());
            ps.setBoolean(12, mailTemplatePo.getValidInd());
            ps.setString(13, mailTemplatePo.getCreatorCode());
            ps.setString(14, mailTemplatePo.getUpdaterCode());
            ps.setObject(15, mailTemplatePo.getCreateTime());
            ps.setObject(16, mailTemplatePo.getUpdateTime());
            return ps;
        }
    }, keyHolder);
    mailTemplatePo.setId(keyHolder.getKey().longValue());
    return mailTemplatePo;
}

public Integer insert(final String sql, final String pkey, final Object... args) {
    log.debug("执行SQL语句:{},参数:{}", sql, args);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(conn -> {
        PreparedStatement ps = conn.prepareStatement(sql, new String[] { pkey });
        int i = 1;
        for (Object o : args) {
            ps.setObject(i++, o);
        }
        return ps;
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

/**
 * Method to execute the update given arguments and
 * retrieve the generated keys using a KeyHolder.
 * @param params array of parameter objects
 * @param generatedKeyHolder the KeyHolder that will hold the generated keys
 * @return the number of rows affected by the update
 */
public int update(Object[] params, KeyHolder generatedKeyHolder) throws DataAccessException {
    if (!isReturnGeneratedKeys() && getGeneratedKeysColumnNames() == null) {
        throw new InvalidDataAccessApiUsageException("The update method taking a KeyHolder should only be used when generated keys have " + "been configured by calling either 'setReturnGeneratedKeys' or " + "'setGeneratedKeysColumnNames'.");
    }
    validateParameters(params);
    int rowsAffected = getJdbcTemplate().update(newPreparedStatementCreator(params), generatedKeyHolder);
    checkRowsAffected(rowsAffected);
    return rowsAffected;
}

/**
 * Delegate method to execute the insert, generating a single key.
 */
private Number executeInsertAndReturnKeyInternal(final List<?> values) {
    KeyHolder kh = executeInsertAndReturnKeyHolderInternal(values);
    if (kh.getKey() != null) {
        return kh.getKey();
    } else {
        throw new DataIntegrityViolationException("Unable to retrieve the generated key for the insert: " + getInsertString());
    }
}

@Override
public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, @Nullable String[] keyColumnNames) throws DataAccessException {
    PreparedStatementCreator psc = getPreparedStatementCreator(sql, paramSource, pscf -> {
        if (keyColumnNames != null) {
            pscf.setGeneratedKeysColumnNames(keyColumnNames);
        } else {
            pscf.setReturnGeneratedKeys(true);
        }
    });
    return getJdbcOperations().update(psc, generatedKeyHolder);
}

@Override
public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder) throws DataAccessException {
    return update(sql, paramSource, generatedKeyHolder, null);
}

private Integer saveOrderRecord(JdbcTemplate jdbcTemplate, final int userId, final long money) {
    final String INSERT_SQL = "INSERT INTO `order` (`order_id`, `user_id`, `money`, `create_time`) VALUES (NULL, ?, ?, ?);";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "id" });
            ps.setInt(1, userId);
            ps.setLong(2, money);
            ps.setDate(3, new Date(System.currentTimeMillis()));
            return ps;
        }
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

/**
 * Method to execute the update given arguments and
 * retrieve the generated keys using a KeyHolder.
 * @param params array of parameter objects
 * @param generatedKeyHolder KeyHolder that will hold the generated keys
 * @return the number of rows affected by the update
 */
public int update(Object[] params, KeyHolder generatedKeyHolder) throws DataAccessException {
    if (!isReturnGeneratedKeys() && getGeneratedKeysColumnNames() == null) {
        throw new InvalidDataAccessApiUsageException("The update method taking a KeyHolder should only be used when generated keys have " + "been configured by calling either 'setReturnGeneratedKeys' or " + "'setGeneratedKeysColumnNames'.");
    }
    validateParameters(params);
    int rowsAffected = getJdbcTemplate().update(newPreparedStatementCreator(params), generatedKeyHolder);
    checkRowsAffected(rowsAffected);
    return rowsAffected;
}

/**
 * Delegate method to execute the insert, generating a single key.
 */
private Number executeInsertAndReturnKeyInternal(final List<?> values) {
    KeyHolder kh = executeInsertAndReturnKeyHolderInternal(values);
    if (kh != null && kh.getKey() != null) {
        return kh.getKey();
    } else {
        throw new DataIntegrityViolationException("Unable to retrieve the generated key for the insert: " + getInsertString());
    }
}

public int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException {
    return jdbcTemplate.update(psc, generatedKeyHolder);
}

/**
 * 保存数据库自动生成的主键值（或其他值）
 * @author ChenJiazhi
 */
public clreplaced KeyHolderResult {

    private Integer count;

    private KeyHolder keyHolder;

    public KeyHolderResult(Integer count, KeyHolder keyHolder) {
        this.count = count;
        this.keyHolder = keyHolder;
    }

    public Integer getCount() {
        return count;
    }

    public void setCount(Integer count) {
        this.count = count;
    }

    public KeyHolder getKeyHolder() {
        return keyHolder;
    }

    public void setKeyHolder(KeyHolder keyHolder) {
        this.keyHolder = keyHolder;
    }
}

public void setKeyHolder(KeyHolder keyHolder) {
    this.keyHolder = keyHolder;
}

private Long createDefaultAuthzZone(final JdbcTemplate jdbcTemplate) {
    final String insertZoneSql = "INSERT INTO authorization_zone (name, description, subdomain) " + "VALUES (?,?,?)";
    KeyHolder holder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(insertZoneSql, new String[] { "id" });
            ps.setString(1, "apm-migrated");
            ps.setString(2, "APM Migrated Zone from mvp1");
            ps.setString(3, "apm-migrated");
            return ps;
        }
    }, holder);
    return holder.getKey().longValue();
}

/**
 * Logs the statement and parameter in debug-level, executes update, retrieves generated keys and logs error.
 *
 * @param logger a logger to write logs to.
 * @param statement an sql-query to execute.
 * @param keys a holder for retrieved generated keys.
 * @param keyColumns columns to retrieve as a generated keys.
 * @param parameter bound parameters for an sql-query.
 * @return number of touched lines in db.
 */
protected int update(Logger logger, String statement, KeyHolder keys, String[] keyColumns, Object... parameter) {
    try {
        validateStatement(statement);
        logSqlStatement(logger, statement, parameter);
        int touchedLines = getJdbcTemplate().update(connection -> {
            PreparedStatement ps = connection.prepareStatement(statement, keyColumns);
            for (int i = 0; i < parameter.length; i++) {
                ps.setObject(i + 1, parameter[i]);
            }
            return ps;
        }, keys);
        if (logger.isDebugEnabled()) {
            logger.debug("lines changed by update: " + touchedLines);
        }
        return touchedLines;
    } catch (DataAccessException e) {
        logSqlError(e, logger, statement, parameter);
        throw e;
    } catch (RuntimeException e) {
        logSqlError(e, logger, statement, parameter);
        throw e;
    }
}

// Spring Update
/**
 * 更新或插入数据<br>
 * 同 {@linkplain NamedParameterJdbcTemplate#update(String, SqlParameterSource, KeyHolder)}<br>
 *
 * @param sql         要执行的更新SQL
 * @param paramSource 更新所用到的参数：{@linkplain MapSqlParameterSource}，{@linkplain BeanPropertySqlParameterSource}
 * @return 自动生成的键(可能由JDBC insert语句返回)或更新的主键id值。
 */
@Transactional
public KeyHolder update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder) {
    getNamedParameterJdbcTemplate().update(sql, paramSource, generatedKeyHolder);
    return generatedKeyHolder;
}

/**
 * [简要描述]:NamedParameterJdbcTemplate方式插入一条数据，返回主键<br/>
 * [详细描述]:<br/>
 *
 * @param sql : 执行的SQL
 * @param params : 参数
 * @return java.lang.Long
 * llxiao  2019/1/29 - 17:44
 */
private Long insertReKey(String sql, SqlParameterSource params) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    namedParameterJdbcTemplate.update(sql, params, keyHolder, new String[] { "id" });
    return keyHolder.getKey().longValue();
}

/**
 * [简要描述]:jdbcTemplate方式插入一条数据，返回主键<br/>
 * [详细描述]:<br/>
 *
 * @param preparedStatementCreator :
 * @return java.lang.Long
 * llxiao  2019/1/29 - 17:11
 */
private Long insertReId(PreparedStatementCreator preparedStatementCreator) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(preparedStatementCreator, keyHolder);
    return keyHolder.getKey().longValue();
}

@Override
public boolean create(boolean isAutoIncreaseId, String sql, List<Object> valueList) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    if (isAutoIncreaseId) {
        this.jdbcTemplate.update(connection -> {
            PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            int i = 1;
            for (Object value : valueList) {
                pstmt.setObject(i++, value);
            }
            return pstmt;
        }, keyHolder);
    } else {
        this.jdbcTemplate.update(connection -> {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            int i = 1;
            for (Object value : valueList) {
                pstmt.setObject(i++, value);
            }
            return pstmt;
        });
    }
    if (isAutoIncreaseId) {
        return keyHolder.getKey().longValue() > 0;
    } else {
        return true;
    }
}

/**
 * Delegate method to execute the insert, generating any number of keys.
 */
private KeyHolder executeInsertAndReturnKeyHolderInternal(final List<?> values) {
    if (logger.isDebugEnabled()) {
        logger.debug("The following parameters are used for call " + getInsertString() + " with: " + values);
    }
    final KeyHolder keyHolder = new GeneratedKeyHolder();
    if (this.tableMetaDataContext.isGetGeneratedKeysSupported()) {
        getJdbcTemplate().update(con -> {
            PreparedStatement ps = prepareStatementForGeneratedKeys(con);
            setParameterValues(ps, values, getInsertTypes());
            return ps;
        }, keyHolder);
    } else {
        if (!this.tableMetaDataContext.isGetGeneratedKeysSimulated()) {
            throw new InvalidDataAccessResourceUsageException("The getGeneratedKeys feature is not supported by this database");
        }
        if (getGeneratedKeyNames().length < 1) {
            throw new InvalidDataAccessApiUsageException("Generated Key Name(s) not specified. " + "Using the generated keys features requires specifying the name(s) of the generated column(s)");
        }
        if (getGeneratedKeyNames().length > 1) {
            throw new InvalidDataAccessApiUsageException("Current database only supports retrieving the key for a single column. There are " + getGeneratedKeyNames().length + " columns specified: " + Arrays.asList(getGeneratedKeyNames()));
        }
        replacedert.state(getTableName() != null, "No table name set");
        final String keyQuery = this.tableMetaDataContext.getSimpleQueryForGetGeneratedKey(getTableName(), getGeneratedKeyNames()[0]);
        replacedert.state(keyQuery != null, "Query for simulating get generated keys must not be null");
        // This is a hack to be able to get the generated key from a database that doesn't support
        // get generated keys feature. HSQL is one, PostgreSQL is another. Postgres uses a RETURNING
        // clause while HSQL uses a second query that has to be executed with the same connection.
        if (keyQuery.toUpperCase().startsWith("RETURNING")) {
            Long key = getJdbcTemplate().queryForObject(getInsertString() + " " + keyQuery, values.toArray(), Long.clreplaced);
            Map<String, Object> keys = new HashMap<>(2);
            keys.put(getGeneratedKeyNames()[0], key);
            keyHolder.getKeyList().add(keys);
        } else {
            getJdbcTemplate().execute((ConnectionCallback<Object>) con -> {
                PreparedStatement ps = null;
                try {
                    ps = con.prepareStatement(getInsertString());
                    setParameterValues(ps, values, getInsertTypes());
                    ps.executeUpdate();
                } finally {
                    JdbcUtils.closeStatement(ps);
                }
                Statement keyStmt = null;
                ResultSet rs = null;
                Map<String, Object> keys = new HashMap<>(2);
                try {
                    keyStmt = con.createStatement();
                    rs = keyStmt.executeQuery(keyQuery);
                    if (rs.next()) {
                        long key = rs.getLong(1);
                        keys.put(getGeneratedKeyNames()[0], key);
                        keyHolder.getKeyList().add(keys);
                    }
                } finally {
                    JdbcUtils.closeResultSet(rs);
                    JdbcUtils.closeStatement(keyStmt);
                }
                return null;
            });
        }
    }
    return keyHolder;
}

public int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException {
    // not supported right now
    return delegate.update(psc, generatedKeyHolder);
}

/**
 * 插入并返回主键
 */
@Comment("执行insert操作，返回插入主键")
public long insert(@Comment("`SQL`语句") String sql) {
    BoundSql boundSql = new BoundSql(sql);
    sqlInterceptors.forEach(sqlInterceptor -> sqlInterceptor.preHandle(boundSql));
    KeyHolder keyHolder = new GeneratedKeyHolder();
    dataSourceNode.getJdbcTemplate().update(con -> {
        PreparedStatement ps = con.prepareStatement(boundSql.getSql(), Statement.RETURN_GENERATED_KEYS);
        new ArgumentPreparedStatementSetter(boundSql.getParameters()).setValues(ps);
        return ps;
    }, keyHolder);
    if (this.cacheName != null) {
        this.sqlCache.delete(this.cacheName);
    }
    Number key = keyHolder.getKey();
    if (key == null) {
        return -1;
    }
    return key.longValue();
}

@Override
public void save(Foo foo) {
    if (foo.getId() == null) {
        KeyHolder holder = new GeneratedKeyHolder();
        template.update("INSERT into foos (value) values (:value)", new BeanPropertySqlParameterSource(foo), holder);
        foo.setId(holder.getKey().longValue());
    } else {
        template.update("UPDATE foos set value=:value where id=:id", new BeanPropertySqlParameterSource(foo));
    }
}

/**
 * Creates the employee in the database and returns the id of the created employee.
 * @param employee the employee to be created
 * @return the id of the created employee.
 */
public long saveAndReturnId(Employee employee) {
    String sqlQuery = "insert into employees(first_name, last_name, yearly_income) " + "values (?, ?, ?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(connection -> {
        PreparedStatement stmt = connection.prepareStatement(sqlQuery, new String[] { "id" });
        stmt.setString(1, employee.getFirstName());
        stmt.setString(2, employee.getLastName());
        stmt.setLong(3, employee.getYearlyIncome());
        return stmt;
    }, keyHolder);
    return keyHolder.getKey().longValue();
}

/**
 * Delegate method to execute the insert, generating any number of keys.
 */
private KeyHolder executeInsertAndReturnKeyHolderInternal(final List<?> values) {
    if (logger.isDebugEnabled()) {
        logger.debug("The following parameters are used for call " + getInsertString() + " with: " + values);
    }
    final KeyHolder keyHolder = new GeneratedKeyHolder();
    if (this.tableMetaDataContext.isGetGeneratedKeysSupported()) {
        getJdbcTemplate().update(con -> {
            PreparedStatement ps = prepareStatementForGeneratedKeys(con);
            setParameterValues(ps, values, getInsertTypes());
            return ps;
        }, keyHolder);
    } else {
        if (!this.tableMetaDataContext.isGetGeneratedKeysSimulated()) {
            throw new InvalidDataAccessResourceUsageException("The getGeneratedKeys feature is not supported by this database");
        }
        if (getGeneratedKeyNames().length < 1) {
            throw new InvalidDataAccessApiUsageException("Generated Key Name(s) not specified. " + "Using the generated keys features requires specifying the name(s) of the generated column(s)");
        }
        if (getGeneratedKeyNames().length > 1) {
            throw new InvalidDataAccessApiUsageException("Current database only supports retrieving the key for a single column. There are " + getGeneratedKeyNames().length + " columns specified: " + Arrays.asList(getGeneratedKeyNames()));
        }
        replacedert.state(getTableName() != null, "No table name set");
        final String keyQuery = this.tableMetaDataContext.getSimpleQueryForGetGeneratedKey(getTableName(), getGeneratedKeyNames()[0]);
        replacedert.state(keyQuery != null, "Query for simulating get generated keys must not be null");
        // This is a hack to be able to get the generated key from a database that doesn't support
        // get generated keys feature. HSQL is one, PostgreSQL is another. Postgres uses a RETURNING
        // clause while HSQL uses a second query that has to be executed with the same connection.
        if (keyQuery.toUpperCase().startsWith("RETURNING")) {
            Long key = getJdbcTemplate().queryForObject(getInsertString() + " " + keyQuery, values.toArray(), Long.clreplaced);
            Map<String, Object> keys = new HashMap<>(2);
            keys.put(getGeneratedKeyNames()[0], key);
            keyHolder.getKeyList().add(keys);
        } else {
            getJdbcTemplate().execute((ConnectionCallback<Object>) con -> {
                PreparedStatement ps = null;
                try {
                    ps = con.prepareStatement(getInsertString());
                    setParameterValues(ps, values, getInsertTypes());
                    ps.executeUpdate();
                } finally {
                    JdbcUtils.closeStatement(ps);
                }
                Statement keyStmt = null;
                ResultSet rs = null;
                try {
                    keyStmt = con.createStatement();
                    rs = keyStmt.executeQuery(keyQuery);
                    if (rs.next()) {
                        long key = rs.getLong(1);
                        Map<String, Object> keys = new HashMap<>(2);
                        keys.put(getGeneratedKeyNames()[0], key);
                        keyHolder.getKeyList().add(keys);
                    }
                } finally {
                    JdbcUtils.closeResultSet(rs);
                    JdbcUtils.closeStatement(keyStmt);
                }
                return null;
            });
        }
    }
    return keyHolder;
}

public long insertUser(String username, String name, String rawTempPreplacedword, String sysRole) {
    String encryptedPreplacedword = PreplacedwordUtils.getMd5Hash(rawTempPreplacedword);
    String sql = "INSERT INTO p_user(username, name, temp_preplacedword, sys_role) " + "VALUES(:username, :name, :temp_preplacedword, :sys_role)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(User.USERNAME, username);
    params.addValue(User.NAME, name);
    params.addValue(User.TEMP_PreplacedWORD, encryptedPreplacedword);
    params.addValue(User.SYS_ROLE, sysRole);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { User.ID });
    return keyHolder.getKey().longValue();
}

public long insert(String shareKey, long reportId, String reportType, long userId, long createdAt, long expiredBy) {
    String sql = "INSERT INTO p_shared_report(share_key, report_id, report_type, user_id, created_at, expired_by) " + "VALUES(:share_key, :report_id, :report_type, :user_id, :created_at, :expired_by)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(SharedReport.SHARE_KEY, shareKey);
    params.addValue(SharedReport.REPORT_ID, reportId);
    params.addValue(SharedReport.REPORT_TYPE, reportType);
    params.addValue(SharedReport.USER_ID, userId);
    params.addValue(SharedReport.CREATED_AT, createdAt);
    params.addValue(SharedReport.EXPIRED_BY, expiredBy);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { SharedReport.ID });
    return keyHolder.getKey().longValue();
}

public long insert(String name, String style) {
    String sql = "INSERT INTO p_report(name, style) VALUES(:name, :style)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(Report.NAME, name);
    params.addValue(Report.STYLE, style);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { Report.ID });
    return keyHolder.getKey().longValue();
}

private Integer saveOrderRecord(final int userId, final long money) {
    final String INSERT_SQL = "INSERT INTO `order` (`order_id`, `user_id`, `money`, `create_time`) VALUES (NULL, ?, ?, ?);";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "id" });
            ps.setInt(1, userId);
            ps.setLong(2, money);
            ps.setDate(3, new Date(System.currentTimeMillis()));
            return ps;
        }
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

public void cancelOrder(int orderId) {
    /**
     * usually we will only update a status of order instead of delete
     * but i did not define a status in the demo schema,so just delete instead
     *
     * 通常不会直接删除之前创建记录，仅仅只是更新记录的状态
     * 但是在测试的表结构里没有定义status字段，因此在样例代码里直接删除
     */
    final String INSERT_SQL = "DELETE FROM `order` WHERE `order_id`= ?";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "id" });
            ps.setInt(1, orderId);
            return ps;
        }
    }, keyHolder);
}

public int[] batchUpdate(final String sql, final BatchPreparedStatementSetter pss, final KeyHolder generatedKeyHolder) throws DataAccessException {
    return execute(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            return conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        }
    }, new PreparedStatementCallback<int[]>() {

        @Override
        public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            if (logger.isDebugEnabled()) {
                logger.debug("Executing SQL batch update and returning generated keys [" + sql + "]");
            }
            try {
                int batchSize = pss.getBatchSize();
                int totalRowsAffected = 0;
                int[] rowsAffected = new int[batchSize];
                List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
                generatedKeys.clear();
                if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        ps.addBatch();
                    }
                    rowsAffected = ps.executeBatch();
                    for (int i = 0, len = rowsAffected.length; i < len; i++) {
                        totalRowsAffected += rowsAffected[i];
                    }
                    extractGeneratedKeys(ps, generatedKeys, (int) (rowsAffected.length * 1.5));
                } else {
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        rowsAffected[i] = ps.executeUpdate();
                        totalRowsAffected += rowsAffected[i];
                        extractGeneratedKeys(ps, generatedKeys, 1);
                    }
                }
                if (logger.isDebugEnabled()) {
                    logger.debug("SQL batch update affected " + totalRowsAffected + " rows and returned " + generatedKeys.size() + " keys");
                }
                return rowsAffected;
            } finally {
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    });
}

public Long addCity(String countryCode, City city) {
    SqlParameterSource paramSource = new MapSqlParameterSource(getMapForCity(countryCode, city));
    KeyHolder keyHolder = new GeneratedKeyHolder();
    namedParamJdbcTemplate.update("INSERT INTO city(" + " name, countrycode, " + " district, population) " + " VALUES (:name, :country_code, " + " :district, :population )", paramSource, keyHolder);
    return keyHolder.getKey().longValue();
}

public long getCurrentGithubId() {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    String sql = "SELECT stat_id FROM github_stats WHERE stat_date = current_date()";
    long statId = -1;
    try {
        statId = jdbcTemplate.queryForObject(sql, Long.clreplaced);
    } catch (EmptyResultDataAccessException e) {
        jdbcTemplate.update(new PreparedStatementCreator() {

            String INSERT_SQL = "INSERT INTO github_stats (stat_date) VALUES (current_date())";

            public PreparedStatement createPreparedStatement(Connection cn) throws SQLException {
                PreparedStatement ps = cn.prepareStatement(INSERT_SQL, new String[] { "stat_id" });
                return ps;
            }
        }, keyHolder);
        statId = keyHolder.getKey().longValue();
    }
    logger.info("Current GitHub Stats ID: " + statId);
    return statId;
}

/**
 * Alternative to RideRepositoryImpl#createRide(com.pluralsight.model.Ride)
 */
public Ride createRideKeyHolderJDBC(Ride ride) {
    final KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(connection -> {
        PreparedStatement ps = connection.prepareStatement("insert INTO RIDE(NAME, DURATION) values (?, ?)", new String[] { "id" });
        ps.setString(1, ride.getName());
        ps.setInt(2, ride.getDuration());
        return ps;
    }, keyHolder);
    return getRide(keyHolder.getKey());
}

public KeyHolderResult updateForKey(String... keyColumns) {
    checkNull();
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int count;
    String sql = strBuilder.toString();
    if (useClreplacedicJdbcTemplate) {
        // TODO 只能使用命名参数形式
        throw new UnsupportedOperationException("updateForKey() 目前只能使用命名参数形式");
    // PreparedStatementCreatorFactory preparedStatementCreatorFactory = new PreparedStatementCreatorFactory(sql);
    // 
    // 
    // PreparedStatementCreator creator = preparedStatementCreatorFactory
    // .newPreparedStatementCreator(Arrays.asList(this.varParams));
    // 
    // 
    // count = this.namedParameterJdbcTemplate.getJdbcOperations().update(
    // creator, keyHolder);
    } else {
        count = this.namedParameterJdbcTemplate.update(sql, this.sqlParameterSource, keyHolder, keyColumns);
    }
    if (count < 1) {
        logger.warn("update更新成功数量为" + count);
    }
    return new KeyHolderResult(count, keyHolder);
}

/**
 * Stores a new cloud region.
 *
 * @param region to be stored.
 * @param credentials if specified then cloud region credentials will be stored as well.
 * @return Stored cloud region.
 */
public AbstractCloudRegion create(final AbstractCloudRegion region, final AbstractCloudRegionCredentials credentials) {
    final KeyHolder keyHolder = new GeneratedKeyHolder();
    getNamedParameterJdbcTemplate().update(createRegionQuery, getParameters(region, credentials), keyHolder, new String[] { CloudRegionParameters.REGION_ID.name().toLowerCase() });
    region.setId(keyHolder.getKey().longValue());
    return region;
}

/**
 * 插入子事务过程记录，返回id
 *
 * @param gp
 * @return
 */
@Override
public Integer insert(TGlobalTransactionProcess gp) {
    final String strSql = "insert into global_transaction_process(transaction_id,transaction_sequence,status,expected_status,service_name,version_name,method_name,rollback_method_name," + "request_json, response_json,redo_times,next_redo_time, created_by, updated_by,created_at) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    this.getJdbcTemplate().update(conn -> {
        int i = 0;
        PreparedStatement ps = conn.prepareStatement(strSql);
        ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
        ps.setInt(++i, gp.getTransactionId());
        ps.setInt(++i, gp.getTransactionSequence());
        ps.setInt(++i, gp.getStatus().getValue());
        ps.setInt(++i, gp.getExpectedStatus().getValue());
        ps.setString(++i, gp.getServiceName());
        ps.setString(++i, gp.getVersionName());
        ps.setString(++i, gp.getMethodName());
        ps.setString(++i, gp.getRollbackMethodName());
        ps.setString(++i, gp.getRequestJson());
        ps.setString(++i, gp.getResponseJson());
        ps.setInt(++i, gp.getRedoTimes());
        ps.setTimestamp(++i, new Timestamp(gp.getNextRedoTime().getTime()));
        ps.setInt(++i, gp.getCreatedBy() == null ? 0 : gp.getCreatedBy());
        ps.setInt(++i, gp.getUpdatedBy() == null ? 0 : gp.getUpdatedBy());
        ps.setTimestamp(++i, new Timestamp(gp.getCreatedAt() == null ? new java.util.Date().getTime() : gp.getCreatedAt().getTime()));
        return ps;
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

/**
 * 插入记录，返回id
 *
 * @param g
 * @return
 */
@Override
public Integer insert(TGlobalTransaction g) {
    final String strSql = "insert into global_transactions(status, curr_sequence, created_at, created_by, updated_by) values(?, ?, ?, ?, ?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    this.getJdbcTemplate().update(conn -> {
        int i = 0;
        PreparedStatement ps = conn.prepareStatement(strSql);
        ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
        ps.setInt(++i, g.getStatus().getValue());
        ps.setInt(++i, g.getCurrSequence());
        ps.setTimestamp(++i, new Timestamp(g.getCreatedAt() == null ? new java.util.Date().getTime() : g.getCreatedAt().getTime()));
        ps.setInt(++i, g.getCreatedBy());
        ps.setInt(++i, g.getCreatedBy());
        return ps;
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

public Long insertUser(String nickName) {
    final String INSERT_SQL = "insert into User (nick_name) values(?)";
    final String name = nickName;
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "id" });
            ps.setString(1, name);
            return ps;
        }
    }, keyHolder);
    // 获取插入后的自增主键值
    return keyHolder.getKey().longValue();
}

/**
 * 添加
 * @param checkParimary   是否需要检查重复主键,默认不检查
 * @param columns  需要插入的列
 * @param dest  dest
 * @param data  data
 * @return return
 */
@Override
public int insert(String dest, Object data, boolean checkParimary, String... columns) {
    RunSQL run = SQLCreaterUtil.getCreater(getJdbc()).createInsertTxt(dest, data, checkParimary, columns);
    if (null == run) {
        return 0;
    }
    int cnt = 0;
    final String sql = run.getInsertTxt();
    final List<Object> values = run.getValues();
    KeyHolder keyholder = new GeneratedKeyHolder();
    long fr = System.currentTimeMillis();
    String random = "";
    if (showSQL) {
        random = "[SQL:" + System.currentTimeMillis() + "-" + BasicUtil.getRandomNumberString(8) + "][thread:" + Thread.currentThread().getId() + "][ds:" + DataSourceHolder.getDataSource() + "]";
        log.warn(random + "[txt:\n{}\n]", sql);
        log.warn(random + "[参数:{}]", paramLogFormat(values));
    }
    try {
        cnt = getJdbc().update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                int idx = 0;
                if (null != values) {
                    for (Object obj : values) {
                        ps.setObject(++idx, obj);
                    }
                }
                return ps;
            }
        }, keyholder);
        if (cnt == 1) {
            try {
                int id = (int) keyholder.getKey().longValue();
                setPrimaryValue(data, id);
            } catch (Exception e) {
            }
        }
        if (showSQL) {
            log.warn(random + "[执行耗时:{}ms][影响行数:{}]", System.currentTimeMillis() - fr, cnt);
        }
    } catch (Exception e) {
        e.printStackTrace();
        if (showSQLWhenError) {
            log.error(random + "[异常][txt:\n{}\n]", sql);
            log.error(random + "[异常参数][param:{}]", paramLogFormat(values));
        }
        throw new SQLUpdateException("插入异常:" + e);
    } finally {
        // 自动切换回默认数据源
        if (DataSourceHolder.isAutoDefault()) {
            DataSourceHolder.recoverDataSource();
        }
    }
    return cnt;
}

public void insertBranchLog(BranchLog branchLog) {
    PreparedStatementCreator psc = new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement("insert into dts_branch_record (trans_id,state,resource_ip,resource_info,gmt_created,gmt_modified)" + " values (?,?,?,?,now(),now())", Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, branchLog.getTransId());
            ps.setInt(2, branchLog.getState());
            ps.setString(3, branchLog.getResourceIp());
            ps.setString(4, branchLog.getResourceInfo());
            return ps;
        }
    };
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(psc, keyHolder);
    long branchId = keyHolder.getKey().longValue();
    branchLog.setBranchId(branchId);
    branchLog.setGmtCreated(Calendar.getInstance().getTime());
    branchLog.setGmtModified(Calendar.getInstance().getTime());
}

public long insert(String name, String style, String project) {
    String sql = "INSERT INTO p_report(name, style, project) VALUES(:name, :style, :project)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(Report.NAME, name);
    params.addValue(Report.STYLE, style);
    params.addValue(Report.PROJECT, project);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { Report.ID });
    return keyHolder.getKey().longValue();
}

public long insert(long userId, long createdAt, String name, String data) {
    String sql = "INSERT INTO p_canned_report(user_id, created_at, name, data) " + "VALUES(:user_id, :created_at, :name, :data)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(CannedReport.USER_ID, userId);
    params.addValue(CannedReport.CREATED_AT, createdAt);
    params.addValue(CannedReport.NAME, name);
    params.addValue(CannedReport.DATA, data);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { CannedReport.ID });
    return keyHolder.getKey().longValue();
}

@Override
public long insert(final String templateConfig) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement("INSERT INTO default_template_config(config) VALUES(?)", PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, templateConfig);
            return ps;
        }
    }, keyHolder);
    return keyHolder.getKey().longValue();
}

@Override
public int createUser(final CalendarUser userToAdd) {
    if (userToAdd == null) {
        throw new IllegalArgumentException("userToAdd cannot be null");
    }
    if (userToAdd.getId() != null) {
        throw new IllegalArgumentException("userToAdd.getId() must be null when creating a " + CalendarUser.clreplaced.getName());
    }
    KeyHolder keyHolder = new GeneratedKeyHolder();
    this.jdbcOperations.update(new PreparedStatementCreator() {

        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement("insert into calendar_users (email, preplacedword, first_name, last_name) values (?, ?, ?, ?)", new String[] { "id" });
            ps.setString(1, userToAdd.getEmail());
            ps.setString(2, userToAdd.getPreplacedword());
            ps.setString(3, userToAdd.getFirstName());
            ps.setString(4, userToAdd.getLastName());
            return ps;
        }
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

private long saveAddress(Address address) {
    String SQL = "INSERT INTO ADDRESS(DOOR_NO,STREET_NAME,AREA_NAME,STATE,COUNTRY,ZIP) " + "VALUES (:doorNo, :streetName, :areaName, :state, :country, :zip)";
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("doorNo", address.getDoorNo());
    params.put("streetName", address.getStreetName());
    params.put("areaName", address.getAreaName());
    params.put("state", address.getState());
    params.put("country", address.getCountry());
    params.put("zip", address.getZipCode());
    SqlParameterSource paramSource = new MapSqlParameterSource(params);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTempleate.update(SQL, paramSource, keyHolder, new String[] { "ID" });
    return keyHolder.getKey().longValue();
}

private long saveCustomer(Customer customer) {
    long addressId = saveAddress(customer.getBillingAddress());
    String SQL = "INSERT INTO CUSTOMER(NAME,PHONE_NUMBER,BILLING_ADDRESS_ID) " + "VALUES (:name, :phoneNumber, :addressId)";
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("name", customer.getName());
    params.put("phoneNumber", customer.getPhoneNumber());
    params.put("addressId", addressId);
    SqlParameterSource paramSource = new MapSqlParameterSource(params);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTempleate.update(SQL, paramSource, keyHolder, new String[] { "ID" });
    return keyHolder.getKey().longValue();
}

public long insert(List<String> columns, List<Object> args, int[] types) {
    String insertColumns = primaryKeyName();
    String insertPlaceholders = "nextval('" + tableName() + "_id_seq')";
    for (String column : columns) {
        insertColumns += "," + column;
        insertPlaceholders += ",?";
    }
    insertColumns += ", " + createdAtName();
    insertPlaceholders += ", now()";
    insertColumns += ", " + updatedAtName();
    insertPlaceholders += ", now()";
    String sql = "insert into " + tableName() + " (" + insertColumns + ") values (" + insertPlaceholders + ")";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sql, types);
    pscf.setReturnGeneratedKeys(true);
    try {
        logger.info("insert.sql: {}, args: {}", sql, jsonMapper.writeValuereplacedtring(args));
    } catch (JsonProcessingException e) {
        e.printStackTrace();
    }
    writeJdbcTemplate.update(pscf.newPreparedStatementCreator(args), keyHolder);
    if (keyHolder.getKeyList() != null && !keyHolder.getKeyList().isEmpty()) {
        return (long) keyHolder.getKeyList().get(0).get(primaryKeyName());
    }
    return -1;
}

@Test
public void githubStatRecordKeyReturned() throws Exception {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    long statId = -1L;
    try {
        statId = jdbcTemplate.queryForObject("SELECT stat_id FROM github_stats WHERE stat_date = '2010-10-10'", Long.clreplaced);
    } catch (EmptyResultDataAccessException e) {
        jdbcTemplate.update(new PreparedStatementCreator() {

            String INSERT_SQL = "INSERT INTO github_stats (stat_date) VALUES ('2010-10-10')";

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "stat_id" });
                return ps;
            }
        }, keyHolder);
        statId = keyHolder.getKey().longValue();
    }
    replacedertThat(statId).isGreaterThan(0);
}

See More Examples