使用 jdbc 连接金仓数据库Kingbase

数据猿 2025-03-21

JDBC jar包获取

可通过金仓官网下载页（电科金仓-成为世界卓越的数据库产品与服务提供商）获取对应版本架构的 JDBC 驱动。

kingbase8-x.x.x.jar：主类名为"com.kingbase8.Driver"，数据库连接的url前缀为"jdbc:kingbase8"，当同一JVM进程内需要同时访问PostgreSQL及kingbase时，使用kingbase8-x.x.x.jar可以避免类名冲突。

开发源程序

以下是一个插入并执行查询的具体示例。

加载驱动：

在创建数据库连接之前，需要加载数据库驱动类，驱动类不同包位置不同。kingbase jdbc驱动为"com.kingbase8.Driver"，其中url前缀为"jdbc:kingbase8"。

可以在 maven 项目的 pom.xml 文件中添加依赖, 根据需求选择需要的版本：

<!-- 最低可支持 JDK1.8 -->
<dependency>
<groupId>cn.com.kingbase</groupId>
<artifactId>kingbase8</artifactId>
<version>8.6.0</version>
</dependency>
<!-- 最低可支持 JDK1.7 -->
<dependency>
<groupId>cn.com.kingbase</groupId>
<artifactId>kingbase8</artifactId>
<version>8.6.0.jre7</version>
</dependency>
<!-- 最低可支持 JDK1.6 -->
<dependency>
<groupId>cn.com.kingbase</groupId>
<artifactId>kingbase8</artifactId>
<version>8.6.0.jre6</version>
</dependency>

复制

连接数据库

说明

jdbc提供了三个方法，用于创建数据库连接：

host 是数据库服务器的地址，包括域名、主机名、主机的 IP 地址等。缺省是”localhost”
port 是数据库服务器监听的端口号。KingbaseES 服务器的缺省端口号：54321；
database 是数据库名。
userID是连接数据库的用户名
myPassword是连接数据库用户的面

DriverManager.getConnection(String url);

