How to get all request stored in databse between two date

[Lijoy John]

I am having a servlet in which i am I accepting two form values i.e 'fromdate' and 'todate' by using req.getParameter(fromdate & todate).
Now i have to query a table called REQUEST which has 4 columns like req_id, name, createdate and status.
In the servlet I would like to retrieve all the request during that period say for example. between 1st July to 31stJuly, and then display it on the browser with all req_id(first column in the table) having a hyperlink to another XML file which consists of all the details when a user had send his request in the month of July.
The output on the browser is preferable in a Table format which has to be created dynamically depending the number of request.
Kindly let me know how I can proceed with this .
Thanks,
Regards,
John

[Aman Rustogi]

ur query would be sumthing like :
'select * from [table] where [column] >= 'mm/dd/yyyy' AND [column] <= 'mm/dd/yyyy'
code :
try {
[statement object] = [connectionObject].createStatement ( "[ur query]" );
[resultset object] = [statementObject].getResultset();
out.println ( "\n<table border='0' cellspacing='2' cellpadding='4' width='100%'>");
while [resultsetObject.next()]
{
out.println( "\n<tr height='25'>");
out.println ( "\n\t<td><a href=[ur link]>" + [resultsetObject].getString ([index]) + "</a></td>" );
out.println ( "\n</tr>")
}
}//end of try
catch ( SQLException e ) {
System.out.println ( e.getMessage() )
}
catch ( NullPointerException e ) {
System.out.println ( e.getMessage() )
}
finally {
[resultsetObject].close();
[statementObject].close();
}
I hope this shall help u out with ur requirement!
cheers
Aman

[Lijoy John]

Hi Aman,
Thanks for the reply.
Temporarily I had taken off the hyperlink facility and have tried the following way:
I am having a servlet which accepts two Dates i.e DateFrom and DateTo. I have to get all the request from the database between those dates. and the subsequent output to be shown in a browser in the form of a table . There are four columns which are
id NUMBER
name varchar
status NUMber
Create_date DATE
The following is the code :
public String allRequest(String from, String to) {
/*
* 'from' and 'to' are coming from req.getParameter(from/to);
*/
String sDateFormatter = "mm/dd/yy";
String fDate = from;
String toDate = to;
java.text.SimpleDateFormat formatter = new SimpleDateFormat(sDateFormatter);

try{
ffdate = formatter.parse(fDate); // util.Date object
ttdate = formatter.parse(toDate); //util.Date object

long fSeconds = ffdate.getTime();
long tSeconds = ttdate.getTime();
fromdate = new java.sql.Date(fSeconds); // sql.Date object
todate = new java.sql.Date(tSeconds); // sql.Date object
System.out.println(fromdate);
System.out.println(todate);
}catch(ParseException ex){}
String query ="SELECT * FROM REQUEST WHERE CREATE_DATE BETWEEN ? and ? ";
StringBuffer sb = new StringBuffer("");
PreparedStatement pstate= null;
Connection con = null;
String nu = null;
try{
con = getConnection(); // this gets the coonection from pool
if(con == null) {
System.out.println("Unable to establish DB connection!");
System.out.println("Cannot continue - Exiting");
return nu; // not sure about this
} else {
pstate = con.prepareStatement(query);
pstate.setDate(1, fromdate);
pstate.setDate(2, todate);
ResultSet rs = pstate.executeQuery();
sb.append("<TABLE>");
sb.append("<TR>");
// Showing till here on the console as I was checking using System.out.println() statements and not taking rs.next() and further.
while (rs.next()) {
System.out.println("**1**");
String requ_id = String.valueOf(rs.getInt(1));
String domaain = rs.getString(3);
String statuss = String.valueOf(rs.getInt(4));
Date dt = rs.getDate(9);
String daate = dt.toString();
sb.append("<TD>"+ requ_id + "</TD><TD>"
+domaain+ "</TD><TD>" + statuss + "</TD><TD>" + daate
+ "</TD>");
sb.append("</TR>");
} // while

sb.append("</TABLE>");

}
return sb.toString();
}
It would be kind to let me know how to proceed further or if there is a better approach to this. say for example I get 10 rows from the database, then all those rows( with 4 columns) should be shown on the browser.
Thanks,
Regards,
John

Originally posted by Aman Rustogi:
ur query would be sumthing like :
'select * from [table] where [column] >= 'mm/dd/yyyy' AND [column] <= 'mm/dd/yyyy'
code :
try {
[statement object] = [connectionObject].createStatement ( "[ur query]" );
[resultset object] = [statementObject].getResultset();
out.println ( "\n<table border='0' cellspacing='2' cellpadding='4' width='100%'>");
while [resultsetObject.next()]
{
out.println( "\n<tr height='25'>");
out.println ( "\n\t<td><a href=[ur link]>" + [resultsetObject].getString ([index]) + "</a></td>" );
out.println ( "\n</tr>")
}
}//end of try
catch ( SQLException e ) {
System.out.println ( e.getMessage() )
}
catch ( NullPointerException e ) {
System.out.println ( e.getMessage() )
}
finally {
[resultsetObject].close();
[statementObject].close();
}
I hope this shall help u out with ur requirement!
cheers
Aman