这里先不管JDBC Type 与Java Type以及JDBC Type与数据库类型的详细映射,只列举部分以前不怎么用的JPA中Java Type 与数据库类型的匹配:
boolean与TINYINT\BIT
项目里面经常会遇到bolean型,数据库字段除了使用TINYINT,也可以用BIT(1):
@Column(name = "is_active")
private boolean active;
@Column(name = "is_success")
private Boolean success;
create table t_column_test(
...
is_active bit(1) default 1,
is_success bit(1)
);
Long/BigInteger 与BIGINT
Java 的Long型与MySQL的BIGINT,都是8字节,所以有符号的BIGINT与Long的可表示的数据范围一致,都是 [ − 2 63 , 2 63 − 1 ] [-2^{63},2^{63}-1] [−263,263−1]
private long id;
create table t_column_test(
id bigint
);
insert into t_column_test (id ) value (9223372036854775807);
对于 2 63 − 1 2^{63}-1 263−1,Long查询完全不是问题。但如果是无符号的bigint,超过 2 63 2^{63} 263,Java的Long就不合适了,比如插入 2 63 2^{63} 263=9223372036854775808
create table t_column_test(
id bigint unsigned
);
insert into t_column_test (id ) value (9223372036854775808);
Java类型还用Long的话查出来的数值变成了**-9223372036854775808**。这种情况下,应该用BigInteger。
String与DATE/TIME/DATETIME\TIMESTAMP
可以直接在Java里面使用String来与MySQL的日期时间类型进行匹配
@Column(name = "one_date")
private String oneDate;
@Column(name = "oneTate")
private String oneTime;
@Column(name = "ont_date_time")
private String ontDateTime;
@Column(name = "ont_timestamp")
private String ontTimestamp;
CREATE TABLE t_column_test (
...
BIRTHDAY date,
one_date date,
oneTate time,
ont_date_time datetime,
ont_timestamp timestamp,
)
只要符合MySQL日期类型的字符表示格式就可以,正常插入:
ColumnTest columnTest = new ColumnTest();
columnTest.setBirthday("2022-01-12");
columnTest.setOneDate("2021-12-31");
columnTest.setOneTime("12:12:12");
columnTest.setOntDateTime("2021-10-01 10:10:10");
columnTest.setOntTimestamp("2021-10-01 10:10:10");
entityManager.persist(columnTest);
需要注意的是,默认情况下,DATETIME类型查出来是2021-10-01T10:10:10。强行用也可以,但是有点自找麻烦。
Date\Calendar 与TIMESTAMP
@Column(name = "timestamp_1")
private Date date1;
@Column(name = "timestamp_2")
private Calendar date2;
CREATE TABLE t_column_test (
...
timestamp_1 timestamp,
timestamp_2 timestamp,
)
entityManager.getTransaction().begin();
ColumnTest columnTest = new ColumnTest();
columnTest.setId(BigInteger.ONE);
columnTest.setDate1(new Date());
columnTest.setDate2(Calendar.getInstance());
entityManager.persist(columnTest);
entityManager.getTransaction().commit();
查询数据库:
ID|timestamp_1 |timestamp_2 |
--+-------------------+-------------------+
1|2022-04-30 23:31:28|2022-04-30 23:31:28|
通过JPA查询出来,时间也是"2022-04-30T23:36:49.000+0800", 时区也是对的。
timestamp时区问题
timestamp是存储的时候会自动转成UTC时区,取得时候也会自动转回来,因此它相对DATETIME较好的地方就是它可以记录时区信息。但是如果处理不好,会造成Java读取到的时间的时区有问题。
比如,当前时间是北京时间 2022-05-01 21:51:01,但是默认情况下,读取now()看到时间差了8小时:
SELECT now()
-- 2022-05-01 13:51:01
原因是读取的时候,timestamp会转换为time_zone变量对应的时区, 而默认情况下是UTC时区:
show variables like '%time_zone%';
Variable_name |Value |
----------------+------+
system_time_zone|UTC |
time_zone |SYSTEM|
可以修改会话级别的时区,这时候now()以及timestamp列读取的时间就是北京时间:
set time_zone= '+8:00';
SELECT now();
-- 2022-05-01 21:51:02
但是如果这个时候,用JPA来查询,读取到的时间是北京时间2022-05-01 13:51:01!时间差了8小时!这是因为在本地跑Java的时候TimeZone.getDefault()是“Asia/Shanghai”,是东八区,但是数据库用的却是UTC时区,两者不匹配。
第一种解决方式是在Java端,将默认时区改成UTC:
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
这种方式可以解决读取MySQL timestamp的时区问题,但是对于应用系统来说,也许会造成不少其他地方的时区问题。
第二种解决方式是修改MySQL的时区为东八区:
我用的是docker-compose.yml,加个TZ环境变量,值为Asia/Shanghai:
version: '3.7'
services:
db:
image: mysql:8.0
command: --default-authentication-plugin=mysql_native_password
restart: always
ports:
- 13306:3306
volumes:
- ./mysql_8_0_data:/var/lib/mysql
environment:
MYSQL_ROOT_PASSWORD: 12345678
MYSQL_DATABASE: test
TZ: Asia/Shanghai
可以看到系统时区已经变成了CST:
Variable_name |Value |
----------------+------+
system_time_zone|CST |
time_zone |SYSTEM|
select now();
-- 2022-05-01 22:31:58.000,是北京时间
这时候使用JPA读取到的时间也是对的,是北京时间的当前时间:
create table t_column_test(
id int,
one_date timestamp,
one_calendar timestamp
);
insert into t_column_test(
id,one_date ,one_calendar
)
values(2, now(),now());
TypedQuery<ColumnTest> query = entityManager.createQuery("select t from ColumnTest t", ColumnTest.class);
query.getResultList();
// 时间都是2022-05-01T22:31:58.000+0800
第三种方式修改jdbc链接参数
show variables like '%time_zone%';
Variable_name |Value |
----------------+------+
system_time_zone|UTC |
time_zone |SYSTEM|
数据库的时区是UTC,JDBC 的url里面也指定serverTimezone=UTC
jdbc:mysql://localhost:13306/test?useTimezone=true&serverTimezone=UTC
这时候,JPA读到的时间也是本地时间2022-05-01T22:31:58.000+0800。而如果去掉useTimezone=true&serverTimezone=UTC,读取到的时间是2022-05-01T14:31:58.000+0800。可以看出,参数是起作用了的。
再次修改MySQL的系统时区为Asia/Shanghai:
Variable_name |Value |
----------------+------+
system_time_zone|CST |
time_zone |SYSTEM|
指定serverTimezone=Asia/Shanghai
jdbc:mysql://localhost:13306/test?useTimezone=true&serverTimezone=Asia/Shanghai
读取到的时间也是对的,是北京时间2022-05-01T22:31:58.000+0800。
即便这时候将Java应用端的默认时区改成UTC,读取到的时间是2022-05-01T14:31:58.000Z,换算成北京时间也是对的。
LocalDate、LocalTime、LocalDateTime与DATE、TIME、TIMESTAMP
Java 8新增的时间类型也可与MySQL的日期时间进行匹配:
@Column(name = "local_date")
private LocalDate localDate;
@Column(name = "local_time")
private LocalTime localTime;
@Column(name = "local_date_time")
private LocalDateTime localDateTime;
CREATE TABLE t_column_test (
ID BIGINT NOT NULL,
local_date DATE,
local_date_time timestamp,
local_time TIME(3),
PRIMARY KEY (ID))
entityManager.getTransaction().begin();
ColumnTest columnTest = new ColumnTest();
columnTest.setId(BigInteger.ONE);
columnTest.setLocalDate(LocalDate.now());
columnTest.setLocalTime(LocalTime.now());
columnTest.setLocalDateTime(LocalDateTime.now());
entityManager.persist(columnTest);
entityManager.getTransaction().commit();
ID|local_date|local_date_time |local_time |
--+----------+-------------------+------------------+
1|2022-04-30|2022-04-30 23:44:03|23:44:03.383000000|
LocalDateTime 也可以与DATETIME进行匹配。