编程 - JDBC

使用

依赖项

  • JDK >= 1.8
  • Maven >= 3.1

只打包 JDBC 工程

在根目录下执行下面的命令:

  1. mvn clean package -pl jdbc -am -Dmaven.test.skip=true

如何到本地 MAVEN 仓库

在根目录下执行下面的命令:

  1. mvn clean install -pl jdbc -am -Dmaven.test.skip=true

如何在 MAVEN 中使用 IoTDB JDBC

  1. <dependencies>
  2. <dependency>
  3. <groupId>org.apache.iotdb</groupId>
  4. <artifactId>iotdb-jdbc</artifactId>
  5. <version>0.8.0</version>
  6. </dependency>
  7. </dependencies>

示例

本章提供了如何建立数据库连接、执行 SQL 和显示查询结果的示例。

要求您已经在工程中包含了数据库编程所需引入的包和 JDBC class.

注意:为了更快地插入,建议使用 insertBatch()

  1. import java.sql.*;
  2. import org.apache.iotdb.jdbc.IoTDBSQLException;
  3. public class JDBCExample {
  4. /**
  5. * Before executing a SQL statement with a Statement object, you need to create a Statement object using the createStatement() method of the Connection object.
  6. * After creating a Statement object, you can use its execute() method to execute a SQL statement
  7. * Finally, remember to close the 'statement' and 'connection' objects by using their close() method
  8. * For statements with query results, we can use the getResultSet() method of the Statement object to get the result set.
  9. */
  10. public static void main(String[] args) throws SQLException {
  11. Connection connection = getConnection();
  12. if (connection == null) {
  13. System.out.println("get connection defeat");
  14. return;
  15. }
  16. Statement statement = connection.createStatement();
  17. //Create storage group
  18. try {
  19. statement.execute("SET STORAGE GROUP TO root.demo");
  20. }catch (IoTDBSQLException e){
  21. System.out.println(e.getMessage());
  22. }
  23. //Show storage group
  24. statement.execute("SHOW STORAGE GROUP");
  25. outputResult(statement.getResultSet());
  26. //Create time series
  27. //Different data type has different encoding methods. Here use INT32 as an example
  28. try {
  29. statement.execute("CREATE TIMESERIES root.demo.s0 WITH DATATYPE=INT32,ENCODING=RLE;");
  30. }catch (IoTDBSQLException e){
  31. System.out.println(e.getMessage());
  32. }
  33. //Show time series
  34. statement.execute("SHOW TIMESERIES root.demo");
  35. outputResult(statement.getResultSet());
  36. //Show devices
  37. statement.execute("SHOW DEVICES");
  38. outputResult(statement.getResultSet());
  39. //Count time series
  40. statement.execute("COUNT TIMESERIES root");
  41. outputResult(statement.getResultSet());
  42. //Count nodes at the given level
  43. statement.execute("COUNT NODES root LEVEL=3");
  44. outputResult(statement.getResultSet());
  45. //Count timeseries group by each node at the given level
  46. statement.execute("COUNT TIMESERIES root GROUP BY LEVEL=3");
  47. outputResult(statement.getResultSet());
  48. //Execute insert statements in batch
  49. statement.addBatch("insert into root.demo(timestamp,s0) values(1,1);");
  50. statement.addBatch("insert into root.demo(timestamp,s0) values(1,1);");
  51. statement.addBatch("insert into root.demo(timestamp,s0) values(2,15);");
  52. statement.addBatch("insert into root.demo(timestamp,s0) values(2,17);");
  53. statement.addBatch("insert into root.demo(timestamp,s0) values(4,12);");
  54. statement.executeBatch();
  55. statement.clearBatch();
  56. //Full query statement
  57. String sql = "select * from root.demo";
  58. ResultSet resultSet = statement.executeQuery(sql);
  59. System.out.println("sql: " + sql);
  60. outputResult(resultSet);
  61. //Exact query statement
  62. sql = "select s0 from root.demo where time = 4;";
  63. resultSet= statement.executeQuery(sql);
  64. System.out.println("sql: " + sql);
  65. outputResult(resultSet);
  66. //Time range query
  67. sql = "select s0 from root.demo where time >= 2 and time < 5;";
  68. resultSet = statement.executeQuery(sql);
  69. System.out.println("sql: " + sql);
  70. outputResult(resultSet);
  71. //Aggregate query
  72. sql = "select count(s0) from root.demo;";
  73. resultSet = statement.executeQuery(sql);
  74. System.out.println("sql: " + sql);
  75. outputResult(resultSet);
  76. //Delete time series
  77. statement.execute("delete timeseries root.demo.s0");
  78. //close connection
  79. statement.close();
  80. connection.close();
  81. }
  82. public static Connection getConnection() {
  83. // JDBC driver name and database URL
  84. String driver = "org.apache.iotdb.jdbc.IoTDBDriver";
  85. String url = "jdbc:iotdb://127.0.0.1:6667/";
  86. // Database credentials
  87. String username = "root";
  88. String password = "root";
  89. Connection connection = null;
  90. try {
  91. Class.forName(driver);
  92. connection = DriverManager.getConnection(url, username, password);
  93. } catch (ClassNotFoundException e) {
  94. e.printStackTrace();
  95. } catch (SQLException e) {
  96. e.printStackTrace();
  97. }
  98. return connection;
  99. }
  100. /**
  101. * This is an example of outputting the results in the ResultSet
  102. */
  103. private static void outputResult(ResultSet resultSet) throws SQLException {
  104. if (resultSet != null) {
  105. System.out.println("--------------------------");
  106. final ResultSetMetaData metaData = resultSet.getMetaData();
  107. final int columnCount = metaData.getColumnCount();
  108. for (int i = 0; i < columnCount; i++) {
  109. System.out.print(metaData.getColumnLabel(i + 1) + " ");
  110. }
  111. System.out.println();
  112. while (resultSet.next()) {
  113. for (int i = 1; ; i++) {
  114. System.out.print(resultSet.getString(i));
  115. if (i < columnCount) {
  116. System.out.print(", ");
  117. } else {
  118. System.out.println();
  119. break;
  120. }
  121. }
  122. }
  123. System.out.println("--------------------------\n");
  124. }
  125. }
  126. }

