Setting the MySQL JDBC Timezone in Spring Boot
When working with databases in a Spring Boot application, it is important to ensure that the timezone settings are correctly configured, especially when dealing with date and time data. MySQL databases often require explicit timezone configuration to ensure the accurate handling of DateTime values.
By default, MySQL stores datetime values in the UTC timezone. However, when retrieving these values in a Spring Boot application, the JVM timezone is used by default, which may lead to unexpected behavior if not configured properly. To ensure consistency, it’s necessary to explicitly set the JDBC connection timezone to match the timezone of the MySQL server.
Key Terminologies
- JDBC(Java Database Connectivity): It is the Java API that allows the Java programs to the interact with database. It provides the methods for querying and updating data in the database and handling the results.
- Timezone: It is a region of the earth where the same standard time is used. TimeZones can typically expressed as the offsets from the Coordinated Universal Time(UTC) and it can vary based on the geographical location, daylight saving time, and other factors.
- Server Timezone: It refers to the timezone configured on the MySQL server. This timezone can be used by the MySQL to store and interpret values if no timezone is specified explicitly in the queries.
Implementation to Set the MySQL JDBC Timezone In Spring Boot Application
Below is the implementation of spring boot application in which we have set the MySQL JDBC Timezone.
Step 1: To create the Spring project using Spring Initializr and add the required dependencies, follow these steps:
Dependencies:
- Spring Web
- Spring Data JPA
- MySQL Driver
- Spring Dev Tools
- Lombok
Once you create the Spring project, the file structure will resemble the following:
w image.
Step 2: Open the application.properties file and add the following code to configure the server port and MySQL JDBC Timezone:
spring.application.name=spring-jdbc-timezone-demo
# MySQL database properties
spring.datasource.url=jdbc:mysql://localhost:3306/example?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# Hibernate properties
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
Step 3: Create a new package named “model”. Within that package, create a new Java class named “User”.
Go to src > org.example.springjdbctimezonedemo > model > User and put the below code.
package org.example.springjdbctimezonedemo.model;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String email;
}
This User class provides the model entity designed for the use within the Spring Framework application that interacts with the relational database. This class leverages the Java Persistence API(JPA) annotations to define how its the object relate to the database tables.
- @Id and @GeneratedValue: These annotations can be used to mar the id field as the primary key of the database table and it can configure the primary key to generated automatically by the database. It can ensuring each user has the unique identifier.
- The fields id, name and email are the attributes of the user that will be stored in the respective columns in database.
Step 4: Create a new package named “repository”. Within that package, create a new Java interface named “UserRepository”.
Go to src > org.example.springjdbctimezonedemo > repository > UserRepository and put the below code.
package org.example.springjdbctimezonedemo.repository;
import org.example.springjdbctimezonedemo.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
}
This UserRepository interface designed to the serve as the data access layer for the User entities in the Spring application. It extends the JpaRepository interface from Spring Data JPA and it provides the rich set of the methods for the CRUD operations and database queries.
Step 5: Create a new package named “service”. Within that package, create a new Java class named “UserService”.
Go to src > org.example.springjdbctimezonedemo > service > UserService and put the below code.
package org.example.springjdbctimezonedemo.service;
import org.example.springjdbctimezonedemo.model.User;
import org.example.springjdbctimezonedemo.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
private final UserRepository userRepository;
@Autowired
public UserService(UserRepository userRepository) {
this.userRepository = userRepository;
}
public User saveUser(User user) {
return userRepository.save(user);
}
public List<User> getAllUsers() {
return userRepository.findAll();
}
}
This UserService class is the service component in Spring application and it indicates that it holds the business logic. It can typically used to the denote as the service layer in the spring application.
- saveUser() method: This method can be used to save the user from the database.
- getAllUsers() method: This method can be used to retrieve the users from the database.
Step 6: Create a new package named “controller”. Within that package, create a new Java class named “UserController”.
Go to src > org.example.springjdbctimezonedemo > controller > UserController and put the below code.
package org.example.springjdbctimezonedemo.controller;
import org.example.springjdbctimezonedemo.model.User;
import org.example.springjdbctimezonedemo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/users")
public class UserController {
private final UserService userService;
@Autowired
public UserController(UserService userService) {
this.userService = userService;
}
@PostMapping
public User saveUser(@RequestBody User user) {
return userService.saveUser(user);
}
@GetMapping
public List<User> getAllUsers() {
return userService.getAllUsers();
}
}
This UserController class serves the RESTful endpoints and it automatically serializes the return objects into JSON/XML responses.
- POST /users: This endpoint save the user.
- GET /users: This endpoints retrieves all the users from the database.
Step 7: Open the main class and add the following code.
package org.example.springjdbctimezonedemo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringJdbcTimezoneDemoApplication {
public static void main(String[] args) {
SpringApplication.run(SpringJdbcTimezoneDemoApplication.class, args);
}
}
Step 8: Once we run the application, the project will run on port 8080.
Save the User:
POST http://localhost:8080/users
Refer the below image for output:
Get the User:
GET http://localhost:8080/users
Refer the below image for output:
By following the above steps, we can effectively set the MySQL JDBC timezone in the Spring application, ensuring consistent handling of datetime values across different timezones.
Contact Us