CODEWITHSUNDEEP
javasql-serverhibernatespring-data-jpanocount
infD
infD

Reputation: 63

Unexpected row count: -1; expected: 1 with Spring Data JPA SQL Server with NOCOUNT ON

PROBLEM: I am having a problem regarding the database under the server in SQL Server which has Server Property > Connection > NOCOUNT ON (is ticked). The table has no trigger, absolutely nothing FYI.

What I am trying to do it just to do a simple insert using repository.save(entity) or entityManager.persist(entity) but I got an error:

Unexpected row count: -1; expected: 1.

I cannot get an entity out and edit it also => there will be an error

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)

I have tried to make a custom Dialect to insert a SET NOCOUNT OFF before every query but it didn't work.

The custom dialect:

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 getSequenceNextValString(String sequenceName) {
        return "SET NOCOUNT OFF;\n" + super.getSequenceNextValString(sequenceName);
    }
}

The property file setting:

spring.jpa.properties.hibernate.dialect=com.ata2.art22.CustomSQLServerDialect

QUESTION: Is there any other ways or changes I need to do to be able to use methods from repository or entityManager WITHOUT turning off the NOCOUNT ON at the Database Server configuration or using native query?

Upvotes: 1

Views: 299

Answers (1)

fixitfeliks
fixitfeliks

Reputation: 1

I just struggled with this problem for 2 days X_X

Our old DB got moved to a larger instance that is managed by another team that has NOCOUNT set to ON and I have no sway to change that since I own 1 out of ~30 DBs on that server!

I am using Hibernate 5.6 and running on SQL Server 2016.

You are on the right track by setting something up to prepend that statement to your queries.

Use a StatementInspector instead

public class NoCountStatementInspector implements StatementInspector {
private static final Logger logger = MyLoggerFactory.getLogger(NoCountStatementInspector.class.getSimpleName());

@Override
public String inspect(String sql) {
    if (sql.toLowerCase().indexOf("insert") == 0 || sql.toLowerCase().indexOf("update") == 0) {
        logger.finer("Inspecting SQL Statement (add NOCOUNT OFF) - BEFORE: " + sql);
        String newSql = "set nocount off; " + sql;
        logger.finer("Inspecting SQL Statement (add NOCOUNT OFF) - AFTER : " + newSql);
        return newSql;
    }
    return sql;
}

}

And then set your hibernate properties to use your inspector. I have my hibernate setup programatically, if you use a file add it to the file.

hibernateProperties.put("hibernate.session_factory.statement_inspector",
            "com.persistentsystems.data.NoCountStatementInspector");

Make sure the class is supplied to hibernate managed classes in whichever way. I am not using spring so I set it up manually.

Hope this helps someone lol

Upvotes: 0

Related Questions