Running queries
When you don’t need a transaction or run single queries, you can run queries directly on the pool; the pool will use one of its connection to run the query and return the result to you.
Here is how to run simple queries:
client.query("SELECT * FROM users WHERE id='andy'").execute({ ar ->
if (ar.succeeded()) {
var result = ar.result()
println("Got ${result.size()} rows ")
} else {
println("Failure: ${ar.cause().getMessage()}")
}
})
Prepared queries
You can do the same with prepared queries.
The SQL string can refer to parameters by position, using the database syntax {PREPARED_PARAMS}
client.preparedQuery("SELECT * FROM users WHERE id=\$$1").execute(Tuple.of("andy"), { ar ->
if (ar.succeeded()) {
var rows = ar.result()
println("Got ${rows.size()} rows ")
} else {
println("Failure: ${ar.cause().getMessage()}")
}
})
Query methods provides an asynchronous RowSet
instance that works for SELECT queries
client.preparedQuery("SELECT first_name, last_name FROM users").execute({ ar ->
if (ar.succeeded()) {
var rows = ar.result()
for (row in rows) {
println("User ${row.getString(0)} ${row.getString(1)}")
}
} else {
println("Failure: ${ar.cause().getMessage()}")
}
})
or UPDATE/INSERT queries:
client.preparedQuery("INSERT INTO users (first_name, last_name) VALUES (\$$1, \$$2)").execute(Tuple.of("Andy", "Guibert"), { ar ->
if (ar.succeeded()) {
var rows = ar.result()
println(rows.rowCount())
} else {
println("Failure: ${ar.cause().getMessage()}")
}
})
The Row
gives you access to your data by index
println("User ${row.getString(0)} ${row.getString(1)}")
or by name
println("User ${row.getString("first_name")} ${row.getString("last_name")}")
The client will not do any magic here and the column name is identified with the name in the table regardless of how your SQL text is.
You can access a wide variety of of types
var firstName = row.getString("first_name")
var male = row.getBoolean("male")
var age = row.getInteger("age")
// ...
You can use cached prepared statements to execute one-shot prepared queries:
// Enable prepare statements caching
connectOptions.cachePreparedStatements = true
client.preparedQuery("SELECT * FROM users WHERE id = ?").execute(Tuple.of("julien"), { ar ->
if (ar.succeeded()) {
var rows = ar.result()
println("Got ${rows.size()} rows ")
} else {
println("Failure: ${ar.cause().getMessage()}")
}
})
You can create a PreparedStatement
and manage the lifecycle by yourself.
sqlConnection.prepare("SELECT * FROM users WHERE id= ?", { ar ->
if (ar.succeeded()) {
var preparedStatement = ar.result()
preparedStatement.query().execute(Tuple.of("julien"), { ar2 ->
if (ar2.succeeded()) {
var rows = ar2.result()
println("Got ${rows.size()} rows ")
preparedStatement.close()
} else {
println("Failure: ${ar2.cause().getMessage()}")
}
})
} else {
println("Failure: ${ar.cause().getMessage()}")
}
})
Batches
You can execute prepared batch
// Add commands to the batch
var batch = mutableListOf<Any?>()
batch.add(Tuple.of("julien", "Julient Viet"))
batch.add(Tuple.of("emad", "Emad Alblueshi"))
batch.add(Tuple.of("andy", "Andy Guibert"))
// Execute the prepared batch
client.preparedQuery("INSERT INTO USERS (id, name) VALUES (\$$1, \$$2)").executeBatch(batch, { res ->
if (res.succeeded()) {
// Process rows
var rows = res.result()
} else {
println("Batch failed ${res.cause()}")
}
})