Java datetime mysql datetime

How to convert java.util.Date to Mysql Date Format

So, there is a need to convert the java’s default Date format to Mysql’s date format. The conversion can be done at the database level (through various mysql functions) or at the program level through some java code. We will discuss only the latter part.

Java provides a class called SimpleDateFormat available in java.text package which allows for formatting (date -> text) through format() method and parsing (text -> date) through parse() method.

Here, we will convert the today’s date from java Date object to mysql date format.

2. Create a SimpleDateFormat object by using the constructor,

public SimpleDateFormat(String pattern)

Constructs a SimpleDateFormat using the given pattern and the default date format symbols for the default locale. Note: This constructor may not support all locales. For full coverage, use the factory methods in the DateFormat class.

Parameters:
pattern – the pattern describing the date and time format

String pattern = "yyyy-MM-dd"; SimpleDateFormat formatter = new SimpleDateFormat(pattern);

3. Now use the format() method to convert the date object to text format provided in the pattern.

String mysqlDateString = formatter.format(now);

import java.text.SimpleDateFormat; import java.util.Date; public class JavaToMysqlDate < public static void main(String[] args) < Date now = new Date(); String pattern = "yyyy-MM-dd"; SimpleDateFormat formatter = new SimpleDateFormat(pattern); String mysqlDateString = formatter.format(now); System.out.println("Java's Default Date Format: " + now); System.out.println("Mysql's Default Date Format: " + mysqlDateString); >>

The following pattern letters are defined in SimpleDateFormat.

So for Mysql DateTime format, you can use the pattern “yyyy-MM-dd HH:mm:ss”

For converting date in String format to Date object, refer this tutorial.

Источник

How to store java date to mysql datetime with jpa?

Storing date in Java and date/time in MySQL is a common task when working with databases. However, there can be issues that arise when trying to persist Java Date objects to the datetime field in MySQL using JPA (Java Persistence API). This can lead to unexpected results, such as loss of information or incorrect storage of the date/time value. The solution to this problem requires a proper mapping between Java Date and the datetime field in MySQL.

Method 1: Using LocalDateTime and JPA AttributeConverter

To store a Java Date to MySQL datetime with JPA using LocalDateTime and JPA AttributeConverter, you can follow these steps:

1. Create a LocalDateTimeAttributeConverter class that implements the JPA AttributeConverter interface. This class will convert LocalDateTime to java.sql.Timestamp and vice versa.

import java.sql.Timestamp; import java.time.LocalDateTime; import javax.persistence.AttributeConverter; import javax.persistence.Converter; @Converter(autoApply = true) public class LocalDateTimeAttributeConverter implements AttributeConverterLocalDateTime, Timestamp>  @Override public Timestamp convertToDatabaseColumn(LocalDateTime locDateTime)  return locDateTime == null ? null : Timestamp.valueOf(locDateTime); > @Override public LocalDateTime convertToEntityAttribute(Timestamp sqlTimestamp)  return sqlTimestamp == null ? null : sqlTimestamp.toLocalDateTime(); > >

1. Apply the LocalDateTimeAttributeConverter to the entity class field that needs to be stored as datetime in MySQL. Use the @Convert annotation to specify the converter class.

import java.time.LocalDateTime; import javax.persistence.Column; import javax.persistence.Convert; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "my_entity") public class MyEntity  @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "date_time") @Convert(converter = LocalDateTimeAttributeConverter.class) private LocalDateTime dateTime; // getters and setters >

MyEntity entity = new MyEntity(); entity.setDateTime(LocalDateTime.now()); entityManager.persist(entity); MyEntity retrievedEntity = entityManager.find(MyEntity.class, entity.getId()); LocalDateTime retrievedDateTime = retrievedEntity.getDateTime();

That’s it! You have successfully stored a Java Date to MySQL datetime with JPA using LocalDateTime and JPA AttributeConverter.

Method 2: Using java.sql.Timestamp and JPA AttributeConverter

Here is an example of how to store a Java Date to MySQL datetime using JPA and a java.sql.Timestamp with an AttributeConverter:

1. Create a class that implements the JPA AttributeConverter interface. This class will convert between a Java Date object and a java.sql.Timestamp object.

import java.sql.Timestamp; import java.util.Date; import javax.persistence.AttributeConverter; import javax.persistence.Converter; @Converter(autoApply = true) public class DateConverter implements AttributeConverterDate, Timestamp>  @Override public Timestamp convertToDatabaseColumn(Date date)  return date == null ? null : new Timestamp(date.getTime()); > @Override public Date convertToEntityAttribute(Timestamp timestamp)  return timestamp == null ? null : new Date(timestamp.getTime()); > >

1. In your entity class, use the @Temporal annotation to specify that the Date field should be persisted as a TIMESTAMP in the database.

import java.util.Date; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity public class MyEntity  @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Temporal(TemporalType.TIMESTAMP) private Date myDate; // getters and setters >

1. When you persist your entity, the Date field will be automatically converted to a java.sql.Timestamp object by the AttributeConverter, and then stored in the database as a TIMESTAMP.

MyEntity entity = new MyEntity(); entity.setMyDate(new Date()); entityManager.persist(entity);

