background
In the previous experience, taos client, RESTful Connector and JDBC-JNI are used. This time, we experience an example closer to the actual application scenario: the integration of TDengine with SpringBoot, MyBatis, MyBatisPlus, etc.
Officials have brought their own examples, https://github.com/taosdata/TDengine/tree/develop/tests/examples/JDBC ； These examples also include: / usr/local/taos/examples/JDBC or C:\TDengine\examples\JDBC in the installed client directory. I ran all these examples, and they can generally run, but there are some problems or bug s, especially some test cases are not rigorous. I put the modified example on my GitHub, and the modified place and description are in readme MD.
| No. | Name | Describe |
|---|---|---|
| 1 | JDBCDemo | Example codes for JDBC-JNI, JDBC-RESTful, Subscribe |
| 2 | connectionPools | Example codes for HikariCP, Druid, dbcp, c3p0 connection pools |
| 3 | SpringJdbcTemplate | Example codes for spring jdbcTemplate |
| 4 | mybatisplus-demo | Example codes for mybatis |
| 5 | springbootdemo | Example codes for springboot |
| 6 | taosdemo | This is an internal tool for testing Our JDBC-JNI, JDBC-RESTful, RESTful interfaces |
SpringBoot+TDengine+MyBatis
The sample content is basically database building, table building and data insertion. See the code for details. The following only lists the problems encountered and solutions.
- The dependent version is compatible with the server
<dependency>
<groupId>com.taosdata.jdbc</groupId>
<artifactId>taos-jdbcdriver</artifactId>
<version>2.0.28</version>
</dependency>
Amend to read:
<dependency>
<groupId>com.taosdata.jdbc</groupId>
<artifactId>taos-jdbcdriver</artifactId>
<version>2.0.28</version>
</dependency>
If the dependent version is not modified, an error will be reported if it is inconsistent with the server version:
Cause: java.sql. SQLException: TDengine ERROR (216): Syntax error in SQL; uncategorized SQLException; SQL state []; error code [534]; TDengine ERROR (216): Syntax error in SQL; nested exception is java.sql. SQLException: TDengine ERROR (216): Syntax error in SQL] with root cause
For version compatibility instructions, see the official table (my server TDengine version is 2.1.2.0):
- TDengine encountered a bug in batch inserting data
There are three ways to write batch insert statements here (only one ordinary table is selected for batch insert, and the subsequent implementation of dynamic batch insert data into multiple ordinary tables). However, one way of writing is correct, but in fact, it is an error. After debugging, it should be a bug..
The first method uses the timestamp in the back-end sql statement as the primary key and now + step size
<insert id="insertBatch" parameterType="java.util.List">
insert into demo.t0 (ts, temperature, humidity) values
<foreach separator=" " collection="list" item="weather" index="index" >
<!-- Refer to the official document of Taosi data: https://www.taosdata.com/cn/documentation/taos-sql#data-type
The time unit after the number can be u(Microsecond),a(millisecond),s(second),m(branch),h(hour),d(day),w(week)
At the specified frequency reduction operation( down sampling)Time window for( interval)Time units can also be used n(Natural month) and y(Natural year). -->
(now + #{index}a, #{weather.temperature}, #{weather.humidity})
</foreach>
</insert>
Note: (now + #{index}a, #{weather.temperature}, #{weather.humidity}) this way of writing made me wonder if I accidentally typed the wrong letter and typed an extra letter A..
The second method: use the timestamp in the front-end parameter list as the primary key, and the table name is out of circulation. This method reports an error, which should be a bug..
<!--In this way, the timestamp transmitted from the front end is directly used to report an error: uncategorized SQLException; SQL state []; error code [534]; TDengine ERROR (216): Syntax error in SQL; -->
<insert id="insertBatch" parameterType="java.util.List">
insert into demo.t0 (ts, temperature, humidity) values
<foreach separator=" " collection="list" item="weather" index="index">
(#{weather.ts}, #{weather.temperature}, #{weather.humidity})
</foreach>
</insert>
The first method is OK, the second method is not. It makes people doubt life.. Then I followed the debugging.
Results: during batch insertion, the timestamp of the second record was not automatically quoted, resulting in an error..
The third method: use the timestamp in the front-end parameter transfer list as the primary key, the table name is in the loop body, and the batch insertion is successful~
<insert id="insertBatch" parameterType="java.util.List">
insert into
<foreach separator=" " collection="list" item="weather" index="index">
demo.t0 values
(#{weather.ts}, #{weather.temperature}, #{weather.humidity})
</foreach>
</insert>
Attachment: parameter transfer method of batch data in Postman (pass an array type parameter to the back end):
curl --location --request POST 'localhost:8080/weather/batch' \
--header 'Content-Type: application/json' \
--data-raw '[
{
"ts": 1626324781093,
"temperature": 30.00000,
"humidity": 99.00000
},
{
"ts": 1626324981322,
"temperature": 9.50609,
"humidity": 10.00000
}
]'
# Or (change the time stamp format of the transfer):
curl --location --request POST 'localhost:8080/weather/batch' \
--header 'Content-Type: application/json' \
--data-raw '[
{
"ts": "2021-07-19 14:53:01.093",
"temperature": 30.00000,
"humidity": 99.00000
},
{
"ts": "2021-07-19 14:53:01.200",
"temperature": 9.50609,
"humidity": 10.00000
}
]'
Note: in the version I use, it is no longer allowed to directly insert data into the super table, but the readme provided with the sample code MD still says this: insert into test Weather (TS, temperature, humidity) values, in short, the document is not very rigorous..
SpringBoot+TDengine+MyBatisPlus
- Dependency modification
Similarly, do not post the source code, the following only lists the problems encountered and solutions.
-
h2 dependency removed
-
druid dependency replaced with starter
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
- Upgraded Taos jdbc driver dependent version
<dependency>
<groupId>com.taosdata.jdbc</groupId>
<artifactId>taos-jdbcdriver</artifactId>
<version>2.0.30</version>
</dependency>
- Configuration modification
user: root
Replace with:
username: root
However, it is strange that whether it is changed or not will not affect the result..
- Code modification
When generating records in a loop, the timestamp should be incremented instead of using the same timestamp for all records, which will lead to overwriting or discarding (depending on the configuration of the database).
TemperatureMapperTest.java
// insert into table
int affectRows = 0;
// insert 10 tables
for (int i = 0; i < 10; i++) {
// each table insert 5 rows
for (int j = 0; j < 5; j++) {
Temperature one = new Temperature();
// If you insert a single record, you can write it like this, but this is in the loop body. The time stamp in the TDengine table is the primary key, and the same time stamp will be overwritten, resulting in the number of records finally inserted being inconsistent with the expectation
one.setTs(new Timestamp(1605024000000l));
one.setTemperature(random.nextFloat() * 50);
one.setLocation("Wangjing");
one.setTbIndex(i);
affectRows += mapper.insertOne(one);
}
}
Assert.assertEquals(50, affectRows);
Change to
// insert into table
int affectRows = 0;
long ts = System.currentTimeMillis();
// insert 10 tables
for (int i = 0; i < 10; i++) {
// each table insert 5 rows
for (int j = 0; j < 5; j++) {
Temperature one = new Temperature();
// If you insert a single record, you can write it like this, but this is in the loop body. The time stamp in the TDengine table is the primary key, and the same time stamp will be overwritten, resulting in the number of records finally inserted being inconsistent with the expectation
// one.setTs(new Timestamp(1605024000000l));
Timestamp timestamp = new Timestamp(ts + j);
one.setTs(timestamp);
one.setTemperature(random.nextFloat() * 50);
one.setLocation("Wangjing");
one.setTbIndex(i);
affectRows += mapper.insertOne(one);
}
}
Assert.assertEquals(50, affectRows);
Source Code
Reference
If you have any questions or any bugs are found, please feel free to contact me.
Your comments and suggestions are welcome!