Skip to main content
编辑本页

Vert.x Common SQL interface

Vert.x Common SQL Interface组件定义了 Vert.x 与各种 SQL 服务交互的方法。

您必须通过使用特定的 SQL 服务(例如 JDBC/MySQL/PostgreSQL)的接口来获取数据库连接。

要使用此组件,需要添加下列依赖:

  • Maven (在 `pom.xml`文件中):

<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-sql-common</artifactId>
 <version>3.6.2</version>
</dependency>
  • Gradle (在 build.gradle 文件中):

compile 'io.vertx:vertx-sql-common:3.6.2'

Simple SQL Operations

There are times when you will want to run a single SQL operation, e.g.: a single select of a row, or a update to a set of rows which do not require to be part of a transaction or have dependencies on the previous or next operation.

For these cases, clients provide a boilerplate-less API SQLOperations. This interface will perform the following steps for you:

  1. acquire a connection from the connection pool

  2. perform your action

  3. close and return the connection to the connection pool

An example where users get loaded from the USERS table could be:

client.query("SELECT * FROM USERS", { ar ->
  if (ar.succeeded()) {
    if (ar.succeeded()) {
      var result = ar.result()
    } else {
      // Failed!
    }
    // NOTE that you don't need to worry about
    // the connection management (e.g.: close)
  }
})

You can perform the following operations as a simple one "shot" method call:

For further details on these API please refer to the SQLOperations interface.

SQL 连接

我们用 SQLConnection 接口来表示数据库连接(译者注:此接口中包含各种基本的操作方法)。

自动提交

当您获取的数据库连接,其自动提交选项(auto commit)默认设置为 true。这意味着您的每个操作都将在单独的事务中有效执行。

如果您希望在同一个事务中执行多个操作,就应该使用 setAutoCommit 方法设置自动提交为`false`。

当操作完成时,回调方法将会被执行:

connection.setAutoCommit(false, { res ->
  if (res.succeeded()) {
    // OK!
  } else {
    // Failed!
  }
})

执行查询

您可以使用 query 方法执行查询操作。

查询语句(原生SQL)传给数据库时,不会经过任何修改。

当查询结束时,将执行回调方法处理结果。查询结果包装在 ResultSet 中。

connection.query("SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE", { res ->
  if (res.succeeded()) {
    // Get the result set
    var resultSet = res.result()
  } else {
    // Failed!
  }
})

ResultSet 类代表查询结果。

您可以通过 getColumnNames 方法获得查询结果的列名 List 集合,实际的结果集可以通过 getResults 方法获得。

结果集被包装成了一组 JsonArray 列表,其中的每个元素代表一行结果。

var columnNames = resultSet.columnNames

var results = resultSet.results

for (row in results) {

  var id = row.getString(0)
  var fName = row.getString(1)
  var lName = row.getString(2)
  var shoeSize = row.getInteger(3)

}

