Any one tell me the HQL for this SQL code
UPDATE ModelClassname SET ClassVariablename=ClassVariablename+10 WHERE ClassVariableId=001;
Add a comment
|
6 Answers
As others say, there is better ways, but if you really have to, then for example with following syntax:
update EntityName m set m.salary = m.salary +10 where m.id = 1
Comments
There is no point using HQL to do that, you can use direct SQL if you want to do that, through a JDBC query (or even through a Hibernate query, you can use SQL queries).
Using HQL queries to update is only recommended when doing batch updates, not a single row. http://docs.jboss.org/hibernate/core/3.3/reference/en/html/batch.html#batch-direct
A more object-oriented way would be to load your object using HQL, do what you need to do in the Java world (columnValue +=10, whatever else you need to do), and then persist it back using hibernate session flush.
I suppose it involves more operations so it's less efficient (in pure performance) but depending on your Hibernate configuration (caching, clustering, second-level cache, etc.) it could be a lot better. Not to mention more testable, of course.
1 Comment
Débora
Thank you very much for your explanation. What would you think if we use normal HQL query and not going on Objective type for one or few rows updating.Would it be less efficient,too? Do you think using HQL and HQL objective type queries are same in process @ back-end?
In addition to Adam Batkin's answer, I would like to add that such queries are generally not used (except if you need to modify a whole loat of rows at once) in Hibernate. The goal of Hibernate is to work with objects. So you generally do:
MyEntity m = (MyEntity) session.get(MyEntity.class, "001");
m.setValue(m.getValue() + 10);
// and the new value will automatically be written to database at flush time
3 Comments
Débora
Thank you for your sample code.would you mention about the column name/entity variable name?
Débora
I really do not need to make an argument here,but I want to get clear one thing.(If you feel this would be an argument,then no need to answer).what do you think when the update is done with 1.normal sql query ? or 2.normal hql query? considering 1.performance and efficient and 2.number of updating rows? Thank you very much
JB Nizet
You just have one row to update in your example, and it's accessed by its primary key. Using a dedicated HQL or SQL query to do that is just fighting against Hibernate, and won't gain you anything except additional complexity. You'll lose batch updating, and optimistic concurrency. Don't fight against Hibernate. Optimize only when needed and when yiou have measured it was useful.
The HQL query should look pretty similar, except instead of using table and column names, you should use the entity and property names (i.e. whatever you use in Java).
Comments
Please try this one
Query q = session.createQuery("from ModelClassname where ClassVariableId= :ClassVariableId");
q.setParameter("ClassVariableId", 001);
ModelClassname result = (ModelClassname)q.list().get(0);
Integer i = result.getClassVariableName();
result.setClassVariableName(i+10);
session.update(result);
With Regards, Lavanyavathi.Bharathidhasan
Comments
HQL will help you here with bringing object to you with its session's help that you can update easily.
//id of employee that you want to update
int updatedEmployeeID = 6;
//exact employee that you want to update
Employee updateEmployee = session.get(Employee.class, updatedEmployeeID);
//for debug to see is that exact data that you want to update
System.out.println("Employee before update: "+updateEmployee);
//basically we use setter to update from the @Entity class
updateEmployee.setCompany("Arthur House");
//commit
session.getTransaction().commit();
