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='julien'", { 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=?", Tuple.of("julien"), { 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", { 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 (?, ?)", Tuple.of("Julien", "Viet"), { 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 cache prepared queries:

// Enable prepare statements caching
connectOptions.cachePreparedStatements = true

Batches

You can execute prepared batch

// Add commands to the batch
var batch = mutableListOf<Any?>()
batch.add(Tuple.of("julien", "Julien Viet"))
batch.add(Tuple.of("emad", "Emad Alblueshi"))

// Execute the prepared batch
client.preparedBatch("INSERT INTO USERS (id, name) VALUES (?, ?)", batch, { res ->
  if (res.succeeded()) {

    // Process rows
    var rows = res.result()
  } else {
    println("Batch failed ${res.cause()}")
  }
})