That’s it! Now you know how to store a Java Date to MySQL datetime using JPA and a java.sql.Timestamp with an AttributeConverter.

Method 3: Using java.util.Date and JPA AttributeConverter

To store Java Date to Mysql datetime with JPA using java.util.Date and JPA AttributeConverter, you can follow these steps:

1. Create a class that implements the JPA AttributeConverter interface. This class will be responsible for converting between the Java Date and the Mysql datetime.

import javax.persistence.AttributeConverter; import javax.persistence.Converter; import java.sql.Timestamp; import java.util.Date; @Converter(autoApply = true) public class DateConverter implements AttributeConverterDate, Timestamp>  @Override public Timestamp convertToDatabaseColumn(Date date)  return date == null ? null : new Timestamp(date.getTime()); > @Override public Date convertToEntityAttribute(Timestamp timestamp)  return timestamp == null ? null : new Date(timestamp.getTime()); > >

1. Annotate the Java Date field in your entity with the @Convert annotation and specify the name of the converter class.

import javax.persistence.*; import java.util.Date; @Entity public class MyEntity  @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Convert(converter = DateConverter.class) @Temporal(TemporalType.TIMESTAMP) private Date myDate; // getters and setters >

In the above example, the @Temporal annotation is used to specify the type of the column in the database.

That’s it! Now you can store and retrieve Java Date objects in your Mysql database using JPA.

Note: The @Converter(autoApply = true) annotation is used to automatically apply the converter to all entities that use the Java Date field. If you want to apply the converter only to a specific entity, you can remove this annotation and specify the converter class in the @Convert annotation on the field.

Источник

java.sql.Date, java.sql.Time, and java.sql.Timestamp

Most of database support three forms of datetime fields which are DATE, TIME and TIMESTAMP. Each of these have a corresponding class in JDBC and each of them extend java.util.Date. These three are:

• java.sql.Date represents SQL DATE, stores years, months and days without time components. java.sql.Date is disregarding timezones.
• java.sql.Time represents SQL TIME and only contains information about hour, minutes, seconds and milliseconds without date component.
• java.sql.Timestamp represents SQL TIMESTAMP which contains both Date and Time information to the nanoseconds precision.

Let’s check below table referring to top 5 databases (not in the order) datetime data types:

I putting the source, my reference in the right most column. Correct me if I’m wrong

Working With JDBC Classes

Which class to choose is depends on the SQL type of the field. PreparedStatement has setters for all three values, setDate() for java.sql.Date, setTime() for java.sql.Time and setTimestamp() for java.sql.Timestamp.

Let’s down to example, we create a test table in our database. For this article, I’ll use MySQL.

CREATE TABLE test_datetime ( dtm_date DATE, dtm_time TIME, dtm_timestamp TIMESTAMP, obj_date DATE, obj_time TIME, obj_timestamp TIMESTAMP );

And then we insert current date/time (get via new java.util.Date()) into the date field of an SQL database table.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class SqlDateTimeInsertExample < public static void main(String[] args) throws Exception < // (1) connect to mysql database String url = "jdbc:mysql://localhost/coffeehouse?serverTimezone=Asia/Singapore"; Class.forName("com.mysql.cj.jdbc.Driver"); try (Connection conn = DriverManager.getConnection(url, "barista", "cappuccino")) < // (2) set java.sql.Date, Time, and Timestamp with current Date (and time) java.util.Date utilDate = new java.util.Date(); java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); java.sql.Time sqlTime = new java.sql.Time(utilDate.getTime()); java.sql.Timestamp sqlTS = new java.sql.Timestamp(utilDate.getTime()); // (3) insert java.sql.Date, Time and Timestamp (including objects) to DB String sql = "INSERT INTO test_datetime(" + "dtm_date, dtm_time, dtm_timestamp," + "obj_date, obj_time, obj_timestamp) VALUES (. )"; try (PreparedStatement pst = conn.prepareStatement(sql)) < pst.setDate(1, sqlDate); pst.setTime(2, sqlTime); pst.setTimestamp(3, sqlTS); pst.setObject(4, utilDate); pst.setObject(5, utilDate); pst.setObject(6, utilDate); // (4) execute update pst.executeUpdate(); >> > > 

As in above example, with setObject(int parameterIndex , x Object); we can just give a util.Date to the last three parameters which accept it without problem (this is also happen in another JDBC driver, not only MySQL). But to just use setObject(. ) lazily can cause some problem, including data (or part of data) loss.

Note: The URL suffix ?serverTimezone=Asia/Singapore is to suppress: Exception in thread «main» java.sql.SQLException: The server time zone value ‘Malay Peninsula Standard Time’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

(Can you see the typo in above MySQL JDBC error message? :p )

Connect to mysql to confirm the result:

$ mysql -u barista -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.5.27 MySQL Community Server (GPL) mysql> connect coffeehouse Connection id: 10 Current database: coffeehouse mysql> select * from test_datetime; +------------+----------+---------------------+------------+----------+---------------------+ | dtm_date | dtm_time | dtm_timestamp | obj_date | obj_time | obj_timestamp | +------------+----------+---------------------+------------+----------+---------------------+ | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | +------------+----------+---------------------+------------+----------+---------------------+ 1 row in set (0.00 sec)

Liked this Tutorial? Share it on Social media!

Источник