how to save & retrieve a null date from mysql db using jdatechooser

Question

I am developing a simple hospital management system for my project with netbeans, so here is my problem in brief. When a patient admits and discharge from a hospital his/her date of admission and date of discharge should be recorded. so I put two jDateChoosers for admission and discharge. So when a new patient admits I have to keep the date of discharge jDateChooser blank and save.

• I changed the date format of the jDateChoosers to "yyyy-MM-dd"

• (I use mysql control center 0.9.4 to create my database and tables) firstly I tried saving the date by changing the discharge column(dod) data type to 'date' then an exception jumped as follows.

  com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'dod' at row

• Then I tried changing the data type to 'varchar' and allowed null values, ok no problem this time it saved blank in the db.

• When retrieving patients' info I have coded the admission and discharge date to appear in the two jDateChoosers seperately. but an exception generates like this

  java.text.ParseException: Unparseable date: "" at java.text.DateFormat.parse(DateFormat.java:357) at Patient.pidKeyReleased(Patient.java:455) at Patient.access$000(Patient.java:23) at Patient$1.keyReleased(Patient.java:118) at java.awt.Component.processKeyEvent(Component.java:6466)

This is the code and the imports involved with the date problem :

import java.text.DateFormat;  
import java.text.SimpleDateFormat;  
import java.util.Date;`

This is coded in the save button

try {
        Statement stmt = connection.hosp().createStatement();
        String dt = ((JTextField)doa.getDateEditor().getUiComponent()).getText();
        String dt1 = ((JTextField)dod.getDateEditor().getUiComponent()).getText();

        stmt.executeUpdate("insert into patients values ('"+pid.getText()+"','"+nic.getText()+"','"+fname.getText()+"','"+lname.getText()+"','"+age.getText()+"','"+address.getText()+"','"+contact.getText()+"','"+ptype.getSelectedItem()+"','"+wardname.getText()+"','"+wardno.getText()+"','"+roomno.getText()+"','"+diagnosis.getText()+"','"+doctor.getText()+"','"+consultant.getText()+"','"+dt+"','"+dt1+"')");

} catch (Exception e) {
                e.printStackTrace();
            }

This is the search Code and it is coded in a text box and the action is keyrelease. This is where the above stated exception java.text.ParseException: Unparseable date: "" comes.

try {
            Statement s = connection.hosp().createStatement();
            String ss = pid.getText().trim();

            ResultSet rs = s.executeQuery("select * from patients where patient_id like ('%"+ss+"%')");
            while(rs.next()){
             nic.setText(nics);
             fname.setText(fnames);
             lname.setText(lnames);
             .... etc
            String datevalue=rs.getString(16);         
            String datevalue1=rs.getString(17);            
            DateFormat formatter ;
            Date date, date1;  
            formatter = new SimpleDateFormat("yyyy-MM-dd");   
            date = (Date)formatter.parse(datevalue);    
            date1 = (Date)formatter.parse(datevalue1); 
            doa.setDate(date);    
            dod.setDate(date1);
            }

        }catch (Exception e) {
            e.printStackTrace();
            }

if there is any other method to solve this problem please mention it.

Answer 1

You still havent provided all the information, So I am assuming. Anyhow this is what I have found.

1. dt and dt1 columns are numbered as 15 and 16 from your insert statement.

   String datevalue = rs.getString(16);  // Supposed to be 15   
   String datevalue1 = rs.getString(17); // Supposed to be 16 
   

2. While getting data from columns use rs.getXXX("Column Name") (gives more visibility) instead of rs.getXXX("columnIndex").

3. Also use JDBC PreparedStatement. Currently your app does not avoid SQL Injection

4. Try not to use varchar for date datatype. Use Date Thats what they were designed for.