04-java.mdx 67.5 KB
Newer Older
1 2
---
title: TDengine Java Connector
D
danielclow 已提交
3 4 5
sidebar_label: Java
description: This document describes the TDengine Java Connector.
toc_max_heading_level: 4
6 7 8 9 10
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

D
danielclow 已提交
11
`taos-jdbcdriver` is the official Java connector for TDengine. Java developers can use it to develop applications that access data in TDengine. `taos-jdbcdriver` implements standard JDBC driver interfaces and two connection methods: One is **native connection**, which connects to TDengine instances natively through the TDengine client driver (taosc), supporting data writing, querying, subscriptions, schemaless writing, and bind interface. The second is **REST connection** which is implemented through taosAdapter. The set of features implemented by the REST connection differs slightly from those implemented by the native connection.
12

D
danielclow 已提交
13
![TDengine Database Connector Java](tdengine-jdbc-connector.webp)
14

D
danielclow 已提交
15
The preceding figure shows the two ways in which a Java application can access TDengine.
16 17

- JDBC native connection: Java applications use TSDBDriver on physical node 1 (pnode1) to call client-driven directly (`libtaos.so` or `taos.dll`) APIs to send writing and query requests to taosd instances located on physical node 2 (pnode2).
18
- JDBC REST connection: The Java application encapsulates the SQL as a REST request via RestfulDriver, sends it to the REST server (taosAdapter) on physical node 2. taosAdapter forwards the request to TDengine server and returns the result.
19

20
The REST connection, which does not rely on TDengine client drivers, is more convenient and flexible, in addition to being cross-platform. However the performance is about 30% lower than that of the native connection.
21 22

:::info
23
TDengine's JDBC driver implementation is as consistent as possible with the relational database driver. Still, there are differences in the use scenarios and technical characteristics of TDengine and relational object databases. So 'taos-jdbcdriver' also has some differences from traditional JDBC drivers. It is important to keep the following points in mind:
24 25 26 27 28 29 30 31

- TDengine does not currently support delete operations for individual data records.
- Transactional operations are not currently supported.

:::

## Supported platforms

D
danielclow 已提交
32
Native connections are supported on the same platforms as the TDengine client driver.
33 34
REST connection supports all platforms that can run Java.

35 36
## Recent update logs

H
huolibo 已提交
37 38
| taos-jdbcdriver version |                                                                   major changes                                                                    | TDengine version |
| :---------------------: | :------------------------------------------------------------------------------------------------------------------------------------------------: | :--------------: |
H
huolibo 已提交
39 40
|          3.2.4          |                     Subscription add the enable.auto.commit parameter and the unsubscribe() method in the WebSocket connection                     |        -         |
|          3.2.3          |                                                 Fixed resultSet data parsing failure in some cases                                                 |        -         |
H
huolibo 已提交
41
|          3.2.2          |                                                           Subscription add seek function                                                           | 3.0.5.0 or later |
H
huolibo 已提交
42 43 44 45 46
|          3.2.1          |                                      JDBC REST connection supports schemaless/prepareStatement over WebSocket                                      | 3.0.3.0 or later |
|          3.2.0          |                                                          This version has been deprecated                                                          |        -         |
|          3.1.0          |                                             JDBC REST connection supports subscription over WebSocket                                              |        -         |
|      3.0.1 - 3.0.4      | fix the resultSet data is parsed incorrectly sometimes. 3.0.1 is compiled on JDK 11, you are advised to use other version in the JDK 8 environment |        -         |
|          3.0.0          |                                                              Support for TDengine 3.0                                                              | 3.0.0.0 or later |
H
huolibo 已提交
47 48
|         2.0.42          |                                             Fix wasNull interface return value in WebSocket connection                                             |        -         |
|         2.0.41          |                                           Fix decode method of username and password in REST connection                                            |        -         |
H
huolibo 已提交
49 50 51 52
|     2.0.39 - 2.0.40     |                                                   Add REST connection/request timeout parameters                                                   |        -         |
|         2.0.38          |                                                    JDBC REST connections add bulk pull function                                                    |        -         |
|         2.0.37          |                                                                 Support json tags                                                                  |        -         |
|         2.0.36          |                                                             Support schemaless writing                                                             |        -         |
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103

**Note**: adding `batchfetch` to the REST connection and setting it to true will enable the WebSocket connection.

### Handling exceptions

After an error is reported, the error message and error code can be obtained through SQLException.

```java
try (Statement statement = connection.createStatement()) {
    // executeQuery
    ResultSet resultSet = statement.executeQuery(sql);
    // print result
    printResult(resultSet);
} catch (SQLException e) {
    System.out.println("ERROR Message: " + e.getMessage());
    System.out.println("ERROR Code: " + e.getErrorCode());
    e.printStackTrace();
}
```

There are four types of error codes that the JDBC connector can report:

- Error code of the JDBC driver itself (error code between 0x2301 and 0x2350),
- Error code of the native connection method (error code between 0x2351 and 0x2360)
- Error code of the consumer method (error code between 0x2371 and 0x2380)
- Error code of other TDengine function modules.

For specific error codes, please refer to.

| Error Code | Description                                                     | Suggested Actions                                                                                                                  |
| ---------- | --------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| 0x2301     | connection already closed                                       | The connection has been closed, check the connection status, or recreate the connection to execute the relevant instructions.      |
| 0x2302     | this operation is NOT supported currently!                      | The current interface does not support the connection. You can use another connection mode.                                        |
| 0x2303     | invalid variables                                               | The parameter is invalid. Check the interface specification and adjust the parameter type and size.                                |
| 0x2304     | statement is closed                                             | The statement is closed. Check whether the statement is closed and used again, or whether the connection is normal.                |
| 0x2305     | resultSet is closed                                             | result set The result set is released. Check whether the result set is released and used again.                                    |
| 0x2306     | Batch is empty!                                                 | prepare statement Add parameters and then execute batch.                                                                           |
| 0x2307     | Can not issue data manipulation statements with executeQuery()  | The update operation should use execute update(), not execute query().                                                             |
| 0x2308     | Can not issue SELECT via executeUpdate()                        | The query operation should use execute query(), not execute update().                                                              |
| 0x230d     | parameter index out of range                                    | The parameter is out of bounds. Check the proper range of the parameter.                                                           |
| 0x230e     | connection already closed                                       | The connection has been closed. Please check whether the connection is closed and used again, or whether the connection is normal. |
| 0x230f     | unknown sql type in tdengine                                    | Check the data type supported by TDengine.                                                                                         |
| 0x2310     | can't register JDBC-JNI driver                                  | The native driver cannot be registered. Please check whether the url is correct.                                                   |
| 0x2312     | url is not set                                                  | Check whether the REST connection url is correct.                                                                                  |
| 0x2314     | numeric value out of range                                      | Check that the correct interface is used for the numeric types in the obtained result set.                                         |
| 0x2315     | unknown taos type in tdengine                                   | Whether the correct TDengine data type is specified when converting the TDengine data type to the JDBC data type.                  |
| 0x2317     |                                                                 | wrong request type was used in the REST connection.                                                                                |
| 0x2318     |                                                                 | data transmission exception occurred during the REST connection. Please check the network status and try again.                    |
| 0x2319     | user is required                                                | The user name information is missing when creating the connection                                                                  |
| 0x231a     | password is required                                            | Password information is missing when creating a connection                                                                         |
| 0x231c     | httpEntity is null, sql:                                        | Execution exception occurred during the REST connection                                                                            |
H
huolibo 已提交
104 105
| 0x231d     | can't create connection with server within                      | Increase the connection time by adding the httpConnectTimeout parameter, or check the connection to the taos adapter.              |
| 0x231e     | failed to complete the task within the specified time           | Increase the execution time by adding the messageWaitTimeout parameter, or check the connection to the taos adapter.               |
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
| 0x2350     | unknown error                                                   | Unknown exception, please return to the developer on github.                                                                       |
| 0x2352     | Unsupported encoding                                            | An unsupported character encoding set is specified under the native Connection.                                                    |
| 0x2353     | internal error of database, please see taoslog for more details | An error occurs when the prepare statement is executed on the native connection. Check the taos log to locate the fault.           |
| 0x2354     | JNI connection is NULL                                          | When the command is executed, the native Connection is closed. Check the connection to TDengine.                                   |
| 0x2355     | JNI result set is NULL                                          | The result set is abnormal. Please check the connection status and try again.                                                      |
| 0x2356     | invalid num of fields                                           | The meta information of the result set obtained by the native connection does not match.                                           |
| 0x2357     | empty sql string                                                | Fill in the correct SQL for execution.                                                                                             |
| 0x2359     | JNI alloc memory failed, please see taoslog for more details    | Memory allocation for the native connection failed. Check the taos log to locate the problem.                                      |
| 0x2371     | consumer properties must not be null!                           | The parameter is empty when you create a subscription. Please fill in the correct parameter.                                       |
| 0x2372     | configs contain empty key, failed to set consumer property      | The parameter key contains a null value. Please enter the correct parameter.                                                       |
| 0x2373     | failed to set consumer property,                                | The parameter value contains a null value. Please enter the correct parameter.                                                     |
| 0x2375     | topic reference has been destroyed                              | The topic reference is released during the creation of the data subscription. Check the connection to TDengine.                    |
| 0x2376     | failed to set consumer topic, topic name is empty               | During data subscription creation, the subscription topic name is empty. Check that the specified topic name is correct.           |
| 0x2377     | consumer reference has been destroyed                           | The subscription data transfer channel has been closed. Please check the connection to TDengine.                                   |
| 0x2378     | consumer create error                                           | Failed to create a data subscription. Check the taos log according to the error message to locate the fault.                       |
H
huolibo 已提交
121 122
| 0x2379     | seek offset must not be a negative number                       | The seek interface parameter cannot be negative. Use the correct parameter                                                         |
| 0x237a     | vGroup not found in result set                                  | subscription is not bound to the VGroup due to the rebalance mechanism                                                             |
123 124 125 126

