Java Hibernate org.hibernate.exception.SQLGrammarException: could not extract ResultSet on createSQLQuery

Question

I have this method.

private final void updateAllTableFields(final Class clazz){
    final String tableName = ((Table)clazz.getAnnotation(Table.class)).name();
    final String sqlQuery = new StringBuilder("SET @ids = NULL; ")
            .append("UPDATE ")
            .append(tableName)
            .append(' ')
            .append("set activeRecord=:activeRecord ")
            .append("where activeRecord=true and updateable=true ")
            .append("and (SELECT @ids \\:= CONCAT_WS(',', id, @ids)); ")
            .append("select @ids;")
            .toString();
    final Query query = session.createSQLQuery(sqlQuery)
            .setParameter("activeRecord",Boolean.FALSE);
    final Object idsList=query.uniqueResult();
    System.out.println("idsList = " + idsList);
}        

I want to do a update and also return the affected Ids this works Perfect using a rawSQL returns the id in a string fashion but i couldn't make it work using Hibernate any tip!!!

Thanks in advance and best regards.

UPDATE

I need to do a update and return the affected id!! I dont want to make a simple UPDATE.

you can check it out the original question here pal: https://stackoverflow.com/questions/44604763/java-hibernate-tips-about-update-all-table-fields-performance

UPDATE The error is

at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
at org.hibernate.loader.Loader.doQuery(Loader.java:909)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
at org.hibernate.loader.Loader.doList(Loader.java:2553)
at org.hibernate.loader.Loader.doList(Loader.java:2539)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
at org.hibernate.loader.Loader.list(Loader.java:2364)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966)
at company.nuevemil.code.finalizarEntornoDePrueba(Test.java:56)
at company.nuevemil.code.main(Test.java:27)


Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE student set activeRecord=false,uid=1 where activeRecord=true at line 1

Answer 1

you have to use HQL Query for bulk update. you are going write way only thing is that, you have to create HQL query for example


    Your Query Might be like this:-
    final String tableName = ((Table)clazz.getAnnotation(Table.class)).name();
        final String sqlQuery = new StringBuilder("SET @ids = NULL; ")
                .append("UPDATE ")
                .append(tableName)
                .append(' ')
                .append("set activeRecord=:activeRecord ")
                .append("where activeRecord=true and updateable=true ")
                .append("and (SELECT @ids \\:= CONCAT_WS(',', id, @ids)); ")
                .append("select @ids;")
                .toString();
        final Query query = session.createQuery(sqlQuery)
                .setParameter("activeRecord",Boolean.FALSE);
        final Object idsList=query.executeUpdate();

    Example Query:
    final String tableName = ((Table)clazz.getAnnotation(Table.class)).name();
       Query qry = session.createQuery("update "+tableName+" p set p.proName=?
    where p.productId=111");
                qry.setParameter(0,"updated..");
                int res = qry.executeUpdate();

Answer 2

There is no "affected id" in an UPDATE statement.

UPDATE student
    set activeRecord=false,uid=1
    where activeRecord=true

may modify 0 rows, 1 rows, or many rows.

What is the PRIMARY KEY of student? Let's say it is studentId. To retrieve all (if any) of the studentId values, you neecd the Hibernate equivalent of this pseudo-code:

START TRANSACTION;
@ids = SELECT studentId
           FROM student
           WHERE activeRecord=true  -- and updateable=true ??
           FOR UPDATE;
UPDATE student
    SET activeRecord=false,
        uid=1
    WHERE activeRecord=true  -- and updateable=true ??
    ;
COMMIT;

More

That code could be bundled up in a Stored Procedure, thereby allowing it to be CALLed as if a single statement. (I do not know how to make it work with Hibernate.)