Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Retrieving last 8 rows in a table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieving last 8 rows in a table" Watch "Retrieving last 8 rows in a table" New topic
Author

Retrieving last 8 rows in a table

Sean Casey
Ranch Hand

Joined: Dec 16, 2000
Posts: 625
What statement could I use to retrieve the last 8 rows in a table?
It would be easier if I could select the last row
and then the last row -1 etc.
any ideas???
Michael Fitzmaurice
Ranch Hand

Joined: Aug 22, 2001
Posts: 168
Hi
Use a loop where you get the last row (as explained in response to your query about that), then (in a loop) subtract 1 from the value of the artID you used to get that row and get the matching row for that artID. Repeat 6 more times. To do it this way, you must be sure that the artID fields are all sequential and ordered, i.e. there is no possibility that one of them has been deleted (e.g. artID 10, 9, and 7 exist, but 8 has been deleted - problems ensue).
It would probably be best to write a small stored procedure to return you a resultset containing the last 8 rows only. But you could do it all by executing 8 Statement.execute() calls, if you really want.
------------------
"One good thing about music - when it hits, you feel no pain"
Bob Marley
[This message has been edited by Michael Fitzmaurice (edited September 12, 2001).]


"One good thing about music - when it hits, you feel no pain" <P>Bob Marley
Suji N
Ranch Hand

