SpringBoot项目中连接与操作MySQL数据库时的时区问题

最新推荐文章于 2022-11-18 09:46:52 发布

跟着BOSS有肉吃

最新推荐文章于 2022-11-18 09:46:52 发布

阅读量1.6k

点赞数

分类专栏： MySQL SpringBoot

本文链接：https://blog.csdn.net/a15028596338/article/details/102737091

版权

SpringBoot 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

MySQL

3 篇文章 0 订阅

订阅专栏

创建好了一个SpringBoot项目，

2019-10-24 16:21:25.275  INFO 27968 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-10-24 16:21:26.367 ERROR 27968 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.

java.sql.SQLException: The server time zone value '�й���׼ʱ��' 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.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:827) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:447) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:237) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467) [HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541) [HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) [HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) [HikariCP-3.2.0.jar:na]
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:157) [spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115) [spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78) [spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:323) [spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.boot.jdbc.EmbeddedDatabaseConnection.isEmbedded(EmbeddedDatabaseConnection.java:120) [spring-boot-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateDefaultDdlAutoProvider.getDefaultDdlAuto(HibernateDefaultDdlAutoProvider.java:42) [spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration.lambda$getVendorProperties$1(HibernateJpaConfiguration.java:132) [spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings.getDdlAuto(HibernateSettings.java:41) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties.determineDdlAuto(HibernateProperties.java:126) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties.getAdditionalProperties(HibernateProperties.java:98) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties.determineHibernateProperties(HibernateProperties.java:91) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration.getVendorProperties(HibernateJpaConfiguration.java:134) [spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.entityManagerFactory(JpaBaseConfiguration.java:134) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration$$EnhancerBySpringCGLIB$$35ca1b27.CGLIB$entityManagerFactory$5(<generated>) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration$$EnhancerBySpringCGLIB$$35ca1b27$$FastClassBySpringCGLIB$$d243b445.invoke(<generated>) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244) ~[spring-core-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:363) ~[spring-context-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration$$EnhancerBySpringCGLIB$$35ca1b27.entityManagerFactory(<generated>) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_66]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_66]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_66]
	at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_66]
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:622) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:607) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1321) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1160) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1105) ~[spring-context-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) ~[spring-context-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141) ~[spring-boot-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:743) ~[spring-boot-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:390) ~[spring-boot-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:312) ~[spring-boot-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1214) ~[spring-boot-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1203) ~[spring-boot-2.1.7.RELEASE.jar:2.1.7.RELEASE]
	at com.cn.demo.DemoApplication.main(DemoApplication.java:13) ~[classes/:na]
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���׼ʱ��' 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.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_66]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_66]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_66]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[na:1.8.0_66]
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2139) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2163) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1301) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:958) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:817) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	... 53 common frames omitted

异常信息提示：服务器时区值 '�й��׼ʱ��' 无法识别或代表多个时区。如果要利用时区支持，则必须配置服务器或JDBC驱动程序（通过serverTimezone配置属性）以使用更特定的时区值。

在application.yml文件spring.datasource.url的后面添加serverTimezone=Asia/Shanghai，如下：

我们使用的是北京时间，位于东八区。东八区（UTC/GMT+08:00）是比世界协调时间（UTC）/格林尼治时间（GMT）快8小时的时区，理论上的位置是位于东经112.5度至127.5度之间，是东盟标准的其中一个候选时区。当格林尼治标准时间为0:00时，东八区的标准时间为08:00。

现有数据库表atm_log：