此连接方法需要在url上面拼接用户名密码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcConn {
  public static void main(String[] args) {
        getConnect();
  }
  public static Connection getConnect() {
        String driver = "com.kingbase8.Driver";
        String sourceURL = "jdbc:kingbase8://host:port/database?user=userID&password=myPassword";
        Connection conn = null;
        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        try {
            conn = DriverManager.getConnection(sourceURL);
            System.out.println("连接成功！");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
复制

DriverManager.getConnection(String url，Properties info);

此连接方法的用户名密码等参数均在Properties对象的实例中，可通过setProperty添加。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcConn {
  public static void main(String[] args) {
        getConnect();
  }
 public static Connection getConnect() {
        String driver = "com.kingbase8.Driver";
        String sourceURL = "jdbc:kingbase8://host:port/database
        Properties info = new Properties();
        info.setProperty("user","userId");
        info.setProperty("password","myPassword");
        Connection conn = null;
        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        try {
            conn = DriverManager.getConnection(sourceURL, info);
            System.out.println("连接成功！");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
复制

DriverManager.getConnection(String url，String user，String password);

此连接方法需要将用户名和密码作为变量输入

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcConn {
  public static void main(String[] args) {
        getConnect();
  }
 public static Connection getConnect() {
        String driver = "com.kingbase8.Driver";
        String sourceURL = "jdbc:kingbase8://host:port/database
        String username="userID";
        String passwd="myPassword";
        Connection conn = null;
        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        try {
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("连接成功！");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
复制

Statement 示例

通过statement执行crud操作

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testStatement {
  protected String Driver = "com.kingbase8.Driver";
  protected static String Url = "jdbc:kingbase8://localhost:54321/test";
  protected Connection connection = null;
  protected Statement statement = null;
  protected String create_table = "create table orders(ORDER_ID SERIAL, "
                                          + "ISBN int, CUSTOMERID varchar(20))";

  /* 加载驱动 */
  protected void Driver() throws ClassNotFoundException {
    Class.forName(Driver);
  }

  public static void main(String[] args) throws ClassNotFoundException,
                                                        SQLException, InterruptedException {
    testStatement test = new testStatement();
    test.Driver();
    test.connection(Url);
    test.statement();
    test.table();
    test.getGeneratedKeys();
    test.close();
  }

  /* 建立连接 */
  protected void connection(String url) throws SQLException,
                                                       InterruptedException {
    connection = DriverManager.getConnection(url, "system", "manager");
    if (connection != null) {
      System.out.println("connection sucessful!");
    } else {
      System.out.println("connection fail!");
    }
  }

  /* 建立语句对象 */
  protected void statement() throws SQLException {
    statement = connection.createStatement();
  }

  /* 执行建表语句 */
  protected void table() throws SQLException {
    statement.executeUpdate(create_table);
  }

  protected void getGeneratedKeys() throws SQLException {
    /* 执行 SQL 语句并返回所有列 */
    int rows = statement.executeUpdate("insert into orders (ISBN," +
                                               "CUSTOMERID) VALUES (195123018,'BILLING')"
            , Statement.RETURN_GENERATED_KEYS);
    System.out.println("rows:" + rows);
    ResultSet rs = null;
    rs = statement.getGeneratedKeys();
    boolean b = rs.next();
    if (b) {
      System.out.println(rs.getString(1));
    }
    rs.close();
    /* 执行 SQL 语句并返回数组中的指定列 */
    statement.executeUpdate("delete from orders");
    boolean result = statement.execute("insert into orders (ISBN," +
                                               "CUSTOMERID) VALUES (195123018,'BILLING')"
            , Statement.RETURN_GENERATED_KEYS);
    System.out.println("result:" + result);
    rs = statement.getGeneratedKeys();
    boolean c = rs.next();
    if (c) {
      System.out.println(rs.getString(1));
    }
    rs.close();
    statement.executeUpdate("delete from orders");
    String keyColumn[] = {"order_id"};
    int row = statement.executeUpdate("insert into orders (ISBN," +
                                              "CUSTOMERID) VALUES (195123018,'BILLING')", keyColumn);
    System.out.println("row:" + row);
    rs = statement.getGeneratedKeys();
    boolean d = rs.next();
    if (d) {
      System.out.println(rs.getString(1));
    }
    rs.close();
    statement.executeUpdate("delete from orders");
    String keyColumn1[] = {"order_id"};
    boolean result1 = statement.execute("insert into orders (ISBN," +
                                                "CUSTOMERID) VALUES (195123018,'BILLING')", keyColumn1);
    System.out.println("result1:" + result1);
    rs = statement.getGeneratedKeys();
    boolean e = rs.next();
    if (e) {
      System.out.println(rs.getString(1));
    }
    rs.close();
  }

  /* 关闭语句、连接对象 */
  protected void close() throws SQLException {
    statement.executeUpdate("drop table orders");
    if (statement != null) {
      statement.close();
    }
    if (connection != null) {
      connection.close();
    }
  }
}

复制

PreparedStatement 示例

通过PreparedStatement执行crud操作

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testPreparedStatement {
  protected String Driver = "com.kingbase8.Driver";
  protected static String Url = "jdbc:kingbase8://localhost:54321/test";
  protected Connection connection = null;
  protected Statement statement = null;
  protected enumn[] employee = new enumn[5];

  protected void Driver() throws ClassNotFoundException {
    Class.forName(Driver);
  }

  public static void main(String[] args) throws SQLException,
                                                        ClassNotFoundException, InterruptedException {
    testPreparedStatement test = new testPreparedStatement();
    test.initEmployee();
    test.Driver();
    test.connection(Url);
    test.statement();
    test.table();
    test.preparedStatement();
    test.getParameterMetaData();
    test.close();
  }

  /* 初始化数据 */
  protected void initEmployee() {
    for (int i = 0; i < employee.length; i++) {
      employee[i] = new enumn();
      employee[i].id = i;
      employee[i].salary = new BigDecimal("100." + i);
    }
  }

  /* 建立连接 */
  protected void connection(String url) throws SQLException,
                                                       InterruptedException {
    connection = DriverManager.getConnection(url, "system", "manager");
    if (connection != null) {
      System.out.println("connection sucessful!");
    } else {
      System.out.println("connection fail!");
    }
  }

  /* 建立语句对象 */
  protected void statement() throws SQLException {
    statement = connection.createStatement();
  }

  /* 执行建表和插入语句 */
  protected void table() throws SQLException {
    statement.executeUpdate("create table employees (ID int not null " +
                                    "primary key, SALARY numeric(9,5))");
    for (int i = 0; i < 5; i++) {
      statement.executeUpdate("insert into employees values (" + i +
                                      "," + "100.10" + ")");
    }
  }

  protected void preparedStatement() throws SQLException {
    /* 使用 PreparedStatement 更新语句 */
    PreparedStatement preparedStatement = connection.prepareStatement(
            "UPDATE employees SET SALARY = ? WHERE ID = ?");
    for (int i = 0; i < employee.length; i++) {
      preparedStatement.setBigDecimal(1, employee[i].salary);
      preparedStatement.setInt(2, employee[i].id);
      preparedStatement.executeUpdate();
    }
    /* 查询数据 */
    ResultSet rs = statement.executeQuery("select SALARY from employees");
    while (rs.next()) {
      System.out.println(rs.getBigDecimal(1));
    }
    rs.close();
    preparedStatement.close();
  }

  /* 获取参数元信息 */
  protected void getParameterMetaData() throws SQLException {
    statement.executeUpdate("delete from employees");
    PreparedStatement preparedStatement = connection.prepareStatement(
            "insert into employees (ID, SALARY) values (?,?)");
    ParameterMetaData pmd = preparedStatement.getParameterMetaData();
    int parameterCount = pmd.getParameterCount();
    System.out.println(parameterCount);
    preparedStatement.close();
  }

  /* 删除表，关闭语句、连接对象 */
  protected void close() throws SQLException {
    statement.executeUpdate("drop table employees");
    if (statement != null) {
      statement.close();
    }
    if (connection != null) {
      connection.close();
    }
  }
}

class enumn {
  BigDecimal salary;
  int id;
}
复制

ResultSet 示例

查询打印结果集、获取结果集元信息、可更新结果集的使用：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class testResultSet {
  protected String Driver = "com.kingbase8.Driver";
  protected static String Url = "jdbc:kingbase8://localhost:54321/test";
  protected Connection connection = null;
  protected Statement statement = null;

  protected void Driver() throws ClassNotFoundException {
    Class.forName(Driver);
  }

  protected void connection(String url) throws SQLException,
                                                       InterruptedException {
    connection = DriverManager.getConnection(url, "system", "manager");
    if (connection != null) {
      System.out.println("connection sucessful!");
    } else {
      System.out.println("connection fail!");
    }
  }

  protected void statement() throws SQLException {
    statement = connection.createStatement();
  }

  protected void table() throws SQLException {
    statement.executeUpdate("create table table1(id int primary key," +
                                    "name char(10))");
    statement.executeUpdate("insert into table1 values (1,'KingbaseES')");
  }

  public static void main(String[] args) throws ClassNotFoundException,
                                                        SQLException, InterruptedException {
    testResultSet test = new testResultSet();
    test.Driver();
    test.connection(Url);
    test.statement();
    test.table();
    test.resultSet();
    test.updataResultSet();
    test.close();
  }

  protected void resultSet() throws SQLException {
    /* 查询并返回结果集 */
    ResultSet rs = statement.executeQuery("select * from table1");
    while (rs.next()) {
      System.out.println(rs.getInt(1));
    }
    /* 获取结果集元信息对象 */
    ResultSetMetaData rsmd = rs.getMetaData();
    /* 获取第一列的类型名称 */
    System.out.println(rsmd.getColumnTypeName(1));
    rs.close();
  }

  /* 通过结果集更新数据 */
  protected void updataResultSet() throws SQLException {
    Statement stmt = connection.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery("select * from table1");
    rs.next();
    System.out.println("update before: " + rs.getInt(1));
    rs.updateInt(1, 21);
    rs.updateRow();
    System.out.println("update after: " + rs.getInt(1));
    rs.close();
    stmt.close();
  }

  protected void close() throws SQLException {
    statement.executeUpdate("drop table table1");
    if (statement != null) {
      statement.close();
    }
    if (connection != null) {
      connection.close();
    }
  }
}复制

金仓数据库 kingbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者