Hibernate gives me invalid update sql query

Question

I try to use HQL query:

 Session session = sessionFactory.getCurrentSession(); Query query = session.createQuery("update AlgorithmScript set isActive = false where user.loginName=:userName"); query.setParameter("userName", userName); query.executeUpdate();

but Hibernate generates invalid SQL query:

 Hibernate: update algorithmfight_checkers_db.algorithmscripts, set IsActive=0 where LoginName=?

Help me.

EDIT1 (Entity class for AlgorithmScript): This code was generated by Hibernate Tools plugin====================================================

@Entity @Table(name = "algorithmscripts", catalog = "algorithmfight_checkers_db") public class AlgorithmScript implements java.io.Serializable { private int algorithmScriptId; private User user; private String fileName; private String content; private Date uploadedDate; private boolean isActive; private boolean isDeleted; public AlgorithmScript() { } public AlgorithmScript(int algorithmScriptId, User user, String fileName, String content, Date uploadedDate, boolean isActive, boolean isDeleted) { this.algorithmScriptId = algorithmScriptId; this.user = user; this.fileName = fileName; this.content = content; this.uploadedDate = uploadedDate; this.isActive = isActive; this.isDeleted = isDeleted; } @Id @Column(name = "AlgorithmScriptId", unique = true, nullable = false) public int getAlgorithmScriptId() { return this.algorithmScriptId; } public void setAlgorithmScriptId(int algorithmScriptId) { this.algorithmScriptId = algorithmScriptId; } @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "UserId", nullable = false) public User getUser() { return this.user; } public void setUser(User user) { this.user = user; } @Column(name = "FileName", nullable = false) public String getFileName() { return this.fileName; } public void setFileName(String fileName) { this.fileName = fileName; } @Column(name = "Content", nullable = false, length = 65535) public String getContent() { return this.content; } public void setContent(String content) { this.content = content; } @Temporal(TemporalType.TIMESTAMP) @Column(name = "UploadedDate", nullable = false, length = 19) public Date getUploadedDate() { return this.uploadedDate; } public void setUploadedDate(Date uploadedDate) { this.uploadedDate = uploadedDate; } @Column(name = "IsActive", nullable = false) public boolean isIsActive() { return this.isActive; } public void setIsActive(boolean isActive) { this.isActive = isActive; } @Column(name = "IsDeleted", nullable = false) public boolean isIsDeleted() { return this.isDeleted; } public void setIsDeleted(boolean isDeleted) { this.isDeleted = isDeleted; } }

Community · Accepted Answer · 2020-06-20 09:12:55Z

First of all, you really, really need to realize that the query is not a SQL query, but a HQL query. Those are not the same languages.

Here's what the documentation says about DML queries:

The pseudo-syntax for UPDATE and DELETE statements is: ( UPDATE | DELETE ) FROM? EntityName (WHERE where_conditions)?.

Some points to note:

In the from-clause, the FROM keyword is optional

There can only be a single entity named in the from-clause. It can, however, be aliased. If the entity name is aliased, then any property references must be qualified using that alias. If the entity name is not aliased, then it is illegal for any property references to be qualified.

No Section 16.4, “Forms of join syntax”, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.

The where-clause is also optional.

Your query is

update AlgorithmScript set isActive = false where user.loginName=:userName"

So it violates the third point, since it uses an implicit join between the AlgorithmScript entity and the User entity.

Instead of using such a query, get the AlgorithmScripts to update using a select query, and set the isActive flag to false. Unless there are really many AlgorithmScripts to update, in which case using a subquery could be useful: update AlgorithmScript a set a.isActive = false where a.id in (select a2.id from AlgorithmScript a2 where a2.user.loginName=:userName.

Collectives™ on Stack Overflow

Hibernate gives me invalid update sql query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related