chiperortiz
chiperortiz

Reputation: 4991

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

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

Upvotes: 4

Views: 1117

Answers (4)

PanBrambor
PanBrambor

Reputation: 89

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

Upvotes: 1

Rick James
Rick James

Reputation: 142298

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

Upvotes: 2

Dmitry Senkovich
Dmitry Senkovich

Reputation: 5911

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.

Upvotes: 1

Pradip Wawge
Pradip Wawge

Reputation: 131

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

Upvotes: 3

Related Questions