Set Local Date Time in a Table Using LocalDateTime Class in Java

The java.time package of Java 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:

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 are respectively as:

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

Setting the Local date/time in a Table

Let us create a table named dispatches in the MySQL database using the 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 the 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');

The following are the steps for setting the local date/time in a table using the LocalDateTime class in Java:

Register the Driver: Select the required database and register the Driver class of the particular database using the registerDriver() method of the DriverManager class or the forName() method of the class named Class.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Get connection: Create a connection object by passing the URL of the database, username, and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.

Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");

Getting the LocalDateTime object, and converting date and time values from local to SQL:

LocalDateTime localDateTime = LocalDateTime.now();
java.sql.Date date = java.sql.Date.valueOf(localDateTime.toLocalDate());
java.sql.Time time = java.sql.Time.valueOf(localDateTime.toLocalTime());

Example

The 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