How to use update hibernate query using setMaxResults?

Question

I hope it is the appropriate section, I have a problem with this code

Transaction transaction = session.beginTransaction(); 
Query query = session.createQuery("update database set floop= :ctrl1" +" where ctrl= :ctrl2 ").setMaxResults(2); 
query.setMaxResults(2);
query.setParameter("ctrl1",3);
query.setParameter("ctrl2", 5);

I ask through setMaxResults(2) to do the update only on the first two and he makes the update of all records as I do what is wrong?? thanks for any help

I thought to use session.createSQLQuery, but I do not know how to do.

Answer 1

This answer is posting delay but it can be helpful for others user who is looking update number of rows in DB with limit using HQL

Unfortunatly setMaxResults() do not work update and delete hibernate query. It works only select criteria.

As per HQL there is no specific solution is available and you want to update rows with number of limit then follow below steps

1. Write a HQL to select all rows with condition or range with
   setMaxResults. It will return you a List object with limit.
2. Then update the specific property (Property you want to update) and store Objects of these rows again by session.update() method.

I'm assuming tablename with map Database class and there are two variable ctrl and floop with getter and setter(as per your question)

            List<Database> list = new ArrayList<>();
            Transaction transaction = session.beginTransaction(); 

            //Fetching record with limit 2 using setMaxResults()
            int setlimit = 2;
            String hql_query = "from Database where ctrl = :ctrl2";
            Query select_query = session.createQuery(hql_query).setMaxResults(setlimit);
            select_query.setParameter("ctrl2", 5);
            list = select_query.list();

            //iterating list and setting new value to particuler column or property
            int result;
            if (list != null) {
                for (Database element : list) {
                    element.setFloop(ctrl1);
                    //Here is updating data in database
                    session.update(element);
                }
                result = list.size();
            } else {
                result = 0;
            }
            System.out.println("Rows affected: " + result);

            transaction.commit();