Application Program Interface (API)

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) - 图1 (opens 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;

4. public class JDBCExample {
5. /**
6. * Before executing a SQL statement with a Statement object, you need to create a Statement object using the createStatement() method of the Connection object.
7. * After creating a Statement object, you can use its execute() method to execute a SQL statement
8. * Finally, remember to close the 'statement' and 'connection' objects by using their close() method
9. * For statements with query results, we can use the getResultSet() method of the Statement object to get the result set.
10. */
11. public static void main(String[] args) throws SQLException {
12. Connection connection = getConnection();
13. if (connection == null) {
14. System.out.println("get connection defeat");
15. return;
16. }
17. Statement statement = connection.createStatement();
18. //Create storage group
19. try {
20. statement.execute("SET STORAGE GROUP TO root.demo");
21. }catch (IoTDBSQLException e){
22. System.out.println(e.getMessage());
23. }

26. //Show storage group
27. statement.execute("SHOW STORAGE GROUP");
28. outputResult(statement.getResultSet());

30. //Create time series
31. //Different data type has different encoding methods. Here use INT32 as an example
32. try {
33. statement.execute("CREATE TIMESERIES root.demo.s0 WITH DATATYPE=INT32,ENCODING=RLE;");
34. }catch (IoTDBSQLException e){
35. System.out.println(e.getMessage());
36. }
37. //Show time series
38. statement.execute("SHOW TIMESERIES root.demo");
39. outputResult(statement.getResultSet());
40. //Show devices
41. statement.execute("SHOW DEVICES");
42. outputResult(statement.getResultSet());
43. //Count time series
44. statement.execute("COUNT TIMESERIES root");
45. outputResult(statement.getResultSet());
46. //Count nodes at the given level
47. statement.execute("COUNT NODES root LEVEL=3");
48. outputResult(statement.getResultSet());
49. //Count timeseries group by each node at the given level
50. statement.execute("COUNT TIMESERIES root GROUP BY LEVEL=3");
51. outputResult(statement.getResultSet());

54. //Execute insert statements in batch
55. statement.addBatch("insert into root.demo(timestamp,s0) values(1,1);");
56. statement.addBatch("insert into root.demo(timestamp,s0) values(1,1);");
57. statement.addBatch("insert into root.demo(timestamp,s0) values(2,15);");
58. statement.addBatch("insert into root.demo(timestamp,s0) values(2,17);");
59. statement.addBatch("insert into root.demo(timestamp,s0) values(4,12);");
60. statement.executeBatch();
61. statement.clearBatch();

63. //Full query statement
64. String sql = "select * from root.demo";
65. ResultSet resultSet = statement.executeQuery(sql);
66. System.out.println("sql: " + sql);
67. outputResult(resultSet);

69. //Exact query statement
70. sql = "select s0 from root.demo where time = 4;";
71. resultSet= statement.executeQuery(sql);
72. System.out.println("sql: " + sql);
73. outputResult(resultSet);

75. //Time range query
76. sql = "select s0 from root.demo where time >= 2 and time < 5;";
77. resultSet = statement.executeQuery(sql);
78. System.out.println("sql: " + sql);
79. outputResult(resultSet);

81. //Aggregate query
82. sql = "select count(s0) from root.demo;";
83. resultSet = statement.executeQuery(sql);
84. System.out.println("sql: " + sql);
85. outputResult(resultSet);

87. //Delete time series
88. statement.execute("delete timeseries root.demo.s0");

90. //close connection
91. statement.close();
92. connection.close();
93. }

95. public static Connection getConnection() {
96. // JDBC driver name and database URL
97. String driver = "org.apache.iotdb.jdbc.IoTDBDriver";
98. String url = "jdbc:iotdb://127.0.0.1:6667/";

100. // Database credentials
101. String username = "root";
102. String password = "root";

104. Connection connection = null;
105. try {
106. Class.forName(driver);
107. connection = DriverManager.getConnection(url, username, password);
108. } catch (ClassNotFoundException e) {
109. e.printStackTrace();
110. } catch (SQLException e) {
111. e.printStackTrace();
112. }
113. return connection;
114. }

116. /**
117. * This is an example of outputting the results in the ResultSet
118. */
119. private static void outputResult(ResultSet resultSet) throws SQLException {
120. if (resultSet != null) {
121. System.out.println("--------------------------");
122. final ResultSetMetaData metaData = resultSet.getMetaData();
123. final int columnCount = metaData.getColumnCount();
124. for (int i = 0; i < columnCount; i++) {
125. System.out.print(metaData.getColumnLabel(i + 1) + " ");
126. }
127. System.out.println();
128. while (resultSet.next()) {
129. for (int i = 1; ; i++) {
130. System.out.print(resultSet.getString(i));
131. if (i < columnCount) {
132. System.out.print(", ");
133. } else {
134. System.out.println();
135. break;
136. }
137. }
138. }
139. System.out.println("--------------------------\n");
140. }
141. }
142. }