JDBC

Note: The current JDBC implementation is only for connecting with third-party tools. We do not recommend using JDBC (when executing insert statements) as it cannot provide high-performance writing. For queries, we recommend using JDBC. PLEASE USE Java Native API INSTEAD

Dependencies

• JDK >= 1.8+
• Maven >= 3.9+

Installation

In root directory:

1. mvn clean install -pl iotdb-client/jdbc -am -DskipTests

Use IoTDB JDBC with Maven

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

Coding Examples

This chapter provides an example of how to open a database connection, execute an 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 database
19. try {
20. statement.execute("CREATE DATABASE root.demo");
21. }catch (IoTDBSQLException e){
22. System.out.println(e.getMessage());
23. }

26. //SHOW DATABASES
27. statement.execute("SHOW DATABASES");
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/";
99. // set rpc compress mode
100. // String url = "jdbc:iotdb://127.0.0.1:6667?rpc_compress=true";

102. // Database credentials
103. String username = "root";
104. String password = "root";

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

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

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 in order to be compatible with the SQL semantics of 0.12 when upgrading to 0.13. The possible values are: V_0_12, V_0_13, V_1_0.

In addition, IoTDB provides additional interfaces in JDBC for users to read and write the database using different character sets (e.g., GB18030) in the connection. The default character set for IoTDB is UTF-8. When users want to use a character set other than UTF-8, they need to specify the charset property in the JDBC connection. For example:

1. Create a connection using the GB18030 charset:

1. DriverManager.getConnection("jdbc:iotdb://127.0.0.1:6667?charset=GB18030", "root", "root");

1. When executing SQL with the IoTDBStatement interface, the SQL can be provided as a byte[] array, and it will be parsed into a string according to the specified charset.

1. public boolean execute(byte[] sql) throws SQLException;

1. When outputting query results, the getBytes method of ResultSet can be used to get byte[], which will be encoded using the charset specified in the connection.

1. System.out.print(resultSet.getString(i) + " (" + new String(resultSet.getBytes(i), charset) + ")");

Here is a complete example:

1. public class JDBCCharsetExample {

3. private static final Logger LOGGER = LoggerFactory.getLogger(JDBCCharsetExample.class);

5. public static void main(String[] args) throws Exception {
6. Class.forName("org.apache.iotdb.jdbc.IoTDBDriver");

8. try (final Connection connection =
9. DriverManager.getConnection(
10. "jdbc:iotdb://127.0.0.1:6667?charset=GB18030", "root", "root");
11. final IoTDBStatement statement = (IoTDBStatement) connection.createStatement()) {

13. final String insertSQLWithGB18030 =
14. "insert into root.测试(timestamp, 维语, 彝语, 繁体, 蒙文, 简体, 标点符号, 藏语) values(1, 'ئۇيغۇر تىلى', 'ꆈꌠꉙ', \"繁體\", 'ᠮᠣᠩᠭᠣᠯ ᠬᠡᠯᠡ', '简体', '——？！', \"བོད་སྐད།\");";
15. final byte[] insertSQLWithGB18030Bytes = insertSQLWithGB18030.getBytes("GB18030");
16. statement.execute(insertSQLWithGB18030Bytes);
17. } catch (IoTDBSQLException e) {
18. LOGGER.error("IoTDB Jdbc example error", e);
19. }

21. outputResult("GB18030");
22. outputResult("UTF-8");
23. outputResult("UTF-16");
24. outputResult("GBK");
25. outputResult("ISO-8859-1");
26. }

28. private static void outputResult(String charset) throws SQLException {
29. System.out.println("[Charset: " + charset + "]");
30. try (final Connection connection =
31. DriverManager.getConnection(
32. "jdbc:iotdb://127.0.0.1:6667?charset=" + charset, "root", "root");
33. final IoTDBStatement statement = (IoTDBStatement) connection.createStatement()) {
34. outputResult(statement.executeQuery("select ** from root"), Charset.forName(charset));
35. } catch (IoTDBSQLException e) {
36. LOGGER.error("IoTDB Jdbc example error", e);
37. }
38. }

40. private static void outputResult(ResultSet resultSet, Charset charset) throws SQLException {
41. if (resultSet != null) {
42. System.out.println("--------------------------");
43. final ResultSetMetaData metaData = resultSet.getMetaData();
44. final int columnCount = metaData.getColumnCount();
45. for (int i = 0; i < columnCount; i++) {
46. System.out.print(metaData.getColumnLabel(i + 1) + " ");
47. }
48. System.out.println();

50. while (resultSet.next()) {
51. for (int i = 1; ; i++) {
52. System.out.print(
53. resultSet.getString(i) + " (" + new String(resultSet.getBytes(i), charset) + ")");
54. if (i < columnCount) {
55. System.out.print(", ");
56. } else {
57. System.out.println();
58. break;
59. }
60. }
61. }
62. System.out.println("--------------------------\n");
63. }
64. }
65. }