-1

I am trying to show the details inserted into my database on a weppage

I have tried converting the date to sql date

public static List<Detail> getAllRecords(){
    List<Detail> list = new ArrayList<Detail>();

    try{
        Connection con = getConnection();
        PreparedStatement ps = con.prepareStatement("select * from studentDetails");
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            Detail u = new Detail();
                        java.sql.Date dbSqlDate = rs.getDate("dob"); 

                        u.setPassword(rs.getInt("password"));  
                        u.setName(rs.getString("name"));  
                        u.setLastname(rs.getString("lastname"));  
                        u.setUsername(rs.getString("username"));  
                        u.setEmail(rs.getString("email"));  
                        u.setAddress(rs.getString("address"));
                        System.out.println("dbSqlDate=" + dbSqlDate);
                        u.setAge(rs.getInt("age")); 
                        list.add(u);
        }
    }catch(Exception e){System.out.println(e);}
    return list;
}

I expect the output to be 02/03/2000 , but it's not working

Matthew
  • 1,905
  • 3
  • 19
  • 26
Solomon Akinbiyi
  • 7
  • 2
  • 2
    "it's not working" is practically useless in describing the behavior that is observed, and in determining why the expected behavior is not being achieved. what is the datatype of the "dob" column? i.e. don't use getDate if it's not date/datetime/timestamp datatype. It sseems like a bad idea to store "age" column, since the age (in years) can easily be derived as the difference (in years) between current date and dob. – spencer7593 Jun 06 '19 at 20:12
  • we can use SimpleDateFormat to get a string in a particular format ... https://stackoverflow.com/questions/12781273/what-are-the-date-formats-available-in-simpledateformat-class (Java 8 adds DateTimeFormatter) – spencer7593 Jun 06 '19 at 20:18
  • yeah thanks I will remove the date but how do i show the date of birth – Solomon Akinbiyi Jun 06 '19 at 20:28
  • somehow i missed the error message included in the question title. i was looking for a description of the observed behavior in the body of the question, and all i saw was "it's not working". it would have helped me out of the question itself had included the error message, – spencer7593 Jun 06 '19 at 20:46
  • Possible duplicate of [handling DATETIME values 0000-00-00 00:00:00 in JDBC](https://stackoverflow.com/questions/782823/handling-datetime-values-0000-00-00-000000-in-jdbc) – Mark Rotteveel Jun 07 '19 at 15:25

1 Answers1

1

Quickfix is to add ?zeroDateTimeBehavior=convertToNull to the MySQL JDBC URL when establishing the connection.

The start of a longer answer would point out that...

By default, MySQL allows special "zero" date values to be stored in DATE / DATETIME columns. When retrieved, those values are strings in format '0000-00-00'. When pulled into Java through JDBC, Java doesn't allow java.util.Date values to have that value.

The "zero" date behavior of MySQL can be disallowed by some settings of sql_mode. We may also want to ensure that column allows for NULL values (i.e. if the column is not defined as NOT NULL), and/or modify the column definition to provide a non-zero default value ...

The "zero" date values already in the column can be replaced with NULL value, e.g.:

UPDATE `studentDetails` SET dob = NULL WHERE dob = '0000-00-00'

zeroDateTimeBehavior is documented here:

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

MySQL DATE behavior documented here

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

excerpt:

MySQL permits you to store a “zero” value of '0000-00-00' as a "dummy date." This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • another option (less desirable) would be to handle it in the code shown in the question... retrieve the dob column as a string `String dob = rs.getString("dob")` then do a comparison `"0000-00-00".equals(dob)`, and if true, set `dbSqlDate = null;` else do `DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); dbSqlDate = sdf.parse(dob);` – spencer7593 Jun 07 '19 at 16:09