状态码

在最新版本中引入了状态码这一概念。例如,因为IoTDB需要在写入数据之前首先注册时间序列,一种可能的解决方案是:

  1. try {
  2. writeData();
  3. } catch (SQLException e) {
  4. // the most case is that the time series does not exist
  5. if (e.getMessage().contains("exist")) {
  6. //However, using the content of the error message is not so efficient
  7. registerTimeSeries();
  8. //write data once again
  9. writeData();
  10. }
  11. }

利用状态码,我们就可以不必写诸如if (e.getErrorMessage().contains("exist"))的代码,只需要使用e.getStatusType().getCode() == TSStatusCode.TIME_SERIES_NOT_EXIST_ERROR.getStatusCode()

这里是状态码和相对应信息的列表:

状态码状态类型状态信息
200SUCCESS_STATUS
201STILL_EXECUTING_STATUS
202INVALID_HANDLE_STATUS
300TIMESERIES_ALREADY_EXIST_ERROR时间序列已经存在
301TIMESERIES_NOT_EXIST_ERROR时间序列不存在
302UNSUPPORTED_FETCH_METADATA_OPERATION_ERROR不支持的获取元数据操作
303METADATA_ERROR处理元数据错误
304CHECK_FILE_LEVEL_ERROR检查文件层级错误
305OUT_OF_TTL_ERROR插入时间少于TTL时间边界
306CONFIG_ADJUSTERIoTDB系统负载过大
307MERGE_ERROR合并错误
308SYSTEM_CHECK_ERROR系统检查错误
309SYNC_DEVICE_OWNER_CONFLICT_ERROR回传设备冲突错误
310SYNC_CONNECTION_EXCEPTION回传连接错误
311STORAGE_GROUP_PROCESSOR_ERROR存储组处理器相关错误
312STORAGE_GROUP_ERROR存储组相关错误
313STORAGE_ENGINE_ERROR存储引擎相关错误
400EXECUTE_STATEMENT_ERROR执行语句错误
401SQL_PARSE_ERRORSQL语句分析错误
402GENERATE_TIME_ZONE_ERROR生成时区错误
403SET_TIME_ZONE_ERROR设置时区错误
404NOT_STORAGE_GROUP_ERROR操作对象不是存储组
405QUERY_NOT_ALLOWED查询语句不允许
406AST_FORMAT_ERRORAST格式相关错误
407LOGICAL_OPERATOR_ERROR逻辑符相关错误
408LOGICAL_OPTIMIZE_ERROR逻辑优化相关错误
409UNSUPPORTED_FILL_TYPE_ERROR不支持的填充类型
410PATH_ERROR路径相关错误
405READ_ONLY_SYSTEM_ERROR操作系统只读
500INTERNAL_SERVER_ERROR服务器内部错误
501CLOSE_OPERATION_ERROR关闭操作错误
502READ_ONLY_SYSTEM_ERROR系统只读
503DISK_SPACE_INSUFFICIENT_ERROR磁盘空间不足
504START_UP_ERROR启动错误
600WRONG_LOGIN_PASSWORD_ERROR用户名或密码错误
601NOT_LOGIN_ERROR没有登录
602NO_PERMISSION_ERROR没有操作权限
603UNINITIALIZED_AUTH_ERROR授权人未初始化

在最新版本中,我们重构了IoTDB的异常类。通过将错误信息统一提取到异常类中,并为所有异常添加不同的错误代码,从而当捕获到异常并引发更高级别的异常时,错误代码将保留并传递,以便用户了解详细的错误原因。 除此之外,我们添加了一个基础异常类“ProcessException”,由所有异常扩展。