JDBC
- 依赖
- 安装方法
  - 在 MAVEN 中使用 IoTDB JDBC
  - 示例代码

JDBC

注意: 目前的JDBC实现仅是为与第三方工具连接使用的。不推荐使用 JDBC （执行插入语句时），因无法提供高性能写入，查询推荐使用 JDBC。对于Java应用，我们推荐使用Java 原生接口*

依赖

JDK >= 1.8
Maven >= 3.6

安装方法

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

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

在 MAVEN 中使用 IoTDB JDBC

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>

示例代码

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

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

注意：为了更快地插入，建议使用 executeBatch()

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. }

25. //SHOW DATABASES
26. statement.execute("SHOW DATABASES");
27. outputResult(statement.getResultSet());

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

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

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

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

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

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

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

88. //close connection
89. statement.close();
90. connection.close();
91. }

93. public static Connection getConnection() {
94. // JDBC driver name and database URL
95. String driver = "org.apache.iotdb.jdbc.IoTDBDriver";
96. String url = "jdbc:iotdb://127.0.0.1:6667/";
97. // set rpc compress mode
98. // String url = "jdbc:iotdb://127.0.0.1:6667?rpc_compress=true";

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. }

可以在 url 中指定 version 参数：

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

version 表示客户端使用的 SQL 语义版本，用于升级 0.13 时兼容 0.12 的 SQL 语义，可能取值有：V_0_12、V_0_13、V_1_0。

此外，IoTDB 在 JDBC 中提供了额外的接口，供用户在连接中使用不同的字符集（例如 GB18030）读写数据库。 IoTDB 默认的字符集为 UTF-8。当用户期望使用 UTF-8 外的字符集时，需要在 JDBC 的连接中，指定 charset 属性。例如：

使用 GB18030 的 charset 创建连接：

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

调用如下 IoTDBStatement 接口执行 SQL 时，可以接受 byte[] 编码的 SQL，该 SQL 将按照被指定的 charset 解析成字符串。

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

查询结果输出时，可使用 ResultSet 的 getBytes 方法得到的 byte[]，byte[] 的编码使用连接指定的 charset 进行。

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

以下是完整示例:

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. }