Win a copy of Micro Frontends in Action this week in the Server-Side JavaScript and NodeJS 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

MySQL / jdbc + Java Servlet

 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My purpose is to delete all messages but the last 10 written.
Im i have tried in many ways but it seems to be harder to delete data than put in data.
Heres my code i hope you answer quickly and correct.

A little bit of my mysql.java file
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

This assumes your ID and row numbers are one and the same which is not an assumption you can make with relational databases.

A different approach would be to select all the ids in descending order, get the first ten then delete from the table where the row id is not in this list.
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Carsten
Normally every table have primary key OR foreign key.If your table also have primary key then you can take last primary key & can easily delete all records except last 10 like this way:

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select PrimayID from beskeder order by id desc");

int rowCount = 0;
while (rset.next()){
rowCount = PrimayID;
out.print ("" + rset.getString("nick") + ": " + rset.getString("msg") + "
\n");
}

int maxMsg = 10;
int max = (rowCount-maxMsg);

PreparedStatement del = null;
del = conn.prepareStatement("DELETE FROM beskeder WHERE PrimayID <= '" + max + "'");

del.executeUpdate();

stmt.close();
del.close();
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Normally every table have primary key OR foreign key


This is wrong. Database implementations don't mandate either on a table and they are not mutualy exclusive. A table should contain a primary key and may contain one or more foreign key fields.

Your example is making the same mistake as Carsten made in his original code; primary key != row id.
 
Carsten Rasmussen
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you that did not help.
I need a code
 
Carsten Rasmussen
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I figured it out after some hours my self.
 
Carsten Rasmussen
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry pankaj vijay didnt see that you have posted a code for me to use, thats how i like it thank you
But the problem was not the MySQL i am a PHP/MySQL expert so thats no problem, it was how i could execute both sql statements i Java Servlet.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Carsten Rasmussen wrote:thank you that did not help.
I need a code



Please read this and this.

Your solution is almost there and should work most of the time, but be aware unless you are assigning your primary key to keep them sequential (and recreating records should you ever delete anything) it will fail some of the time.
 
Carsten Rasmussen
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But.... my solution works i build my DB like this.
 
If I'd had more time, I would have written a shorter letter. -T.S. Eliot such a short, tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic