人大金仓开发笔记

技术交流
, , , ,
1

下载安装

官网下载

  • 服务与支持》下载中心》数据库》选择版本》类型（docker）
  • docker load -i导入docker镜像
  • 下载中心》授权文件》数据库授权文件》选择自己想要的授权文件上传
  • unzip license_xxx.zip解压

docker运行

docker run -dit --name kingbase \
    --privileged \
	-p 54321:54321 \
	-v /root/license_41249/license_41249_0.dat:/home/kingbase/userdata/etc/license.dat \
	-e DB_USER=root \
	-e PASSWORD=root \
	kingbase_v009r001c002b0014_single_x86:v1

工具连接

下载jar驱动

  • 人大金仓官网》下载中心》软件版本》接口驱动》接口驱动类型为JDBC

下载dbeaver

打开dbeaver》数据库》驱动管理器》新建

驱动名称：自行定义，如：kingbase9
类名：com.kingbase8.Driver
url模板：jdbc:kingbase8://{host}:{port}/[{database}]
默认端口：54321
库：添加文件》选择本地下载的jar

数据库

-- 设置schema，不设定schema的话，查询需要指定schema:select * from wmsys.table
set search_path to 'wmsys';
set search_path = 'wmsys';

-- =============手动分区==============
-- 手动分区必须创建默认分区，不然分区范围外的数据无法插入
CREATE TABLE wmsys.s_material (
--    id SERIAL PRIMARY KEY,
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    matl_code VARCHAR(50),
    matl_name VARCHAR(100),
    ds VARCHAR(7)
) PARTITION BY RANGE(ds);

COMMENT ON TABLE wmsys.s_material IS '分区表';
COMMENT ON COLUMN wmsys.s_material.id IS '主键id';
COMMENT ON COLUMN wmsys.s_material.matl_code IS '物料编码';
COMMENT ON COLUMN wmsys.s_material.matl_name IS '物料名称';
COMMENT ON COLUMN wmsys.s_material.ds IS '分区，格式为yyyy-MM';

CREATE TABLE wmsys.s_material_202501 PARTITION OF wmsys.s_material FOR VALUES FROM ('2025-01') TO ('2025-02');
CREATE TABLE wmsys.s_material_202502 PARTITION OF wmsys.s_material FOR VALUES FROM ('2025-02') TO ('2025-03');

-- 创建默认分区
CREATE TABLE wmsys.s_material_default PARTITION OF wmsys.s_material DEFAULT;


-- 自定范围分区，小于2025年的数据存入这个分区中
ALTER TABLE wmsys.s_material ADD PARTITION s_material_less_202412 VALUES LESS THAN (to_date('2025-01','yyyy-mm'));

-- =============自动分区================
-- 自动分区【按月分区，一个月的数据存入到一个分区表中，小于2024-01的数据放入s_material_default_202412中】，分区自动命名为：table_p1~pN
CREATE TABLE wmsys.s_material (
--    id SERIAL PRIMARY KEY,
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    matl_code VARCHAR(50),
    matl_name VARCHAR(100),
    ds VARCHAR(7)
) PARTITION BY RANGE(to_date(ds, 'yyyy-mm')) INTERVAL ('1 MONTH')
(
    PARTITION default_202412 VALUES LESS THAN (to_date('2024-01','yyyy-mm'))
);

-- 查看数据所处分区
explain SELECT *, tableoid::regclass FROM wmsys.s_material where matl_name = '物料A02';

INSERT INTO wmsys.s_material (id, matl_code, matl_name, ds) VALUES(4, 'A04', '物料A4', '2023-02');
INSERT INTO wmsys.s_material (id, matl_code, matl_name, ds) VALUES(1, 'A01', '物料A', '2024-10');
INSERT INTO wmsys.s_material (id, matl_code, matl_name, ds) VALUES(2, 'A02', '物料A02', '2025-01');
INSERT INTO wmsys.s_material (id, matl_code, matl_name, ds) VALUES(3, 'A03', '物料A03', '2025-02');


-- ============索引===========
-- 创建索引 create [unique] index
create index if not exists idx_s_material_matl_code on wmsys.s_material (matl_code);
create index if not exists idx_s_material_matl_name on wmsys.s_material (matl_name);

-- 删除索引
drop index wmsys.idx_s_material_matl_name;

-- 设置表备注
comment on table wmsys.s_material is '分区表1';
-- 设置字段备注
comment on column wmsys.s_material.matl_code is '物料编码';


-- ==============表操作================
-- 添加字段
alter table wmsys.s_material add column deleted int;
-- 删除字段
ALTER TABLE wmsys.s_material DROP COLUMN deleted;
-- 修改字段名
ALTER TABLE wmsys.s_material RENAME COLUMN deleted TO del;
-- 修改字段类型
ALTER TABLE wmsys.s_material ALTER COLUMN del TYPE tinyint;
-- 修改表名
ALTER TABLE material RENAME TO s_material;

-- 授权，给自定用户授权
GRANT SELECT, UPDATE, USAGE ON ALL SEQUENCES IN SCHEMA schema_name TO user_name;

-- 若有新加表，则需要执行下面的脚本
 GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO user_name;


-- 关闭严格分组group by
set sql_mode = '';
-- 开启严格分组
set sql_mode = 'ONLY_FULL_GROUP_BY';
select * from wmsys.s_material group by matl_code;

-- 关闭顺序扫描，加快查询效率，对于超大数据量查询有效
SET enable_seqscan = off;
explain select * from wmsys.s_material;
SET enable_seqscan = on;



-- 查询活跃且等待锁的进程
SELECT * FROM sys_stat_activity WHERE state != 'idle';

-- 根据pid释放死锁
SELECT sys_terminate_backend(pid);

Java

yml配置

spring:
  datasource:
    driverClassName: com.kingbase8.Driver
    url: jdbc:kingbase://localhost:54321/dabase_name?currentSchema=schemaName
    username: root
    password: root
    #init-sql: SET sql_mode=''; SET enable_seqscan = off
    # SET sql_mode=''关闭严格分组group by
    # SET enable_seqscan = off关闭顺序查询
    initConnectionSqls: SET sql_mode='';SET enable_seqscan = off

JavaConfig

@Order
@Configuration
public class DruidDataSourceConfig {

    @Value("${spring.datasource.initConnectionSqls}")
    private String initConnectionSqls;

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DruidDataSource dataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDbType(DbType.kingbase);
        if (StringUtils.hasText(initConnectionSqls)) {
            druidDataSource.setConnectionInitSqls(Arrays.stream(initConnectionSqls.split(";")).toList());
        }
        return druidDataSource;
    }
}
2 Likes