部分项目可能需要同时适配多种不同的数据库,比如常见的 Mysql、Oracle、Mssql 这几种数据库。首先我们准备测试数据库环境。
测试数据库准备
为了快速测试,我们使用 Docker 快速搭建数据库环境,对应的 Docker Compose 文件内容如下:
Mysql
version: '2' services: mysql: image: mysql:8.0.20 volumes: - ./mysql/data/:/var/lib/mysql/ environment: - MYSQL_USER=root - MYSQL_ALLOW_EMPTY_PASSWORD=yes - MYSQL_DATABASE=mysql ports: - 3306:3306 command: mysqld --lower_case_table_names=1 --skip-ssl --character_set_server=utf8mb4 --explicit_defaults_for_timestamp
Oracle
version: '3' services: oracle-db: image: oracleinanutshell/oracle-xe-11g:latest restart: always volumes: - ./mysql/data/:/u01/app/oracle/ environment: - ORACLE_PWD=oracle # DB_CONN: credentials (system/sys:oracle) - ORACLE_ALLOW_REMOTE=true - ORACLE_DISABLE_ASYNCH_IO=true - ORACLE_ENABLE_XDB=true - DBCA_TOTAL_MEMORY=1024 ports: - 1521:1521 - 1522:8080 # for apex
由于 Oracle 数据库一个用户对应一个 Schema,要创建一个数据库,我们就得先创建对应的用户,创建用户又分为以下几个步骤:
使用 system 账号(DBA 角色)创建用户
CREATE USER "username" IDENTIFIED BY "password" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
分配空间
ALTER USER "spc" QUOTA UNLIMITED ON "SYSTEM";
为用户授予基本的权限,比如创建序列、创建表、建立连接
GRANT CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW TO "username";
如果使用
Liqubibase
,会有下面的警告:Liquibase needs to access the DBA_RECYCLEBIN table so we can automatically handle the case where constraints are deleted and restored. Since Oracle doesn’t properly restore the original table names referenced in the constraint, we use the information from the DBA_RECYCLEBIN to automatically correct this issue.
需要使用
sys
账号以SYSDBA
角色登陆,如下面的 Oracle SQL Developer,然后执行:GRANT SELECT ON SYS.DBA_RECYCLEBIN TO “username”;
对于正式环境的 Oracle 使用,请从 Oracle 官方镜像注册中心获取,强烈建议使用 19c 长期支持版本(直到 2027 年)。
可以在以下位置找到 Oracle 数据库 19c(EE,SE2,单实例和 RAC)Docker 镜像:https://container-registry.oracle.com
可以在以下位置找到 Oracle 数据库 19c Docker 构建文件:https://github.com/oracle/docker-images/tree/master/OracleDatabase
Mssql
version: '2' services: mssql: image: mcr.microsoft.com/mssql/server:2017-latest-ubuntu # volumes are not supported on macOS volumes: - ./mssql/data/:/var/opt/mssql/data/ environment: - ACCEPT_EULA=Y - MSSQL_PID=Express - SA_PASSWORD=yourStrong(!)Password - MSSQL_DATABASE=mssql - MSSQL_SLEEP=60 ports: - 1433:1433 command: /bin/bash -c '/opt/mssql/bin/sqlservr & echo "wait $$MSSQL_SLEEP sec for DB to start "; sleep $$MSSQL_SLEEP; /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -d tempdb -q "EXIT(CREATE DATABASE $$MSSQL_DATABASE)"; wait;'
依赖引入
这里我们以 Gradle 为例,对于不同的 profile 选择不同的依赖,由于项目使用 jhipster BOM,这里没有指明版本。
if (project.hasProperty("oracle")) {
implementation "com.oracle.database.jdbc:ojdbc8"
liquibaseRuntime "com.oracle.database.jdbc:ojdbc8"
} else if (project.hasProperty("mssql")) {
implementation "com.microsoft.sqlserver:mssql-jdbc"
liquibaseRuntime "com.microsoft.sqlserver:mssql-jdbc"
} else {
implementation "mysql:mysql-connector-java"
liquibaseRuntime "mysql:mysql-connector-java"
}
开发环境配置文件
数据库需要的库及用户名和密码都有了,对于 SpringBoot 项目可以建立不同环境的 profile 文件,这里仅说明核心的配置信息:
Mysql
application-mysql.yml
spring: datasource: type: com.zaxxer.hikari.HikariDataSource url: jdbc:mysql://localhost:3306/spc?useUnicode=true&characterEncoding=utf8&useSSL=false&&allowPublicKeyRetrieval=true username: root password: root jpa: properties: hibernate.id.new_generator_mappings: false
Oralce
application-oracle.yml
spring: datasource: type: com.zaxxer.hikari.HikariDataSource url: jdbc:p6spy:oracle:thin:@192.168.1.194:1521:xe username: user password: password jpa: properties: hibernate.id.new_generator_mappings: true
Mssql
application-mssql.yml
spring: datasource: type: com.zaxxer.hikari.HikariDataSource url: jdbc:p6spy:sqlserver://192.168.1.194:1433;database=spc username: SA password: yourStrong(!)Password jpa: properties: hibernate.id.new_generator_mappings: false
这里有一个核心的配置项 hibernate.id.new_generator_mappings
,该属性在 5.0 之后的 hibernate 中默认为 true
,为 true
时如果主键生成策略为 AUTO
,则将处理为基于序列的 SequenceStyleGenerator
,如果数据库支持序列,将使用序列,否则使用基于单表(TABLE
)维护的主键生成器。这里我们为 Oracle 使用设置为 true
。
为 false
时将回退到数据库方言中原生的主键策略,具体由 Dialect#getNativeIdentifierGeneratorStrategy 返回值决定(identity
或 sequence
)。由于 mysql
、mssql
本身支持自增主键,这里我们使用数据库原生的自增主键策略。
实体配置
实体没有太多配置,只需要设置主键的策略为 AUTO
就行。
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
下方配置可选
由于 Oracle 使用序列作为主键生成策略,hibernate 将会在我们新增实体时获取下一个序列号,序列的名称默认为:hibernate_sequence
。
我们也可以为每个表指定一个序列,或者为每个表其指定表名 + 可选后缀的序列名。
@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "sequence-generator")
@GenericGenerator(
name = "sequence-generator",
strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
parameters = {
@Parameter(name = SEQUENCE_PARAM, value = "user_sequence"), // 使用固定的序列名
@Parameter(name = CONFIG_PREFER_SEQUENCE_PER_ENTITY, value = "true"), // 每个表一个序列,基于表名+可选后缀的形式
@Parameter(name = CONFIG_SEQUENCE_PER_ENTITY_SUFFIX, value = "sequence"), // 可选后缀
@Parameter(name = INITIAL_PARAM, value = "4"),
@Parameter(name = INCREMENT_PARAM, value = "1")
}
)
private long id;
Liquibase 或脚本配置
初始化 Oracle 序列
由于我们没有使用 hibernate 的自动建表,所以需要手动创建相应的序列,以默认的 hibernate_sequence
为例,对于 Liquibase 需要,增加如下 ChangeSet:
<changeSet id="00000000000000" author="Zeral" dbms="oracle">
<createSequence sequenceName="hibernate_sequence" minValue="1" startValue="20" incrementBy="1"/>
</changeSet>
dbms 指这条变更集针对的数据库为 oracle。
如果加载了带有主键的数据,请注意序列的起始值为数据中主键最大的下一个数字。
Mssql 加载带有主键的数据
由于 Mssql 表默认不允许带有主键的数据进行新增,要加载带有主键的数据,Liquibase 可以做如下配置:
<changeSet id="20191118183100" author="Zeral">
<sql dbms="mssql">
SET IDENTITY_INSERT base_factor ON;
</sql>
<loadData encoding="UTF-8"
file="config/liquibase/base_factor.csv"
separator=";"
tableName="base_factor">
<column name="is_auto_report_necessary" type="boolean"/>
</loadData>
<sql dbms="mssql">
SET IDENTITY_INSERT base_factor OFF;
</sql>
</changeSet>
及使用 SET IDENTITY_INSERT 表名 ON/OFF;
包裹加载数据脚本,并设置 dbms
仅对 mssql
作用。
数据库字段类型选择
在用 Liquibase 创建表字段时,选择合适的类型,不要使用特定于数据库的数据类型。
Data types mapping in Liquibase 3.6.x
Liquibase data type | SQL Server data type | Oracle data type | MySQL | PostgreSQL |
---|---|---|---|---|
bigint | bigint | number(38,0) | bigint | bigint/bigserial |
blob | varbinary(max) | blob | blob | oid |
boolean | bit | number(1) | bit | bit |
char | char | char | char | character |
clob | nvarchar(max) | clob | longtext | text |
currency | money | number(15,2) | decimal | decimal |
datetime | datetime | timestamp | timestamp | timestamp |
date | date | date | date | date |
decimal | decimal | decimal | decimal | decimal |
double | float | float(24) | double | double precision |
float | float | float | float | float |
int | int | integer | int | integer/serial |
mediumint | int | mediumint | mediumint | mediumint |
nchar | nchar | nchar | nchar | nchar |
nvarchar | nvarchar | nvarchar2 | nvarchar | varchar |
number | numeric | number | numeric | numeric |
smallint | smallint | number(5) | smallint | smallint/smallserial |
time | time | date | time | time |
timestamp | datetime | timestamp | timestamp | timestamp |
tinyint | tinyint | number(3) | tinyint | smallint |
uuid | uniqueidentifier | raw(16) | char(36) | uuid |
varchar | varchar | varchar2 | varchar | varchar/character (varying) |
其它注意
原生语句
尽量避免使用原生语句,如果必须使用原生语句,请保证语法相对简单,能适配不同数据库。
很多时候为了安全性等,登录用户和当前 schema 并不是一定保持一致,所以执行语句时必须显式指明当前操作的 schema 下的表,可以通过在要执行语句中所有表名前加上
{h-domain}
即可,详情查看官方使用指南相关链接,例:select * from {h-domain}person where age(hired_on) < '30 days';
Oracle 长度限制
对于 Oracle 版本 19c, 18c and 12cR2:
- 实体名称不能超过 124 个字符,这是由于 Oracle 对对象名称的限制为 128 个字符,并且我们保留 4 个字符来为生成的表生成主键序列。
- 实体字段名称不能超过 128 个字符。
- 建立关系时,外键名称不能超过 128 个字符,因此,如果它们太长,将被截断。
- 在进行多对多关系时,联接表名称将遵循 JPA规范(格式为 “firstTable_secondTable”):如果长度超过 128 个字符,则将其截断。
- 数据库索引名称不能超过 128 个字符。
对于 Oracle 版本 11g,12cR1:
- 实体名称不能超过 26 个字符,这是由于 Oracle 对对象名称的限制为 30 个字符,并且我们保留 4 个字符来为生成的表生成主键序列。
- 实体字段名称不能超过 30 个字符。
- 建立关系时,外键名称不能超过 30 个字符,因此,如果它们太长,则会被截断。
- 在进行多对多关系时,联接表名称将遵循 JPA 规范(格式为 “firstTable_secondTable”):如果长度超过 30 个字符,则将其截断。
关键字使用注意
在为实体或字段起名时,避免使用到相关数据库的关键字和保留关键字,可以通过加限制前缀来避免。下方罗列可能和你使用版本有差异。
Mysql 相关关键字链接:https://dev.mysql.com/doc/refman/8.0/en/keywords.html
Oracle 相关关键字链接:https://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
Mssql 相关关键字链接:https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/reserved-keywords?view=sql-server-ver15