• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL / jdbc + Java Servlet

 
Carsten Rasmussen
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
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate 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.
 
pankaj vijay
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
Eclipse IDE Hibernate 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
Eclipse IDE Hibernate 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic