Newsletter |
Part 6 Hibernate Query Language, HQL Update,Delete Queries
so far we have been executed the programs on Hibernate Query Language (HQL) select only, now we will see the DML operations in HQL like insert, delete, update., you know some thing..? this delete, update query’s are something similar to the select query only but insert query in Hibernate Query Language(HQL) is quite different, i will let you know while we are going to execute the program on HQL insert, for now let us see an example on delete, update queries
Remember.., while we are working with DML operations in HQL we have to call executeUpdate(); to execute the query, which will returns one integer value after the execution, will discuss more on this later..
files required.. (for both delete, update query’s)
- Product.java (POJO class)
- Product.hbm.xml
- hibernate.cfg.xml
- ForOurLogic.java (Our logic)
Let Us See An Example On HQL DELETE
Actually Product.java, Product.hbm.xml and hibernate configuration files are some for both logics
Product.java
package str; public class Product{ private int productId; private String proName; private double price; public void setProductId(int productId) { this.productId = productId; } public int getProductId() { return productId; } public void setProName(String proName) { this.proName = proName; } public String getProName() { return proName; } public void setPrice(double price) { this.price = price; } public double getPrice() { return price; } }
Product.hbm.xml
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="str.Product" table="products"> <id name="productId" column="pid"Β /> <property name="proName" column="pname" length="10"/> <property name="price"/> </class> </hibernate-mapping>
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver </property> <property name="connection.url">jdbc:oracle:thin:@www.java4s.com:1521:XE</property> <property name="connection.username">system</property> <property name="connection.password">admin</property> <property name="dialect">org.hibernate.dialect.OracleDialect</property> <property name="show_sql">true</property> <property name="hbm2ddl.auto">update</property> <mapping resource="Product.hbm.xml"></mapping> </session-factory> </hibernate-configuration>
ForOurLogic.java
package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ForOurLogic { public static void main(String[] args) { Configuration cfg = new Configuration(); cfg.configure("hibernate.cfg.xml"); SessionFactory factory = cfg.buildSessionFactory(); Session session = factory.openSession(); Query qry = session.createQuery("delete from Product p where p.productId=:java4s"); qry.setParameter("java4s",110); int res = qry.executeUpdate(); System.out.println("Command successfully executed...."); System.out.println("Numer of records effected due to delete query"+res); session.close(); factory.close(); } }
Notes:
- see line number 17, i have been used label to pass the value at run time, and nothing to explain more in this…. what do you say π
Database Output Before Execution
Run the program in eclipse
Eclipse console
Database Output After Execution
Let Us See An Example On HQL UPDATE
Mates, Product.java, Product.hbm.xml, hibernate.cfg.xml are similar to previous hql program (HQL part 5), so am directly writing the code for ForOurLogic.java
ForOurLogic.java
package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ForOurLogic { public static void main(String[] args) { Configuration cfg = new Configuration(); cfg.configure("hibernate.cfg.xml"); SessionFactory factory = cfg.buildSessionFactory(); Session session = factory.openSession(); Query qry = session.createQuery("update Product p set p.proName=? where p.productId=111"); qry.setParameter(0,"updated.."); int res = qry.executeUpdate(); System.out.println("Command successfully executed...."); System.out.println("Numer of records effected due to update query"+res); session.close(); factory.close(); } }
Notes:
- update query written in line number 17,Β here i am going to set value in run time by using question mark symbol in the query, that’s it.
Database Output Before Execution
Run the program in eclipse
Eclipse Console
Database Output After Execution
And that’s it ………………..Β Β π
You Might Also Like
::. About the Author .:: | ||
Actually you are told that every DML operations are performed in presence of session only ok thats fine but when the time of using HQL update,delete you are not used any transaction how it will work please explain me.
Thank you in advance.
@Raju
Excellently Analyzed…
We have to use Transaction and has to say transaction.commit().
then only database modifications will be done.
In the above program, no deletion of the entities in the database is happening coz we are just querying but not commiting… when commiting only, modifications will be done and be saved into the database.
@Raju, @Vaseem
Raju am very sorry for giving late reply, we thought its better to wait until we know the exact answer.
Actually am checking the same with my friends as well.
As we said, modifications wont be effected until we say commit, but i guess it got committed.
I think transactions is mandatory for DMLs. Even i mailed the same to hibernate forums but no response from them.
Any ways vaseem can you check with/with out transaction and check the database and conform once ?
When i checked with out transaction and did not say tx.commit(), then in the console of the program it has shown that database is updated but when checking in database, database was not updated. This means query is executed but not committed.
When i checked with Transaction and used tx.commit(),then the value is updated in the database. nothing but query is executed and as well committed.
I can say that if we want to update any values in the database we must has to use transaction and has to use tx.commit(), then only the values will be updated or else not.
Hello java4s,
Thanks for sharing this much of valuable information with all…
As a user, I has to get all the updated information very easily. So I request to maintain a separate web page for “What readers are saying” option so that i can easily find the happening updates very easily…
Thanks again..
@ Vaseem
Oops some thing what we expected π
Thank for the conformation.
Hi all i got the same problem Not updating at database side when i coded without committing transaction for delete or update. i think trnx.commit() ; is needed. I wish you all keep experimenting and letting others know them.
Good Job !!!!!!!!
In above example we have passed value as updated.. but in screenshot only 1 dot is being displayed. Will there be 2 dots or 1 dot only
how to delete record without providing id no. (like here 110)……..
Nice one
Hi java4s please help me
can you please explain me how to insert 5 records or some records at a time by using insert query.
and in insert query we writing like this insert….table_name + select….table_name both the table name should same or different
Good Analysisβ¦!!
DML delete and update not work on my system.
Hi java4s,
In ForOurLogic.java class two lines are missing, without these lines code is not able to delete or update in database table
Transaction tx=session.beginTransaction(); // missing
int res=query.executeUpdate();
tx.commit(); // missing
Regards
Alok singh
Nice example but without transaction we cannot perform any DML operations.
Hello Friends…!
I observe..
Without Transaction and without commit also delete and update are successfully working on database…that I am sure…
need more explanation on this concept…
thank you
HQl Update i’m getting this message in connsole
Exception in thread “main” java.lang.IllegalArgumentException: node to traverse cannot be null!
Configuration cfg=new Configuration();
cfg.configure(“resource/configuration.cfg.xml”);
SessionFactory sf=cfg.buildSessionFactory();
Session ses=sf.openSession();
Transaction ts=ses.beginTransaction();
String hql = “update Pojo set Name=’ghjf’ where Id=’335′”;
Query q =ses.createQuery(hql);
int i = q.executeUpdate();
ts.commit();
System.out.println(“Command successfully executed….”);
System.out.println(“Numer of records effected due to update query “+i);
ses.close();
sf.close();
Showing message in hql update
Exception in thread “main” java.lang.IllegalArgumentException: node to traverse cannot be null!
something is wrong,,,whichever demo i m trying ,I am getting exception of "connection not open"..please help me
Hello Sir,
Please add the Transaction Interface in case of "UPDATE" and "DELETE" Operations in Hibernate…..
Thanks & Regards,
Mahboob Ali
Dear All & @Java4s your can perform DML operations without transaction if you have defined @Transactional annotation in your services class.
yes there must be transaction otherwise changes is not shown on database