Joined: Sep 04, 2001
Posts: 35
check the following code....which was done on using a w t
it will be verymuch useful
if u got any pblm...tell me
i will solve it
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class UserSearch extends Panel implements ActionListener, TextListener
{
Label totalrecords, userIDLbl,userNameLbl,title, label1, label2, label3, label4, label5;
TextField userIDTxt,userNameTxt;
Button previous,search,nextPage,reset,exit;
TextField tf[][];
int i,recPos,recCount;
boolean nextP,prev,ent;

static MyConnection myConus1;
Statement st,st1,st2;
ResultSet rs,rs2;

UserSearch()
{
myConus1 = new MyConnection();
try
{
st = myConus1.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
st1 = myConus1.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
st2 = myConus1.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
}
catch(Exception e)
{
System.out.println("excep in defining my con objects "+e);
}
tf=new TextField[6][5];
userIDLbl = new Label();
userNameLbl = new Label();
userNameTxt = new TextField(3);
userIDTxt = new TextField(3);
title = new Label();
search = new Button();
previous = new Button();
nextPage = new Button();
reset = new Button();
exit = new Button();
label1=new Label();
label2=new Label();
label3=new Label();
label4=new Label();
label5=new Label();
totalrecords=new Label();
recPos = 1;
userIDTxt.setCursor(java.awt.Cursor.getPredefinedCursor(java.awt.Cursor.TEXT_CURSOR));
add(userIDTxt);
userIDTxt.setFont(new Font("DialogInput", Font.PLAIN, 17));
userIDTxt.setBounds(120,60,108,26);
userNameTxt.setCursor(java.awt.Cursor.getPredefinedCursor(java.awt.Cursor.TEXT_CURSOR));
add(userNameTxt);
userNameTxt.setFont(new Font("DialogInput", Font.PLAIN, 17));
userNameTxt.setBounds(348,60,108,26);

search.setLabel("Search");
add(search);
search.setBackground(java.awt.Color.lightGray);
search.setBounds(480,60,93,24);

setLocation(295,115); // width and height
setSize(570,508);// size of the window
setVisible(true);
setLayout(null);
userNameLbl.setText("UserName");
add(userNameLbl);
userNameLbl.setBounds(264,60,66,28);

add(totalrecords);
totalrecords.setForeground(java.awt.Color.blue);
totalrecords.setFont(new Font("DialogInput", Font.PLAIN, 12));
totalrecords.setBounds(175,483,280,24);
userIDLbl.setText("UserID");
userIDLbl.setBounds(48,60,60,28);
add(userIDLbl);

title.setText("User Search");
add(title);
title.setForeground(java.awt.Color.blue);
title.setFont(new Font("Dialog", Font.BOLD, 18));
title.setBounds(252,24,115,19);
previous.setLabel("Previous");
add(previous);
previous.setBackground(java.awt.Color.lightGray);
previous.setBounds(84,444,93,26);
nextPage.setLabel("NextPage");
add(nextPage);
previous.setEnabled(false);
nextPage.setBackground(java.awt.Color.lightGray);
nextPage.setBounds(444,444,93,26);
nextPage.setEnabled(false);
reset.setLabel("Reset");
add(reset);
reset.setBackground(java.awt.Color.lightGray);
reset.setBounds(264,444,93,26);
exit.setLabel("Exit");
//add(exit);
exit.setBackground(java.awt.Color.lightGray);
exit.setBounds(432,466,93,26);
label1.setText("UserID");
add(label1);
label1.setBounds(55,118,58,22);
label1.setForeground(java.awt.Color.blue);
label1.setFont(new Font("DialogInput", Font.PLAIN, 15));

label2.setText("Password");
add(label2);
label2.setBounds(150,118,74,22);
label2.setForeground(java.awt.Color.blue);
label2.setFont(new Font("DialogInput", Font.PLAIN, 15));

label3.setText("Name");
add(label3);
label3.setBounds(270,118,50,22);
label3.setForeground(java.awt.Color.blue);
label3.setFont(new Font("DialogInput", Font.PLAIN, 15));

label4.setText("Authority");
add(label4);
label4.setBounds(355,118,82,22);
label4.setForeground(java.awt.Color.blue);
label4.setFont(new Font("DialogInput", Font.PLAIN, 15));

label5.setText("Citizen-Code");
add(label5);
label5.setBounds(460,118,110,22);
label5.setForeground(java.awt.Color.blue);
label5.setFont(new Font("DialogInput", Font.PLAIN, 15));

search.addActionListener(this);
nextPage.addActionListener(this);
reset.addActionListener(this);
exit.addActionListener(this);
previous.addActionListener(this);
userIDTxt.addTextListener(this);
userNameTxt.addTextListener(this);

previous.setEnabled(false);
nextPage.setEnabled(false);
int xPos;
for(int i=0;i<6;i++)
{
xPos=1;
for(int j=0;j<5;j++)
{
tf[i][j] = new TextField();
tf[i][j].setFont(new Font("DialogInput", Font.PLAIN, 17));
tf[i][j].setVisible(false);
tf[i][j].setEditable(false);
add(tf[i][j]);

if(j==2)
{
tf[i][j].setBounds(48*xPos,50*(i+3),115,25);//from left //gap of tf between row to row // gap starting between tfs in row //height of tf //i+3 = starting of tf columns from height
xPos = xPos+1;
}
else if(j==3)
{
tf[i][j].setBounds(63*xPos,50*(i+3),40,25);
xPos = xPos+1;
}
else if(j==4)
{
tf[i][j].setBounds(65*xPos,50*(i+3),135,25);//from left //gap of tf between row to row // gap starting between tfs in row //height of tf //i+3 = starting of tf columns from height
xPos = xPos+1;
}
else
{
tf[i][j].setBounds(48*xPos,50*(i+3),85,25);
xPos = xPos+2;
}
}
}
}
/*
Starting of both nextpage and previous button methods
*/
public void displayRecords()
{
//nextP=true;
for(int i=0;i<6;i++)
{
for(int j=0;j<5;j++)
{
tf[i][j].setVisible(false);
}
}
show();
try
{
if(recPos==1)
previous.setEnabled(false);
rs.absolute(recPos);
i=0;
do
{
if (rs.isLast())
{
nextPage.setEnabled(false);
}
for(int j=0;j<5;j++)
{
tf[i][j].setText(rs.getString(j+1));
tf[i][j].setVisible(true);
}
i++;
}while (i<6 && rs.next());
}
catch(Exception e)
{
System.out.println("Exc in displayrecords() "+e);
}
invalidate();
show();
}
/*
End of both nextpage and previous button methods
*/
public void actionPerformed(ActionEvent ae)
{
String sqlString=null;

if(ae.getSource()==search)
{
for(int i=0;i<6;i++)
{
for(int j=0;j<5;j++)
{
tf[i][j].setVisible(false);
}
}
nextPage.setEnabled(true);
previous.setEnabled(false);
if(!(userIDTxt.getText().equals("") && userNameTxt.getText().equals("")))
{
sqlString = "select * from users where userid like '" + userIDTxt.getText()+ "%' and name like '" + userNameTxt.getText() + "%'";
}
if(!(userIDTxt.getText().equals("")) && userNameTxt.getText().equals(""))
{
sqlString = "select * from users where userid like '" + userIDTxt.getText()+ "%'";
}
if(userIDTxt.getText().equals("") && !(userNameTxt.getText().equals("")))
{
sqlString = "select * from users where name like '" + userNameTxt.getText()+ "%'";
}
if(userIDTxt.getText().equals("") && userNameTxt.getText().equals(""))
{
sqlString = "select * from users";
}
try
{
recCount=0;
recPos=1;
if(nextP)
{
rs.close();
nextP = false;
}
rs = st.executeQuery(sqlString);

while(rs.next())
{
recCount++;
}

totalrecords.setText("Total No of Records Found "+recCount);
System.out.println("Record Count" + recCount);
if(recCount<6)
{
previous.setEnabled(false);
nextPage.setEnabled(false);
}
if(recCount<1)
{
System.out.println("No Data Found ");
(new NoData(new Frame(), true)).setVisible(true);
System.out.println("No Data Found ");
}
nextP=true;
displayRecords();
}
catch(Exception e)
{
System.out.println("in search method "+e);
}
//nextPage.setEnabled(true);
System.out.println(sqlString);
}
if(ae.getSource()==nextPage)
{
previous.setEnabled(true);
recPos = recPos+6;
displayRecords();
show();
}
if(ae.getSource()==previous)
{
nextPage.setEnabled(true);
recPos = recPos-6;
displayRecords();
show();
}
if(ae.getSource()==reset)
{
//new UserModify();
previous.setEnabled(false);
nextPage.setEnabled(false);
totalrecords.setText("");
for(int i=0;i<6;i++)
{
for(int j=0;j<5;j++)
{
tf[i][j].setVisible(false);
}
}
userNameTxt.setText("");
userIDTxt.setText("");
}
if(ae.getSource()==exit)
{
closeconnection();
setVisible(false);
}
}
public static void main(String args[])
{
new UserSearch();
}
public static void closeconnection()
{
try
{
myConus1.con.close();
System.out.println("Con Closed");
}
catch(Exception eno)
{
//System.out.println(eno);
}
} //closeconnection();
public void keyPressed(KeyEvent ke)
{
//System.out.println(ke);
}

public void keyReleased(KeyEvent ke)
{
}

public void keyTyped(KeyEvent ke)
{
}

public void textValueChanged(TextEvent te)
{
if(te.getSource()==userIDTxt)
if(userIDTxt.getText().length()>=8)
userIDTxt.transferFocus();
if(te.getSource()==userNameTxt)
if(userNameTxt.getText().length()>=30)
userNameTxt.transferFocus();
}
}
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

