How to add a datetime Into my SQL database using JDBC

Question

Currently, I am trying to add my datetime into SQL from JavaFX GUI, but I keep getting the number format exception error. The Datetime format is yyyy-MM-dd HH:mm:ss, but I can also add in just like 12:30 etc.

private void doAdd() {
    //Input from the user in GUI format
    int EntryID = Integer.valueOf(tfEntryID.getText());
    String PersonName = tfPersonName.getText();
    int CheckInTime = Integer.parseInt(tfCheckInTime.getText());
    String CheckTime = String.valueOf(CheckInTime);
    Date date = new Date(CheckInTime);
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
    String currentDateTime = format.format(date);
    String insertSql = String.format("INSERT INTO entry_records(
        EntryID, PersonName, CheckTime) VALUES ('%s', '%s', %s)",
        EntryID , PersonName ,currentDateTime );
    int rowsAdded = DBUtil.execSQL(insertSql);
    if (rowsAdded == 1) {
        System.out.println("STATUS: ADD Entry Record (ID" + EntryID + ") Successful!");
    } else {
        System.out.println("Adding failed!");
    }
}

Couple things: **(1)** I suggest avoiding the old date&time classes (e.g. `Date`, `SimpleDateFormat`) if at all possible, and instead use the "new" `java.time` API added in Java 8 (e.g. `LocalDateTime`, `DateTimeFormatter`, etc.); **(2)** _Never_ create your SQL queries like you're currently doing; it's unsafe, the worst being the potential for SQL injection—intentional or not. Use a `PreparedStatement` and use its API to set the needed parameters. — Slaw, Aug 05 '21 at 16:37
Then you have at least two things to research: **(1)** How to use data-time objects with JDBC, and **(2)** how to parse a date-time with optional parts. — Slaw, Aug 05 '21 at 16:40
Related: [Dates with no time or timezone component in Java/MySQL](https://stackoverflow.com/questions/285553/dates-with-no-time-or-timezone-component-in-java-mysql) — Ole V.V., Aug 06 '21 at 06:55
The `NumberFormatException` probably comes from either `Integer.valueOf(tfEntryID.getText())` or `Integer.parseInt(tfCheckInTime.getText())`. Your stack trace should tell you which of those two lines. So please also tell us: (1) What text is in those text fields? (2) Which line does the stack trace refer to? (3) What is the full exception message (please paste it into your question)? — Ole V.V., Aug 06 '21 at 07:09

Basil Bourque · Answer 1 · 2021-08-06T02:07:05.547

Never use Date and SimpleDateFormat classes. They are terribly flawed in design. They were years ago supplanted by the modern java.time classes defined in JSR 310.

With JDBC 4.2 and later, you can exchange date-time objects with your database use java.time objects. No need for string manipulations.

Parse your input string into a LocalDateTime object if you have only date and time-of-day and intend to ignore time zones.

DateTimeFormatter f = DateTimeFormatter.ofPattern( … ) ;
LocalDateTime ldt = LocalDateTime.parse( input , f ) ;

Write that value to your database using a PreparedStatement to avoid the security risks of SQL-injection.

myPreparedStatement.setObject( … , ldt ) ;

Retrieval.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

The LocalDateTime class is appropriate to a database column of a type akin to SQL-standard TIMESTAMP WITHOUT TIME ZONE. These types are inherently ambiguous as they both purposely lack the context of a time zone or offset-from-UTC. So they cannot represent a moment, are not a specific point on the timeline.

To represent a moment, use classes Instant/OffsetDateTime/ZonedDateTime, with OffsetDateTime being appropriate to SQL exchange. For database column type, use a type akin to the SQL-standard TIMESTAMP WITH TIME ZONE.

All of this has been covered many times already on Stack Overflow. Search to learn more.

Very good answer. Peter, there’s important information in this paragraph: *To represent a moment, use …* Please read twice, at least. For the sake of your users. — Ole V.V., Aug 06 '21 at 06:57

How to add a datetime Into my SQL database using JDBC

1 Answers1