使用 SQL 连接 DingoDB

使用 SQL 工具

与 DingoDB 建立连接

1.启动DBeaver工具，导航到菜单窗口->驱动管理器

2.在驱动程序管理器对话框中，点击“新建”按钮，创建新的驱动程序

3.在“新建驱动程序”对话框中提供“驱动程序名称”和“类名称”，这里请输入类名称为“io.dingodb.driver.client.DingoDriverClient”。

4.然后点击“库”选项卡，点击“添加文件”按钮。

5.添加DingoDB驱动程序“dingo-driver-client-all.jar”，点击“Search”，选择DingoDB驱动程序“io.dingodb.driver.client.DingoDriverClient”。

6.点击“OK”，将在驱动列表中显示DingoDB驱动程序。

7.回到DBeaver主界面，点击工具栏中的“新建数据库连接”，选择“DingoDB”项。

8.在“新建数据库连接”对话框中，提供有效的JDBC URL、用户和密码，然后单击“测试连接”，即可看到数据库连接成功。

9.单击Finish，在左侧的数据库连接导航器中看到DingoDB连接。

使用编辑器操作

可以使用dbeaver查看表和查询。

• 创建表格

create table userinfo (
    id int,
    name varchar(32) not null,
    age int,
    amount double,
    primary key(id)
);

• 向表中插入值

insert into userinfo values
(1, 'Alice', 18, 3.5),
(2, 'Betty', 22, 4.1),
(3, 'Cindy', 39, 4.6),
(4, 'Doris', 25, 5.2),
(5, 'Emily', 24, 5.8),
(6, 'Alice', 32, 6.1),
(7, 'Betty', 30, 6.9),
(8, 'Alice', 22, 7.3),
(9, 'Cindy', 26, 7.5);

• 执行查询

1.条件查询

select * from userinfo where id=3;

2.按聚合条件查询表

select name, sum(amount) as sumAmount from userinfo group by name;

3.排序查询

select * from userinfo order by id;

4.按行或条件更新表

update userinfo set age = age + 10;
update userinfo set age = age + 10 where id = 1;

使用 Java 连接 DingoDB

建立连接

启动服务器后连接到dingodb数据库：

Class.forName("io.dingodb.driver.client.DingoDriverClient");
Connection connection = DriverManager.getConnection("jdbc:dingo:thin:url=ip:8765");

jdbc:dingo:thin:url=ip:8765 是连接字符串。

** IP地址以服务器实际IP地址为准，默认端口号为8765

使用 JAVA 操作

• 创建表格

Statement statement = connection.createStatement();
String sql = "create table exampleTest ("
            + "id int,"
            + "name varchar(32) not null,"
            + "age int,"
            + "amount double,"
            + "primary key(id)"
            + ")";
statement.execute(sql);

• 向表中插入数据

Statement statement = connection.createStatement();
String sql = "insert into exampleTest values (1, 'example001', 19, 11.0)";
int count = statement.executeUpdate(sql);

• 从表中查询数据

Statement statement = connection.createStatement();
String sql = "select * from exampleTest";
try (ResultSet resultSet = statement.executeQuery(sql)) {
  while (resultSet.next()) {
    // iterator the result set
    }
}

使用Java代码连接MySQL

启动服务器后，可以连接到mysql数据库：

public class MySQLDemo {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String connectUrl = "jdbc:mysql://172.20.3.15:3307/dingo";
    private static Connection connection;

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String tableName = "myjdbctest1";
        // load driver
        Class.forName(JDBC_DRIVER);
        
        // get connection
        connection = DriverManager.getConnection(connectUrl, "root", "");
        Statement statement = connection.createStatement();
        
        // create table
        String createSql = "create table " + tableName +
                "(" +
                "id int, " +
                "name varchar(20), " +
                "age int, " +
                "amount double, " +
                "primary key(id))";
        statement.execute(createSql);

        // insert record
        for(int i=1; i<=10; i++) {
            String insertSql = "insert into " + tableName +
                    " values (" + i + ",'dingo', 25, 23.45)";
            statement.executeUpdate(insertSql);
        }
        
        // query data
        String querySql = "select * from " + tableName;
        ResultSet resultSet = statement.executeQuery(querySql);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                String s = resultSet.getObject(columnName).toString();
                System.out.println(s);
            }
        }
        resultSet.close();
        
        // get tables
        DatabaseMetaData dmd = connection.getMetaData();
        List<String> tableList = new ArrayList<String>();
        String[] types={"TABLE"};
        ResultSet rst = dmd.getTables(null, "dingo", null, types);
        while (rst.next()) {
            tableList.add(rst.getString("TABLE_NAME").toUpperCase());
        }
        System.out.println("Table list: " + tableList);
        rst.close();
        
        // drop table
        String dropSql = "drop table " + tableName;
        statement.execute(dropSql);
        
        // close resource
        statement.close();
        connection.close();
    }
}