摘要：本文分享了KingbaseES V8.6数据库与SpringBoot 2.7.x框架的集成实战经验。内容包括：1. 环境准备（Ubuntu系统安装配置、驱动获取方式）；2. JDBC基础操作（连接、查询、事务处理）；3. SpringBoot项目完整配置（pom依赖、数据源配置）；4. MyBatis-Plus集成（实体类、Mapper、Service层实现）；5. RESTful接口开发示例。文章提供了详细的代码示例，涵盖从数据库安装到应用开发的完整流程，帮助开发者快速实现国产数据库适配。
目录
前言
最近公司项目需要适配国产数据库，经过调研选择了电科金仓的KingbaseES。前前后后折腾了两周，踩了无数坑，从驱动配置到性能调优，每个环节都有故事。本文基于KingbaseES V8.6版本，JDK 1.8环境，Spring Boot 2.7.x框架，分享完整的实战经验。希望能帮助兄弟们少走弯路。
一、环境准备与驱动获取
1.1 数据库安装与配置
首先需要在服务器上安装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/data
端口默认54321，如果冲突需要修改
字符集选择UTF8，避免中文乱码
我往期作品有2篇文章分别详细介绍windows10和ubunu系统下安装步骤，大家有需要可以去参考参考：
零改造迁移实录：2000+存储过程从SQL Server滑入KingbaseES V9R4C12的72小时
在Ubuntu服务器上安装KingbaseES V009R002C012（Orable兼容版）数据库过程详细记录
1.2 JDBC驱动获取与配置
Kingbase的JDBC驱动获取比较麻烦，有几种方式：
方式1：官网下载https://www.kingbase.com.cn/download.html 访问电科金仓官网（需注册账号）：
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>1.3 创建测试数据库
个人建议创建一个单独数据库，不然跟之前表有可能跟系统库冲突，如下图：
-- 连接数据库
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', '管理员', 'admin@kingbase.com', '13800138000'),
('zhangsan', 'e10adc3949ba59abbe56e057f20f883e', '张三', 'zhangsan@test.com', '13900139000'),
('lisi', 'e10adc3949ba59abbe56e057f20f883e', '李四', 'lisi@test.com', '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) VALUES
('ORDER202312010001', 2, 'Java编程思想（第4版）', 2, 89.00, 178.00),
('ORDER202312010002', 2, 'Spring实战（第5版）', 1, 99.00, 99.00),
('ORDER202312010003', 3, 'MySQL必知必会', 3, 59.00, 177.00);
二、基础JDBC连接与操作
2.1 最基础的JDBC连接示例
创建一个简单的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 {
// 1. 加载驱动
Class.forName(DRIVER_CLASS);
System.out.println("驱动加载成功！");
// 2. 获取连接
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
System.out.println("数据库连接成功！");
// 3. 获取数据库元数据
DatabaseMetaData metaData = conn.getMetaData();
System.out.println("数据库产品名称: " + metaData.getDatabaseProductName());
System.out.println("数据库版本: " + metaData.getDatabaseProductVersion());
System.out.println("驱动版本: " + metaData.getDriverVersion());
System.out.println("用户名: " + metaData.getUserName());
} catch (ClassNotFoundException e) {
System.err.println("驱动类找不到: " + e.getMessage());
} catch (SQLException e) {
System.err.println("数据库连接失败: " + e.getMessage());
} finally {
if (conn != null) {
try {
conn.close();
System.out.println("数据库连接已关闭！");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.2 查询测试
/**
* 查询测试
*/
public static void testQuery() {
String sql = "SELECT user_id, username, real_name, email, create_time FROM sys_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());
}
}2.3 新增测试
/**
* 插入测试
*/
public static void testInsert() {
String sql = "INSERT INTO sys_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, "wangwu@test.com");
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());
}
}2.4 删除测试
/**
* 事务测试
*/
public static void testTransaction() {
String insertUserSql = "INSERT INTO sys_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
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 批量删除
*/
public static void batchDelete() {
String sql = "DELETE FROM sys_user WHERE username = ?";
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
// 删除测试数据
String[] usernames = {"test_user_1", "test_user_2", "test_user_3"};
for (String username : usernames) {
pstmt.setString(1, username);
pstmt.addBatch();
}
int[] results = pstmt.executeBatch();
conn.commit();
System.out.println("批量删除完成，删除条数: " + results.length);
} catch (SQLException e) {
e.printStackTrace();
}
}2.5 大数据分配处理
/**
* 大数据量分批处理
*/
public static void largeDataProcessing() {
String selectSql = "SELECT user_id, username FROM sys_user WHERE user_id > ? ORDER BY user_id ASC LIMIT ?";
String updateSql = "UPDATE sys_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 = new User();
user.userId = rs.getLong("user_id");
user.username = rs.getString("username");
userList.add(user);
}
}
if (userList.isEmpty()) {
hasMoreData = false;
continue;
}
// 更新这批数据
for (User user : userList) {
String newEmail = user.username + "@newdomain.com";
updateStmt.setString(1, newEmail);
updateStmt.setLong(2, user.userId);
updateStmt.addBatch();
lastUserId = user.userId;
}
updateStmt.executeBatch();
conn.commit();
updateStmt.clearBatch();
System.out.println("已处理至用户ID: " + lastUserId);
}
System.out.println("大数据量分批处理完成！");
} catch (SQLException e) {
e.printStackTrace();
}
}三、Spring Boot集成方案
3.1 完整项目配置
创建一个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>
<properties>
<java.version>11</java.version>
<kingbase.version>8.6.0</kingbase.version>
<mybatis-plus.version>3.5.3.1</mybatis-plus.version>
<druid.version>1.2.20</druid.version>
</properties>
<dependencies>
<!-- Spring Boot Starter -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!-- 人大金仓JDBC驱动 -->
<dependency>
<groupId>cn.com.kingbase</groupId>
<artifactId>kingbase8</artifactId>
<version>${kingbase.version}</version>
</dependency>
<!-- 数据库连接池 - Druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- MyBatis Plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<!-- 测试依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- 工具类 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.22</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>3.2 application.yml配置
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连接池配置
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
use-global-data-source-stat: true
# MyBatis Plus配置
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
cache-enabled: false
call-setters-on-nulls: true
jdbc-type-for-null: 'null'
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
id-type: ASSIGN_ID
logic-delete-field: deleted
logic-delete-value: 1
logic-not-delete-value: 0
mapper-locations: classpath*:mapper/**/*Mapper.xml
# 日志配置
logging:
level:
com.example.kingbase.mapper: debug
org.springframework.jdbc: debug3.3 实体类与Mapper
这个实体类就是我们上面刚刚创建的表
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;
/**
* 用户ID
*/
@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;
/**
* 状态 0-禁用 1-启用
*/
@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 id="selectByUsernameLike" resultType="com.example.kingbase.entity.SysUser">
SELECT user_id, username, password, real_name, email, phone, status, create_time, update_time, deleted
FROM sys_user
WHERE username LIKE CONCAT('%', #{username}, '%')
AND deleted = 0
ORDER BY create_time DESC
</select>
<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO sys_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 id="countByStatus" resultType="java.lang.Integer">
SELECT COUNT(1)
FROM sys_user
WHERE status = #{status}
AND deleted = 0
</select>
</mapper>3.4 服务层与控制层
服务接口：
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);
}
}
int insertCount = baseMapper.batchInsert(userList);
log.info("批量插入用户完成，插入条数：{}", insertCount);
return insertCount == userList.size();
}
@Override
public List<SysUser> getUsersByUsername(String username) {
if (username == null || username.trim().isEmpty()) {
return list();
}
return baseMapper.selectByUsernameLike(username);
}
@Override
@Transactional(rollbackFor = Exception.class)
public boolean updateUserStatus(Long userId, Integer status) {
SysUser user = getById(userId);
if (user == null) {
log.warn("用户不存在，userId：{}", userId);
return false;
}
user.setStatus(status);
user.setUpdateTime(LocalDateTime.now());
return updateById(user);
}
}控制层，就是我们平常用的controller
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 = new LambdaQueryWrapper<>();
if (username != null && !username.trim().isEmpty()) {
queryWrapper.like(SysUser::getUsername, username);
}
if (status != null) {
queryWrapper.eq(SysUser::getStatus, status);
}
queryWrapper.orderByDesc(SysUser::getCreateTime);
IPage<SysUser> userPage = userService.page(page, queryWrapper);
return Result.success(userPage);
}
/**
* 根据ID查询用户
*/
@ApiOperation("根据ID查询用户")
@GetMapping("/{id}")
public Result<SysUser> getUserById(@PathVariable Long id) {
SysUser user = userService.getById(id);
if (user == null) {
return Result.error("用户不存在");
}
return Result.success(user);
}
/**
* 新增用户
*/
@ApiOperation("新增用户")
@PostMapping
public Result<String> addUser(@RequestBody SysUser user) {
if (user.getUsername() == null || user.getUsername().trim().isEmpty()) {
return Result.error("用户名不能为空");
}
if (user.getPassword() == null || user.getPassword().trim().isEmpty()) {
return Result.error("密码不能为空");
}
// 检查用户名是否已存在
LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(SysUser::getUsername, user.getUsername());
if (userService.count(queryWrapper) > 0) {
return Result.error("用户名已存在");
}
// 设置默认值
if (user.getStatus() == null) {
user.setStatus(1);
}
LocalDateTime now = LocalDateTime.now();
user.setCreateTime(now);
user.setUpdateTime(now);
boolean result = userService.save(user);
if (result) {
return Result.success("用户新增成功");
} else {
return Result.error("用户新增失败");
}
}
/**
* 批量新增用户
*/
@ApiOperation("批量新增用户")
@PostMapping("/batch")
public Result<String> batchAddUsers(@RequestBody List<SysUser> userList) {
if (userList == null || userList.isEmpty()) {
return Result.error("用户列表不能为空");
}
// 校验数据
for (SysUser user : userList) {
if (user.getUsername() == null || user.getUsername().trim().isEmpty()) {
return Result.error("用户名不能为空");
}
if (user.getPassword() == null || user.getPassword().trim().isEmpty()) {
return Result.error("密码不能为空");
}
}
boolean result = userService.batchSaveUsers(userList);
if (result) {
return Result.success("批量新增用户成功，共" + userList.size() + "条");
} else {
return Result.error("批量新增用户失败");
}
}
/**
* 更新用户
*/
@ApiOperation("更新用户")
@PutMapping("/{id}")
public Result<String> updateUser(@PathVariable Long id, @RequestBody SysUser user) {
SysUser existingUser = userService.getById(id);
if (existingUser == null) {
return Result.error("用户不存在");
}
// 更新字段
if (user.getRealName() != null) {
existingUser.setRealName(user.getRealName());
}
if (user.getEmail() != null) {
existingUser.setEmail(user.getEmail());
}
if (user.getPhone() != null) {
existingUser.setPhone(user.getPhone());
}
existingUser.setUpdateTime(LocalDateTime.now());
boolean result = userService.updateById(existingUser);
if (result) {
return Result.success("用户更新成功");
} else {
return Result.error("用户更新失败");
}
}
/**
* 更新用户状态
*/
@ApiOperation("更新用户状态")
@PutMapping("/{id}/status/{status}")
public Result<String> updateUserStatus(
@PathVariable Long id,
@PathVariable Integer status) {
if (status != 0 && status != 1) {
return Result.error("状态值只能是0或1");
}
boolean result = userService.updateUserStatus(id, status);
if (result) {
return Result.success("用户状态更新成功");
} else {
return Result.error("用户状态更新失败");
}
}
/**
* 删除用户
*/
@ApiOperation("删除用户")
@DeleteMapping("/{id}")
public Result<String> deleteUser(@PathVariable Long id) {
boolean result = userService.removeById(id);
if (result) {
return Result.success("用户删除成功");
} else {
return Result.error("用户删除失败");
}
}
/**
* 批量删除用户
*/
@ApiOperation("批量删除用户")
@DeleteMapping("/batch")
public Result<String> batchDeleteUsers(@RequestBody List<Long> ids) {
if (ids == null || ids.isEmpty()) {
return Result.error("用户ID列表不能为空");
}
boolean result = userService.removeByIds(ids);
if (result) {
return Result.success("批量删除用户成功");
} else {
return Result.error("批量删除用户失败");
}
}
/**
* 根据用户名查询用户
*/
@ApiOperation("根据用户名查询用户")
@GetMapping("/search")
public Result<List<SysUser>> searchUsers(@RequestParam String username) {
List<SysUser> userList = userService.getUsersByUsername(username);
return Result.success(userList);
}
/**
* 统计用户数量
*/
@ApiOperation("统计用户数量")
@GetMapping("/count")
public Result<Integer> countUsers(@RequestParam(required = false) Integer status) {
int count;
if (status == null) {
count = userService.count();
} else {
count = userService.count(new LambdaQueryWrapper<SysUser>().eq(SysUser::getStatus, status));
}
return Result.success(count);
}
}编写完就可以用postman去测试咯
四、总结
本文分享了KingbaseES V8.6数据库与SpringBoot 2.7.x框架的集成实战经验。内容包括：1. 环境准备（Ubuntu系统安装配置、驱动获取方式）；2. JDBC基础操作（连接、查询、事务处理）；3. SpringBoot项目完整配置（pom依赖、数据源配置）；4. MyBatis-Plus集成（实体类、Mapper、Service层实现）；5. RESTful接口开发示例。文章提供了详细的代码示例，涵盖从数据库安装到应用开发的完整流程，帮助开发者快速实现国产数据库适配。
编码不易，希望各位点赞支持，也支持我们国产的数据库，纸上得来终觉浅，希望各位大佬也亲自动手尝试一下，如果遇到什么问题欢迎评论区留言交流，相互学习，共同进步~正在走向自律
本文相关《电科金仓》分类链接推荐：
第一章：基础与入门
1、【金仓数据库征文】政府项目数据库迁移：从MySQL 5.7到KingbaseES的蜕变之路
2、【金仓数据库征文】学校AI数字人：从Sql Server到KingbaseES的数据库转型之路
3、电科金仓2025发布会，国产数据库的AI融合进化与智领未来
5、《一行代码不改动！用KES V9 2025完成SQL Server → 金仓“平替”迁移并启用向量检索》
6、《赤兔引擎×的卢智能体：电科金仓如何用“三骏架构”重塑AI原生数据库一体机》
7、探秘KingbaseES在线体验平台：技术盛宴还是虚有其表？
9、KDMS V4 一键搞定国产化迁移：零代码、零事故、零熬夜——金仓社区发布史上最省心数据库迁移评估神器
10、KingbaseES V009版本发布：国产数据库的新飞跃
第二章：能力与提升
1、零改造迁移实录：2000+存储过程从SQL Server滑入KingbaseES V9R4C12的72小时
2、国产数据库迁移神器，KDMSV4震撼上线
3、在Ubuntu服务器上安装KingbaseES V009R002C012（Orable兼容版）数据库过程详细记录
4、金仓数据库迁移评估系统（KDMS）V4 正式上线：国产化替代的技术底气