SQL Server开启NOCOUNT ON时Spring Data JPA操作报错的解决方案咨询

问题根源在于SQL Server全局开启NOCOUNT ON后，会抑制SQL语句执行时返回受影响行数的信息，而Hibernate依赖这个行数确认写操作成功状态及乐观锁校验，因此触发Unexpected row count: -1和StaleObjectStateException错误。你之前的自定义Dialect仅处理了序列查询场景，未覆盖所有需要返回行数的操作，所以无效。以下是三种无需关闭全局NOCOUNT或使用原生查询的解决方法：

方案1：完善自定义Dialect，覆盖全量写操作SQL生成 #

修改CustomSQLServerDialect，覆盖插入、更新、删除、自增主键查询等方法，在生成的SQL前添加SET NOCOUNT OFF;：

package com.ata2.art22;

import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class CustomSQLServerDialect extends SQLServer2012Dialect {
    
    public CustomSQLServerDialect() {
        super();
        registerFunction("concat", new StandardSQLFunction("concat", StandardBasicTypes.STRING));
    }

    @Override
    public String getIdentityInsertString() {
        return "SET NOCOUNT OFF;\n" + super.getIdentityInsertString();
    }

    @Override
    public String getIdentitySelectString(String table, String column, int type) {
        return "SET NOCOUNT OFF;\n" + super.getIdentitySelectString(table, column, type);
    }

    @Override
    public String getUpdateString() {
        return "SET NOCOUNT OFF;\n" + super.getUpdateString();
    }

    @Override
    public String getDeleteString() {
        return "SET NOCOUNT OFF;\n" + super.getDeleteString();
    }

    @Override
    public String getSequenceNextValString(String sequenceName) {
        return "SET NOCOUNT OFF;\n" + super.getSequenceNextValString(sequenceName);
    }
}

此方法让Hibernate在生成所有需要返回行数的SQL时自动添加SET NOCOUNT OFF，确保能获取正确的受影响行数。

方案2：使用SessionEventListener初始化会话时关闭NOCOUNT #

创建会话监听器，在每个Hibernate会话打开时执行SET NOCOUNT OFF：

package com.ata2.art22;

import org.hibernate.Session;
import org.hibernate.event.spi.SessionEvent;
import org.hibernate.event.spi.SessionEventListener;
import java.sql.Statement;

public class NocountOffSessionListener implements SessionEventListener {
    @Override
    public void sessionOpened(SessionEvent event) {
        Session session = event.getSession();
        session.doWork(connection -> {
            try (Statement stmt = connection.createStatement()) {
                stmt.execute("SET NOCOUNT OFF");
            }
        });
    }
}

在Spring配置文件中添加监听器配置：

spring.jpa.properties.hibernate.session_event_listener=com.ata2.art22.NocountOffSessionListener

会话启动后，所有操作都会在NOCOUNT OFF环境下执行，无需修改单条SQL。

方案3：自定义ConnectionProvider，获取连接时关闭NOCOUNT #

实现自定义连接提供者，每次获取数据库连接时执行SET NOCOUNT OFF：

package com.ata2.art22;

import org.hibernate.engine.jdbc.connections.spi.AbstractDataSourceBasedMultiTenantConnectionProviderImpl;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import org.springframework.beans.factory.annotation.Autowired;

public class CustomConnectionProvider extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {
    private DataSource dataSource;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    protected DataSource selectAnyDataSource() {
        return dataSource;
    }

    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return dataSource;
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        Connection connection = super.getConnection(tenantIdentifier);
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("SET NOCOUNT OFF");
        }
        return connection;
    }

    @Override
    public Connection getConnection() throws SQLException {
        Connection connection = super.getConnection();
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("SET NOCOUNT OFF");
        }
        return connection;
    }
}

在配置文件中指定自定义连接提供者：

spring.jpa.properties.hibernate.connection.provider_class=com.ata2.art22.CustomConnectionProvider

此方法确保所有Hibernate获取的连接默认关闭NOCOUNT，对全量操作生效。

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