Skip to main content

Vert.x Common SQL interface

通用SQL接口用于与Vert.x SQL服务进行交互.

您可以通过所使用的特定SQL服务(例如JDBC / MySQL / PostgreSQL)的服务接口获得与数据库的连接.

要使用此项目,请将以下依赖项添加到构建描述符的" dependencies"部分:

  • Maven(在您的pom.xml ):

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

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

Simple SQL Operations

有时,您将需要运行单个SQL操作,例如:单行选择或对一组行的更新,这些操作不需要成为事务的一部分,也不需要依赖于上一个或下一个操作.

对于这些情况,客户端提供了无样板的API SQLOperations . 该界面将为您执行以下步骤:

  1. 从连接池获取连接

  2. 执行你的动作

  3. 关闭并将连接返回到连接池

USERS表加载用户的示例可能是:

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)
  }
})

您可以通过一个简单的" shot"方法调用来执行以下操作:

有关这些API的更多详细信息,请参考SQLOperations接口.

The SQL Connection

与数据库的连接由SQLConnection表示.

Auto-commit

获取连接时,自动提交设置为true . 这意味着您执行的每个操作将在其自己的事务中有效执行.

如果希望在单个事务中执行多个操作,则应使用setAutoCommit将auto commit设置为false.

操作完成后,将调用处理程序:

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

Executing queries

要执行查询,请使用query

查询字符串是原始SQL,无需更改实际数据库即可通过.

The handler will be called with the results, represented by ResultSet when the query has been run.

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 ,而实际结果可用于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对象实例的列表进行检索-这可以为您提供更简单的API,但请注意,SQL结果可以包含重复的列名-如果是这种情况,则应改用getResults .

这是将结果作为Json对象实例进行迭代的示例:

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")

}

Prepared statement queries

要执行准备好的语句查询,可以使用queryWithParams .

这将执行查询,该查询包含参数占位符以及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!
  }
})

Executing INSERT, UPDATE or 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!
  }
})

Prepared statement updates

要执行准备好的语句更新,可以使用updateWithParams .

这将进行更新,其中包含参数占位符以及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!

  }
})

Callable statements

要执行可调用语句(SQL函数或SQL过程),可以使用callWithParams .

这需要使用标准JDBC格式的调用语句{ call func_proc_name() } ,任选地包括参数占位例如: { call func_proc_name(?, ?) }一个JsonArray包含的参数值和最后一个JsonArray包含输出类型例如: [null, 'VARCHAR'] .

请注意,输出类型的索引与params数组一样重要. 如果返回值是第二个参数,则输出数组必须包含一个空值作为第一个元素.

一个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!
  }
})

在使用Procedures时,仍然通过其参数从过程中返回值,如果不返回任何内容,则用法如下:

// 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!
  }
})

请注意,参数的索引与?的索引匹配? 并且输出参数应该是描述您要接收的类型的字符串.

为避免歧义,实现应遵循以下规则:

  • IN数组中的占位符NOT NULL ,它将被采用

  • IN值为NULL时,将对OUT进行检查.当OUT值不为null时,它将被注册为输出参数.当OUT也为null时,则期望IN值为NULL值.

已注册的OUT参数将以数组形式出现在output属性下的结果集中.

Batch operations

SQL通用接口还定义了如何执行批处理操作. 批处理操作分为3种:

批处理语句将执行sql语句列表,例如:

// 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!
  }
})

Executing other operations

要执行任何其他数据库操作,例如CREATE TABLE ,可以使用execute .

字符串将直接传递而不会更改实际数据库. 操作完成后调用处理程序

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!
  }
})

Multiple ResultSet responses

在某些情况下,您的查询可能返回多个结果集,在这种情况下,为了保留兼容性,当返回的结果集对象转换为纯json时,下一个结果集将链接到属性next下的当前结果集. 可以像这样简单地遍历所有结果集:

// do something with the result set...

// next step
rs = rs.next

Streaming

在处理大型数据集时,建议不要使用刚刚描述的API,而应使用流数据,因为这样可以避免将整个响应膨胀到内存和JSON中,并且仅逐行处理数据,例如:

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

您仍然可以完全控制流何时暂停,恢复和结束. 对于查询返回多个结果集的情况,您应使用结果集结束事件来获取下一个事件(如果有). 如果有更多数据,则流处理程序将接收新数据,否则将调用结束处理程序.

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...
    })
  }
})

Using transactions

要使用事务,请首先使用setAutoCommit将auto-commit设置为false.

然后,您执行事务操作,并在想要提交或回滚时使用commitrollback .

提交/回滚完成后,将调用处理程序,并且下一个事务将自动启动.

// Do stuff with connection - updates etc

// Now commit

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

Closing connections

完成连接后,应使用close将其返回到池中.

by  ICOPY.SITE