How can I retrieve date from database and convert to Calendar?

Question

I am doing code in Eclipse using Java Swing and MySQL. I am storing Date of birth using Calendar in database.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String DOB=sdf.format(db1.getDate());

I want to retrieve date from database and display in GUI for updating if user want to update.

How can I do that?

String idpop = JOptionPane.showInputDialog(null , "Enter Student ID to update record:");
int sid=Integer.parseInt(idpop);

Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/schl","root","root");

String sql = "select * from stud_info where ID='"+sid+"' ";
PreparedStatement ps=con.prepareStatement(sql);
                      
ResultSet rs = ps.executeQuery();
if(rs.next()) {
                            
                String Sid=rs.getString("ID");
                id1.setText(Sid);
                String Snm=rs.getString("Name");
                nm1.setText(Snm);
                                       
                SimpleDateFormat sdf = new SimpleDateFormat("dd-M-yyyy");
                java.util.Date date = sdf.parse("DOB");
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                db1.setCalendar(calender);         
                                   
                String Sem=rs.getString("Email");
                em1.setText(Sem);
                String Smb=rs.getString("MobNo");
                mb1.setText(Smb);
                String Saddr=rs.getString("Address");
                addr1.setText(Saddr);
                String Sssc=rs.getString("SSCMrks");
                ssc1.setText(Sssc);
                String Shsc=rs.getString("HSCMrks");
                hsc1.setText(Shsc);        
                       }

In that, I am trying for updating records, and for that, I am taking id from a user by pop-up and then It is loading data from the database but for dob, it giving error for parsing. So I want to know how to convert the date to Calendar?? I have removed the code of date after that it gave the current date and other data loading normally.

Answer 1

java.time

I recommend that you use java.time, the modern Java date and time API, for your date work.

        LocalDate date = rs.getObject("DOB", LocalDate.class);

This gives you an instance of the modern LocalDate type, which is much nicer to work with than the old Calendar and Date classes. At the same time it saves you from any parsing. Edit: Since JDBC 4.2 (out in 2014) the conversion from a date in SQL to a LocalDate is supported (implemented by MySQL for very long now). You’re now ready for the day when either your date picker gets updated to java.time or you pick a more modern date picker. In the meantime, if you need a Calendar for the date picker that you are currently using, convert like this:

        ZonedDateTime startOfDay = date.atStartOfDay(ZoneId.systemDefault());
        Calendar calendar = GregorianCalendar.from(startOfDay);

One more question. Why MM is capital in SimpleDateFormat("yyyy-MM-dd")?

No matter if using the modern DateTimeFormatter or the old and notoriously troublesome SimpleDateFormator a similar formatter in some other language than Java format pattern letters are case sensitive. If both upper case and lower case of a letter can be used, they have different meanings. For example:

• Lower case yyyy means year of era while upper case YYYY means week-based year (the year that the week number belongs to, not always the same as the calendar year that the date belongs to). Often lower case is for the most used meaning.
• Lower case mm is for minute of hour while upper case MM is for month of year (think: a month is longer than a minute).
• Lower case dd is for day of month (1–31 in the Gregorian calendar) while upper case DDD is for day of year (1–366) (think: the day of year number is typically greater and again not so often used).

Links

• Oracle tutorial: Date Time explaining how to use java.time.
• Related question: Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
• Example of a date picker that supports java.time: LGoodDatePicker
• Question about the difference between format pattern letters mm and MM: SimpleDateFormat ignoring month when parsing

Answer 2

According to the documentation, the DATE data type maps to class java.sql.Date. So you should use method getDate, in interface java.sql.ResultSet to retrieve the value of column DOB.

java.sql.Date dob = rs.getDate("DOB");

I'm guessing that db1 (in the code in your question) is a JDateChooser, so all you need to do is call method setDate

db1.setDate(dob);

et voilà

Answer 3

I think the problem is that java.util.Date date = sdf.parse("DOB"); is trying to parse the String "DOB", not the value of DOB in your database.

java.util.Date date = sdf.parse(rs.getString("DOB")); might work instead.

You can also try passing in a dummy date string (like "2020-12-09") to see if that shows up in your GUI, without an exception, and then working to figure out how to get the string from the DB to replace the dummy date string.

Abra's comment does suggest a possible future improvement: most databases have specific date/time/timestamp types you could use instead, which might offer more guarantees than converting to a string, storing a string, and then converting back into a Date.

Answer 4

Your code makes your application vulnerable to SQL Injection

Forget about date operations for a moment and focus on your JDBC code. The way you have passed the value of sid in your query, is a classic example of how SQL Injection works. You should use ? as the placeholder for parameters and pass the parameters using setter methods. That will also help you get rid of the single quotes that you have used explicitly. Learn more about using PreparedStatement from this tutorial.

Also, make a habit of using the try-with-resources Statement when you are dealing with some resource (e.g. DB connection, I/O stream etc).

Your code should be as follows:

String idpop = JOptionPane.showInputDialog(frame , "Enter Student ID to update record:");
int sid = Integer.parseInt(idpop);

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/schl","root","root");

String sql = "SELECT * FROM stud_info WHERE id =?";

try (PreparedStatement ps = con.prepareStatement(sql)) {

    ps.setString(1, sid); 
    ResultSet rs = ps.executeQuery();

    //...
} catch (SQLException e) {
    e.printStackTrace();
}

The existing answers have already provided you with the fix related to date operations. I have added a couple of more helpful links regarding your following question:

One more question. Why MM is capital in SimpleDateFormat("yyyy-MM-dd")?

SimpleDateFormat evaluates M as Month in year while m as Minute in hour. Check the documentation of SimpleDateFormat to learn more about various symbols related to date, time, timezone, timezone offset etc.

This is true even for the parsing/formatting API, DateTimeFormatter for the modern date-time types.