Error in JPQL Update Query using Hibernate on MariaDB

Question

Hi

I am trying to update a row in MariaDB database table using hibernate. It is like this :

    public static void updateJPQL() {
        EntityManager entmngr = JPAProvider.getEntityManager();
        EntityTransaction transaction = entmngr.getTransaction();
        transaction.begin();
        Query query = entmngr.createQuery("UPDATE users o set o.name = 'John' where o.id =: NID");
        query.setParameter("NID", 2L);

        query.executeUpdate();


        transaction.commit();
        entmngr.close();
    }

Hibernate gives this error :

Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - NavigablePath[model.entity.Users(o)]

the other queries would execute fine. I could insert into my table, I could fetch all of the records but this update query is the problem.

Ken Chan · Accepted Answer · 2019-12-08 08:41:22Z

1

Several things to check and note:

The table name used in the UPDATE query should refer to the mapped Java class name rather than the database table name and it is case-sensitive. So does the users in the query is the table name as we seldom name the Java class starting with the lower case ? If yes , please change it to the mapped Java class name. (The same applied to the column too , refer them using java object field name rather than the actual table columns name)
The point of using ORM is that we don't want to manually write SQL to update the DB record. We want to treat the DB record as an Object such as we can use the OOP technique to implement the update logic. so through changing the state of the object , JPA will automatically generate the SQL to update the corresponding DB records rather than writing it by ourself. So the correct way to update the record using JPA is to get the record that you want to update , change its state :

User user = entmngr.find(User.class, 2L);
user.setName("John")

edited Dec 8, 2019 at 8:41

answered Dec 8, 2019 at 8:13

Ken Chan

91.4k26 gold badges157 silver badges189 bronze badges

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

1 Comment

rufsmd Over a year ago

the users is @Entity(name = "users") introduced in the entity class, and not the name of the class itself. either way I checked with the name of the class and I got Could not resolve entity name [Users] as DML targ. and @ken-chan this is a practice of using JPQL for the job otherwise I am agreeing with you about the method.

Laurel · Accepted Answer · 2022-07-13 10:49:13Z

0

Had the same problem for this code, db is postgresql:

@Modifying
@Query("DELETE FROM Account a WHERE a.id = :id AND a.user.id = :userId")
int delete(@Param("id") int id, @Param("userId") int userId);

I used dependency

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>6.0.0.Alpha5</version>
    <type>pom</type>
</dependency>

After only change to the following dependency, all works:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.4.15.Final</version>
</dependency>

Maybe it's a bug in the new version, or maybe we need some magic for Hibernate 6.0.0.Alpha5 + Spring Data JPA 2.3.0.RELEASE. I didn't find any information. If you know something about that, let me know.

edited Jul 13, 2022 at 10:49

Laurel

6,23114 gold badges35 silver badges60 bronze badges

answered May 19, 2020 at 19:44

Anton

7302 gold badges11 silver badges23 bronze badges

Collectives™ on Stack Overflow

Error in JPQL Update Query using Hibernate on MariaDB

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related