I want to INSERT Spring Local Time with MySQL Time (also milliseconds)

Overview

`` `11: 12: 13.14``` (hours: minutes: seconds.milliseconds) obtained as a String type INSERT into MySQL image.png And how to display up to milliseconds. (No date information required)

*** Isn't it possible to implement it normally? *** *** Do you think

It didn't work unless it seemed easy and had troublesome processing. .. .. If anyone knows a smarter way, I would be very happy if you could tell me! !!

version

Spring

build.gradle


id 'org.springframework.boot' version '2.2.2.BUILD-SNAPSHOT'
sourceCompatibility = '1.8'

	runtimeOnly('mysql:mysql-connector-java')
	implementation 'org.springframework.boot:spring-boot-starter'
	implementation('org.springframework.boot:spring-boot-starter-web')
	implementation('org.springframework.boot:spring-boot-starter-data-jpa')

Database

MySQL 8.0

Step 1 Create a table

Supports up to 3 decimal places MySQL :: MySQL 5.6 Reference Manual :: 11.1.2 Date and Time Type Overview

create table timesample( 
    ...
    start_time TIME(3)
);

Step 2 Create Attribute Converter

Since JPA does not support java.time.LocalDate, java.time.LocalTime, java.time.LocalDateTime as the field types of entities, you need to create the following AttributeConverter.

package app.entities.converter;

import java.time.LocalTime;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true)
public class LocalTimeConverter implements AttributeConverter<LocalTime,  java.sql.Time> {

    @Override
    public java.sql.Time convertToDatabaseColumn(LocalTime time) {
    	return time == null ? null : java.sql.Time.valueOf(time);
    }

    @Override
    public LocalTime convertToEntityAttribute(java.sql.Time  value) {
    	return value == null ? null : value.toLocalTime();
    }
}

Entity


	@Column(name = "start_time")
	private LocalTime startTime;
entity.setStartTime(LocalTime.of(11, 12, 13, 14*10000000));

repository.save(entity);

image.png

There are no milliseconds!

Step 3 Hold millisecond and convert to java.sql.Time

Change the convertToDatabaseColumn method of the LocalTimeConverter created earlier [How to map spring-millisecond java.time.LocalTime to millisecond java.sql.Time](https://stackoverflow.com/questions/51182366/how-to-map-java-time-localtime- with-milliseconds-to-a-java-sql-time-with-milisec)

LocalTimeConverter


package app.entities.converter;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalTime;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true)
public class LocalTimeConverter implements AttributeConverter<LocalTime,  java.sql.Time> {

    @Override
    public java.sql.Time convertToDatabaseColumn(LocalTime time) {
    	if(time == null) return null;
    	
    	//This date will be discarded by conversion, so anything is OK
		long epochMilli = time.atDate(LocalDate.of(2019, 1, 1)).atZone(java.time.ZoneId.systemDefault())
				.toInstant().toEpochMilli();
		java.sql.Time sqlTime = new java.sql.Time(epochMilli);
    	return sqlTime;
    }

    @Override
    public LocalTime convertToEntityAttribute(java.sql.Time  value) {
    	if (value == null) return null;
    	
    	String localTimeStr = new SimpleDateFormat("HH:mm:ss.SSS").format(value);
    	LocalTime localTime = LocalTime.parse(localTimeStr);
    	return localTime;
    }
}

image.png

Recommended Posts

I want to INSERT Spring Local Time with MySQL Time (also milliseconds)
I tried connecting to MySQL using JDBC Template with Spring MVC
I want to use DBViewer with Eclipse 2018-12! !!
I want to display images with REST Controller of Java and Spring!
I want to test Action Cable with RSpec test
I want to use java8 forEach with index
I want to play with Firestore from Rails
I wanted to gradle spring boot with multi-project
I want to perform aggregation processing with spring-batch
[Rails] I want to load CSS with webpacker
Settings for connecting to MySQL with Spring Boot + Spring JDBC
I tried to implement file upload with Spring MVC
I started MySQL 5.7 with docker-compose and tried to connect
I tried to get started with Spring Data JPA
I want to dark mode with the SWT app
I want to monitor a specific file with WatchService
I want to authenticate users to Rails with Devise + OmniAuth
I want to transition screens with kotlin and java!
I want to get along with Map [Java beginner]
I want to redirect sound from Ubuntu with xrdp
I want to connect to Heroku MySQL from a client
I tried to make an introduction to PHP + MySQL with Docker
I want to push an app made with Rails 6 to GitHub
I want to make a list with kotlin and java!
I want to make a function with kotlin and java!
Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
Even in Java, I want to output true with a == 1 && a == 2 && a == 3
I want to manually send an authorization email with Devise
I want to distinct the duplicated data with has_many through
I want to implement various functions with kotlin and java!
docker-compose.yml when you want to keep mysql running with docker
I tried to get started with Swagger using Spring Boot
I want to pass the startup command to postgres with docker-compose.
[Java] I want to test standard input & standard output with JUnit
Connect to MySQL 8 with Java
[Spring Boot] I want to add my own property file and get the value with env.getProperty ().
I want to make a button with a line break with link_to [Note]
I want to connect SONY headphones WH-1000XM4 with LDAC on ubuntu 20.04! !!
I want to convert an array to Active Record Relation with Rails
I want to hook log file generation / open with log4j # FileAppender
I want to add a browsing function with ruby on rails
I want to understand the flow of Spring processing request parameters
I want to return to the previous screen with kotlin and java!
I want to avoid OutOfMemory when outputting large files with POI
I want to operate cron with GUI, so I will install Dkron
I wanted to make JavaFX programming easier with the Spring Framework
[Java] I want to perform distinct with the key in the object
I want to control the default error message of Spring Boot
[Rails] I want to add data to Params when transitioning with link_to
I want to perform asynchronous processing and periodic execution with Rail !!!
I want to extract between character strings with a regular expression
Swift: I want to chain arrays
I went to JJUG CCC 2019 Spring
I want to use FormObject well
mysql2 fails to install with bundle install
I want to convert InputStream to String
I tried to interact with Java
I want to docker-compose up Next.js!
I tried GraphQL with Spring Boot
I tried Flyway with Spring Boot
Update MySQL from 5.7 to 8.0 with Docker