This is an automated email from the ASF dual-hosted git repository. rong pushed a commit to branch jdbc-charset in repository https://gitbox.apache.org/repos/asf/iotdb-docs.git
commit 2e6b2ede110429e2a93dead4b7420b427c1f3a32 Author: Steve Yurong Su <[email protected]> AuthorDate: Thu Jun 6 12:07:29 2024 +0800 JDBC: add charset support in connection properties --- src/UserGuide/Master/API/Programming-JDBC.md | 83 +++++++++++++++++++++++++ src/zh/UserGuide/Master/API/Programming-JDBC.md | 83 +++++++++++++++++++++++++ 2 files changed, 166 insertions(+) diff --git a/src/UserGuide/Master/API/Programming-JDBC.md b/src/UserGuide/Master/API/Programming-JDBC.md index bf82234..f099af0 100644 --- a/src/UserGuide/Master/API/Programming-JDBC.md +++ b/src/UserGuide/Master/API/Programming-JDBC.md @@ -211,3 +211,86 @@ 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: +```java +DriverManager.getConnection("jdbc:iotdb://127.0.0.1:6667?charset=GB18030", "root", "root"); +``` +2. 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. +```java +public boolean execute(byte[] sql) throws SQLException; +``` +3. 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. +```java +System.out.print(resultSet.getString(i) + " (" + new String(resultSet.getBytes(i), charset) + ")"); +``` +Here is a complete example: +```java +public class JDBCCharsetExample { + + private static final Logger LOGGER = LoggerFactory.getLogger(JDBCCharsetExample.class); + + public static void main(String[] args) throws Exception { + Class.forName("org.apache.iotdb.jdbc.IoTDBDriver"); + + try (final Connection connection = + DriverManager.getConnection( + "jdbc:iotdb://127.0.0.1:6667?charset=GB18030", "root", "root"); + final IoTDBStatement statement = (IoTDBStatement) connection.createStatement()) { + + final String insertSQLWithGB18030 = + "insert into root.测试(timestamp, 维语, 彝语, 繁体, 蒙文, 简体, 标点符号, 藏语) values(1, 'ئۇيغۇر تىلى', 'ꆈꌠꉙ', \"繁體\", 'ᠮᠣᠩᠭᠣᠯ ᠬᠡᠯᠡ', '简体', '——？！', \"བོད་སྐད།\");"; + final byte[] insertSQLWithGB18030Bytes = insertSQLWithGB18030.getBytes("GB18030"); + statement.execute(insertSQLWithGB18030Bytes); + } catch (IoTDBSQLException e) { + LOGGER.error("IoTDB Jdbc example error", e); + } + + outputResult("GB18030"); + outputResult("UTF-8"); + outputResult("UTF-16"); + outputResult("GBK"); + outputResult("ISO-8859-1"); + } + + private static void outputResult(String charset) throws SQLException { + System.out.println("[Charset: " + charset + "]"); + try (final Connection connection = + DriverManager.getConnection( + "jdbc:iotdb://127.0.0.1:6667?charset=" + charset, "root", "root"); + final IoTDBStatement statement = (IoTDBStatement) connection.createStatement()) { + outputResult(statement.executeQuery("select ** from root"), Charset.forName(charset)); + } catch (IoTDBSQLException e) { + LOGGER.error("IoTDB Jdbc example error", e); + } + } + + private static void outputResult(ResultSet resultSet, Charset charset) throws SQLException { + if (resultSet != null) { + System.out.println("--------------------------"); + final ResultSetMetaData metaData = resultSet.getMetaData(); + final int columnCount = metaData.getColumnCount(); + for (int i = 0; i < columnCount; i++) { + System.out.print(metaData.getColumnLabel(i + 1) + " "); + } + System.out.println(); + + while (resultSet.next()) { + for (int i = 1; ; i++) { + System.out.print( + resultSet.getString(i) + " (" + new String(resultSet.getBytes(i), charset) + ")"); + if (i < columnCount) { + System.out.print(", "); + } else { + System.out.println(); + break; + } + } + } + System.out.println("--------------------------\n"); + } + } +} +``` \ No newline at end of file diff --git a/src/zh/UserGuide/Master/API/Programming-JDBC.md b/src/zh/UserGuide/Master/API/Programming-JDBC.md index 269bc80..0346615 100644 --- a/src/zh/UserGuide/Master/API/Programming-JDBC.md +++ b/src/zh/UserGuide/Master/API/Programming-JDBC.md @@ -206,3 +206,86 @@ public class JDBCExample { 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 属性。例如： +1. 使用 GB18030 的 charset 创建连接： +```java +DriverManager.getConnection("jdbc:iotdb://127.0.0.1:6667?charset=GB18030", "root", "root") +``` +2. 调用如下 `IoTDBStatement` 接口执行 SQL 时，可以接受 `byte[]` 编码的 SQL，该 SQL 将按照被指定的 charset 解析成字符串。 +```java +public boolean execute(byte[] sql) throws SQLException; +``` +3. 查询结果输出时，可使用 `ResultSet` 的 `getBytes` 方法得到的 `byte[]`，`byte[]` 的编码使用连接指定的 charset 进行。 +```java +System.out.print(resultSet.getString(i) + " (" + new String(resultSet.getBytes(i), charset) + ")"); +``` +以下是完整示例: +```java +public class JDBCCharsetExample { + + private static final Logger LOGGER = LoggerFactory.getLogger(JDBCCharsetExample.class); + + public static void main(String[] args) throws Exception { + Class.forName("org.apache.iotdb.jdbc.IoTDBDriver"); + + try (final Connection connection = + DriverManager.getConnection( + "jdbc:iotdb://127.0.0.1:6667?charset=GB18030", "root", "root"); + final IoTDBStatement statement = (IoTDBStatement) connection.createStatement()) { + + final String insertSQLWithGB18030 = + "insert into root.测试(timestamp, 维语, 彝语, 繁体, 蒙文, 简体, 标点符号, 藏语) values(1, 'ئۇيغۇر تىلى', 'ꆈꌠꉙ', \"繁體\", 'ᠮᠣᠩᠭᠣᠯ ᠬᠡᠯᠡ', '简体', '——？！', \"བོད་སྐད།\");"; + final byte[] insertSQLWithGB18030Bytes = insertSQLWithGB18030.getBytes("GB18030"); + statement.execute(insertSQLWithGB18030Bytes); + } catch (IoTDBSQLException e) { + LOGGER.error("IoTDB Jdbc example error", e); + } + + outputResult("GB18030"); + outputResult("UTF-8"); + outputResult("UTF-16"); + outputResult("GBK"); + outputResult("ISO-8859-1"); + } + + private static void outputResult(String charset) throws SQLException { + System.out.println("[Charset: " + charset + "]"); + try (final Connection connection = + DriverManager.getConnection( + "jdbc:iotdb://127.0.0.1:6667?charset=" + charset, "root", "root"); + final IoTDBStatement statement = (IoTDBStatement) connection.createStatement()) { + outputResult(statement.executeQuery("select ** from root"), Charset.forName(charset)); + } catch (IoTDBSQLException e) { + LOGGER.error("IoTDB Jdbc example error", e); + } + } + + private static void outputResult(ResultSet resultSet, Charset charset) throws SQLException { + if (resultSet != null) { + System.out.println("--------------------------"); + final ResultSetMetaData metaData = resultSet.getMetaData(); + final int columnCount = metaData.getColumnCount(); + for (int i = 0; i < columnCount; i++) { + System.out.print(metaData.getColumnLabel(i + 1) + " "); + } + System.out.println(); + + while (resultSet.next()) { + for (int i = 1; ; i++) { + System.out.print( + resultSet.getString(i) + " (" + new String(resultSet.getBytes(i), charset) + ")"); + if (i < columnCount) { + System.out.print(", "); + } else { + System.out.println(); + break; + } + } + } + System.out.println("--------------------------\n"); + } + } +} +``` \ No newline at end of file
