Hibernate HQL delete with and

Question

Hibernate doesn't delete my row:

public boolean deleteVote(Login user, int pid){

      Session session = getSession();

      try{
          String hql = "delete from Vote where uid= :uid AND pid= :pid";
          Query query = session.createQuery(hql);
          System.out.println(user.getUid() + " and pid: " + pid);
          query.setString("uid", user.getUid());
          query.setInteger("pid", pid);
          System.out.println(query.executeUpdate());
      }catch(Exception e){

Outprint:

uid: 123 and pid: 1
Hibernate: delete from votes where uid=? and pid=?
1

The SQL Syntax is working when I'm trying directly in SQL. Direct SQL Syntax:

 delete from votes where uid= '123' AND pid= 1

Mapping:

<class name="package.model.Vote" table="votes">
   <id name="vid" column="vid" >
   <generator class="increment"/>
</id>
<property name="pid" column="pid" />
<property name="uid" column="uid" />
<property name="tid" column="tid" />
<property name="votes" column="votes" />
 </class>

Table:

CREATE TABLE IF NOT EXISTS `votes` (
  `vid` int(11) NOT NULL 
  `pid` int(11) NOT NULL,
  `uid` varchar(20) NOT NULL,
  `tid` int(11) NOT NULL,
  `votes` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`vid`),
  KEY `pcid` (`pid`,`uid`),
  KEY `uid` (`uid`),
  KEY `tid` (`tid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

ALTER TABLE `votes`
ADD CONSTRAINT `votes_ibfk_3` FOREIGN KEY (`pid`) REFERENCES `poll` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `votes_ibfk_4` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON     UPDATE CASCADE,
ADD CONSTRAINT `votes_ibfk_5` FOREIGN KEY (`tid`) REFERENCES `teams` (`tid`) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO `votes` (`vid`, `pid`, `uid`, `tid`, `votes`) VALUES
(20, 1, '123', 1, 1);

I guess it's something pretty easy because everything looks okay for me so far. I got no error or anything else, just that no delete is happening.

Any help is appreciated.

How do you know that it is not deleted? Did you checked in database or did you do another query in the application? — fiso, Nov 21 '12 at 08:43
Does an insert work for your? Do you have transactions configured somewhere? — dr jerry, Nov 21 '12 at 09:04
insert is working. I have no transactions in the method. Do I need? //EDIT: Posted the full method to more clear — user1671980, Nov 21 '12 at 09:12
I think that there are tow things that you can try. One is to change the capital letter of Vote in the query to lowercase (if you are working with a key sensitive system it can be the problem). And another thing that you can try is to do a query in the application and check there if the problem are the transactions. — fiso, Nov 21 '12 at 09:18
flush the session. query.executeUpdate() is returning 1. This means record is deleted. — Johny T Koshy, Nov 21 '12 at 09:19
@fiso Vote must big uppercase cause its a class of the model, otherwise hibernate cant map. — user1671980, Nov 21 '12 at 09:29
@johny I tried direct before I used the session, no change. Still its rly confusing hibernate find the raw but its still definitely not deleted. — user1671980, Nov 21 '12 at 09:30

score 39 · Accepted Answer · answered Nov 21 '12 at 09:38

You need to begin and commit a transaction.

Transaction transaction = session.beginTransaction();
try {
  // your code
  String hql = "delete from Vote where uid= :uid AND pid= :pid";
  Query query = session.createQuery(hql);
  System.out.println(user.getUid() + " and pid: " + pid);
  query.setString("uid", user.getUid());
  query.setInteger("pid", pid);
  System.out.println(query.executeUpdate());
  // your code end

  transaction.commit();
} catch (Throwable t) {
  transaction.rollback();
  throw t;
}

It is also possible that you need to close the session before the changes will be visible in the database.

score 1 · Answer 2 · answered Nov 21 '12 at 09:36

From the Output you provided

uid: 123 and pid: 1
Hibernate: delete from votes where uid=? and pid=?
1

it is clear that query.executeUpdate() is returning 1. The method returns the number of entities updated or deleted. This means that 1 row has been updated or deleted, which is okay.

Try doing a session.flush() to flush the session, or a session.evict() to remove the object from the session.

score 0 · Answer 3 · answered Jul 28 '19 at 17:57

full java code with HQL & session.update

        try {

            try (Session session = this.sessionFactory.openSession()) {

                session.beginTransaction();

                String hql = "UPDATE Employee SET " +
                        "firstName = :firstName, " +
                        "lastName = :lastName," +
                        "gender = :gender," +
                        "jobTitle = :jobTitle," +
                        "departmentId = :departmentId " +
                        "where employeeId = :employeeId";

                Query query = session.createQuery(hql);

                query
                        .setParameter("employeeId", employee.getEmployeeId())
                        .setParameter("departmentId", employee.getDepartmentId())
                        .setParameter("firstName", employee.getFirstName())
                        .setParameter("lastName", employee.getLastName())
                        .setParameter("gender", employee.getGender())
                        .setParameter("jobTitle", employee.getJobTitle())
                ;

                int rows = query.executeUpdate();

//              session.update(Objects.requireNonNull(employee));

                session.getTransaction().commit();

                return session.get(Employee.class, employee.getEmployeeId()).equals(employee);
            }

        } catch (RuntimeException e) {
            e.printStackTrace();
            throw e;
        }

score 0 · Answer 4 · answered Aug 03 '19 at 09:00

        int deletedRows = 0;
        try {
            session = sessionProvider.get();
            String hql = "delete from ApprovalRemark approvalRemark where approvalRemark.approvalItem.id =:approvalItemId ";
            Query query = session.createQuery(hql);
            query.setParameter("approvalItemId", approvalItemId);
            deletedRows = query.executeUpdate();
        } catch (javax.persistence.NoResultException e) {
            return false;
        } catch (HibernateException he) {
            log.error("deleteAllApprovalRemarksForApprovalItem exception", he);
            throw he;
        }
        log.debug("deleteAllApprovalRemarksForApprovalItem deleted " + deletedRows + " rows");
        return true;

Hibernate HQL delete with and

4 Answers4

Linked