JDBC 使用介绍

JDBC 使用，需要把 java SDK 包 lightning-java-api.jar 引入工程类路径。

创建连接

    public Connection getConnect() throws SQLException, ClassNotFoundException {
        Class.forName("com.magus.ldb.jdbc.Driver");

        Connection connection = DriverManager.getConnection("jdbc:lightning://192.168.2.50:19504/db", "root",
                "root");
        return  connection;
    }

上述示例是连接到数据库服务 IP 为 192.168.2.50 且服务端口为 19504 的数据库

• 驱动全类名为 com.magus.ldb.jdbc.Driver
• 连接的库名为 db
• 使用的用户为 root
• 用户密码为 root

创库和创表

        Statement stmt = connection.createStatement();

        // create database
        stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS db");

        // use database
        stmt.executeUpdate("USE db");

        // create table
        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS gy (\n" +
        "                                  IsArray VtDouble not null default 0.0,\n" +
        "                                  Quality VtDouble not null default 0.0,\n" +
        "                                  TagValue VtDouble not null default 0.0,\n" +
        "                                  UaDataType VtDouble not null default 0.0,\n" +
        "                                  TagStringValue VtString not null default '',\n" +
        "                                  HostNameTag VtString not null default '' keyCol,\n" +
        "                                  e_date VtDateTime not null DEFAULT '2000-01-01 00:00:00' timeCol\n" +
        ")");

注意：如果不使用 USE db 指定数据库，则后续对表的操作都需要增加数据库名称作为前缀，如 db.gy。

修改表结构

    public void testAlterTable() throws ClassNotFoundException, SQLException {

        SQLConnection connection = getConnect();
        PreparedStatement statement = connection.prepareStatement("ALTER TABLE gy dataSpace=1024M");
        statement.execute();
        connection.close();

    }

上述示例把表 gy 的数据空间大小限定为 1 个 G.

删除表结构

    public void testDropTable() throws ClassNotFoundException, SQLException {

        SQLConnection connection = getConnect();
        PreparedStatement statement = connection.prepareStatement("drop table gy");
        statement.execute();
        connection.close();

    }

删除表 gy

查询数据

    public void testQuery() throws ClassNotFoundException, SQLException {

        SQLConnection connection = getConnect();
        PreparedStatement statement = connection.prepareStatement("select top(*) from  gy");
        ResultSet resultSet = statement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            System.out.printf("%s\t", metaData.getColumnName(i + 1));
        }
        System.out.println();
        while (resultSet.next()) {

            for (int i = 0; i < metaData.getColumnCount(); i++) {
                System.out.printf(resultSet.getObject(i + 1) + "\t");

            }
            System.out.println();

        }
        resultSet.close();
        connection.close();

    }

上述示例是查出 gy 表最新一条记录

写入数据

    public void testInsertSql() throws  ClassNotFoundException, SQLException {

        String sql = "insert into zh_test(IsArray,Quality,TagValue,UaDataType,TagStringValue,HostNameTag,e_date)\n" +
        "values (1,1,,1,\"test\",\"key\",\"2023-12-12 10:00:00\")";
        SQLConnection connection = getConnect();

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.execute();
        statement.close();
        connection.close();
        }

上述示例是向 gy 表里写入一条记录

批量写数据，并自动提交

    public void testInsertSqlBatchAutoCommit() throws  ClassNotFoundException, SQLException {
        String sql = "insert into zh_test(IsArray,Quality,TagValue,UaDataType,TagStringValue,HostNameTag,e_date)\n" +
                "values (1,1,,?,?,?,\"2023-12-12 10:00:00\")";
        SQLConnection connection = getConnect();

        PreparedStatement statement = connection.prepareStatement(sql);
        for (int i = 0; i < 10; i++) {
            statement.setInt(1, 1);
            statement.setString(2, "test" + i);
            statement.setString(3, "key" + i);
            statement.addBatch();
        }
        int[] ints = statement.executeBatch();
        for (int anInt : ints) {
            System.out.printf("%d\t", anInt);
        }
        System.out.println();
        statement.close();
        connection.close();
    }

上述示例是批量写数据，默认为自动提交事务

批量写数据，并手动提交

    public void testInsertSqlBatch() throws ClassNotFoundException, SQLException, InterruptedException {
        String sql = "insert into zh_test(IsArray,Quality,TagValue,UaDataType,TagStringValue,HostNameTag,e_date)\n" +
                "values (1,1,,?,?,?,\"2023-12-14 10:00:00\")";
        SQLConnection connection = getConnect();
        connection.setAutoCommit(false);

        PreparedStatement statement = connection.prepareStatement(sql);
        for (int i = 0; i < 10; i++) {
            statement.setInt(1, 1);
            statement.setString(2, "test" + i);
            statement.setString(3, "key" + i);
            statement.addBatch();
        }
        int[] ints = statement.executeBatch();
        for (int anInt : ints) {
            System.out.printf("%d\t", anInt);
        }
        System.out.println();
        System.out.println("暂停 10 秒后提交事务...");
        TimeUnit.SECONDS.sleep(10);
        connection.commit();
        statement.close();
        connection.close();


    }

上述示例为批量写数据，需要代码里主动提交，才会真正把数据落盘，这里是暂停 10 秒后提交数据，一般用作多次写入事务性提交

更新数据

    public void testUpdate() throws  ClassNotFoundException, SQLException {

        String sql = "update gy set IsArray =2 where HostNameTag = 'geyang'";
        SQLConnection connection = getConnect();

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.executeUpdate();
        statement.close();
        connection.close();
    }

上述示例把 gy 表里所有 HostNameTag 为 geyang 的 IsArray 字段都修改为 2

删除数据

    public void testDelete() throws  ClassNotFoundException, SQLException {

        String sql = "delete from gy where e_date < '2024-03-01'";
        SQLConnection connection = getConnect();

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.executeUpdate();
        statement.close();
        connection.close();


    }

上述示例是删除 zh_gy 表里 2024 年 3 月之前的数据