通用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'
有时,您将需要运行单个SQL操作,例如:单行选择或对一组行的更新,这些操作不需要成为事务的一部分,也不需要依赖于上一个或下一个操作.
对于这些情况,客户端提供了无样板的API SQLOperations
. 该界面将为您执行以下步骤:
从连接池获取连接
执行你的动作
关闭并将连接返回到连接池
从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
接口.
与数据库的连接由SQLConnection
表示.
获取连接时,自动提交设置为true
. 这意味着您执行的每个操作将在其自己的事务中有效执行.
如果希望在单个事务中执行多个操作,则应使用setAutoCommit
将auto commit设置为false.
操作完成后,将调用处理程序:
connection.setAutoCommit(false, { res ->
if (res.succeeded()) {
// OK!
} else {
// Failed!
}
})
要执行查询,请使用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")
}
要执行准备好的语句查询,可以使用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!
}
})
要执行更新数据库的操作,请使用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
.
这将进行更新,其中包含参数占位符以及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!
}
})
要执行可调用语句(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属性下的结果集中.
SQL通用接口还定义了如何执行批处理操作. 批处理操作分为3种:
批处理语句batch
批处理准备好的语句batchWithParams
批处理可调用语句batchCallableWithParams
批处理语句将执行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!
}
})
要执行任何其他数据库操作,例如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!
}
})
在某些情况下,您的查询可能返回多个结果集,在这种情况下,为了保留兼容性,当返回的结果集对象转换为纯json时,下一个结果集将链接到属性next
下的当前结果集. 可以像这样简单地遍历所有结果集:
// do something with the result set...
// next step
rs = rs.next
在处理大型数据集时,建议不要使用刚刚描述的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...
})
}
})
要使用事务,请首先使用setAutoCommit
将auto-commit设置为false.
提交/回滚完成后,将调用处理程序,并且下一个事务将自动启动.
// Do stuff with connection - updates etc
// Now commit
connection.commit({ res ->
if (res.succeeded()) {
// Committed OK!
} else {
// Failed!
}
})
完成连接后,应使用close
将其返回到池中.