1. if using oracle:
"select x,y from table order by rownum desc"
then you can iterate through the first 8 rows(which are actually the last 8 rows)
2. if you have a timestamp field in your table:
"select x,y from table order by timestampfield desc"
then you can iterate through the first 8 rows
3. if have no other alternative(you will have to select all the records and only use the last 8!)


for 1 and 2 you can statement.setMaxRows(8);
Jamie

[This message has been edited by Jamie Robertson (edited September 13, 2001).]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

4. If you have a primary key that is autonumber or some type of incrementing number then you could:
"select x,y from table order by id desc"
then you can iterate through the first 8 rows and again setMaxRows(8).
Jamie
Pranit Saha
Ranch Hand

Joined: Sep 09, 2001
Posts: 130
Hi jamie..
Thanks for ur sugession.. it has helped me a lot to solve my problem..
Pranit..
Originally posted by Jamie Robertson:
4. If you have a primary key that is autonumber or some type of incrementing number then you could:
"select x,y from table order by id desc"
then you can iterate through the first 8 rows and again setMaxRows(8).
Jamie

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrieving last 8 rows in a table
 
Similar Threads
Question about deleting rows from a table
Editing the fields of the last row in JTable
How to move the selected row in Jtable to be the first row in the table
generate xml to java Bean
JTable and getValueAt(int, int)