Search...
FAQs Subscribe
Pie
FAQs
Recent topics Flagged topics Hot topics Best topics
Search...
Search within Object Relational Mapping Search Coderanch
Advance search Google search
Register / Login
thread boost granny advertise on coderanch polymorphism book review grid
Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
thread boost meaningless drivel advertise on coderanch book review grid building better world book
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

How to delete all records in a JPA table?

 
Siegfried Heintze
Ranch Hand
Posts: 426
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
Posts: 304
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
Posts: 426
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
Posts: 25
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
This works:
DELETE FROM Employee where id >= 0
 
Siegfried Heintze
Ranch Hand
Posts: 426
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
Posts: 25
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
Posts: 426
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
Posts: 25
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
Is your program working otherwise ok because I tested that query and it worked for me.
 
Siegfried Heintze
Ranch Hand
Posts: 426
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
Posts: 304
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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.

-Mike
Pro JPA 2: Mastering the Java Persistence API

 
Siegfried Heintze
Ranch Hand
Posts: 426
posted 14 years ago
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
    Send
  • Quote
  • Report post to moderator
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
 
I'm THIS CLOSE to ruling the world! Right after reading this tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
reply
    Bookmark Topic Watch Topic
  • New Topic
Boost this thread!
Similar Threads
More...