Deleting data in SQL table using Hibernate

Question

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();

What is the exception you get?

Averroes
– Averroes

2012-09-19 09:50:05 +00:00
Commented Sep 19, 2012 at 9:50 — Averroes
– Averroes, Commented Sep 19, 2012 at 9:50

bpgergo · Accepted Answer · 2012-09-20 13:42:14Z

4

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.

edited Sep 20, 2012 at 13:42

answered Sep 19, 2012 at 9:51

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

phani_yelugula Over a year ago

yaa...i have done it...like....delete from TodayList_model where _todaydate=20-09-2012.....and also like,....DELETE FROM TodayList_model todayList_Obj WHERE _todaydate = '20-09-2012' ....but no use...

phani_yelugula Over a year ago

error is like....could not execute update queryorg.hibernate.exception.SQLGrammarException: could not execute update query......Caused by: java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'todaylist_0_ where (_todaydate='20-09-2012')' at line 1"

bpgergo Over a year ago

A few things. 1) can you have Hibernate log the full SQL statement and add it to the original post? 2) can you add the actual table names and column names to the original post? 3) Can you olso add the source of the Hibernate Entity class representing this table (or alternatively the relevant part of the hbm.xml)? 4) Do not concatenate the actual parameter values into the query string. Use setParameter instead. Did you try this? Your first comment suggest you did not.

AlexR · Accepted Answer · 2012-09-19 09:50:36Z

3

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.

answered Sep 19, 2012 at 9:50

AlexR

116k16 gold badges137 silver badges216 bronze badges

1 Comment

phani_yelugula Over a year ago

yaa...i have done it...like....delete from TodayList_model where _todaydate=20-09-2012.....and also like,....DELETE FROM TodayList_model todayList_Obj WHERE _todaydate = '20-09-2012' ....but no use

grachol · Accepted Answer · 2012-09-19 09:57:00Z

0

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.

answered Sep 19, 2012 at 9:57

grachol

821 silver badge7 bronze badges

2 Comments

phani_yelugula Over a year ago

i tried after deleting from also but error is same...and i read saome where that hibernate 3.0 is having problem in update since the method is declared wrong like execute.upate() but not execute.update()..its modified in next versions itseems..i have to try next versions now

grachol Over a year ago

Yes. Hibernate has problems with update(). A lot of people prefer to use saveOrUpdater(). Sometimes its better, but not always.

chamzz.dot · Accepted Answer · 2018-04-01 07:07:19Z

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 !! :)

Collectives™ on Stack Overflow

Deleting data in SQL table using Hibernate

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related