• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to get all request stored in databse between two date

 
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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


reply
    Bookmark Topic Watch Topic
  • New Topic