Friday, June 30, 2017

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

By Hường Hana 4:30 AM , , , , Leave a Comment

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

4 Answers

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

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

Answers 3

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.

Answers 4

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

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment