aspose file tools*
The moose likes Object Relational Mapping and the fly likes How to delete all records in a JPA table? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to delete all records in a JPA table?" Watch "How to delete all records in a JPA table?" New topic
Author

How to delete all records in a JPA table?

Siegfried Heintze
Ranch Hand

Joined: Aug 11, 2000
Posts: 388
Now why is JPA complaining about my statement (see it imbedded in the error message):



According to http://edocs.bea.com/kodo/docs41/full/html/ejb3_overview_query.html#ejb3_overview_query_delete
it should work, correct? I just don't need a WHERE clause because I want to delete them all.

Thanks,
Siegfried
[ January 06, 2008: Message edited by: Siegfried Heintze ]
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
Looks like that doc is wrong. The syntax is actually

"DELETE FROM <abstract-schema> [[AS] <alias>]"

So take out the first occurrence of "e" in your query.


-Mike
Pro JPA 2: Mastering the Java Persistence API
Siegfried Heintze
Ranch Hand

Joined: Aug 11, 2000
Posts: 388
Thanks, but I'm still getting an error (see below).

"DELETE FROM Employee e" does not work. "DELETE FROM Employee" does not work either.

"DELETE FROM Employee e WHERE e.DTYPE LIKE '%' also does not work.



[ January 11, 2008: Message edited by: Siegfried Heintze ]
Vesa Tanhua-Tyrkk�
Greenhorn

Joined: Jan 16, 2008
Posts: 25
This works:
DELETE FROM Employee where id >= 0
Siegfried Heintze
Ranch Hand

Joined: Aug 11, 2000
Posts: 388
Thanks but I'm still getting the stack traces below.

Since it is putting "?" in my query I tried adding some parameters.






So with the above setParameter call I now get this stack trace:


Thanks again!
siegfried

[ January 20, 2008: Message edited by: Siegfried Heintze ]
[ January 20, 2008: Message edited by: Siegfried Heintze ]
Vesa Tanhua-Tyrkk�
Greenhorn

Joined: Jan 16, 2008
Posts: 25
Hi,


Exception in thread "main" java.lang.IllegalArgumentException: You have attempted to set a parameter at position 1 which does not exist in this query string DELETE FROM Employee e WHERE e.employeeID > 0.


There should not be need for setting any parameters.
Try also adding alias after DELETE
DELETE e FROM Employee e WHERE e.employeeID > 0

[ January 20, 2008: Message edited by: Vesa Tanhua-Tyrkk� ]
[ January 20, 2008: Message edited by: Vesa Tanhua-Tyrkk� ]
Siegfried Heintze
Ranch Hand

Joined: Aug 11, 2000
Posts: 388
I tried DELETE e FROM Employee WHERE e.employeeID > 0:


So I take out the e and I get:



Now why is it trying to bind? Presently I'm not binding anything. Hmmm. OK, I'll bind with this code:





And, I already tried "delete FROM Employee WHERE EmployeeID >0". I tried it with capitalizing the first letter in employeeID and not capitalizing. Neither worked.

No matter what I try, I just cannot seem to win!
Thanks,
Siegfried

Here is the stack trace:


[ January 20, 2008: Message edited by: Siegfried Heintze ]
Vesa Tanhua-Tyrkk�
Greenhorn

Joined: Jan 16, 2008
Posts: 25
Is your program working otherwise ok because I tested that query and it worked for me.
Siegfried Heintze
Ranch Hand

Joined: Aug 11, 2000
Posts: 388
Well, yes but it does not do much. I can read and write records if I take the inheritance annotation out of the Employee class.

As soon as I put that inheritance annotation in, I get problems with DTYPE for both SELECT and DELETE statements (the only kind I have tried).

Thanks,
siegfried
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
Hi Siegfried,

Sorry I didn't notice this thread again since I last responded, but after you fixed the syntax problem in your query then what you saw next was an exception indicating that it couldn't find the DTYPE column in your table. That just means that you have an inconsistency between your table and your mappings.

DTYPE is the default discriminator column name used for inheritance hierarchies (its value in a db row indicates the java class that the row maps to). It looks as if you mapped an inheritance hierarchy to existing tables but didn't map the discriminator column, so it is defaulting to DTYPE (but you don't have a column of that name in your table). If you had auto-generated the tables then it would have been created for you. Assuming that you have a discriminator column in your table then you can map it using the @DiscriminatorColumn annotation.

Hope this makes things clearer for you.
Siegfried Heintze
Ranch Hand

Joined: Aug 11, 2000
Posts: 388
I finally got a non-spring example working after following the directions at http://javahowto.blogspot.com/2006/07/helloworld-with-jpa-toplink-and-mysql.html .

I am able to make class messages a descendant of class greeting where class message as the additional data members of src and dst. While my example works (I have an instance of a descendant class that will display the src and dst), I cannot find the src and dst fields in the MySQL Client SQL browser! Using the SQuirreL browser did not help either! Anyway, it must be there somewhere!

Now I need to figure out the difference between the example that works and the example that does not work.

Thanks everyone for your help!
Siegfried
 
 
subject: How to delete all records in a JPA table?