File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Servlets and the fly likes How to get all request stored in databse between two date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "How to get all request stored in databse between two date" Watch "How to get all request stored in databse between two date" New topic
Author

How to get all request stored in databse between two date

Lijoy John
Ranch Hand

Joined: Jul 24, 2001
Posts: 32
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
Greenhorn

Joined: May 11, 2001
Posts: 22
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

Joined: Jul 24, 2001
Posts: 32
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

 
 
subject: How to get all request stored in databse between two date
 
Similar Threads
Compare two custom date input fields each having 3 inputText fields using validator
Converting string to util.date & then sql.date to query a table
Converting String to util.Date and then sql.Date
ResultSet rs = prestatement.executeQuery()
How to get records from a table in between dates