JDBC

NOTICE: CURRENTLY, JDBC IS USED FOR CONNECTING SOME THIRD-PART TOOLS. IT CAN NOT PROVIDE HIGH THROUGHPUT FOR WRITE OPERATIONS. PLEASE USE JAVA NATIVE APIJDBC (Not Recommend) - 图1open in new window INSTEAD

Dependencies

  • JDK >= 1.8
  • Maven >= 3.6

Installation

In root directory:

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

Use IoTDB JDBC with Maven

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

Coding Examples

This chapter provides an example of how to open a database connection, execute a SQL query, and display the results.

It requires including the packages containing the JDBC classes needed for database programming.

NOTE: For faster insertion, the insertTablet() in Session is recommended.

  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 database
  18. try {
  19. statement.execute("CREATE DATABASE root.demo");
  20. }catch (IoTDBSQLException e){
  21. System.out.println(e.getMessage());
  22. }
  23. //SHOW DATABASES
  24. statement.execute("SHOW DATABASES");
  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. // set rpc compress mode
  87. // String url = "jdbc:iotdb://127.0.0.1:6667?rpc_compress=true";
  88. // Database credentials
  89. String username = "root";
  90. String password = "root";
  91. Connection connection = null;
  92. try {
  93. Class.forName(driver);
  94. connection = DriverManager.getConnection(url, username, password);
  95. } catch (ClassNotFoundException e) {
  96. e.printStackTrace();
  97. } catch (SQLException e) {
  98. e.printStackTrace();
  99. }
  100. return connection;
  101. }
  102. /**
  103. * This is an example of outputting the results in the ResultSet
  104. */
  105. private static void outputResult(ResultSet resultSet) throws SQLException {
  106. if (resultSet != null) {
  107. System.out.println("--------------------------");
  108. final ResultSetMetaData metaData = resultSet.getMetaData();
  109. final int columnCount = metaData.getColumnCount();
  110. for (int i = 0; i < columnCount; i++) {
  111. System.out.print(metaData.getColumnLabel(i + 1) + " ");
  112. }
  113. System.out.println();
  114. while (resultSet.next()) {
  115. for (int i = 1; ; i++) {
  116. System.out.print(resultSet.getString(i));
  117. if (i < columnCount) {
  118. System.out.print(", ");
  119. } else {
  120. System.out.println();
  121. break;
  122. }
  123. }
  124. }
  125. System.out.println("--------------------------\n");
  126. }
  127. }
  128. }

The parameter version can be used in the url:

  1. String url = "jdbc:iotdb://127.0.0.1:6667?version=V_1_0";

The parameter version represents the SQL semantic version used by the client, which is used to be compatible with the SQL semantics of 0.12 when upgrading 0.13. The possible values are: V_0_12, V_0_13, V_1_0.