Java SQL cant insert Date and Time

Question

I keep getting this error

class java.time.format.Parsed cannot be cast to class java.time.LocalDate (java.time.format.Parsed and java.time.LocalDate are in module java.base of loader 'bootstrap')

I am trying to insert date and time values into my database.

My code:

String sTime = Helper.readString("Enter Closing Time (HH:MM:SS) > ");
            try {
                Time time = new Time(timeFormat.parse(sTime).getTime());
                String sDate = Helper.readString("Enter Date (YYYY-MM-DD) > ");
                LocalDate date = (LocalDate) dateFormat.parse(sDate);
                String event = Helper.readString("Enter Event > ");

                addStadium(id, name, category, time, date, event);

            } catch (ParseException e) {
            }




private void addStadium(String id, String name, String category, Time time, LocalDate date, String event) {
        try {
            String query = "INSERT INTO Stadium(ID, Name, Category, ClosingTime, DateUnavailable, Event) "
                    + "SELECT J.ID,J.Name, J.Category, J.ClosingTime, U.DateUnavailable, U.Event FROM jogging_spot J INNER JOIN unavailability_date U ON J.ID = U.ID "
                    + "VALUES('" + id + "', '" + name + "', '" + category + "', '" + time + ", " + date + ", '" + event + "')";

            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.execute();

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

    }

I understand this is just an assignment, but if you’re going to use PreparedStatement, you should use it correctly and safely. Don’t use `+` to place values in the statement; use `VALUES(?, ?, ?, ?, ?, ?)` instead, and use code like `pstmt.setString(1, id);`, `pstmt.setObject(5, date);`, and so on. In real world applications, building SQL statements using `+` is **very dangerous** and is an extremely common reason for databases being hacked. You also should use java.time.LocalTime instead of the Time class; Time was an unfortunate design mistake which LocalTime corrects. — VGR, Aug 08 '22 at 12:32
Don't use `java.sql.Time`. Don't use `java.text.DateFormat` and `ParseException` (if that's what you were trying). Use `LocalTime.parse(sTime)` and `LocalDate.parse(sDate)` to obtain a `LocalTime` and a `LocalDate` object that will go into your database. Catch `DateTimeParseException` in case the user types an incorrect time or date. And don't ever in your life leave your `catch` block empty: do make yourself and your user aware when something goes wrong. — Ole V.V., Aug 08 '22 at 12:54
Related and very helpful: [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). You can handle `LocalTime` in much the same way as `LocalDate` is handled in the answer there. Or even better, use `datetime` in your database and `LocalDateTIme` in Java and still in much the same way. — Ole V.V., Aug 08 '22 at 16:18

Deepesh Dagar · Answer 1 · 2022-08-08T12:49:42.483

0

Try using

LocalDate.parse(dateString, formatter)

DateTimeFormatter format in "yyyy-MM-dd". Reference : https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html

edited Aug 08 '22 at 12:49

answered Aug 08 '22 at 12:15

Deepesh Dagar

171
7

Convert date time formatter to DateTimeFormatter.ofPattern("yyyy-MM-dd") Refer : https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html – Deepesh Dagar Aug 08 '22 at 12:48

score 0 · Accepted Answer · answered Aug 08 '22 at 12:35

you can use LocalTime instead of Time

i modified your code in this way:

enter code here `private void addStadium(String id, String name, String category, LocalTime time, LocalDate date, String event) {
    try {
        String query = "INSERT INTO Stadium(ID, Name, Category, ClosingTime, DateUnavailable, Event) "
                + "SELECT J.ID,J.Name, J.Category, J.ClosingTime, U.DateUnavailable, U.Event FROM jogging_spot J INNER JOIN unavailability_date U ON J.ID = U.ID "
                + "VALUES('" + id + "', '" + name + "', '" + category + "', '" + Time.valueOf(time)+ ", " + Date.valueOf(date) + ", '" + event + "')";

        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.execute();

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

}`

i hope it will help you

A very good and helpful suggestion. The OP may additionally need to know how to obtain a `LocalDate` and a `LocalTime` from the string input they are getting. — Ole V.V., Aug 08 '22 at 12:58
*you can use LocalTime instead of Time* It’s not what you are doing in the code, though. You are using both, which rather increases the complication. I do recommend that you use `LocalDate` and `LocalTime` *and neither* `Date` nor `Time`. — Ole V.V., Aug 08 '22 at 16:21

Java SQL cant insert Date and Time

2 Answers2