在 Ubuntu 环境下安装配置电科金仓 KingbaseES V8.6 数据库，并基于 Spring Boot 2.7.x 框架实现集成的完整流程。内容包括 JDBC 驱动获取与配置、基础连接查询事务操作、MyBatis-Plus 实体类与 Mapper 映射、以及 RESTful 接口开发示例。通过 pom.xml 依赖管理与 application.yml 数据源配置，展示了如何快速完成国产数据库适配，涵盖从建表到应用开发的实践细节。
本文分享了 KingbaseES V8.6 数据库与 Spring Boot 2.7.x 框架的集成实战经验。内容包括环境准备、JDBC 基础操作、Spring Boot 项目配置、MyBatis-Plus 集成及 RESTful 接口开发示例。
首先需要在服务器上安装 KingbaseES 数据库。这里以 Ubuntu 环境为例：
# 创建用户
useradd kingbase
passwd kingbase
# 创建安装目录
mkdir -p /opt/kingbase/ES/V8
chown -R kingbase:kingbase /opt/kingbase
# 解压安装包（假设安装包已经下载到/opt/software）
cd /opt/software
tar -xvf KingbaseES_V008R006C007B0024_Lin64_single_install.tar.gz
# 开始安装
./setup.sh -i console
安装过程中需要注意：
/kingbase/dataKingbase 的 JDBC 驱动获取比较麻烦，有几种方式：
方式 1：官网下载 访问电科金仓官网（需注册账号）：
wget https://www.kingbase.com.cn/download/jdbc/kingbase8-8.6.0.jar
方式 2：安装目录获取 安装完成后，驱动位于：
/opt/kingbase/ES/V8/Interface/jdbc/kingbase8-8.6.0.jar
方式 3：Maven 仓库（推荐） 如果公司搭建了私有仓库，可以上传后使用：
<dependency>
<groupId>cn.com.kingbase</groupId>
<artifactId>kingbase8</artifactId>
<version>8.6.0</version>
</dependency>
个人建议创建一个单独数据库，不然跟之前表有可能跟系统库冲突。
-- 连接数据库
ksql -U system -d testdb -p 54321
-- 创建测试数据库
CREATE DATABASE devdb WITH OWNER = system ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8';
-- 切换到新数据库
\c devdb
创建测试表 king_user：
-- 创建测试表
CREATE TABLE IF NOT EXISTS king_user (
user_id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
real_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
status SMALLINT DEFAULT 1,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
创建对应的索引，方便快速查询：
-- 创建索引
CREATE INDEX idx_sys_user_username ON king_user(username);
CREATE INDEX idx_sys_user_status ON king_user(status);
CREATE INDEX idx_sys_user_create_time ON king_user(create_time);
-- 插入测试数据
INSERT INTO king_user (username, password, real_name, email, phone) VALUES
('admin', 'e10adc3949ba59abbe56e057f20f883e', '管理员', '[email protected]', '13800138000'),
('zhangsan', 'e10adc3949ba59abbe56e057f20f883e', '张三', '[email protected]', '13900139000'),
('lisi', 'e10adc3949ba59abbe56e057f20f883e', '李四', '[email protected]', '13700137000');
创建订单表和插入测试数据：
-- 创建订单表
CREATE TABLE IF NOT EXISTS biz_order (
order_id BIGSERIAL PRIMARY KEY,
order_no VARCHAR(32) NOT NULL UNIQUE,
user_id BIGINT NOT NULL,
product_name VARCHAR(200) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
unit_price DECIMAL(10,2) NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
order_status SMALLINT DEFAULT 1,
payment_status SMALLINT DEFAULT 0,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES king_user(user_id)
);
-- 创建订单表索引
CREATE INDEX idx_biz_order_user_id ON biz_order(user_id);
CREATE INDEX idx_biz_order_order_no ON biz_order(order_no);
CREATE INDEX idx_biz_order_create_time ON biz_order(create_time);
-- 插入测试订单数据
INSERT INTO biz_order (order_no, user_id, product_name, quantity, unit_price, total_amount)
(, , , , , ),
(, , , , , ),
(, , , , , );
创建一个简单的 Java 项目，先验证能否正常连接：
package com.example.kingbase;
import java.sql.*;
import java.util.Properties;
/**
* KingbaseES 基础连接测试
*/
public class BasicConnectionTest {
// 数据库连接信息
private static final String DB_URL = "jdbc:kingbase8://localhost:54321/devdb";
private static final String DB_USER = "system";
private static final String DB_PASSWORD = "123456";
private static final String DRIVER_CLASS = "com.kingbase8.Driver";
public static void main(String[] args) {
testBasicConnection();
testQuery();
testInsert();
testTransaction();
}
/**
* 基础连接测试
*/
public static void testBasicConnection() {
Connection conn = null;
try {
Class.forName(DRIVER_CLASS);
System.out.println();
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
System.out.println();
conn.getMetaData();
System.out.println( + metaData.getDatabaseProductName());
System.out.println( + metaData.getDatabaseProductVersion());
System.out.println( + metaData.getDriverVersion());
System.out.println( + metaData.getUserName());
} (ClassNotFoundException e) {
System.err.println( + e.getMessage());
} (SQLException e) {
System.err.println( + e.getMessage());
} {
(conn != ) {
{ conn.close(); System.out.println(); } (SQLException e) { e.printStackTrace(); }
}
}
}
}
/**
* 查询测试
*/
public static void testQuery() {
String sql = "SELECT user_id, username, real_name, email, create_time FROM king_user WHERE status = ? ORDER BY create_time DESC";
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, 1);
try (ResultSet rs = pstmt.executeQuery()) {
System.out.println("\n=== 用户列表 ===");
System.out.printf("%-10s %-20s %-20s %-30s %-20s%n", "用户 ID", "用户名", "真实姓名", "邮箱", "创建时间");
System.out.println("--------------------------------------------------------------------------------");
while (rs.next()) {
System.out.printf("%-10d %-20s %-20s %-30s %-20s%n",
rs.getLong("user_id"), rs.getString("username"), rs.getString("real_name"),
rs.getString("email"), rs.getTimestamp("create_time"));
}
}
} catch (SQLException e) {
System.err.println("查询失败：" + e.getMessage());
}
}
/**
* 插入测试
*/
public static void testInsert() {
String sql = "INSERT INTO king_user (username, password, real_name, email, phone) VALUES (?, ?, ?, ?, ?)";
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, "wangwu");
pstmt.setString(2, "e10adc3949ba59abbe56e057f20f883e");
pstmt.setString(3, "王五");
pstmt.setString(4, "[email protected]");
pstmt.setString(5, "13600136000");
int affectedRows = pstmt.executeUpdate();
System.out.println("\n插入成功，影响行数：" + affectedRows);
try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
if (generatedKeys.next()) {
long userId = generatedKeys.getLong(1);
System.out.println("新插入的用户 ID: " + userId);
}
}
} catch (SQLException e) {
System.err.println("插入失败：" + e.getMessage());
}
}
/**
* 事务测试
*/
public static void testTransaction() {
String insertUserSql = "INSERT INTO king_user (username, password, real_name) VALUES (?, ?, ?)";
String insertOrderSql = "INSERT INTO biz_order (order_no, user_id, product_name, quantity, unit_price, total_amount) VALUES (?, ?, ?, ?, ?, ?)";
Connection conn = null;
try {
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
conn.setAutoCommit(false);
// 执行插入逻辑...
conn.commit();
} catch (SQLException e) {
try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
e.printStackTrace();
} finally {
if (conn != null) {
try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
}
}
}
/**
* 批量删除
*/
public static void batchDelete() {
String sql = "DELETE FROM king_user WHERE username = ?";
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement pstmt conn.prepareStatement(sql)) {
conn.setAutoCommit();
String[] usernames = {, , };
(String username : usernames) {
pstmt.setString(, username);
pstmt.addBatch();
}
[] results = pstmt.executeBatch();
conn.commit();
System.out.println( + results.length);
} (SQLException e) {
e.printStackTrace();
}
}
/**
* 大数据量分批处理
*/
public static void largeDataProcessing() {
String selectSql = "SELECT user_id, username FROM king_user WHERE user_id > ? ORDER BY user_id ASC LIMIT ?";
String updateSql = "UPDATE king_user SET email = ? WHERE user_id = ?";
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement selectStmt = conn.prepareStatement(selectSql);
PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
conn.setAutoCommit(false);
long lastUserId = 0;
int batchSize = 1000;
boolean hasMoreData = true;
while (hasMoreData) {
selectStmt.setLong(1, lastUserId);
selectStmt.setInt(2, batchSize);
List<User> userList = new ArrayList<>();
try (ResultSet rs = selectStmt.executeQuery()) {
while (rs.next()) {
User ();
user.userId = rs.getLong();
user.username = rs.getString();
userList.add(user);
}
}
(userList.isEmpty()) {
hasMoreData = ;
;
}
(User user : userList) {
user.username + ;
updateStmt.setString(, newEmail);
updateStmt.setLong(, user.userId);
updateStmt.addBatch();
lastUserId = user.userId;
}
updateStmt.executeBatch();
conn.commit();
updateStmt.clearBatch();
System.out.println( + lastUserId);
}
System.out.println();
} (SQLException e) {
e.printStackTrace();
}
}
创建一个 Spring Boot 项目，完整的配置如下：
pom.xml 完整配置：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.18</version>
<relativePath/>
</parent>
<groupId>com.example</groupId>
<artifactId>kingbase-demo</artifactId>
<version>1.0.0</version>
<packaging>jar</packaging>
<name>kingbase-demo</name>
<description>人大金仓数据库集成示例</description>
11
8.6.0
3.5.3.1
1.2.20
org.springframework.boot
spring-boot-starter-web
org.springframework.boot
spring-boot-starter-validation
org.springframework.boot
spring-boot-starter-aop
cn.com.kingbase
kingbase8
${kingbase.version}
com.alibaba
druid-spring-boot-starter
${druid.version}
com.baomidou
mybatis-plus-boot-starter
${mybatis-plus.version}
org.springframework.boot
spring-boot-starter-test
test
org.apache.commons
commons-lang3
cn.hutool
hutool-all
5.8.22
org.springframework.boot
spring-boot-maven-plugin
server:
port: 8080
servlet:
context-path: /api
spring:
application:
name: kingbase-demo
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.kingbase8.Driver
url: jdbc:kingbase8://localhost:54321/devdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
username: system
password: 123456
druid:
initial-size: 10
min-idle: 10
max-active: 100
max-wait: 60000
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
filters: stat,wall,slf4j
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
这个实体类就是我们上面刚刚创建的表：
package com.example.kingbase.entity;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* 用户实体
*/
@Data
@TableName("king_user")
public class SysUser implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "user_id", type = IdType.ASSIGN_ID)
private Long userId;
@TableField("username")
private String username;
@TableField("password")
private String password;
@TableField("real_name")
private String realName;
@TableField("email")
private String email;
@TableField("phone")
private String phone;
@TableField("status")
private Integer status;
@TableField(value = "create_time", fill = FieldFill.INSERT)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
@TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime updateTime;
@TableField("deleted")
@TableLogic
private Integer deleted;
}
Mapper 接口：
package com.example.kingbase.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.kingbase.entity.SysUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* 用户 Mapper
*/
@Mapper
public interface SysUserMapper extends BaseMapper<SysUser> {
List<SysUser> selectByUsernameLike(@Param("username") String username);
int batchInsert(@Param("userList") List<SysUser> userList);
int countByStatus(@Param("status") Integer status);
}
mapper xml：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.kingbase.mapper.SysUserMapper">
<select resultType="com.example.kingbase.entity.SysUser">
SELECT user_id, username, password, real_name, email, phone, status, create_time, update_time, deleted
FROM king_user
WHERE username LIKE CONCAT('%', #{username}, '%') AND deleted = 0
ORDER BY create_time DESC
</select>
<insert parameterType="java.util.List">
INSERT INTO king_user (username, password, real_name, email, phone, status, create_time, update_time, deleted)
VALUES <foreach collection="userList" item="user" separator=",">
(#{user.username}, #{user.password}, #{user.realName}, #{user.email}, #{user.phone}, #{user.status}, #{user.createTime}, #{user.updateTime}, 0)
</foreach>
</insert>
<select resultType="java.lang.Integer">
SELECT COUNT(1) FROM king_user WHERE status = #{status} AND deleted = 0
</select>
</mapper>
服务接口：
package com.example.kingbase.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.kingbase.entity.SysUser;
import java.util.List;
/**
* 用户服务接口
*/
public interface ISysUserService extends IService<SysUser> {
boolean batchSaveUsers(List<SysUser> userList);
List<SysUser> getUsersByUsername(String username);
boolean updateUserStatus(Long userId, Integer status);
}
服务实现类：
package com.example.kingbase.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.kingbase.entity.SysUser;
import com.example.kingbase.mapper.SysUserMapper;
import com.example.kingbase.service.ISysUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDateTime;
import java.util.List;
/**
* 用户服务实现
*/
@Slf4j
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements ISysUserService {
@Override
@Transactional(rollbackFor = Exception.class)
public boolean batchSaveUsers(List<SysUser> userList) {
if (userList == null || userList.isEmpty()) return false;
LocalDateTime now = LocalDateTime.now();
for (SysUser user : userList) {
if (user.getStatus() == null) user.setStatus(1);
if (user.getCreateTime() == null) user.setCreateTime(now);
if (user.getUpdateTime() == null) user.setUpdateTime(now);
}
baseMapper.batchInsert(userList);
log.info(, insertCount);
insertCount == userList.size();
}
List<SysUser> {
(username == || username.trim().isEmpty()) list();
baseMapper.selectByUsernameLike(username);
}
{
getById(userId);
(user == ) {
log.warn(, userId);
;
}
user.setStatus(status);
user.setUpdateTime(LocalDateTime.now());
updateById(user);
}
}
控制层：
package com.example.kingbase.controller;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.kingbase.common.Result;
import com.example.kingbase.entity.SysUser;
import com.example.kingbase.service.ISysUserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
/**
* 用户管理控制器
*/
@Api(tags = "用户管理")
@RestController
@RequestMapping("/user")
public class SysUserController {
@Autowired
private ISysUserService userService;
@ApiOperation("分页查询用户")
@GetMapping("/page")
public Result<IPage<SysUser>> getUserPage(
@ApiParam("当前页") @RequestParam(defaultValue = "1") Integer current,
@ApiParam("每页条数") @RequestParam(defaultValue = "10") Integer size,
@ApiParam("用户名") @RequestParam(required = false) String username,
@ApiParam("状态") @RequestParam(required = false) Integer status) {
Page<SysUser> page = new Page<>(current, size);
LambdaQueryWrapper<SysUser> queryWrapper = <>();
(username != && !username.trim().isEmpty()) queryWrapper.like(SysUser::getUsername, username);
(status != ) queryWrapper.eq(SysUser::getStatus, status);
queryWrapper.orderByDesc(SysUser::getCreateTime);
IPage<SysUser> userPage = userService.page(page, queryWrapper);
Result.success(userPage);
}
Result<SysUser> {
userService.getById(id);
user != ? Result.success(user) : Result.error();
}
Result<String> {
(user.getUsername() == || user.getUsername().trim().isEmpty()) Result.error();
(user.getPassword() == || user.getPassword().trim().isEmpty()) Result.error();
LambdaQueryWrapper<SysUser> queryWrapper = <>();
queryWrapper.eq(SysUser::getUsername, user.getUsername());
(userService.count(queryWrapper) > ) Result.error();
(user.getStatus() == ) user.setStatus();
LocalDateTime.now();
user.setCreateTime(now);
user.setUpdateTime(now);
userService.save(user);
result ? Result.success() : Result.error();
}
Result<String> {
(userList == || userList.isEmpty()) Result.error();
(SysUser user : userList) {
(user.getUsername() == || user.getUsername().trim().isEmpty()) Result.error();
(user.getPassword() == || user.getPassword().trim().isEmpty()) Result.error();
}
userService.batchSaveUsers(userList);
result ? Result.success( + userList.size() + ) : Result.error();
}
Result<String> {
userService.getById(id);
(existingUser == ) Result.error();
(user.getRealName() != ) existingUser.setRealName(user.getRealName());
(user.getEmail() != ) existingUser.setEmail(user.getEmail());
(user.getPhone() != ) existingUser.setPhone(user.getPhone());
existingUser.setUpdateTime(LocalDateTime.now());
userService.updateById(existingUser);
result ? Result.success() : Result.error();
}
Result<String> {
(status != && status != ) Result.error();
userService.updateUserStatus(id, status);
result ? Result.success() : Result.error();
}
Result<String> {
userService.removeById(id);
result ? Result.success() : Result.error();
}
Result<String> {
(ids == || ids.isEmpty()) Result.error();
userService.removeByIds(ids);
result ? Result.success() : Result.error();
}
Result<List<SysUser>> {
List<SysUser> userList = userService.getUsersByUsername(username);
Result.success(userList);
}
Result<Integer> {
status == ? userService.count() : userService.count( <SysUser>().eq(SysUser::getStatus, status));
Result.success(count);
}
}
本文分享了 KingbaseES V8.6 数据库与 Spring Boot 2.7.x 框架的集成实战经验。涵盖了从数据库安装、驱动配置到应用开发的完整流程，包括 JDBC 基础操作、MyBatis-Plus 集成及 RESTful 接口开发，帮助开发者快速实现国产数据库适配。
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
查找任何按下的键的javascript键代码、代码、位置和修饰符。 在线工具，Keycode 信息在线工具，online
JavaScript 字符串转义/反转义；Java 风格 \uXXXX（Native2Ascii）编码与解码。 在线工具，Escape 与 Native 编解码在线工具，online
使用 Prettier 在浏览器内格式化 JavaScript 或 HTML 片段。 在线工具，JavaScript / HTML 格式化在线工具，online
Terser 压缩、变量名混淆，或 javascript-obfuscator 高强度混淆（体积会增大）。 在线工具，JavaScript 压缩与混淆在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online