spring boot 使用 com.mysql.jdbc.Driver 和 com.mysql.cj.jdbc.Driver的区别

最新推荐文章于 2024-09-01 13:55:46 发布

weixin_34238633

最新推荐文章于 2024-09-01 13:55:46 发布

阅读量9.1k

点赞数 1

文章标签：数据库 java python

原文链接：https://my.oschina.net/u/3777515/blog/3004393

版权

2019独角兽企业重金招聘Python工程师标准>>>

今天集成spring boot 2.1.1构建web应用并且集成jdbc，发现默认用的8.0.13，

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.13</version>
</dependency>

jdbc配置如下：

spring.datasource.xss.name=db-xss
spring.datasource.xss.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.xss.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.xss.url=jdbc:mysql://127.0.0.1:3306/xss?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
spring.datasource.xss.username=root
spring.datasource.xss.password=123456

运行项目报错：

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

按照提示修改：

spring.datasource.xss.driver-class-name=com.mysql.cj.jdbc.Driver

总结：

com.mysql.jdbc.Driver 是 mysql-connector-java 5中的，
com.mysql.cj.jdbc.Driver 是 mysql-connector-java 版本6以后的

一、jdbc里不配置时区信息，代码里设置日期，配置如下，发现时间有问题

1、代码里jdbc配置
spring.datasource.xss.name=db-xss
spring.datasource.xss.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.xss.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.xss.url=jdbc:mysql://127.0.0.1:3306/xss?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
spring.datasource.xss.username=root
spring.datasource.xss.password=123456

2、保存
@RequestMapping(value = "/save")
public Address save() {
    Address address = new Address();
    address.setUserId(16L);
    address.setAddress("address.16");
    // 设置日期
    address.setCreateTime(new Date());
    addressDao.save(address);
    return address;
}

3、mybaites的写法
<insert id="save" parameterType="com.moon.xss.module.Address" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO address
        (
          user_id,
          address,
          create_time
        )
        VALUES
        (
          #{userId},
          #{address},
          #{createTime}
        )
</insert>

保存到数据库的数据如下：（当前电脑时间是14:20，保存到数据库的时间是00:20）

通过接口查询，发现用fastjson序列化时返回的是对的：

但是用jackson，转后的数据不对：

二、jdbc里不配置时区信息，代码里不设置日期，配置如下，发现时间有问题

#1、jdbc配置
spring.datasource.xss.name=db-xss
spring.datasource.xss.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.xss.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.xss.url=jdbc:mysql://127.0.0.1:3306/xss?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
spring.datasource.xss.username=root
spring.datasource.xss.password=123456

#2、save到数据库
@RequestMapping(value = "/save")
public Address save() {
    Address address = new Address();
    address.setUserId(16L);
    address.setAddress("address.16");
    // 这里不设置日期，在xml里设置
    //address.setCreateTime(new Date());
    addressDao.save(address);
    return address;
}

#3、mybatis
<insert id="save" parameterType="com.moon.xss.module.Address" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO address
        (
          user_id,
          address,
          create_time
        )
        VALUES
        (
          #{userId},
          #{address},
          now()
        )
</insert>

数据库看结果：

fastjson转后的结果如下，不对

用jackson转后的结果如下，不对：

三、jdbc里配置时区信息，java文件里设置日期

#1、增加时区配置serverTimezone=Asia/Shanghai
spring.datasource.xss.name=db-xss
spring.datasource.xss.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.xss.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.xss.url=jdbc:mysql://127.0.0.1:3306/xss?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
spring.datasource.xss.username=root
spring.datasource.xss.password=123456


@RequestMapping(value = "/save")
public Address save() {
    Address address = new Address();
    address.setUserId(16L);
    address.setAddress("address.16");
    address.setCreateTime(new Date());
    addressDao.save(address);
    return address;
}

<insert id="save" parameterType="com.moon.xss.module.Address" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO address
        (
          user_id,
          address,
          create_time
        )
        VALUES
        (
          #{userId},
          #{address},
          #{createTime}
        )
</insert>

fastjson转后的结果如下，正确

用jackson转后的结果如下，不正确：

四、jdbc里配置时区信息，java文件里不设置日期

#设定时区
spring.datasource.xss.name=db-xss
spring.datasource.xss.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.xss.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.xss.url=jdbc:mysql://127.0.0.1:3306/xss?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
spring.datasource.xss.username=root
spring.datasource.xss.password=123456


#接口
@RequestMapping(value = "/save")
public Address save() {
    Address address = new Address();
    address.setUserId(16L);
    address.setAddress("address.16");
    // 不设置日期
    //address.setCreateTime(new Date());
    addressDao.save(address);
    return address;
}

#mybatis
<insert id="save" parameterType="com.moon.xss.module.Address" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO address
        (
          user_id,
          address,
          create_time
        )
        VALUES
        (
          #{userId},
          #{address},
          now()
        )
</insert>

数据库保存如下

fastjson转后的结果如下，正确

用jackson转后的结果如下，不正确：

jackson转后时间不对原因分析：

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.util.Date;

@Data
public class Address {
    private Long id;
    private Long userId;
    private String address;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
}

/**
 * Value that indicates that default {@link java.util.TimeZone}
 * (from deserialization or serialization context) should be used:
 * annotation does not define value to use.
 *<p>
 * NOTE: default here does NOT mean JVM defaults but Jackson databindings
 * default, usually UTC, but may be changed on <code>ObjectMapper</code>.
 */
public final static String DEFAULT_TIMEZONE = "##default";

JsonFormat里面默认时区是UTC，所以转化后时间不对

2种解决方式：

1、在每个要转的字段上面增加注解属性指定时区

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.util.Date;

@Data
public class Address {
    private Long id;
    private Long userId;
    private String address;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
    private Date createTime;
}

2、添加bean，增加一个属性，指定时区

@Bean
public Jackson2ObjectMapperBuilderCustomizer jacksonObjectMapperCustomization() {
    return jacksonObjectMapperBuilder -> jacksonObjectMapperBuilder.timeZone(TimeZone.getTimeZone("GMT+8"));
}

转载于:https://my.oschina.net/u/3777515/blog/3004393