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'").execute(function (ar, ar_err) {
if (ar_err == null) {
var result = ar;
console.log("Got " + result.size() + " rows ");
} else {
console.log("Failure: " + ar_err.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}
var Tuple = require("vertx-sql-client-js/tuple");
client.preparedQuery("SELECT * FROM users WHERE id=?").execute(Tuple.of("julien"), function (ar, ar_err) {
if (ar_err == null) {
var rows = ar;
console.log("Got " + rows.size() + " rows ");
} else {
console.log("Failure: " + ar_err.getMessage());
}
});
Query methods provides an asynchronous RowSet
instance that works for SELECT queries
client.preparedQuery("SELECT first_name, last_name FROM users").execute(function (ar, ar_err) {
if (ar_err == null) {
var rows = ar;
Array.prototype.forEach.call(rows, function(row) {
console.log("User " + row.getString(0) + " " + row.getString(1));
});
} else {
console.log("Failure: " + ar_err.getMessage());
}
});
or UPDATE/INSERT queries:
var Tuple = require("vertx-sql-client-js/tuple");
client.preparedQuery("INSERT INTO users (first_name, last_name) VALUES (?, ?)").execute(Tuple.of("Julien", "Viet"), function (ar, ar_err) {
if (ar_err == null) {
var rows = ar;
console.log(rows.rowCount());
} else {
console.log("Failure: " + ar_err.getMessage());
}
});
The Row
gives you access to your data by index
console.log("User " + row.getString(0) + " " + row.getString(1));
or by name
console.log("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:
var Tuple = require("vertx-sql-client-js/tuple");
// Enable prepare statements caching
connectOptions.cachePreparedStatements = true;
client.preparedQuery("SELECT * FROM users WHERE id = ?").execute(Tuple.of("julien"), function (ar, ar_err) {
if (ar_err == null) {
var rows = ar;
console.log("Got " + rows.size() + " rows ");
} else {
console.log("Failure: " + ar_err.getMessage());
}
});
You can create a PreparedStatement
and manage the lifecycle by yourself.
var Tuple = require("vertx-sql-client-js/tuple");
sqlConnection.prepare("SELECT * FROM users WHERE id = ?", function (ar, ar_err) {
if (ar_err == null) {
var preparedStatement = ar;
preparedStatement.query().execute(Tuple.of("julien"), function (ar2, ar2_err) {
if (ar2_err == null) {
var rows = ar2;
console.log("Got " + rows.size() + " rows ");
preparedStatement.close();
} else {
console.log("Failure: " + ar2_err.getMessage());
}
});
} else {
console.log("Failure: " + ar_err.getMessage());
}
});
Batches
You can execute prepared batch
var Tuple = require("vertx-sql-client-js/tuple");
// Add commands to the batch
var batch = [];
batch.push(Tuple.of("julien", "Julien Viet"));
batch.push(Tuple.of("emad", "Emad Alblueshi"));
// Execute the prepared batch
client.preparedQuery("INSERT INTO USERS (id, name) VALUES (?, ?)").executeBatch(batch, function (res, res_err) {
if (res_err == null) {
// Process rows
var rows = res;
} else {
console.log("Batch failed " + res_err);
}
});