JDBC准备好的语句创建了一个额外的数据库命中，没有任何参数 [英] JDBC prepared statement creates one extra database hit without any params

问题描述

我有一个烦人的问题，我不知道为什么会发生.为了给您简短的介绍，我在Spring Boot项目上使用MySql和JDBC模板实现了批处理(批量插入).

I have one annoying issue and i can not figure out why it is happening. To give you short introduction, i implemented batch processing (batch insert) using MySql and JDBC template on my Spring Boot project.

因此，基本上，批处理插入应该可以正常工作，并且性能确实很棒，但是有一个小问题确实令人讨厌，并且当我插入在两列中具有唯一键的表中时，它会导致约束异常( id，值).

So basically batch insert is working like it should, and performance is really amazing, BUT there is this one small issue that really is annoying and it causes constraint exception when i am inserting in one table that has unique key on two columns (id, value).

所以我有这个代码:

private String INSERT_SQL_PARAMS = "INSERT INTO item_params(p_key, p_value, item_id) values (?,?,?)"

override fun saveParams(configParams: Set<ItemParam>) {
    jdbcTemplate!!.update { connection ->
        connection.autoCommit = false
        val ps: PreparedStatement = connection.prepareStatement(INSERT_SQL_PARAMS)

        configParams.forEachIndexed { index, it ->
            ps.setLong(1, it.configurationId)
            ps.setString(2, it.pKey)
            ps.setString(3, it.pValue)
            ps.addBatch()

            if (index != 0 && index % 1000 == 0) {
                ps.executeBatch()
                connection.commit()
            }
        }

        ps.executeBatch()
        connection.commit()
        ps
    }
}

当我在Spring Boot中使用数据源代理查看日志时，我可以看到实际执行的查询.

And when i am looking at the logs using datasource proxy in spring boot, i can see actual queries that are executed.

因此，当我想一次插入例如2个项目时，我会在日志中看到这一点:

So when i want to insert for example 2 items at once i see this in log:

Batch: True, INSERT INTO item_param(item_id, p_key, p_value) values (1, 1, 1), (2, 2, 2)
Batch: False, INSERT INTO item_param(item_id, p_key, p_value) values ()

因此，正如您所看到的，我总是在末尾看到一个没有任何值的多余/备用语句，并且由于某种原因，日志显示Batch = False.

So as you can see i always at the end see that one extra/spare statement without any values, and from some reason log says Batch = False.

任何人都可以看到我的代码中缺少的东西吗，为什么会发生这种情况以及我可以做什么呢?

Can anyone see something that i am missing in my code, why is this happening and what i can do with this ?

我还有一个问题，例如，如果我有1000条记录，我将执行2次批处理，一次是if语句，最后一次.只有在查询中有参数的情况下，有什么方法可以让我说ps.executeBatch吗?

Also i have one more question, for example if i have 1000 records, i will execute batch 2 times, one in if statement and one at the end. Is there some way where i can say ps.executeBatch only if there are params in query ?

推荐答案

因为jdbcTemplate.update为您执行了SQL，所以它会在代码末尾自动调用execute.

Because jdbcTemplate.update executes your SQL for you, so it automatically calls execute at the end of your code.

由于它只调用一次execute，因此不符合您的目的.试试使用jdbcTemplate.batchUpdate的教程.它为您实现了对jdbcConnection的多次调用.

Since it calls execute exactly once, it doesn't fit your purposes. Try this tutorial that uses jdbcTemplate.batchUpdate. It implements the multiple calls to jdbcConnection for you.

这篇关于JDBC准备好的语句创建了一个额外的数据库命中，没有任何参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！