CODEWITHSUNDEEP
java
phani_yelugula
phani_yelugula

Reputation: 321

Deleting data in SQL table using Hibernate

I want to delete data from a mysql table if its with current date.

This is the code I wrote and is showing an exception at line "ERROR":

What is the procedure to delete data from tables with a where condition applied?

                    Date date=new Date();

                     SimpleDateFormat myFormat=new SimpleDateFormat("dd-MM-yyyy");

                     String todayDate=myFormat.format(date);

                    String query="delete * from today_list where today_date="+"'"+todayDate+"'";    
        Transaction t1=session.beginTransaction();  
        session.createQuery(query).executeUpate();//ERROR
                    t1.commit();

Upvotes: 1

Views: 8171

Answers (4)

chamzz.dot
chamzz.dot

Reputation: 775

First thing is there is a spelling mistake in your code. it must become session.createQuery(query).executeUpdate(); instead of ;

session.createQuery(query)..executeUpate();

using this session transaction you can delete a data in your DB:

Configuration cfg = new Configuration();
                cfg.configure("Hibernate.cfg.xml");
                SessionFactory ss = cfg.buildSessionFactory();
                Session s = ss.openSession();
                Transaction tx = s.beginTransaction();
                Query query = s.createQuery("delete from Employee where Id:Id");
                query.setParameter("Id", valueToDelete);
                query.executeUpdate();
                tx.commit();
                s.close();

this works for me perfectly !! :)

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

Do not concatenate the actual parameter values into the query string. Use setParameter instead.

Query query = session.createQuery("delete from todayList where todayDate = :date ");
query.setParameter("date", date);
query.executeUpate();

This was easy to figure out but next time please post the error message too.

EDIT please also note the table name in the query String. It is not the actual table name in the database, but the Hibernate entity name.

Upvotes: 4

grachol
grachol

Reputation: 82

They are 2 things which can throw exception:

1 - You should write "Delete from" without "*"

2 - Try to figure out what data format you need, this can also make a throw if you put wrong format.

Upvotes: 0

AlexR
AlexR

Reputation: 115328

According to SQL syntax you should not write * after delete, i.e. change your statement to

delete from ...

Although it is not the right way when dealing with any ORM including Hibernate. This way bypasses cache (if you have cache). Better way is to delete entity using session API method.

Upvotes: 3

Related Questions