您还可以使用 getRows - 方法来获得被包装成了 JSON 对象列表(List<JsonObject> `)的结果集,这样能让 API 的操作更简单些。 但要注意的是,查询出的结果集中可能会出现重复的列名。 若遇到这样的情况,您应该选择使用 `getResults 方法。

下面是将结果集作为 JsonObject 进行迭代的例子:

var rows = resultSet.rows

for (row in rows) {

  var id = row.getString("ID")
  var fName = row.getString("FNAME")
  var lName = row.getString("LNAME")
  var shoeSize = row.getInteger("SHOE_SIZE")

}

预编译查询

您可以使用 queryWithParams 方法执行预编译查询(prepared statement queries)。

此方法接受含参数占位符的SQL查询语句以及 JsonArray 对象(用于传递参数)或参数值。

var query = "SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE WHERE LNAME=? AND SHOE_SIZE > ?"
var params = json {
  array("Fox", 9)
}

connection.queryWithParams(query, params, { res ->

  if (res.succeeded()) {
    // Get the result set
    var resultSet = res.result()
  } else {
    // Failed!
  }
})

执行 INSERT/UPDATE/DELETE 语句

您可以使用 update 方法来执行更新数据库的操作(包括增、删、改)。

更新语句(原生SQL)传给数据库时,不会经过任何处理。

当更新结束时,将执行回调方法处理结果。更新结果包装在 UpdateResult 对象中。

您可以通过 getUpdated 方法获得更新的数据条数,并且如果更新操作有生成主键,可以通过 getKeys 方法获得对应的主键。

connection.update("INSERT INTO PEOPLE VALUES (null, 'john', 'smith', 9)", { res ->
  if (res.succeeded()) {

    var result = res.result()
    println("Updated no. of rows: ${result.updated}")
    println("Generated keys: ${result.keys}")

  } else {
    // Failed!
  }
})

预编译更新

您可以使用 updateWithParams 方法来执行预编译更新(prepared statement updates)。

此方法接受含参数占位符的SQL更新语句以及 JsonArray 对象(用于传递参数)或参数值。

var update = "UPDATE PEOPLE SET SHOE_SIZE = 10 WHERE LNAME=?"
var params = json {
  array("Fox")
}

connection.updateWithParams(update, params, { res ->

  if (res.succeeded()) {

    var updateResult = res.result()

    println("No. of rows updated: ${updateResult.updated}")

  } else {

    // Failed!

  }
})

可调用语句

您可以使用 callWithParams 方法来执行可调用语句(callable statements),例如 SQL 函数或者存储过程。

可调用语句。可以使用标准 JDBC 格式 { call func_proc_name() }, 也可以选择使用占位符传参数的形式,例如: { call func_proc_name(?, ?) }, 输入参数集(params), JsonArray 类型, 包含输出类型的输出结果集(output), JsonArray 类型,例如:[null, 'VARCHAR'], 对应的回调函数(resultHandler

请注意,输出结果集的 JsonArray 的下标和输入参数的 JsonArray 同样重要。 如果第二个参数代表输出结果集,那么应该设置结果集的 JsonArray 的第一个元素为 null。

有些 SQL 函数只使用 return 关键字返回输出结果集,这时可以这样调用:

// Assume that there is a SQL function like this:
//
// create function one_hour_ago() returns timestamp
//    return now() - 1 hour;

// note that you do not need to declare the output for functions
var func = "{ call one_hour_ago() }"

connection.call(func, { res ->

  if (res.succeeded()) {
    var result = res.result()
  } else {
    // Failed!
  }
})

但是当您使用存储过程时,还是需要使用它的参数来返回结果集。如果一个存储过程没有返回值的话,可以像下面这样调用:

// Assume that there is a SQL procedure like this:
//
// create procedure new_customer(firstname varchar(50), lastname varchar(50))
//   modifies sql data
//   insert into customers values (default, firstname, lastname, current_timestamp);

var func = "{ call new_customer(?, ?) }"

connection.callWithParams(func, json {
  array("John", "Doe")
}, null, { res ->

  if (res.succeeded()) {
    // Success!
  } else {
    // Failed!
  }
})

但是如果存储过程有返回值的话,需要像下面这样调用:

// Assume that there is a SQL procedure like this:
//
// create procedure customer_lastname(IN firstname varchar(50), OUT lastname varchar(50))
//   modifies sql data
//   select lastname into lastname from customers where firstname = firstname;

var func = "{ call customer_lastname(?, ?) }"

connection.callWithParams(func, json {
  array("John")
}, json {
  array(null, "VARCHAR")
}, { res ->

  if (res.succeeded()) {
    var result = res.result()
  } else {
    // Failed!
  }
})

请注意:输入输出参数的下标必须匹配 ? 的下标,并且输出结果集元素的值必须是结果集类型的字符串表示。

为避免歧义,实现类需要遵循以下规则(译者注:可参考 Vert.x JDBC Client 的实现源码 [JDBCStatementHelper.fillStatement(statement, in, out)](https://github.com/vert-x3/vertx-jdbc-client/blob/master/src/main/java/io/vertx/ext/jdbc/impl/actions/JDBCStatementHelper.java#L97)):

  • IN 参数的元素是 NOT NULL 时,此元素将被注册为输入参数

  • IN 参数的元素是 null 时,将进一步去检查 OUT 参数的元素值,再做判断 若当 IN 参数的元素是 null,且 OUT 参数的元素值不是 null 时,OUT 参数的元素值将被注册为输出参数 若当 IN 参数的元素是 null,且 OUT 参数的元素值也是 null 时, IN 参数的元素将被当作 NULL 值传入存储过程

注册为 OUT 的参数,设置成了 ResultSetoutput 属性。

批量操作

Vert.x SQL 公共接口定义了3种批量操作的方法:

批量操作能执行一组 SQL 语句(List 类型),例如:

// Batch values
var batch = mutableListOf<Any?>()
batch.add("INSERT INTO emp (NAME) VALUES ('JOE')")
batch.add("INSERT INTO emp (NAME) VALUES ('JANE')")

connection.batch(batch, { res ->
  if (res.succeeded()) {
    var result = res.result()
  } else {
    // Failed!
  }
})

预编译或者调用语句将会根据参数列表,来重复使用 SQL 语句,例如:

// Batch values
var batch = mutableListOf<Any?>()
batch.add(json {
  array("joe")
})
batch.add(json {
  array("jane")
})

connection.batchWithParams("INSERT INTO emp (name) VALUES (?)", batch, { res ->
  if (res.succeeded()) {
    var result = res.result()
  } else {
    // Failed!
  }
})

执行其他操作

若需要执行其他数据库操作,例如您可以使用 execute 方法来执行 CREATE TABLE 语句。

SQL语句传给数据库时,不会经过任何处理。操作结束时将调用回调方法。

var sql = "CREATE TABLE PEOPLE (ID int generated by default as identity (start with 1 increment by 1) not null,FNAME varchar(255), LNAME varchar(255), SHOE_SIZE int);"

connection.execute(sql, { execute ->
  if (execute.succeeded()) {
    println("Table created !")
  } else {
    // Failed!
  }
})

返回多个结果集

某些情况下,您的查询语句可能返回多个结果集 ResultSet, 此时,返回的结果集会被转成纯 JSON,并且为了保持稳定性,下一个 ResultSet 被作为当前 ResultSetnext 属性链接着。一种简单的遍历所有结果集的方式如下:

// do something with the result set...

// next step
rs = rs.next

Streaming

在处理大数据结果集时,不建议使用上面提到的API,而是使用数据流(stream data)的方式。因为它能够避免把所有的返回值加载到内存中,而且得到的 JSON 格式的数据也能够一行行的处理,例如:

connection.queryStream("SELECT * FROM large_table", { stream ->
  if (stream.succeeded()) {
    stream.result().handler({ row ->
      // do something with the row...
    })
  }
})

您还可以控制 Stream 何时停止,何时恢复,何时结束。对于查询返回多个结果集的情况,您应该使用 ended event 来获得下一个结果集。如果有,Stream 将会得到新的结果集,若没有,将会调用结束方法。

connection.queryStream("SELECT * FROM large_table; SELECT * FROM other_table", { stream ->
  if (stream.succeeded()) {
    var sqlRowStream = stream.result()

    sqlRowStream.resultSetClosedHandler({ v ->
      // will ask to restart the stream with the new result set if any
      sqlRowStream.moreResults()
    }).handler({ row ->
      // do something with the row...
    }).endHandler({ v ->
      // no more data available...
    })
  }
})

使用事务

要使用事务,首先要用 setAutoCommit 方法设置 auto-commit 为 false

然后您就可以执行在同一个事务中的操作,在需要提交事务时,调用 commit 方法;在需要回滚时,调用 rollback 方法。

一旦 commit/rollback 方法执行结束,将会调用回调方法。然后下一个事务也将自动开始。

// Do stuff with connection - updates etc

// Now commit

connection.commit({ res ->
  if (res.succeeded()) {
    // Committed OK!
  } else {
    // Failed!
  }
})

关闭连接

您在用完连接后,必须使用 close 方法把连接返回给连接池。