*
The moose likes JDBC and the fly likes MySQL / jdbc + Java Servlet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL / jdbc + Java Servlet" Watch "MySQL / jdbc + Java Servlet" New topic
Author

MySQL / jdbc + Java Servlet

Carsten Rasmussen
Ranch Hand

Joined: Sep 22, 2009
Posts: 65
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

Joined: Apr 14, 2004
Posts: 10336


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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
pankaj vijay
Ranch Hand

Joined: Apr 01, 2008
Posts: 75
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();


Pankaj Vijay (SCJP, SCBCD)
Learn Core Java,Learn Servlet Jsp, SCJP Questions,Struts Tutorial
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Sep 22, 2009
Posts: 65
thank you that did not help.
I need a code
Carsten Rasmussen
Ranch Hand

Joined: Sep 22, 2009
Posts: 65
I figured it out after some hours my self.
Carsten Rasmussen
Ranch Hand

Joined: Sep 22, 2009
Posts: 65
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Sep 22, 2009
Posts: 65
But.... my solution works i build my DB like this.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL / jdbc + Java Servlet