KingbaseES JDBC: From Default Admin to a Proper Business Connection

JDBC Driver and Connection String in Practice: From Default `system/test` to Business Database and Application Account

This article has a prerequisite: it assumes you have already set up the basic environment. I'll use a Windows client and a Linux server as an example, meaning a Windows 11 local development machine can access a CentOS 7.6 server, the Kingbase database instance is running, and the port, firewall, and remote access rules are all configured. Windows installation, CentOS installation, and connecting Windows to the database on CentOS have been covered in previous articles and will not be repeated here.

Prerequisite tutorials:

Windows local Kingbase database installation tutorial: KingbaseES Database Nanny-Level Installation Tutorial on Windows (with Common Problem Solutions)
CentOS server Kingbase database installation tutorial: Detailed Tutorial for Installing KingbaseES (ISO Package) on CentOS_Kingbase Database Installation Tutorial-CSDN Blog
Connecting Windows to Kingbase database on CentOS server: "Kingbase Moat"——Cross-Platform Environment Database Joint Debugging Practice-CSDN Blog

Application connection failures to the database are often not due to complex business code, but because three fundamental things are misaligned: the driver package, the connection string, and account permissions. This article starts from the default state after a fresh installation, first connecting to the default test database with the system account, then creating the business database, schema, application accounts, and test tables that will be used consistently in this column, and finally completing a JDBC query verification with a minimal Java program.

@[toc]

I. Experiment Goals

This article aims to accomplish five things:

Connect to the remote server using the default system/test.
Create the business database and accounts to be used consistently in this column.
Write a readable, reusable connection string.
Prepare the JDBC driver.
Query a test table in the remote database using a Java program.

The unified environment is as follows. The IP in the table is only an example; replace it with your own server address when using it:

Item	Example Value
Local System	Windows 11
Server System	CentOS 7.6
Database Server IP	`192.168.10.101`
Database Port	`54321`
Initial Database	`test`
Initial User	`system`
Business Database	`kb_app`
Business Schema	`shop`
Application Account	`app_user`
Read-only Account	`report_user`
Test Table	`shop.t_connection_check`

Before starting to write Java code, first confirm that this link can be manually connected using the default account:

ksql -h 192.168.10.101 -p 54321 -U system -d test

If you still cannot connect here, go back to the cross-platform connection tutorial to troubleshoot the network, listening address, firewall, and authentication rules. This column starts from the premise that "the default system/test can already connect remotely."

II. Creating the Business Database, Schema, and Application Accounts

The default test database is suitable for initial verification. However, using it directly for subsequent application development is not recommended. So, let's first use the system user to create a business database kb_app:

CREATE DATABASE kb_app;

Exit the current connection. Then reconnect to kb_app:

ksql -h 192.168.10.101 -p 54321 -U system -d kb_app

Then we create the business schema, application account, and read-only account:

CREATE SCHEMA shop;

CREATE USER app_user WITH PASSWORD 'App_user_123';
CREATE USER report_user WITH PASSWORD 'Report_user_123';

GRANT USAGE ON SCHEMA shop TO app_user;
GRANT USAGE ON SCHEMA shop TO report_user;
GRANT CREATE ON SCHEMA shop TO app_user;

If your environment already has a database, schema, or user with the same name, first confirm whether they are the ones intended for this article. If not, I suggest changing the business database name or account name. This avoids accidentally operating on the existing environment.

Next, we create the first test table for this column:

