Forum:

JDBC and Relational Databases

Problem in using date in WHERE clause in SELECT query in Servlet.

Greenhorn

Posts: 6

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hello Friends!
I am new in this Forum and I have logged here for my problem.
I am developing a online system for applying for a post for my client.
But the problem is when I use 'date' in 'WHERE' clause in 'SELECT' query in servlet which is used to allow user to re-print the registration page, it is giving null pointer exception.
In this when I fill correct information it is giving null pointer exception otherwise result is 'records not exists'.
I am pasting all the codes, the print.java servlet and also print.html file for better help.

print.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Print the acknowledgement again</title>
<style type="text/css">
tr.row1{background-color: #CCE6FF}
tr.row2{background-color: #F9FDFE}
tr.head1{background:#4634EB;color:#BADAFE}
</style>
<script type="text/javascript">
//--main function--
function validate()
{
 var regno= document.printfrm.regno;
 if(!regno_val(regno)){ return false;}
 if(!dob_val()){ return false;}
}
//--end of main function--
//--functions called in main function--

<body>
<form name="printfrm" method="post" action="/factory/print" autocomplete="off">
<table align="center" width="600" cellspacing="3" cellpadding="3" style="border-width: 2px; border-color:#EEF8FC; border-style: solid;">
<tr>
<td colspan="6">
<table>
<td align="left" colspan="4" ><img src="img/xxx.gif" height="80" width="80"></td>
<td align="center" colspan="4" bgcolor="red">           
  <b>Client company             
          Client address </b>       
                           </td>
<td align="right" colspan="5" ><img src="img/x2.jpg" height="80" width="80"></td>
</table>
<hr>
</td>
</tr>
<tr class="row1">
<td colspan="3">Registration Number:</td>
<td colspan="2"><input type="text" name="regno" id="regno" size="30" /></td>
</tr>
<tr class="row2">
<td colspan="3">Date of Birth:</td>
<td colspan="2">
<select name="seldobday">
<option selected="selected" value="0">Day</option>
<option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option>
<option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option>
<option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option>
<option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option>
<option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option>
<option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option>
<option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option>
<option value="29">29</option><option value="30">30</option><option value="31">31</option>
</select>
<select name="seldobmon">
<option selected="selected" value="0">Month</option>
<option value="01">January</option><option value="02">February</option><option value="03">March</option><option value="04">April</option>
<option value="05">May</option><option value="06">June</option><option value="07">July</option><option value="08">August</option>
<option value="09">September</option><option value="10">October</option><option value="11">November</option><option value="12">December</option>
</select>
<select name="seldobyr" >
<option selected="selected" value="0">Year</option>
<option value="1955">1955</option><option value="1956">1956</option><option value="1957">1957</option><option value="1958">1958</option>
<option value="1959">1959</option><option value="1960">1960</option><option value="1961">1961</option><option value="1962">1962</option>
<option value="1963">1963</option><option value="1964">1964</option><option value="1965">1965</option><option value="1966">1966</option>
<option value="1967">1967</option><option value="1968">1968</option><option value="1969">1969</option><option value="1970">1970</option>
<option value="1971">1971</option><option value="1972">1972</option><option value="1973">1973</option><option value="1974">1974</option>
<option value="1975">1975</option><option value="1976">1976</option><option value="1977">1977</option><option value="1978">1978</option>
<option value="1979">1979</option><option value="1980">1980</option><option value="1981">1981</option><option value="1982">1982</option>
<option value="1983">1983</option><option value="1984">1984</option><option value="1985">1985</option><option value="1986">1986</option>
<option value="1987">1987</option><option value="1988">1988</option><option value="1989">1989</option><option value="1990">1990</option>
<option value="1991">1991</option><option value="1992">1992</option><option value="1993">1993</option><option value="1994">1994</option>
</select>
</td>
</tr>
<tr class="row1">
<td align="center" colspan="6"><input type="submit" value="Submit" onClick="return validate()" >
<input type="reset" value="Reset">
</tr>
</table>
</form>
</body>
</html>

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
now print.java

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.text.*;
import java.util.regex.*;
import java.math.BigDecimal;
import javax.servlet.*;
import javax.servlet.http.*;

public class print extends HttpServlet
{
 public void init(ServletConfig config) throws ServletException
 {
  super.init(config);
 }
 /**Process the HTTP Get request*/
 public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException
 {
  PrintWriter out = response.getWriter();
  try 
  {
   String regno = request.getParameter("regno");
   long regno_long = Long.parseLong(regno);
   //--dob code--
   Date dob=null;
   String dob_day = request.getParameter("seldobday");
   String dob_mon = request.getParameter("seldobmon");
   String dob_yr = request.getParameter("seldobyr");
   String dob_string = dob_yr+"-"+dob_mon+"-"+dob_day;
   dob = new SimpleDateFormat("yyyy-MM-dd").parse(dob_string);
   //long t= dob.getTime();
   //java.sql.Date dob2 = new java.sql.Date(t);
   
   //--end of dob code--
   // connecting to database
   Connection con = null;  
   //Statement stmt = null;
   ResultSet rs = null;
   String reg_num=null,full_name=null,post_app=null,img_name=null;
   Class.forName("com.mysql.jdbc.Driver");
   con =DriverManager.getConnection("jdbc:mysql://localhost/fac_db","sar","sar");//db name is fac_db uname and pass is sar
   //stmt = con.createStatement();
   //rs = stmt.executeQuery("SELECT reg_no,post,name,phtsign FROM sar WHERE reg_no='"+regno+"' AND dob='"+new java.sql.Date(dob2.getTime())+"'");
   String sql="SELECT reg_no,post,name,phtsign FROM sar WHERE reg_no=? AND dob=?";
   PreparedStatement stmt =con.prepareStatement(sql);
   stmt.setLong(1,regno_long);
   stmt.setDate(2,new java.sql.Date(dob.getTime()));
   rs = stmt.executeQuery();
   //out.println(sql);
   //out.println(rs);
   //out.println(stmt);
   // displaying records
   //out.println("query runs");
   if(!rs.first())
   {
    out.println("<html>");
    out.println("<head><title>Acknowledgement</title></head>");
    out.println("<body>");
    out.println("</br></br></br>");
    out.println("<table align=\"center\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"800\">");
    out.println("<tr><th colspan=\"2\"><h3>Acknowledgement</h3></th></tr>");
    out.println("<tr><td colspan=\"2\" align=\"center\"><b>No Record Found.</b></td></tr>");
    out.println("<tr><td colspan=\"2\" align=\"center\">Please try again.</td></tr>");
    out.println("</body></html>");
   }
   else
   {
    while(rs.next())
    {
 	 reg_num= rs.getObject(1).toString();
	 post_app= rs.getObject(2).toString();
	 full_name= rs.getObject(3).toString();
	 img_name= rs.getObject(4).toString();
    }
    if(post_app.equals("X"))
     post_app="XXX";
    else if(post_app.equals("Y"))
     post_app="YYY";
    else if(post_app.equals("Z"))
     post_app="ZZZ";
    String src="images/"+img_name;
    out.println("<html>");
    out.println("<head><title>Acknowledgement</title></head>");
    out.println("<body>");
    out.println("</br></br></br>");
    out.println("<table align=\"center\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"800\">");
    out.println("<tr><th colspan=\"2\"><h3>Acknowledgement</h3></th></tr>");
    out.println("<tr><td colspan=\"2\">Shri <b>"+full_name+"</b> you have been selected for appearing in written test for the post of <b>"+post_app+"</b> at Client.Your Registration Number is <b>"+reg_num+"</b>. </td></tr>");
    out.println("<tr><td></br></br></br><br></td></tr>");
    out.println("<tr><td>Signature of candidate____________________________</td>");
    out.println("<td><img src="+src+" alt=\"error\" width=\"130\" height=\"150\" ></td></tr>");
    out.println("<tr><td></br></br><br></td></tr>");
    out.println("<tr><td></br></br></td></tr>");
    out.println("<tr><td align =\"center\" colspan=\"2\">Please print two copies of this Acknowledgement and preserve one copy.The Registration Number given will be used in the entire Recruitment Process.</td></tr>");
    out.println("<tr><td></br></br></td></tr>");
    out.println("<tr><td align=\"center\" colspan=\"2\"><input type=\"button\" value = \"Print\" onClick=\"window.print()\"/></td></tr>");
    out.println("<tr><td align =\"center\" colspan=\"2\">This page cannot be refreshed.If you need it for future Printing please Save it.<td/><tr/></table>");
    out.println("</body></html>");
   }
   rs.close();
   stmt.close();
   con.close();
  }
  catch (Exception e) 
  { 
   e.printStackTrace();
   out.println(e);
  }
 }
}

in my table reg_no is bigint(40) and dob is of 'date' type. I am using mysql and xampp.
Please help me, you can check this code by implementing this code, and then date you have entered and regno in the db,use exactly that in print.html and run this code, it will give you error null pointer exception, and if you use only regno then it is running without error. and if you use regno with wrong date then again it is running without error and give 'no record find' I am not understanding what is the problem in 'date' whenever i am using right value of 'date' it is giving error.
Please help me.

Sarthak Gupta

Greenhorn

Posts: 6

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

what do you mean by that!

Neeraj Dhiman

Ranch Hand

Posts: 68

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

please use code tag for code.

Correct Me if i am wrong

Paul Clapham

Sheriff

Posts: 28394

100

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Sarthak Gupta wrote:what do you mean by that!

That means: Just because your code happens to be in a servlet, that doesn't make it a servlet problem.

You've probably got one of the standard JDBC problems. Unfortunately your code isn't in the code tags, so it's very hard to read. There's also a lot of code there which doesn't even have anything to do with Java, which just makes it harder to locate your problem.

Anyway what you have to do is to look at the stack trace. It should tell you exactly what line of code threw the exception, and then you can start looking at that line to see what variable is unexpectedly null. You do have a stack trace, don't you?

Java 8 (verified skill)
Skill verified by Paul Clapham

Sarthak Gupta

Greenhorn

Posts: 6

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I know Java has built-in stack trace but how to use that, I don't know.
Please guide me.

Sarthak Gupta

Greenhorn

Posts: 6

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Neeraj Dhiman wrote:please use code tag for code.

Please guide me how to use code tag.
I'm new to this Forum.

Martin Vashko

Sheriff

Posts: 3837

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Sarthak, and welcome to the Ranch!

I've added the code tags to your post, so that you can see how does that look like. See UseCodeTags (<-- click) to learn how to use them on this site.

You're using e.printStackTrace() in your catch block, which is fine - that way the stack trace should be written to a log somewhere on your web server. I don't know xampp myself, so I cannot tell you where you'll find it, but you might try to find it out in xampp documentation. Find the stack trace in your logs and post it here.

Sarthak Gupta

Greenhorn

Posts: 6

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Martin Vajsar wrote:Hi Sarthak, and welcome to the Ranch!

I've added the code tags to your post, so that you can see how does that look like. See UseCodeTags (<-- click) to learn how to use them on this site.

You're using e.printStackTrace() in your catch block, which is fine - that way the stack trace should be written to a log somewhere on your web server. I don't know xampp myself, so I cannot tell you where you'll find it, but you might try to find it out in xampp documentation. Find the stack trace in your logs and post it here.

Thank you for your support Martin.

the error is(tracked form printstacktrace)- java.lang.NullPointerException

Whenever I use the value which is stored in DB it is giving error otherwise it is display no record found.

I think I must post this to JDBC Forum.

Sarthak Gupta

Greenhorn

Posts: 6

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hey when I checked in JDBC Forum, my thread is already there, so now for all JDBC problem solvers-
please solve my also.

When ever I run the above code I got error(which I wrote just above this post), please help me

Martin Vashko

Sheriff

Posts: 3837

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Yes, this thread already is in the JDBC forum. One of the moderators here moved your post when he noticed it is indeed a JDBC related problem.

Did you actually find the stack trace in your logs? In this case, there will be not only the exception name (NullPointerException), but also the line number on which the exception occurred. This would help tremendously to track down the problem. It is much more probable that the "JDBC solvers" will help you if you provide this information (see also TellTheDetails).

In any case, take a closer look at this code fragment of yours:

There are several problems with it:

1) If any of these database fields is NULL, you'll get a NullPointerException, as you cannot use toString() with nulls.

2) This code loops over all records returned by the query, overwriting the values in your variables in each go without actually using them. Only the values from the last record are kept in the variables and used by the later code. This is suspicious and even if it was a desired functionality, it should be done differently.

3) Though you close the resources (rs, stmt, con), you don't do this in a finally block, therefore they don't get closed when an exception occurs.

Consider Paul's rocket mass heater.