Skip to main content

参数绑定写入

通过参数绑定方式写入数据时,能避免SQL语法解析的资源消耗,从而显著提升写入性能。参数绑定能提高写入效率的原因主要有以下几点:

  • 减少解析时间:通过参数绑定,SQL 语句的结构在第一次执行时就已经确定,后续的执行只需要替换参数值,这样可以避免每次执行时都进行语法解析,从而减少解析时间。
  • 预编译:当使用参数绑定时,SQL 语句可以被预编译并缓存,后续使用不同的参数值执行时,可以直接使用预编译的版本,提高执行效率。
  • 减少网络开销:参数绑定还可以减少发送到数据库的数据量,因为只需要发送参数值而不是完整的 SQL 语句,特别是在执行大量相似的插入或更新操作时,这种差异尤为明显。

Tips: 数据写入推荐使用参数绑定方式

下面我们继续以智能电表为例,展示各语言连接器使用参数绑定高效写入的功能:

  1. 准备一个参数化的 SQL 插入语句,用于向超级表 meters 中插入数据。这个语句允许动态地指定子表名、标签和列值。
  2. 循环生成多个子表及其对应的数据行。对于每个子表:
    • 设置子表的名称和标签值(分组 ID 和位置)。
    • 生成多行数据,每行包括一个时间戳、随机生成的电流、电压和相位值。
    • 执行批量插入操作,将这些数据行插入到对应的子表中。
  3. 最后打印实际插入表中的行数。

Websocket 连接

public class WSParameterBindingBasicDemo {

// modify host to your own
private static final String host = "127.0.0.1";
private static final Random random = new Random(System.currentTimeMillis());
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";
try (Connection conn = DriverManager.getConnection(jdbcUrl, "root", "taosdata")) {
init(conn);

String sql = "INSERT INTO ? USING power.meters TAGS(?,?) VALUES (?,?,?,?)";

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

for (int i = 1; i <= numOfSubTable; i++) {
// set table name
pstmt.setTableName("d_bind_" + i);

// set tags
pstmt.setTagInt(0, i);
pstmt.setTagString(1, "location_" + i);

// 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() * 30);
pstmt.setInt(3, random.nextInt(300));
pstmt.setFloat(4, random.nextFloat());
pstmt.addBatch();
}
int [] exeResult = pstmt.executeBatch();
// you can check exeResult here
System.out.println("Successfully inserted " + exeResult.length + " rows to power.meters.");
}
}
} catch (Exception ex) {
// please refer to the JDBC specifications for detailed exceptions info
System.out.printf("Failed to insert to table meters using stmt, %sErrMessage: %s%n",
ex instanceof SQLException ? "ErrCode: " + ((SQLException) ex).getErrorCode() + ", " : "",
ex.getMessage());
// Print stack trace for context in examples. Use logging in production.
ex.printStackTrace();
throw ex;
}
}

private static void init(Connection conn) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.execute("CREATE DATABASE IF NOT EXISTS power");
stmt.execute("USE power");
stmt.execute("CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId INT, location BINARY(24))");
}
}
}

查看源码

这是一个更详细的参数绑定示例

原生连接

public class ParameterBindingBasicDemo {

// modify host to your own
private static final String host = "127.0.0.1";
private static final Random random = new Random(System.currentTimeMillis());
private static final int numOfSubTable = 10, numOfRow = 10;

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

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

init(conn);

String sql = "INSERT INTO ? USING power.meters TAGS(?,?) VALUES (?,?,?,?)";

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

for (int i = 1; i <= numOfSubTable; i++) {
// set table name
pstmt.setTableName("d_bind_" + i);

// set tags
pstmt.setTagInt(0, i);
pstmt.setTagString(1, "location_" + i);

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

// set column current
ArrayList<Float> currentList = new ArrayList<>();
for (int j = 0; j < numOfRow; j++)
currentList.add(random.nextFloat() * 30);
pstmt.setFloat(1, currentList);

// set column voltage
ArrayList<Integer> voltageList = new ArrayList<>();
for (int j = 0; j < numOfRow; j++)
voltageList.add(random.nextInt(300));
pstmt.setInt(2, voltageList);

// set column phase
ArrayList<Float> phaseList = new ArrayList<>();
for (int j = 0; j < numOfRow; j++)
phaseList.add(random.nextFloat());
pstmt.setFloat(3, phaseList);
// add column
pstmt.columnDataAddBatch();
}
// execute column
pstmt.columnDataExecuteBatch();
// you can check exeResult here
System.out.println("Successfully inserted " + (numOfSubTable * numOfRow) + " rows to power.meters.");
}
} catch (Exception ex) {
// please refer to the JDBC specifications for detailed exceptions info
System.out.printf("Failed to insert to table meters using stmt, %sErrMessage: %s%n",
ex instanceof SQLException ? "ErrCode: " + ((SQLException) ex).getErrorCode() + ", " : "",
ex.getMessage());
// Print stack trace for context in examples. Use logging in production.
ex.printStackTrace();
throw ex;
}
}

private static void init(Connection conn) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.execute("CREATE DATABASE IF NOT EXISTS power");
stmt.execute("USE power");
stmt.execute("CREATE STABLE IF NOT EXISTS meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId INT, location BINARY(24))");
}
}
}

查看源码

这是一个更详细的参数绑定示例