0

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.

Dopa
  • 5
  • 2
MRUNAL MUNOT
  • 395
  • 1
  • 5
  • 18
  • 3
    For the last question, you could always read the documentation, https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/SimpleDateFormat.html – Roger Gustavsson Dec 09 '20 at 15:41
  • *"One more question"* You're welcome to ask many questions on SO, so long as they are well researched and adequately expressed, but for the site to work best, each question should be asked on a separate question thread. Please [edit] this question to remove the 2nd one, and start a new thread about it **if you need to after checking** the [Java Docs for `SimpleDateFormat`](https://docs.oracle.com/en/java/javase/14/docs/api/java.base/java/text/SimpleDateFormat.html#(java.lang.String)). – Andrew Thompson Dec 09 '20 at 15:43
  • 2
    Mrunal, what results are you getting now? Is the problem that you aren't getting _any_ data from the DB, that the `DOB` field is not returning what you expect, or that you're getting an error at a specific line? (btw the `'MM'` is a convention for specifying date formats, the doc link above goes into more detail but `MM` -> two-digit number, `MMM` -> short name of month, `MMMM` -> full name) – Jim J Dec 09 '20 at 15:51
  • All other data I am getting but I have problem with date which i am storing while inserting information. I am used Calendar to store date but when i want to retrive and want to show in again in datechooser. But how ? – MRUNAL MUNOT Dec 09 '20 at 15:56
  • *"All other data I am getting.."* Tip: Add @JimJ (or whoever, the `@` is important) to *notify* the person of a new comment. – Andrew Thompson Dec 09 '20 at 16:07
  • 1
    @MRUNALMUNOT can you tell what's stored in your database? It looks like it might be a string. Are you getting a specific error, like a type error, or is the type conversion (from `String DOB` to `Timestamp Sdb`) doing something unexpected with your data (like returning a blank string, or the wrong date?) – Jim J Dec 09 '20 at 16:11
  • @JimJ I am edited question and added image. @Abra data type in database is `DATE` only. – MRUNAL MUNOT Dec 09 '20 at 16:55
  • 2
    I recommend you don’t use `SimpleDateFormat`, `Date` and `Calendar`. Those classes are poorly designed and long outdated, the firstr in particular notoriously troublesome. Instead use `LocalDate` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). See [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). – Ole V.V. Dec 10 '20 at 02:29
  • @MRUNALMUNOT - Your code makes your application vulnerable to SQL Injection. Make sure you apply the recommendations from [this answer](https://stackoverflow.com/a/65396571/10819573). – Arvind Kumar Avinash Dec 23 '20 at 21:03

4 Answers4

3

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

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
1

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.

Jim J
  • 546
  • 3
  • 11
  • I am applied code given by you. Now it is not giving error loading all the field except date field is remain blank. – MRUNAL MUNOT Dec 09 '20 at 17:39
  • @MRUNALMUNOT can you tell what is in rs.getString("DOB")? I'm not familiar with all of your code, but if I _was_ working in an unfamiliar codebase, the next steps I'd take would be (a) making sure that if I pass a fixed, dummy string to `sdf.parse()` (like `sdf.parse("2020-01-01")`) it shows up in the calendar field and (b) putting the `String` value of `rs.getString("DOB")` somewhere _without_ a type conversion, so I can see what is being passed to `sdf.parse()`. Like, assign the value of `rs.getString("DOB")` to the textarea on your form, or logging it to the console. – Jim J Dec 09 '20 at 17:47
  • DOB-date of birth of student. Which stored using that Calendar in java swing in database. `sdf.parse("2020-01-01")` I am tried it but it is showing other date irrelated with it. – MRUNAL MUNOT Dec 09 '20 at 18:06
  • another date like 10-jun-1 something – MRUNAL MUNOT Dec 10 '20 at 20:25
  • @MRUNALMUNOT I assume the problem is that `sdf.parse()` is expecting a particular date format by default (like "01-01-2020", for example) but the string you're passing in doesn't match that format. What _exact_ string are you passing to `sdf.parse()` that is displaying `10-jun-1`? – Jim J Dec 10 '20 at 22:15
  • I am passing string like "2000-12-09". – MRUNAL MUNOT Dec 11 '20 at 11:15
1

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.

Arvind Kumar Avinash
  • 71,965
  • 6
  • 74
  • 110
0

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à

Abra
  • 19,142
  • 7
  • 29
  • 41
  • 1
    Mrunal Munot, if Abra’s guess is correct, you may want to look for a newer date picker component, one that supports java.time, the modern Java date and time API. JDateChooser is really old. I haven’t got experience with any myself. What I have seen of [LGoodDatePicker](https://github.com/LGoodDatePicker/LGoodDatePicker) looks good to me, for example. Search for others. – Ole V.V. Dec 10 '20 at 02:36