How to set local date/time in a table using LocalDateTime class in Java?

The java.time package of Java8 provides a class named LocalDateTime is used to get the current value of local date and time. Using this in addition to date and time values you can also get other date and time fields, such as day-of-year, day-of-week and week-of-year.

Setting the Local time to a column

To set the local date and time value to a column in a table −

• Obtain the LocalDateTime object − You can obtain the LocalDateTime object by invoking the static method now() as −

//Getting the LocalDateTime object
LocalDateTime localDateTime = LocalDateTime.now();

• Get the LocalDate and LocalTime objects from the above obtained LocalDateTime as −

LocalDate localDate = localDateTime.toLocalDate();
LocalTime localTime = localDateTime.toLocalTime()

• Now, pass the LocalDate and LocalTime objects to the valueOf() method of the java.sql.Date and java.sql.Time classes respectively as−

java.sql.Date date = java.sql.Date.valueOf(localDate);
java.sql.Time time = java.sql.Time.valueOf(localTime);

Example

Let us create a table with name dispatches in MySQL database using CREATE statement as follows −

CREATE TABLE dispatches(
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);

Now, we will insert 5 records in dispatches table using INSERT statements −

insert into dispatches values('Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into dispatches values('Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into dispatches values('Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into dispatches values('Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into dispatches values('Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following JDBC program inserts a new record into the dispatches table by passing the required values. In here, we are getting the current local date and time values and inserting them as values of the Date and Time columns of the table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
public class settingLocatDate {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Getting the LocalDateTime object
      LocalDateTime localDateTime = LocalDateTime.now();
      System.out.println(localDateTime.toString());
      //Converting date and time values from local to SQL
      java.sql.Date date = java.sql.Date.valueOf(localDateTime.toLocalDate());
      java.sql.Time time = java.sql.Time.valueOf(localDateTime.toLocalTime());
      //Creating a Prepared Statement
      String query = "INSERT INTO Dispatches VALUES (?, ?, ?, ?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "Watch");
      pstmt.setString(2, "Rajan");
      pstmt.setDate(3, date);
      pstmt.setObject(4, time);
      pstmt.setInt(5, 4000);
      pstmt.setString(6, "Chennai");
      pstmt.execute();
      System.out.println("Rows inserted ....");
      //Retrieving values
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from dispatches");
      while(rs.next()) {
         System.out.println("Product Name: "+rs.getString("ProductName"));
         System.out.println("Customer Name: "+rs.getString("CustomerName"));
         System.out.println("Date Of Dispatch: "+rs.getDate("DispatchDate"));
         System.out.println("Delivery Time: "+rs.getTime("DeliveryTime"));
         System.out.println("Location: "+rs.getString("Location"));
         System.out.println();
      }
   }
}

Output

Connection established......
2019-05-14T15:48:42.457
Rows inserted ....
Product Name: Key-Board
Customer Name: Raja
Date Of Dispatch: 2019-09-01
Delivery Time: 11:00:00
Location: Hyderabad
Product Name: Earphones
Customer Name: Roja
Date Of Dispatch: 2019-05-01
Delivery Time: 11:00:00
Location: Vishakhapatnam
Product Name: Mouse
Customer Name: Puja
Date Of Dispatch: 2019-03-01
Delivery Time: 10:59:59
Location: Vijayawada
Product Name: Mobile
Customer Name: Vanaja
Date Of Dispatch: 2019-03-01
Delivery Time: 10:10:52
Location: Chennai
Product Name: Headset
Customer Name: Jalaja
Date Of Dispatch: 2019-04-06
Delivery Time: 11:08:59
Location: Goa
Product Name: Watch
Customer Name: Rajan
Date Of Dispatch: 2019-05-14
Delivery Time: 15:48:42
Location: Chennai