CREATE TABLE shop.t_connection_check (
    id INT PRIMARY KEY,
    check_name VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO shop.t_connection_check(id, check_name)
VALUES (1, 'jdbc_check_ready');

Grant read-write permissions to the application account. Grant only query permission to the read-only account:

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.t_connection_check TO app_user;
GRANT SELECT ON shop.t_connection_check TO report_user;

Finally, verify once with the application account:

ksql -h 192.168.10.101 -p 54321 -U app_user -d kb_app

After entering, execute:

SELECT current_database(), current_user;
SELECT * FROM shop.t_connection_check;

If you can see kb_app, app_user, and the jdbc_check_ready record, it means all the basic objects needed later are ready.

III. Preparing the JDBC Driver

The JDBC driver can usually be found in the Kingbase database installation directory, the client tool directory, or the official driver package. The example Windows installation directory used in this article is D:\Tools\Kingbase. If your installation path is different, remember to replace it with your actual directory.

On Windows 11, we can first search for the driver package. The following command uses my local path as an example. When executing, replace $kbHome with your actual installation directory:

$kbHome = "D:\Tools\Kingbase"

Get-ChildItem -LiteralPath $kbHome -Recurse -File -Filter "kingbase*.jar" -ErrorAction SilentlyContinue |
    Where-Object { $_.FullName -like "*\Interface\jdbc\*" } |
    Select-Object FullName

The path for the KingbaseES V9R2C13 JDBC driver found in my environment is:

D:\Tools\Kingbase\KESV9R2C13\KES\KESRealPro\V009R002C013\Interface\jdbc\kingbase8-9.0.0.jar

In the same directory, you might also see files like kingbase8-9.0.0.jre6.jar or kingbase8-9.0.0.jre7.jar. These are packages compatible with older JREs. If the JDK used locally is relatively new, just choose kingbase8-9.0.0.jar.

After finding the JDBC driver, I suggest copying it uniformly to the project's lib directory. For example:

kb-jdbc-demo
|-- lib
|   `-- kingbase8-9.0.0.jar
|-- src
|   `-- DbConnectCheck.java

The copy command is as follows:

New-Item -ItemType Directory -Force -Path .\lib

Copy-Item -LiteralPath "D:\Tools\Kingbase\KESV9R2C13\KES\KESRealPro\V009R002C013\Interface\jdbc\kingbase8-9.0.0.jar" `
    -Destination ".\lib\kingbase8-9.0.0.jar" `
    -Force

If your project uses Maven or Gradle, prioritize introducing the driver according to the internal artifact repository at the enterprise level or the officially recommended method. To reduce upfront dependencies, this article first demonstrates using a minimal Java file.

IV. Understanding the Connection String

The core of an application connecting to the database is the JDBC URL. An example format is:

jdbc:kingbase8://192.168.10.101:54321/kb_app

It's actually quite simple when broken down; it's just a few segments put together:

jdbc:kingbase8: JDBC protocol and driver identification prefix, subject to the actual driver documentation.
192.168.10.101: CentOS database server IP.
54321: Database listening port.
kb_app: The business database name created earlier.

If your environment has enabled additional security parameters or character set parameters, you need to append them to the URL according to the driver documentation. For initial connection, it's not recommended to pile on many parameters at once. First, establish a connection with a minimal connection string, then gradually add parameters like connection timeout and application name.

V. Writing a Minimal Java Connection Program

Create src\DbConnectCheck.java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DbConnectCheck {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:kingbase8://192.168.10.101:54321/kb_app";
        String user = "app_user";
        String password = "App_user_123";

        String sql = "select id, check_name, created_at from shop.t_connection_check where id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            System.out.println("connected=" + !conn.isClosed());
            ps.setInt(1, 1);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    System.out.println("id=" + rs.getInt("id"));
                    System.out.println("check_name=" + rs.getString("check_name"));
                    System.out.println("created_at=" + rs.getTimestamp("created_at"));
                } else {
                    System.out.println("no data found");
                }
            }
        }
    }
}

There are a few noteworthy points in this code:

Use PreparedStatement; do not concatenate parameters directly into the SQL string.
Use try-with-resources to ensure connections, statements, and result sets are properly closed.
Explicitly write the business schema in the SQL to avoid relying on the default schema search path.

VI. Compiling and Running on Windows 11

Enter the project directory:

cd D:\work\kb-jdbc-demo

Compile:

javac -encoding UTF-8 -cp ".;lib\kingbase8-9.0.0.jar" src\DbConnectCheck.java

