大金仓数据库:kingbase学习

1.简介

官网: https://www.kingbase.com.cn/

2. 安装

TODO 补充 docker安装

3. 基础使用

3.1 客户端连接

使用idea自带database查询

3.2 表数据测试

3.2.1 建表

创建从10000开启自增id的用户表，username增加唯一索引

CREATE TABLE account(
    id          BIGINT      NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10000),
    username    VARCHAR(32) NOT NULL,
    password    VARCHAR(32)  DEFAULT NULL,
    name        VARCHAR(20)  DEFAULT NULL,
    sex         CHAR(1)      DEFAULT NULL,
    phone       VARCHAR(100) DEFAULT NULL,
    email       VARCHAR(100) DEFAULT NULL,
    create_time TIMESTAMP    DEFAULT NULL,
    update_time TIMESTAMP    DEFAULT NULL,
    extend_json JSON         DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE (username)
);

创建字段备注

TODO

3.2.2 数据写入测试

-- 插入第一行数据
INSERT INTO account (username, password, name, sex, phone, email, create_time, update_time, extend_json)
VALUES ('user1', 'pass1', 'John Doe', 'M', '123456789', 'user1@example.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '{"key": "value1"}');

-- 插入第二行数据
INSERT INTO account (username, password, name, sex, phone, email, create_time, update_time, extend_json)
VALUES ('user2', 'pass2', 'Jane Doe', 'F', '987654321', 'user2@example.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '{"key": "value2"}');

-- 插入第三行数据
INSERT INTO account (username, password, name, sex, phone, email, create_time, update_time, extend_json)
VALUES ('user3', 'pass3', 'Bob Smith', 'M', '555555555', 'user3@example.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '{"key": "value3"}');


INSERT INTO account (username, password, name, sex, phone, email, create_time, update_time, extend_json)
VALUES
('user4', 'pass1', 'John Doe', 'M', '123456789', 'user1@example.com', '2022-01-01 10:00:00', '2022-01-01 10:00:00', '{"key": "value1"}'),
('user5', 'pass2', 'Jane Doe', 'F', '987654321', 'user2@example.com', '2022-01-01 11:00:00', '2022-01-01 11:00:00', '{"key": "value2"}'),
('user6', 'pass3', 'Bob Smith', 'M', '555555555', 'user3@example.com', '2022-01-01 12:00:00', '2022-01-01 12:00:00', '{"key": "value3"}');

3.2.2 json测试

3.2.2.1 json查询测试

json指定key查询

SELECT * FROM account WHERE extend_json ->> 'address' LIKE '%吕街28号%';

SELECT * FROM account WHERE extend_json ->> 'address' = 'Suite 231 吕街28号, 包头, 藏 351579';

查询性能对比(100万数据)

• like查询性能结果

数据库名	json like SQL 查询内容	耗时 (毫秒)
kingbase	SELECT * FROM account_b WHERE extend_json ->> ‘address’ LIKE ‘%吕街28号%’	598
oracle	SELECT * FROM ACCOUNT a WHERE JSON_VALUE(extend_json, ‘$.address’) LIKE ‘%吕街28号%’	1412
mysql	SELECT * FROM account WHERE extend_json -> ‘$.address’ LIKE ‘%吕街28号%’	1947
openGauss	SELECT * FROM account_b WHERE extend_json ->> ‘address’ LIKE ‘%吕街28号%’	1952
dm	SELECT * FROM ACCOUNT a WHERE JSON_VALUE(extend_json, ‘$.address’) LIKE ‘%吕街28号%’	4196

• 精确查询性能结果

数据库名	json equals SQL 查询内容	耗时 (毫秒)
kingbase	SELECT * FROM account_b WHERE extend_json ->> ‘address’ = ‘Suite 231 吕街28号, 包头, 藏 351579’	554
oracle	SELECT * FROM ACCOUNT a WHERE JSON_VALUE(extend_json, ‘$.address’) = ‘Suite 231 吕街28号, 包头, 藏 351579’	1198
mysql	SELECT * FROM account WHERE extend_json -> ‘$.address’ = ‘Suite 231 吕街28号, 包头, 藏 351579’	1326
openGauss	SELECT * FROM account_b WHERE extend_json ->> ‘address’ = ‘Suite 231 吕街28号, 包头, 藏 351579’	1584
dm	SELECT * FROM ACCOUNT a WHERE JSON_VALUE(extend_json, ‘$.address’) = ‘Suite 231 吕街28号, 包头, 藏 351579’	3992

3.2.2.2 json修改测试

测试json内部数据操作便利性: 如删除指定key，增加写入指定key, 从其他数据列提取指定数据

-- json字段中的删除指定key
update account_b set extend_json = extend_json::jsonb - 'newKey' where id = 10000;
-- json字段中增加指定key并设置值--设置字符串
update account_b set extend_json = jsonb_set(extend_json, '{address}', '"hello"'::jsonb) where id = 10000;
-- json字段中增加指定key并设置值--设置设置数字
update account_b set extend_json = jsonb_set(extend_json, '{newInt}', '1000'::jsonb) where id = 10000;
-- json字段中增加指定key并且value值从其他列提取
update account_b set extend_json = jsonb_set(extend_json, '{email}', to_jsonb(email)) where id = 10000;

3.2.2.3 json数据迁移测试

-- 模拟增加一个列
ALTER TABLE account_b ADD address VARCHAR(500);
-- 新增列提取json字段中指定key的value
update account_b set account_b.address = extend_json ->> 'address';
-- 新增列提取json字段中指定key的value, 并且移除被提取的key
update account_b set address = extend_json ->> 'address', extend_json = extend_json::jsonb - 'address'

• 提取效果
  
• 提取并清理效果
  

4.springboot实战

使用依赖版本:

<spring-boot.version>2.3.2.RELEASE</spring-boot.version>

4.1 maven依赖

        <!--人大金仓数据库驱动-->
        <dependency>
            <groupId>cn.com.kingbase</groupId>
            <artifactId>kingbase8</artifactId>
            <version>8.6.0</version>
        </dependency>

4.2 连接配置

application.yml补充kingbase连接

spring:
  datasource:
    driver-class-name: dm.jdbc.driver.DmDriver
    url: jdbc:kingbase8://192.168.8.33:54321/testdb
    username: system
    password: kingbase

4.3 mybatis-plus测试

TODO

4.4 liquibase整合

liquibase基本不支持国产数据库，但是kingbase借助切换pg的驱动可以间接实现

(1). 使用pg方式替换kingbase驱动上面

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

(2). 修改jdbc url配置

spring:
  datasource:
    #driver-class-name: dm.jdbc.driver.DmDriver
    #url: jdbc:kingbase8://192.168.8.33:54321/testdb
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://192.168.8.33:54321/testdb
    username: system
    password: kingbase

参考文档

1. 官网文档:4. Liquibase注意点