- [TDengine Java Connector](https://github.com/taosdata/taos-connector-jdbc/blob/main/src/main/java/com/taosdata/jdbc/TSDBErrorNumbers.java)
<!-- - [TDengine_ERROR_CODE](../error-code) -->

127 128 129 130
## TDengine DataType vs. Java DataType

TDengine currently supports timestamp, number, character, Boolean type, and the corresponding type conversion with Java is as follows:

H
huolibo 已提交
131 132 133 134 135 136 137 138 139 140 141 142 143
| TDengine DataType | JDBCType           |
| ----------------- | ------------------ |
| TIMESTAMP         | java.sql.Timestamp |
| INT               | java.lang.Integer  |
| BIGINT            | java.lang.Long     |
| FLOAT             | java.lang.Float    |
| DOUBLE            | java.lang.Double   |
| SMALLINT          | java.lang.Short    |
| TINYINT           | java.lang.Byte     |
| BOOL              | java.lang.Boolean  |
| BINARY            | byte array         |
| NCHAR             | java.lang.String   |
| JSON              | java.lang.String   |
144 145 146

**Note**: Only TAG supports JSON types

D
danielclow 已提交
147
## Installation Steps
148 149 150 151 152 153 154 155 156 157 158

### Pre-installation preparation

Before using Java Connector to connect to the database, the following conditions are required.

- Java 1.8 or above runtime environment and Maven 3.6 or above installed
- TDengine client driver installed (required for native connections, not required for REST connections), please refer to [Installing Client Driver](/reference/connector#Install-Client-Driver)

### Install the connectors

<Tabs defaultValue="maven">
D
dingbo 已提交
159
<TabItem value="maven" label="Install via Maven">
160

D
danielclow 已提交
161 162
taos-jdbcdriver has been published on the [Sonatype Repository](https://search.maven.org/artifact/com.taosdata.jdbc/taos-jdbcdriver) and synchronized to other major repositories.

163 164 165 166 167 168
- [sonatype](https://search.maven.org/artifact/com.taosdata.jdbc/taos-jdbcdriver)
- [mvnrepository](https://mvnrepository.com/artifact/com.taosdata.jdbc/taos-jdbcdriver)
- [maven.aliyun](https://maven.aliyun.com/mvn/search)

Add following dependency in the `pom.xml` file of your Maven project:

D
danielclow 已提交
169
```xml-dtd
170 171 172
<dependency>
 <groupId>com.taosdata.jdbc</groupId>
 <artifactId>taos-jdbcdriver</artifactId>
H
huolibo 已提交
173
 <version>3.2.2</version>
174 175 176 177 178 179
</dependency>
```

</TabItem>
<TabItem value="source" label="Build from source code">

180
You can build Java connector from source code after cloning the TDengine project:
181

D
danielclow 已提交
182 183
```shell
git clone https://github.com/taosdata/taos-connector-jdbc.git
H
huolibo 已提交
184
cd taos-connector-jdbc
185 186 187
mvn clean install -Dmaven.test.skip=true
```

188
After you have compiled taos-jdbcdriver, the `taos-jdbcdriver-3.2.*-dist.jar` file is created in the target directory. The compiled JAR file is automatically stored in your local Maven repository.
189 190 191 192

</TabItem>
</Tabs>

D
danielclow 已提交
193
## Establishing a connection
194 195

TDengine's JDBC URL specification format is:
D
danielclow 已提交
196
`jdbc:[TAOS|TAOS-RS]://[host_name]:[port]/[database_name]?[user={user}|&password={password}|&charset={charset}|&cfgdir={config_dir}|&locale={locale}|&timezone={timezone}]`
197 198 199

For establishing connections, native connections differ slightly from REST connections.

200
<Tabs defaultValue="rest">
D
danielclow 已提交
201
<TabItem value="native" label="native connection">
202 203 204 205 206 207 208 209 210

```java
Class.forName("com.taosdata.jdbc.TSDBDriver");
String jdbcUrl = "jdbc:TAOS://taosdemo.com:6030/test?user=root&password=taosdata";
Connection conn = DriverManager.getConnection(jdbcUrl);
```

In the above example, TSDBDriver, which uses a JDBC native connection, establishes a connection to a hostname `taosdemo.com`, port `6030` (the default port for TDengine), and a database named `test`. In this URL, the user name `user` is specified as `root`, and the `password` is `taosdata`.

wafwerar's avatar
wafwerar 已提交
211
Note: With JDBC native connections, taos-jdbcdriver relies on the client driver (`libtaos.so` on Linux; `taos.dll` on Windows; `libtaos.dylib` on macOS).
212 213 214 215 216

The configuration parameters in the URL are as follows:

- user: Log in to the TDengine username. The default value is 'root'.
- password: User login password, the default value is 'taosdata'.
wafwerar's avatar
wafwerar 已提交
217
- cfgdir: client configuration file directory path, default '/etc/taos' on Linux OS, 'C:/TDengine/cfg' on Windows OS, '/etc/taos' on macOS.
218 219 220
- charset: The character set used by the client, the default value is the system character set.
- locale: Client locale, by default, use the system's current locale.
- timezone: The time zone used by the client, the default value is the system's current time zone.
D
danielclow 已提交
221
- batchfetch: true: pulls result sets in batches when executing queries; false: pulls result sets row by row. The default value is true. Enabling batch pulling and obtaining a batch of data can improve query performance when the query data volume is large.
222 223 224 225 226 227 228 229
- batchErrorIgnore:true: When executing statement executeBatch, if there is a SQL execution failure in the middle, the following SQL will continue to be executed. false: No more statements after the failed SQL are executed. The default value is: false.

**Connect using the TDengine client-driven configuration file **

When you use a JDBC native connection to connect to a TDengine cluster, you can use the TDengine client driver configuration file to specify parameters such as `firstEp` and `secondEp` of the cluster in the configuration file as below:

1. Do not specify hostname and port in Java applications.

D
danielclow 已提交
230 231 232 233 234 235 236 237 238 239 240 241
```java
public Connection getConn() throws Exception{
  Class.forName("com.taosdata.jdbc.TSDBDriver");
  String jdbcUrl = "jdbc:TAOS://:/test?user=root&password=taosdata";
  Properties connProps = new Properties();
  connProps.setProperty(TSDBDriver.PROPERTY_KEY_CHARSET, "UTF-8");
  connProps.setProperty(TSDBDriver.PROPERTY_KEY_LOCALE, "en_US.UTF-8");
  connProps.setProperty(TSDBDriver.PROPERTY_KEY_TIME_ZONE, "UTC-8");
  Connection conn = DriverManager.getConnection(jdbcUrl, connProps);
  return conn;
}
```
242 243 244

2. specify the firstEp and the secondEp in the configuration file taos.cfg

D
danielclow 已提交
245 246 247
```shell
# first fully qualified domain name (FQDN) for TDengine system
firstEp               cluster_node1:6030
248

D
danielclow 已提交
249 250
# second fully qualified domain name (FQDN) for TDengine system, for cluster only
secondEp              cluster_node2:6030
251

D
danielclow 已提交
252 253
# default system charset
# charset               UTF-8
254

D
danielclow 已提交
255 256 257
# system locale
# locale                en_US.UTF-8
```
258 259 260 261 262

In the above example, JDBC uses the client's configuration file to establish a connection to a hostname `cluster_node1`, port 6030, and a database named `test`. When the firstEp node in the cluster fails, JDBC attempts to connect to the cluster using secondEp.

In TDengine, as long as one node in firstEp and secondEp is valid, the connection to the cluster can be established normally.

wafwerar's avatar
wafwerar 已提交
263
The configuration file here refers to the configuration file on the machine where the application that calls the JDBC Connector is located, the default path is `/etc/taos/taos.cfg` on Linux, the default path is `C://TDengine/cfg/taos.cfg` on Windows, and the default path is `/etc/taos/taos.cfg` on macOS.
D
dingbo 已提交
264

265 266 267 268 269 270 271 272 273 274 275
</TabItem>
<TabItem value="rest" label="REST connection">

```java
Class.forName("com.taosdata.jdbc.rs.RestfulDriver");
String jdbcUrl = "jdbc:TAOS-RS://taosdemo.com:6041/test?user=root&password=taosdata";
Connection conn = DriverManager.getConnection(jdbcUrl);
```

In the above example, a RestfulDriver with a JDBC REST connection is used to establish a connection to a database named `test` with hostname `taosdemo.com` on port `6041`. The URL specifies the user name as `root` and the password as `taosdata`.

276
There is no dependency on the client driver when Using a JDBC REST connection. Compared to a JDBC native connection, only the following are required:
277 278 279 280 281

1. driverClass specified as "com.taosdata.jdbc.rs.RestfulDriver".
2. jdbcUrl starting with "jdbc:TAOS-RS://".
3. use 6041 as the connection port.

D
danielclow 已提交
282
The configuration parameters in the URL are as follows:
283

D
danielclow 已提交
284 285 286
- user: Log in to the TDengine username. The default value is 'root'.
- password: User login password, the default value is 'taosdata'.
- batchfetch: true: pulls result sets in batches when executing queries; false: pulls result sets row by row. The default value is: false. batchfetch uses HTTP for data transfer. JDBC REST supports batch pulls. taos-jdbcdriver and TDengine transfer data via WebSocket connection. Compared with HTTP, WebSocket enables JDBC REST connection to support large data volume querying and improve query performance.
H
huolibo 已提交
287
- charset: specify the charset to parse the string, this parameter is valid only when set batchfetch to true.
288
- batchErrorIgnore: true: when executing executeBatch of Statement, if one SQL execution fails in the middle, continue to execute the following SQL. false: no longer execute any statement after the failed SQL. The default value is: false.
H
huolibo 已提交
289 290 291
- httpConnectTimeout: REST connection timeout in milliseconds, the default value is 60000 ms.
- httpSocketTimeout: socket timeout in milliseconds, the default value is 60000 ms. It only takes effect when batchfetch is false.
- messageWaitTimeout: message transmission timeout in milliseconds, the default value is 60000 ms. It only takes effect when batchfetch is true.
sangshuduo's avatar
sangshuduo 已提交
292
- useSSL: connecting Securely Using SSL. true: using SSL connection, false: not using SSL connection.
H
huolibo 已提交
293
- httpPoolSize: size of REST concurrent requests. The default value is 20.
294 295 296 297 298

**Note**: Some configuration items (e.g., locale, timezone) do not work in the REST connection.

:::note

D
danielclow 已提交
299
- Unlike the native connection method, the REST interface is stateless. When using the JDBC REST connection, you need to specify the database name of the table and super table in SQL. For example:
300 301

```sql
G
gccgdb1234 已提交
302
INSERT INTO test.t1 USING test.weather (ts, temperature) TAGS('California.SanFrancisco') VALUES(now, 24.6);
303 304
```

D
danielclow 已提交
305
- If the dbname is specified in the URL, the JDBC REST connection uses /rest/sql/dbname as the default URL for RESTful requests. In this case, it is not necessary to specify the dbname in SQL. For example, if the URL is `jdbc:TAOS-RS://127.0.0.1:6041/test`, then the SQL can be executed: insert into test using weather(ts, temperature) tags('California.SanFrancisco') values(now, 24.6);
306 307 308 309 310 311 312 313 314 315

:::

</TabItem>
</Tabs>

### Specify the URL and Properties to get the connection

In addition to getting the connection from the specified URL, you can use Properties to specify parameters when the connection is established.

D
danielclow 已提交
316
Note:
317 318

- The client parameter set in the application is process-level. If you want to update the parameters of the client, you need to restart the application. This is because the client parameter is a global parameter that takes effect only the first time the application is set.
319
- The following sample code is based on taos-jdbcdriver-3.1.0.
320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344

```java
public Connection getConn() throws Exception{
  Class.forName("com.taosdata.jdbc.TSDBDriver");
  String jdbcUrl = "jdbc:TAOS://taosdemo.com:6030/test?user=root&password=taosdata";
  Properties connProps = new Properties();
  connProps.setProperty(TSDBDriver.PROPERTY_KEY_CHARSET, "UTF-8");
  connProps.setProperty(TSDBDriver.PROPERTY_KEY_LOCALE, "en_US.UTF-8");
  connProps.setProperty(TSDBDriver.PROPERTY_KEY_TIME_ZONE, "UTC-8");
  connProps.setProperty("debugFlag", "135");
  connProps.setProperty("maxSQLLength", "1048576");
  Connection conn = DriverManager.getConnection(jdbcUrl, connProps);
  return conn;
}

public Connection getRestConn() throws Exception{
  Class.forName("com.taosdata.jdbc.rs.RestfulDriver");
  String jdbcUrl = "jdbc:TAOS-RS://taosdemo.com:6041/test?user=root&password=taosdata";
  Properties connProps = new Properties();
  connProps.setProperty(TSDBDriver.PROPERTY_KEY_BATCH_LOAD, "true");
  Connection conn = DriverManager.getConnection(jdbcUrl, connProps);
  return conn;
}
```

D
danielclow 已提交
345
In the above example, a connection is established to `taosdemo.com`, port is 6030/6041, and database named `test`. The connection specifies the user name as `root` and the password as `taosdata` in the URL and specifies the character set, language environment, time zone, and whether to enable bulk fetching in the connProps.The url specifies the user name as `root` and the password as `taosdata`.
346 347 348

The configuration parameters in properties are as follows.

H
huolibo 已提交
349
- TSDBDriver.PROPERTY_KEY_USER: login TDengine user name, default value 'root'.
350 351
- TSDBDriver.PROPERTY_KEY_PASSWORD: user login password, default value 'taosdata'.
- TSDBDriver.PROPERTY_KEY_BATCH_LOAD: true: pull the result set in batch when executing query; false: pull the result set row by row. The default value is: false.
D
danielclow 已提交
352
- TSDBDriver.PROPERTY_KEY_BATCH_ERROR_IGNORE: true: when executing executeBatch of Statement, if there is a SQL execution failure in the middle, continue to execute the following sql. false: no longer execute any statement after the failed SQL. The default value is: false.
wafwerar's avatar
wafwerar 已提交
353
- TSDBDriver.PROPERTY_KEY_CONFIG_DIR: only works when using JDBC native connection. Client configuration file directory path, default value `/etc/taos` on Linux OS, default value `C:/TDengine/cfg` on Windows OS, default value `/etc/taos` on macOS.
H
huolibo 已提交
354
- TSDBDriver.PROPERTY_KEY_CHARSET: In the character set used by the client, the default value is the system character set.
355 356
- TSDBDriver.PROPERTY_KEY_LOCALE: this only takes effect when using JDBC native connection. Client language environment, the default value is system current locale.
- TSDBDriver.PROPERTY_KEY_TIME_ZONE: only takes effect when using JDBC native connection. In the time zone used by the client, the default value is the system's current time zone.
H
huolibo 已提交
357 358 359
- TSDBDriver.HTTP_CONNECT_TIMEOUT: REST connection timeout in milliseconds, the default value is 60000 ms. It only takes effect when using JDBC REST connection.
- TSDBDriver.HTTP_SOCKET_TIMEOUT: socket timeout in milliseconds, the default value is 60000 ms. It only takes effect when using JDBC REST connection and batchfetch is false.
- TSDBDriver.PROPERTY_KEY_MESSAGE_WAIT_TIMEOUT: message transmission timeout in milliseconds, the default value is 60000 ms. It only takes effect when using JDBC REST connection and batchfetch is true.
sangshuduo's avatar
sangshuduo 已提交
360
- TSDBDriver.PROPERTY_KEY_USE_SSL: connecting Securely Using SSL. true: using SSL connection, false: not using SSL connection. It only takes effect when using JDBC REST connection.
H
huolibo 已提交
361
- TSDBDriver.HTTP_POOL_SIZE: size of REST concurrent requests. The default value is 20.
H
huolibo 已提交
362
  For JDBC native connections, you can specify other parameters, such as log level, SQL length, etc., by specifying URL and Properties. For more detailed configuration, please refer to [Client Configuration](/reference/config/#Client-Only).
363 364 365 366 367 368 369 370 371

### Priority of configuration parameters

If the configuration parameters are duplicated in the URL, Properties, or client configuration file, the `priority` of the parameters, from highest to lowest, are as follows:

1. JDBC URL parameters, as described above, can be specified in the parameters of the JDBC URL.
2. Properties connProps
3. the configuration file taos.cfg of the TDengine client driver when using a native connection

372
For example, if you specify the password as `taosdata` in the URL and specify the password as `taosdemo` in the Properties simultaneously, JDBC will use the password in the URL to establish the connection.
373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392

## Usage examples

### Create database and tables

```java
Statement stmt = conn.createStatement();

// create database
stmt.executeUpdate("create database if not exists db");

// use database
stmt.executeUpdate("use db");

// create table
stmt.executeUpdate("create table if not exists tb (ts timestamp, temperature int, humidity float)");
```

> **Note**: If you do not use `use db` to specify the database, all subsequent operations on the table need to add the database name as a prefix, such as db.tb.

sangshuduo's avatar
sangshuduo 已提交
393
### Insert data
394 395 396 397 398 399 400 401 402 403 404 405 406 407

```java
// insert data
int affectedRows = stmt.executeUpdate("insert into tb values(now, 23, 10.3) (now + 1s, 20, 9.3)");

System.out.println("insert " + affectedRows + " rows.");
```

> now is an internal function. The default is the current time of the client's computer.
> `now + 1s` represents the current time of the client plus 1 second, followed by the number representing the unit of time: a (milliseconds), s (seconds), m (minutes), h (hours), d (days), w (weeks), n (months), y (years).

### Querying data

```java
D
danielclow 已提交
408
// insert data
409 410 411 412 413 414 415 416 417 418 419 420 421 422 423
ResultSet resultSet = stmt.executeQuery("select * from tb");

Timestamp ts = null;
int temperature = 0;
float humidity = 0;
while(resultSet.next()){

    ts = resultSet.getTimestamp(1);
    temperature = resultSet.getInt(2);
    humidity = resultSet.getFloat("humidity");

    System.out.printf("%s, %d, %s\n", ts, temperature, humidity);
}
```

424
> The query is consistent with operating a relational database. When using subscripts to get the contents of the returned fields, you have to start from 1. However, we recommend using the field names to get the values of the fields in the result set.
425

H
huolibo 已提交
426 427 428 429
### execute SQL with reqId

This reqId can be used to request link tracing.

H
huolibo 已提交
430
```java
H
huolibo 已提交
431 432 433 434 435 436 437 438
AbstractStatement aStmt = (AbstractStatement) connection.createStatement();
aStmt.execute("create database if not exists db", 1L);
aStmt.executeUpdate("use db", 2L);
try (ResultSet rs = aStmt.executeQuery("select * from tb", 3L)) {
    Timestamp ts = rs.getTimestamp(1);
}
```

439 440
### Writing data via parameter binding

D
danielclow 已提交
441
TDengine has significantly improved the bind APIs to support data writing (INSERT) scenarios. Writing data in this way avoids the resource consumption of SQL syntax parsing, resulting in significant write performance improvements in many cases.
442

D
danielclow 已提交
443
**Note:**
444 445

- JDBC REST connections do not currently support bind interface
446
- The following sample code is based on taos-jdbcdriver-3.2.1
447
- The setString method should be called for binary type data, and the setNString method should be called for nchar type data
H
huolibo 已提交
448
- Do not use `db.?` in prepareStatement when specify the database with the table name, should directly use `?`, then specify the database in setTableName, for example: `prepareStatement.setTableName("db.t1")`.
449 450 451

<Tabs defaultValue="native">
<TabItem value="native" label="native connection">
452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653

```java
public class ParameterBindingDemo {

    private static final String host = "127.0.0.1";
    private static final Random random = new Random(System.currentTimeMillis());
    private static final int BINARY_COLUMN_SIZE = 20;
    private static final String[] schemaList = {
            "create table stable1(ts timestamp, f1 tinyint, f2 smallint, f3 int, f4 bigint) tags(t1 tinyint, t2 smallint, t3 int, t4 bigint)",
            "create table stable2(ts timestamp, f1 float, f2 double) tags(t1 float, t2 double)",
            "create table stable3(ts timestamp, f1 bool) tags(t1 bool)",
            "create table stable4(ts timestamp, f1 binary(" + BINARY_COLUMN_SIZE + ")) tags(t1 binary(" + BINARY_COLUMN_SIZE + "))",
            "create table stable5(ts timestamp, f1 nchar(" + BINARY_COLUMN_SIZE + ")) tags(t1 nchar(" + BINARY_COLUMN_SIZE + "))"
    };
    private static final int numOfSubTable = 10, numOfRow = 10;

    public static void main(String[] args) throws SQLException {

        String jdbcUrl = "jdbc:TAOS://" + host + ":6030/";
        Connection conn = DriverManager.getConnection(jdbcUrl, "root", "taosdata");

        init(conn);

        bindInteger(conn);

        bindFloat(conn);

        bindBoolean(conn);

        bindBytes(conn);

        bindString(conn);

        conn.close();
    }

    private static void init(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("drop database if exists test_parabind");
            stmt.execute("create database if not exists test_parabind");
            stmt.execute("use test_parabind");
            for (int i = 0; i < schemaList.length; i++) {
                stmt.execute(schemaList[i]);
            }
        }
    }

    private static void bindInteger(Connection conn) throws SQLException {
        String sql = "insert into ? using stable1 tags(?,?,?,?) values(?,?,?,?,?)";

        try (TSDBPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSDBPreparedStatement.class)) {

            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t1_" + i);
                // set tags
                pstmt.setTagByte(0, Byte.parseByte(Integer.toString(random.nextInt(Byte.MAX_VALUE))));
                pstmt.setTagShort(1, Short.parseShort(Integer.toString(random.nextInt(Short.MAX_VALUE))));
                pstmt.setTagInt(2, random.nextInt(Integer.MAX_VALUE));
                pstmt.setTagLong(3, random.nextLong());
                // set columns
                ArrayList<Long> tsList = new ArrayList<>();
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++)
                    tsList.add(current + j);
                pstmt.setTimestamp(0, tsList);

                ArrayList<Byte> f1List = new ArrayList<>();
                for (int j = 0; j < numOfRow; j++)
                    f1List.add(Byte.parseByte(Integer.toString(random.nextInt(Byte.MAX_VALUE))));
                pstmt.setByte(1, f1List);

                ArrayList<Short> f2List = new ArrayList<>();
                for (int j = 0; j < numOfRow; j++)
                    f2List.add(Short.parseShort(Integer.toString(random.nextInt(Short.MAX_VALUE))));
                pstmt.setShort(2, f2List);

                ArrayList<Integer> f3List = new ArrayList<>();
                for (int j = 0; j < numOfRow; j++)
                    f3List.add(random.nextInt(Integer.MAX_VALUE));
                pstmt.setInt(3, f3List);

                ArrayList<Long> f4List = new ArrayList<>();
                for (int j = 0; j < numOfRow; j++)
                    f4List.add(random.nextLong());
                pstmt.setLong(4, f4List);

                // add column
                pstmt.columnDataAddBatch();
            }
            // execute column
            pstmt.columnDataExecuteBatch();
        }
    }

    private static void bindFloat(Connection conn) throws SQLException {
        String sql = "insert into ? using stable2 tags(?,?) values(?,?,?)";

        TSDBPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSDBPreparedStatement.class);

        for (int i = 1; i <= numOfSubTable; i++) {
            // set table name
            pstmt.setTableName("t2_" + i);
            // set tags
            pstmt.setTagFloat(0, random.nextFloat());
            pstmt.setTagDouble(1, random.nextDouble());
            // set columns
            ArrayList<Long> tsList = new ArrayList<>();
            long current = System.currentTimeMillis();
            for (int j = 0; j < numOfRow; j++)
                tsList.add(current + j);
            pstmt.setTimestamp(0, tsList);

            ArrayList<Float> f1List = new ArrayList<>();
            for (int j = 0; j < numOfRow; j++)
                f1List.add(random.nextFloat());
            pstmt.setFloat(1, f1List);

            ArrayList<Double> f2List = new ArrayList<>();
            for (int j = 0; j < numOfRow; j++)
                f2List.add(random.nextDouble());
            pstmt.setDouble(2, f2List);

            // add column
            pstmt.columnDataAddBatch();
        }
        // execute
        pstmt.columnDataExecuteBatch();
        // close if no try-with-catch statement is used
        pstmt.close();
    }

    private static void bindBoolean(Connection conn) throws SQLException {
        String sql = "insert into ? using stable3 tags(?) values(?,?)";

        try (TSDBPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSDBPreparedStatement.class)) {
            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t3_" + i);
                // set tags
                pstmt.setTagBoolean(0, random.nextBoolean());
                // set columns
                ArrayList<Long> tsList = new ArrayList<>();
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++)
                    tsList.add(current + j);
                pstmt.setTimestamp(0, tsList);

                ArrayList<Boolean> f1List = new ArrayList<>();
                for (int j = 0; j < numOfRow; j++)
                    f1List.add(random.nextBoolean());
                pstmt.setBoolean(1, f1List);

                // add column
                pstmt.columnDataAddBatch();
            }
            // execute
            pstmt.columnDataExecuteBatch();
        }
    }

    private static void bindBytes(Connection conn) throws SQLException {
        String sql = "insert into ? using stable4 tags(?) values(?,?)";

        try (TSDBPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSDBPreparedStatement.class)) {

            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t4_" + i);
                // set tags
                pstmt.setTagString(0, new String("abc"));

                // set columns
                ArrayList<Long> tsList = new ArrayList<>();
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++)
                    tsList.add(current + j);
                pstmt.setTimestamp(0, tsList);

                ArrayList<String> f1List = new ArrayList<>();
                for (int j = 0; j < numOfRow; j++) {
                    f1List.add(new String("abc"));
                }
                pstmt.setString(1, f1List, BINARY_COLUMN_SIZE);

                // add column
                pstmt.columnDataAddBatch();
            }
            // execute
            pstmt.columnDataExecuteBatch();
        }
    }

    private static void bindString(Connection conn) throws SQLException {
        String sql = "insert into ? using stable5 tags(?) values(?,?)";

        try (TSDBPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSDBPreparedStatement.class)) {

            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t5_" + i);
                // set tags
D
danielclow 已提交
654
                pstmt.setTagNString(0, "California.SanFrancisco");
655 656 657 658 659 660 661 662 663 664

                // set columns
                ArrayList<Long> tsList = new ArrayList<>();
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++)
                    tsList.add(current + j);
                pstmt.setTimestamp(0, tsList);

                ArrayList<String> f1List = new ArrayList<>();
                for (int j = 0; j < numOfRow; j++) {
D
danielclow 已提交
665
                    f1List.add("California.LosAngeles");
666 667 668 669 670 671 672 673 674 675 676 677 678
                }
                pstmt.setNString(1, f1List, BINARY_COLUMN_SIZE);

                // add column
                pstmt.columnDataAddBatch();
            }
            // execute
            pstmt.columnDataExecuteBatch();
        }
    }
}
```

679
**Note**: both setString and setNString require the user to declare the width of the corresponding column in the size parameter of the table definition
680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695

The methods to set VALUES columns:

```java
public void setInt(int columnIndex, ArrayList<Integer> list) throws SQLException
public void setFloat(int columnIndex, ArrayList<Float> list) throws SQLException
public void setTimestamp(int columnIndex, ArrayList<Long> list) throws SQLException
public void setLong(int columnIndex, ArrayList<Long> list) throws SQLException
public void setDouble(int columnIndex, ArrayList<Double> list) throws SQLException
public void setBoolean(int columnIndex, ArrayList<Boolean> list) throws SQLException
public void setByte(int columnIndex, ArrayList<Byte> list) throws SQLException
public void setShort(int columnIndex, ArrayList<Short> list) throws SQLException
public void setString(int columnIndex, ArrayList<String> list, int size) throws SQLException
public void setNString(int columnIndex, ArrayList<String> list, int size) throws SQLException
```

696
</TabItem>
H
huolibo 已提交
697
<TabItem value="ws" label="WebSocket connection">
698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883

```java
public class ParameterBindingDemo {
    private static final String host = "127.0.0.1";
    private static final Random random = new Random(System.currentTimeMillis());
    private static final int BINARY_COLUMN_SIZE = 30;
    private static final String[] schemaList = {
            "create table stable1(ts timestamp, f1 tinyint, f2 smallint, f3 int, f4 bigint) tags(t1 tinyint, t2 smallint, t3 int, t4 bigint)",
            "create table stable2(ts timestamp, f1 float, f2 double) tags(t1 float, t2 double)",
            "create table stable3(ts timestamp, f1 bool) tags(t1 bool)",
            "create table stable4(ts timestamp, f1 binary(" + BINARY_COLUMN_SIZE + ")) tags(t1 binary(" + BINARY_COLUMN_SIZE + "))",
            "create table stable5(ts timestamp, f1 nchar(" + BINARY_COLUMN_SIZE + ")) tags(t1 nchar(" + BINARY_COLUMN_SIZE + "))"
    };
    private static final int numOfSubTable = 10, numOfRow = 10;

    public static void main(String[] args) throws SQLException {

        String jdbcUrl = "jdbc:TAOS-RS://" + host + ":6041/?batchfetch=true";
        Connection conn = DriverManager.getConnection(jdbcUrl, "root", "taosdata");

        init(conn);

        bindInteger(conn);

        bindFloat(conn);

        bindBoolean(conn);

        bindBytes(conn);

        bindString(conn);

        conn.close();
    }

    private static void init(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("drop database if exists test_ws_parabind");
            stmt.execute("create database if not exists test_ws_parabind");
            stmt.execute("use test_ws_parabind");
            for (int i = 0; i < schemaList.length; i++) {
                stmt.execute(schemaList[i]);
            }
        }
    }

    private static void bindInteger(Connection conn) throws SQLException {
        String sql = "insert into ? using stable1 tags(?,?,?,?) values(?,?,?,?,?)";

        try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t1_" + i);
                // set tags
                pstmt.setTagByte(1, Byte.parseByte(Integer.toString(random.nextInt(Byte.MAX_VALUE))));
                pstmt.setTagShort(2, Short.parseShort(Integer.toString(random.nextInt(Short.MAX_VALUE))));
                pstmt.setTagInt(3, random.nextInt(Integer.MAX_VALUE));
                pstmt.setTagLong(4, random.nextLong());
                // set columns
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++) {
                    pstmt.setTimestamp(1, new Timestamp(current + j));
                    pstmt.setByte(2, Byte.parseByte(Integer.toString(random.nextInt(Byte.MAX_VALUE))));
                    pstmt.setShort(3, Short.parseShort(Integer.toString(random.nextInt(Short.MAX_VALUE))));
                    pstmt.setInt(4, random.nextInt(Integer.MAX_VALUE));
                    pstmt.setLong(5, random.nextLong());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
        }
    }

    private static void bindFloat(Connection conn) throws SQLException {
        String sql = "insert into ? using stable2 tags(?,?) values(?,?,?)";

        try(TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t2_" + i);
                // set tags
                pstmt.setTagFloat(1, random.nextFloat());
                pstmt.setTagDouble(2, random.nextDouble());
                // set columns
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++) {
                    pstmt.setTimestamp(1, new Timestamp(current + j));
                    pstmt.setFloat(2, random.nextFloat());
                    pstmt.setDouble(3, random.nextDouble());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
        }
    }

    private static void bindBoolean(Connection conn) throws SQLException {
        String sql = "insert into ? using stable3 tags(?) values(?,?)";

        try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {
            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t3_" + i);
                // set tags
                pstmt.setTagBoolean(1, random.nextBoolean());
                // set columns
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++) {
                    pstmt.setTimestamp(1, new Timestamp(current + j));
                    pstmt.setBoolean(2, random.nextBoolean());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
        }
    }

    private static void bindBytes(Connection conn) throws SQLException {
        String sql = "insert into ? using stable4 tags(?) values(?,?)";

        try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t4_" + i);
                // set tags
                pstmt.setTagString(1, new String("abc"));

                // set columns
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++) {
                    pstmt.setTimestamp(1, new Timestamp(current + j));
                    pstmt.setString(2, "abc");
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
        }
    }

    private static void bindString(Connection conn) throws SQLException {
        String sql = "insert into ? using stable5 tags(?) values(?,?)";

        try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

            for (int i = 1; i <= numOfSubTable; i++) {
                // set table name
                pstmt.setTableName("t5_" + i);
                // set tags
                pstmt.setTagNString(1, "California.SanFrancisco");

                // set columns
                long current = System.currentTimeMillis();
                for (int j = 0; j < numOfRow; j++) {
                    pstmt.setTimestamp(0, new Timestamp(current + j));
                    pstmt.setNString(1, "California.SanFrancisco");
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
        }
    }
}
```

</TabItem>
</Tabs>

The methods to set TAGS values:

```java
public void setTagNull(int index, int type)
public void setTagBoolean(int index, boolean value)
public void setTagInt(int index, int value)
public void setTagByte(int index, byte value)
public void setTagShort(int index, short value)
public void setTagLong(int index, long value)
public void setTagTimestamp(int index, long value)
public void setTagFloat(int index, float value)
public void setTagDouble(int index, double value)
public void setTagString(int index, String value)
public void setTagNString(int index, String value)
```

884 885
### Schemaless Writing

D
danielclow 已提交
886
TDengine supports schemaless writing. It is compatible with InfluxDB's Line Protocol, OpenTSDB's telnet line protocol, and OpenTSDB's JSON format protocol. For more information, see [Schemaless Writing](../../schemaless).
887

888 889
<Tabs defaultValue="native">
<TabItem value="native" label="native connection">
890 891

```java
892
public class SchemalessJniTest {
893 894 895
    private static final String host = "127.0.0.1";
    private static final String lineDemo = "st,t1=3i64,t2=4f64,t3=\"t3\" c1=3i64,c3=L\"passit\",c2=false,c4=4f64 1626006833639000000";
    private static final String telnetDemo = "stb0_0 1626006833 4 host=host0 interface=eth0";
G
gccgdb1234 已提交
896
    private static final String jsonDemo = "{\"metric\": \"meter_current\",\"timestamp\": 1346846400,\"value\": 10.3, \"tags\": {\"groupid\": 2, \"location\": \"California.SanFrancisco\", \"id\": \"d1001\"}}";
897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919

    public static void main(String[] args) throws SQLException {
        final String url = "jdbc:TAOS://" + host + ":6030/?user=root&password=taosdata";
        try (Connection connection = DriverManager.getConnection(url)) {
            init(connection);

            SchemalessWriter writer = new SchemalessWriter(connection);
            writer.write(lineDemo, SchemalessProtocolType.LINE, SchemalessTimestampType.NANO_SECONDS);
            writer.write(telnetDemo, SchemalessProtocolType.TELNET, SchemalessTimestampType.MILLI_SECONDS);
            writer.write(jsonDemo, SchemalessProtocolType.JSON, SchemalessTimestampType.NOT_CONFIGURED);
        }
    }

    private static void init(Connection connection) throws SQLException {
        try (Statement stmt = connection.createStatement()) {
            stmt.executeUpdate("drop database if exists test_schemaless");
            stmt.executeUpdate("create database if not exists test_schemaless");
            stmt.executeUpdate("use test_schemaless");
        }
    }
}
```

920
</TabItem>
H
huolibo 已提交
921
<TabItem value="ws" label="WebSocket connection">
922 923 924 925 926 927 928 929 930 931

```java
public class SchemalessWsTest {
    private static final String host = "127.0.0.1";
    private static final String lineDemo = "st,t1=3i64,t2=4f64,t3=\"t3\" c1=3i64,c3=L\"passit\",c2=false,c4=4f64 1626006833639000000";
    private static final String telnetDemo = "stb0_0 1626006833 4 host=host0 interface=eth0";
    private static final String jsonDemo = "{\"metric\": \"meter_current\",\"timestamp\": 1626846400,\"value\": 10.3, \"tags\": {\"groupid\": 2, \"location\": \"California.SanFrancisco\", \"id\": \"d1001\"}}";

    public static void main(String[] args) throws SQLException {
        final String url = "jdbc:TAOS-RS://" + host + ":6041/?user=root&password=taosdata&batchfetch=true";
H
huolibo 已提交
932 933 934 935 936 937 938 939 940
        try(Connection connection = DriverManager.getConnection(url)){
            init(connection);

            try(SchemalessWriter writer = new SchemalessWriter(connection, "test_ws_schemaless")){
                writer.write(lineDemo, SchemalessProtocolType.LINE, SchemalessTimestampType.NANO_SECONDS);
                writer.write(telnetDemo, SchemalessProtocolType.TELNET, SchemalessTimestampType.MILLI_SECONDS);
                writer.write(jsonDemo, SchemalessProtocolType.JSON, SchemalessTimestampType.SECONDS);
            }
        }
941 942 943 944 945 946 947 948 949 950 951 952 953 954 955
    }

    private static void init(Connection connection) throws SQLException {
        try (Statement stmt = connection.createStatement()) {
            stmt.executeUpdate("drop database if exists test_ws_schemaless");
            stmt.executeUpdate("create database if not exists test_ws_schemaless keep 36500");
            stmt.executeUpdate("use test_ws_schemaless");
        }
    }
}
```

</TabItem>
</Tabs>

H
huolibo 已提交
956 957 958 959
### Schemaless with reqId

This reqId can be used to request link tracing.

H
huolibo 已提交
960
```java
H
huolibo 已提交
961 962 963
writer.write(lineDemo, SchemalessProtocolType.LINE, SchemalessTimestampType.NANO_SECONDS, 1L);
```

D
danielclow 已提交
964
### Data Subscription
965 966 967

The TDengine Java Connector supports subscription functionality with the following application API.

D
danielclow 已提交
968
#### Create a Topic
969 970

```java
D
danielclow 已提交
971 972 973
Connection connection = DriverManager.getConnection(url, properties);
Statement statement = connection.createStatement();
statement.executeUpdate("create topic if not exists topic_speed as select ts, speed from speed_table");
974 975 976 977
```

The three parameters of the `subscribe()` method have the following meanings.

D
danielclow 已提交
978 979 980 981
- topic_speed: the subscribed topic (name). This is the unique identifier of the subscription.
- sql: the query statement of the subscription which can only be a _select_ statement. Only the original data should be queried, and data can only be queried in temporal order..

The preceding example uses the SQL statement `select ts, speed from speed_table` and creates a subscription named `topic_speed`.
982

D
danielclow 已提交
983 984 985 986
#### Create a Consumer

```java
Properties config = new Properties();
H
huolibo 已提交
987
config.setProperty("bootstrap.servers", "localhost:6030");
D
danielclow 已提交
988 989 990 991 992 993 994
config.setProperty("enable.auto.commit", "true");
config.setProperty("group.id", "group1");
config.setProperty("value.deserializer", "com.taosdata.jdbc.tmq.ConsumerTest.ResultDeserializer");

TaosConsumer consumer = new TaosConsumer<>(config);
```

H
huolibo 已提交
995
- bootstrap.servers: `ip:port` where the TDengine server is located, or `ip:port` where the taosAdapter is located if WebSocket connection is used.
D
danielclow 已提交
996 997 998
- enable.auto.commit: Specifies whether to commit automatically.
- group.id: consumer: Specifies the group that the consumer is in.
- value.deserializer: To deserialize the results, you can inherit `com.taosdata.jdbc.tmq.ReferenceDeserializer` and specify the result set bean. You can also inherit `com.taosdata.jdbc.tmq.Deserializer` and perform custom deserialization based on the SQL result set.
999
- td.connect.type: Specifies the type connect with TDengine, `jni` or `WebSocket`. default is `jni`
1000 1001
- httpConnectTimeout: WebSocket connection timeout in milliseconds, the default value is 5000 ms. It only takes effect when using WebSocket type.
- messageWaitTimeout: socket timeout in milliseconds, the default value is 10000 ms. It only takes effect when using WebSocket type.
H
huolibo 已提交
1002
- httpPoolSize: Maximum number of concurrent requests on the a connection。It only takes effect when using WebSocket type.
D
danielclow 已提交
1003
- For more information, see [Consumer Parameters](../../../develop/tmq).
1004 1005 1006 1007 1008

#### Subscribe to consume data

```java
while(true) {
D
danielclow 已提交
1009
    ConsumerRecords<ResultBean> records = consumer.poll(Duration.ofMillis(100));
1010 1011 1012
        for (ConsumerRecord<ResultBean> record : records) {
            ResultBean bean = record.value();
            process(bean);
D
danielclow 已提交
1013
        }
1014 1015 1016
}
```

D
danielclow 已提交
1017
`poll` obtains one message each time it is run.
1018

H
huolibo 已提交
1019 1020
#### Assignment subscription Offset

H
huolibo 已提交
1021
```java
H
huolibo 已提交
1022
// get offset
H
huolibo 已提交
1023 1024 1025 1026 1027
long position(TopicPartition partition) throws SQLException;
Map<TopicPartition, Long> position(String topic) throws SQLException;
Map<TopicPartition, Long> beginningOffsets(String topic) throws SQLException;
Map<TopicPartition, Long> endOffsets(String topic) throws SQLException;

H
huolibo 已提交
1028
// Overrides the fetch offsets that the consumer will use on the next poll(timeout).
H
huolibo 已提交
1029 1030 1031
void seek(TopicPartition partition, long offset) throws SQLException;
```

H
huolibo 已提交
1032 1033
Example usage is as follows.

H
huolibo 已提交
1034
```java
H
huolibo 已提交
1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054
String topic = "offset_seek_test";
Map<TopicPartition, Long> offset = null;
try (TaosConsumer<ResultBean> consumer = new TaosConsumer<>(properties)) {
    consumer.subscribe(Collections.singletonList(topic));
    for (int i = 0; i < 10; i++) {
        if (i == 3) {
            // Saving consumption position
            offset = consumer.position(topic);
        }
        if (i == 5) {
            // reset consumption to the previously saved position
            for (Map.Entry<TopicPartition, Long> entry : offset.entrySet()) {
                consumer.seek(entry.getKey(), entry.getValue());
            }
        }
        ConsumerRecords<ResultBean> records = consumer.poll(Duration.ofMillis(500));
    }
}
```

1055 1056 1057
#### Close subscriptions

```java
D
danielclow 已提交
1058 1059 1060 1061
// Unsubscribe
consumer.unsubscribe();
// Close consumer
consumer.close()
1062 1063
```

D
danielclow 已提交
1064
For more information, see [Data Subscription](../../../develop/tmq).
1065

W
wade zhang 已提交
1066
#### Full Sample Code
1067

1068 1069 1070 1071 1072
<Tabs defaultValue="native">
<TabItem value="native" label="native connection">

In addition to the native connection, the Java Connector also supports subscribing via websocket.

1073
```java
D
danielclow 已提交
1074 1075 1076 1077 1078 1079 1080 1081
public abstract class ConsumerLoop {
    private final TaosConsumer<ResultBean> consumer;
    private final List<String> topics;
    private final AtomicBoolean shutdown;
    private final CountDownLatch shutdownLatch;

    public ConsumerLoop() throws SQLException {
        Properties config = new Properties();
H
huolibo 已提交
1082 1083 1084 1085 1086
        config.setProperty("td.connect.type", "jni");
        config.setProperty("bootstrap.servers", "localhost:6030");
        config.setProperty("td.connect.user", "root");
        config.setProperty("td.connect.pass", "taosdata");
        config.setProperty("auto.offset.reset", "earliest");
D
danielclow 已提交
1087 1088
        config.setProperty("msg.with.table.name", "true");
        config.setProperty("enable.auto.commit", "true");
H
huolibo 已提交
1089
        config.setProperty("auto.commit.interval.ms", "1000");
D
danielclow 已提交
1090
        config.setProperty("group.id", "group1");
H
huolibo 已提交
1091
        config.setProperty("client.id", "1");
D
danielclow 已提交
1092
        config.setProperty("value.deserializer", "com.taosdata.jdbc.tmq.ConsumerTest.ConsumerLoop$ResultDeserializer");
H
huolibo 已提交
1093
        config.setProperty("value.deserializer.encoding", "UTF-8");
D
danielclow 已提交
1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108

        this.consumer = new TaosConsumer<>(config);
        this.topics = Collections.singletonList("topic_speed");
        this.shutdown = new AtomicBoolean(false);
        this.shutdownLatch = new CountDownLatch(1);
    }

    public abstract void process(ResultBean result);

    public void pollData() throws SQLException {
        try {
            consumer.subscribe(topics);

            while (!shutdown.get()) {
                ConsumerRecords<ResultBean> records = consumer.poll(Duration.ofMillis(100));
1109 1110 1111
                for (ConsumerRecord<ResultBean> record : records) {
                    ResultBean bean = record.value();
                    process(bean);
D
danielclow 已提交
1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126
                }
            }
            consumer.unsubscribe();
        } finally {
            consumer.close();
            shutdownLatch.countDown();
        }
    }

    public void shutdown() throws InterruptedException {
        shutdown.set(true);
        shutdownLatch.await();
    }

    public static class ResultDeserializer extends ReferenceDeserializer<ResultBean> {
1127

D
danielclow 已提交
1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151
    }

    public static class ResultBean {
        private Timestamp ts;
        private int speed;

        public Timestamp getTs() {
            return ts;
        }

        public void setTs(Timestamp ts) {
            this.ts = ts;
        }

        public int getSpeed() {
            return speed;
        }

        public void setSpeed(int speed) {
            this.speed = speed;
        }
    }
}
```
1152

1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165
</TabItem>
<TabItem value="ws" label="WebSocket connection">

```java
public abstract class ConsumerLoop {
    private final TaosConsumer<ResultBean> consumer;
    private final List<String> topics;
    private final AtomicBoolean shutdown;
    private final CountDownLatch shutdownLatch;

    public ConsumerLoop() throws SQLException {
        Properties config = new Properties();
        config.setProperty("td.connect.type", "ws");
H
huolibo 已提交
1166 1167 1168 1169
        config.setProperty("bootstrap.servers", "localhost:6041");
        config.setProperty("td.connect.user", "root");
        config.setProperty("td.connect.pass", "taosdata");
        config.setProperty("auto.offset.reset", "earliest");
1170 1171
        config.setProperty("msg.with.table.name", "true");
        config.setProperty("enable.auto.commit", "true");
H
huolibo 已提交
1172
        config.setProperty("auto.commit.interval.ms", "1000");
1173
        config.setProperty("group.id", "group2");
H
huolibo 已提交
1174
        config.setProperty("client.id", "1");
1175
        config.setProperty("value.deserializer", "com.taosdata.jdbc.tmq.ConsumerTest.ConsumerLoop$ResultDeserializer");
H
huolibo 已提交
1176 1177
        config.setProperty("value.deserializer.encoding", "UTF-8");
        config.setProperty("experimental.snapshot.enable", "true");
1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192

        this.consumer = new TaosConsumer<>(config);
        this.topics = Collections.singletonList("topic_speed");
        this.shutdown = new AtomicBoolean(false);
        this.shutdownLatch = new CountDownLatch(1);
    }

    public abstract void process(ResultBean result);

    public void pollData() throws SQLException {
        try {
            consumer.subscribe(topics);

            while (!shutdown.get()) {
                ConsumerRecords<ResultBean> records = consumer.poll(Duration.ofMillis(100));
1193 1194 1195
                for (ConsumerRecord<ResultBean> record : records) {
                    ResultBean bean = record.value();
                    process(bean);
1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241
                }
            }
            consumer.unsubscribe();
        } finally {
            consumer.close();
            shutdownLatch.countDown();
        }
    }

    public void shutdown() throws InterruptedException {
        shutdown.set(true);
        shutdownLatch.await();
    }

    public static class ResultDeserializer extends ReferenceDeserializer<ResultBean> {

    }

    public static class ResultBean {
        private Timestamp ts;
        private int speed;

        public Timestamp getTs() {
            return ts;
        }

        public void setTs(Timestamp ts) {
            this.ts = ts;
        }

        public int getSpeed() {
            return speed;
        }

        public void setSpeed(int speed) {
            this.speed = speed;
        }
    }
}
```

</TabItem>
</Tabs>

> **Note**: The value of value.deserializer should be adjusted based on the package path of the test environment.

1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270
### Use with connection pool

#### HikariCP

Example usage is as follows.

```java
 public static void main(String[] args) throws SQLException {
    HikariConfig config = new HikariConfig();
    // jdbc properties
    config.setJdbcUrl("jdbc:TAOS://127.0.0.1:6030/log");
    config.setUsername("root");
    config.setPassword("taosdata");
    // connection pool configurations
    config.setMinimumIdle(10);           //minimum number of idle connection
    config.setMaximumPoolSize(10);      //maximum number of connection in the pool
    config.setConnectionTimeout(30000); //maximum wait milliseconds for get connection from pool
    config.setMaxLifetime(0);       // maximum life time for each connection
    config.setIdleTimeout(0);       // max idle time for recycle idle connection
    config.setConnectionTestQuery("select server_status()"); //validation query

    HikariDataSource ds = new HikariDataSource(config); //create datasource

    Connection  connection = ds.getConnection(); // get connection
    Statement statement = connection.createStatement(); // get statement

    //query or insert
    // ...

sangshuduo's avatar
sangshuduo 已提交
1271
    connection.close(); // put back to connection pool
1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302
}
```

> getConnection(), you need to call the close() method after you finish using it. It doesn't close the connection. It just puts it back into the connection pool.
> For more questions about using HikariCP, please see the [official instructions](https://github.com/brettwooldridge/HikariCP).

#### Druid

Example usage is as follows.

```java
public static void main(String[] args) throws Exception {

    DruidDataSource dataSource = new DruidDataSource();
    // jdbc properties
    dataSource.setDriverClassName("com.taosdata.jdbc.TSDBDriver");
    dataSource.setUrl(url);
    dataSource.setUsername("root");
    dataSource.setPassword("taosdata");
    // pool configurations
    dataSource.setInitialSize(10);
    dataSource.setMinIdle(10);
    dataSource.setMaxActive(10);
    dataSource.setMaxWait(30000);
    dataSource.setValidationQuery("select server_status()");

    Connection  connection = dataSource.getConnection(); // get connection
    Statement statement = connection.createStatement(); // get statement
    //query or insert
    // ...

sangshuduo's avatar
sangshuduo 已提交
1303
    connection.close(); // put back to connection pool
1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317
}
```

> For more questions about using druid, please see [Official Instructions](https://github.com/alibaba/druid).

### More sample programs

The source code of the sample application is under `TDengine/examples/JDBC`:

- JDBCDemo: JDBC sample source code.
- JDBCConnectorChecker: JDBC installation checker source and jar package.
- connectionPools: using taos-jdbcdriver in connection pools such as HikariCP, Druid, dbcp, c3p0, etc.
- SpringJdbcTemplate: using taos-jdbcdriver in Spring JdbcTemplate.
- mybatisplus-demo: using taos-jdbcdriver in Springboot + Mybatis.
H
huolibo 已提交
1318
- consumer-demo: consumer TDengine data example, the consumption rate can be controlled by parameters.
1319

D
danielclow 已提交
1320
[JDBC example](https://github.com/taosdata/TDengine/tree/3.0/examples/JDBC)
1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333

## Frequently Asked Questions

1. Why is there no performance improvement when using Statement's `addBatch()` and `executeBatch()` to perform `batch data writing/update`?

   **Cause**: In TDengine's JDBC implementation, SQL statements submitted by `addBatch()` method are executed sequentially in the order they are added, which does not reduce the number of interactions with the server and does not bring performance improvement.

   **Solution**: 1. splice multiple values in a single insert statement; 2. use multi-threaded concurrent insertion; 3. use parameter-bound writing

2. java.lang.UnsatisfiedLinkError: no taos in java.library.path

   **Cause**: The program did not find the dependent native library `taos`.

wafwerar's avatar
wafwerar 已提交
1334
   **Solution**: On Windows you can copy `C:\TDengine\driver\taos.dll` to the `C:\Windows\System32` directory, on Linux the following soft link will be created `ln -s /usr/local/taos/driver/libtaos.so.x.x.x.x /usr/lib/libtaos.so` will work, on macOS the lib soft link will be `/usr/local/lib/libtaos.dylib`.
1335

D
danielclow 已提交
1336
3. java.lang.UnsatisfiedLinkError: taos.dll Can't load AMD 64 bit on a IA 32-bit platform
1337 1338 1339

   **Cause**: Currently, TDengine only supports 64-bit JDK.

H
huolibo 已提交
1340 1341 1342 1343
   **Solution**: Reinstall the 64-bit JDK.

4. java.lang.NoSuchMethodError: setByteArray

H
huolibo 已提交
1344
   **Cause**: taos-jbdcdriver 3.\* only supports TDengine 3.0 and later.
H
huolibo 已提交
1345

H
huolibo 已提交
1346
   **Solution**: Use taos-jdbcdriver 2.\* with your TDengine 2.\* deployment.
1347

1348 1349
5. java.lang.NoSuchMethodError: java.nio.ByteBuffer.position(I)Ljava/nio/ByteBuffer; ... taos-jdbcdriver-3.0.1.jar

1350
**Cause**: taos-jdbcdriver 3.0.1 is compiled on JDK 11.
1351

1352
**Solution**: Use taos-jdbcdriver 3.0.2.
1353

D
danielclow 已提交
1354
For additional troubleshooting, see [FAQ](../../../train-faq/faq).
1355 1356 1357 1358

## API Reference

[taos-jdbcdriver doc](https://docs.taosdata.com/api/taos-jdbcdriver)
H
huolibo 已提交
1359 1360 1361 1362

```

```