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!

Update

I need to do a update and return the affected id. I don't want to make a simple UPDATE.

You can check it out the original question here.

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

Please provide the value of the variable activeRecord leading into the code you provided. If it is simply true, then the code you provided did not generate the error message you provided. — Rick James
– Rick James, Commented Jun 26, 2017 at 20:45

Pradip Wawge · Accepted Answer · 2017-06-20 07:56:05Z

3

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();

answered Jun 20, 2017 at 7:56

Pradip Wawge

1314 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chiperortiz Over a year ago

I need to do a update and return the affected id!! you can check it out the original question here pal: stackoverflow.com/questions/44604763/…

Rick James · Accepted Answer · 2017-06-27 17:23:27Z

2

+25

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.)

edited Jun 27, 2017 at 17:23

answered Jun 26, 2017 at 20:51

Rick James

144k15 gold badges144 silver badges255 bronze badges

3 Comments

chiperortiz Over a year ago

MY SQL works i want to implement it using hibernate but im afraid is not possible....

chiperortiz Over a year ago

your pseudo code works but is exactly how is implemented ready now in 2 queries i would like to do in a single query.

Rick James Over a year ago

@chiperortiz - I added a clue.

Dmitry Senkovich · Accepted Answer · 2017-06-21 07:19:42Z

1

I suppose, you won't be able to make it in Hibernate fashion.

Hibernate is independent from a database. But the part of the query that initializes a variable (I mean set @ids = null;) is not portable across all the relational databases so I wouldn't expect it to be in Hibernate API somewhere.

edited Jun 21, 2017 at 7:19

answered Jun 20, 2017 at 19:00

Dmitry Senkovich

5,9819 gold badges41 silver badges82 bronze badges

1 Comment

Dmitry Senkovich Over a year ago

@chiperortiz Yes, but how there could be Hibernate API for a particular database? it's impossible to my mind

PanBrambor · Accepted Answer · 2017-06-29 15:12:02Z

1

I would sugest extracting records to be updated as list of entity, then you can iterate to set values, persist and still return afected ids at the end of your method

answered Jun 29, 2017 at 15:12

PanBrambor

899 bronze badges

Collectives™ on Stack Overflow

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

Update

Update

4 Answers 4

1 Comment

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

Update

4 Answers 4

1 Comment

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related