Run:

java -cp ".;lib\kingbase8-9.0.0.jar;src" DbConnectCheck

If the output is similar to the following, it means the JDBC link is established:

If the command returns quickly after execution but has no output, it's usually not that the program is "stuck," but that the query didn't print visible information. The example above will first output connected=true, then the query results. If you see no data found, it means the connection was successful, but there is no record with id = 1 in shop.t_connection_check. You need to go back and check the table creation and insertion steps.

If you are using PowerShell, note that the classpath separator on Windows is a semicolon ;. When running on Linux, the separator is generally a colon :.

VII. Extracting Connection Information from Code

The approach above is suitable for initial verification but not for long-term maintenance. Connection information should not be hardcoded in Java classes.

You can first create a simple configuration file db.properties:

db.url=jdbc:kingbase8://192.168.10.101:54321/kb_app
db.user=app_user
db.password=App_user_123

Reading the configuration in Java:

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class DbConfigCheck {
    public static void main(String[] args) throws Exception {
        Properties props = new Properties();
        try (FileInputStream in = new FileInputStream("db.properties")) {
            props.load(in);
        }

        try (Connection conn = DriverManager.getConnection(
                props.getProperty("db.url"),
                props.getProperty("db.user"),
                props.getProperty("db.password"))) {

            System.out.println("connected=" + !conn.isClosed());
            System.out.println("database=" + conn.getCatalog());
        }
    }
}

The benefit of this is that when migrating to Spring Boot, testing environments, or production environments later, connection information can be managed by configuration centers, environment variables, or startup parameters without changing the code.

VIII. What to Verify After a Successful Connection

Don't just assume the connection is successful because the program didn't report an error. Perform at least three types of verification.

1. Identity Verification

Confirm which database and which account the current connection is actually using:

SELECT current_database(), current_user;

You can execute this SQL in a Java program or compare it via ksql.

2. Permission Verification

Test normal read and write with app_user:

INSERT INTO shop.t_connection_check(id, check_name)
VALUES (2, 'jdbc_insert_ok');

SELECT * FROM shop.t_connection_check WHERE id = 2;

Then connect to kb_app with report_user to test read-only access, confirming it can query shop.t_connection_check but cannot perform write operations.

3. Exception Verification

Intentionally write the wrong password to see if the application clearly outputs an authentication failure; intentionally write the wrong IP to see if there is a connection timeout; intentionally write the wrong table name to see if you can locate the SQL error. Previewing these errors before going live will make troubleshooting much faster later.

IX. Common Errors

1. Driver Class Not Found or Protocol Not Recognized

This usually means the JDBC jar was not placed in the classpath, or the driver version does not match the connection string prefix. First, confirm that the run command includes the driver jar.

2. Connection Refused

This is not a Java code problem. First, go back to the prerequisite cross-platform connection tutorial to check the port listening, firewall, security group, and database listening address.

3. Authentication Failure

Check the username, password, database name, authentication rules, and whether the account is locked. The first half of this article uses system/test for the initial connection, and the second half uses app_user/kb_app for the application connection. Do not mix these two sets of accounts and databases during troubleshooting.

4. Relation or Table Does Not Exist

If the table actually exists, focus on checking the Schema. It is recommended to explicitly write shop.t_connection_check in the SQL and not rely on the default search path.

5. Chinese Character Encoding Issues

First, confirm the encoding of the database, client, Java source files, and console output. In a Windows command-line environment, it's preferable to use PowerShell and specify -encoding UTF-8 during compilation.

X. Summary

The core of JDBC connection is not about writing a lot of code, but about getting the driver, connection string, account permissions, and verification SQL right. This article started from the default system/test, created the kb_app, shop, app_user, report_user, and shop.t_connection_check to be used consistently in this column, and completed the first JDBC query verification with a minimal Java program.

In the next article, we will move into Spring Boot, upgrading the single-file connection verification to an application configuration method closer to a real project.