CREATE TABLE `atm_log` (
  `log_no` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志编号',
  `atm_no` varchar(32) NOT NULL COMMENT '自助机编号',
  `card_id` varchar(32) NOT NULL COMMENT '卡号',
  `operate_name` varchar(32) NOT NULL COMMENT '操作名称',
  `operate_context` text COMMENT '操作内容',
  `operate_time` datetime NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`log_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='自助机日志表';

里面有如下数据：

现在向表atm_log中插入一条数据。

1、实体类AtmLog.java

package com.cn.demo.count.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "atm_log")
public class AtmLog implements Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "log_no")
	private Integer logNo;
	
	@Column(name = "atm_no")
	private String atmNo;
	
	@Column(name = "card_id")
	private String cardId;
	
	@Column(name = "operate_name")
	private String operateName;
	
	@Column(name = "operate_context")
	private String operateContext;
	
	@Column(name = "operate_time")
	private Date operateTime;

	public Integer getLogNo() {
		return logNo;
	}

	public void setLogNo(Integer logNo) {
		this.logNo = logNo;
	}

	public String getAtmNo() {
		return atmNo;
	}

	public void setAtmNo(String atmNo) {
		this.atmNo = atmNo;
	}

	public String getCardId() {
		return cardId;
	}

	public void setCardId(String cardId) {
		this.cardId = cardId;
	}

	public String getOperateName() {
		return operateName;
	}

	public void setOperateName(String operateName) {
		this.operateName = operateName;
	}

	public String getOperateContext() {
		return operateContext;
	}

	public void setOperateContext(String operateContext) {
		this.operateContext = operateContext;
	}

	public Date getOperateTime() {
		return operateTime;
	}

	public void setOperateTime(Date operateTime) {
		this.operateTime = operateTime;
	}
}

2、接口处AtmLogRepository.java

package com.cn.demo.count.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.cn.demo.count.entity.AtmLog;

public interface AtmLogRepository extends JpaRepository<AtmLog, Integer>{

}

3、服务层AtmLogService.java

package com.cn.demo.count.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.cn.demo.count.entity.AtmLog;
import com.cn.demo.count.repository.AtmLogRepository;

@Service
public class AtmLogService {
	@Autowired
	private AtmLogRepository atmLogRepository;

	public AtmLog addLog(AtmLog atmLog) {
		return atmLogRepository.save(atmLog);
		
	}
}

4、控制层AtmLogController.java

package com.cn.demo.count.controller;

import java.text.ParseException;
import java.text.SimpleDateFormat;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.cn.demo.count.entity.AtmLog;
import com.cn.demo.count.service.AtmLogService;

@RestController
@RequestMapping("/api")
public class AtmLogController {
	@Autowired
	private AtmLogService atmLogService;
	
	@RequestMapping(value = "/addLog", method = RequestMethod.POST)
	public AtmLog addLog(@RequestParam(value = "atmNo", required = true)String atmNo,
			@RequestParam(value = "cardId", required = true)String cardId,
			@RequestParam(value = "operateName", required = true)String operateName,
			@RequestParam(value = "operateContext", required = false, defaultValue = "")String operateContext,
			@RequestParam(value = "operateTime", required =true)String operateTime){
		AtmLog atmLog = new AtmLog();
		atmLog.setAtmNo(atmNo);
		atmLog.setCardId(cardId);
		atmLog.setOperateName(operateName);
		atmLog.setOperateContext(operateContext);
		try {
			atmLog.setOperateTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(operateTime));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		atmLogService.addLog(atmLog);
		return atmLog;
	}
	
}

运行程序，使用postman添加数据：localhost:9000/test/api/addLog?atmNo=ATM001&cardId=card0009&operateName=存款&operateContext=存款&operateTime=2019-10-25 09:10:00

可以看到数据库中多了一条数据，且时间正确：

但是post显示的返回结果如下，时间比入参的时间少了8小时，因为是0时区的值。

在application.yml文件中添加如下配置：

重新运行程序，使用postman添加数据：localhost:9000/test/api/addLog?atmNo=ATM003&cardId=card0007&operateName=取款&operateContext=取款&operateTime=2019-10-25 09:33:00

数据库中多了一条数据：

且postman返回的日期正确，为8时区的值。

跟着BOSS有肉吃

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot项目中连接与操作MySQL数据库时的时区问题

创建好了一个SpringBoot项目，2019-10-24 16:21:25.275 INFO 27968 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...2019-10-24 16:21:26.367 ERROR 27968 --- [ ...
复制链接

扫一扫