Skip to content

Executing Statements

Jump to bottom
Ohad Shai edited this page Jan 30, 2019 · 13 revisions

Overview

This page describes executing statements and getting the result with the driver.

The examples in this page are in Kotlin and use coroutines with SuspendingConnection. To obtain a SuspendingConnection call connection.asSuspending property. The examples are similar to Java. In Java you will work with Connection directly and all results are using callbacks with CompletableFuture. Other than that the lib interface is the same.

Query

Let's see how to execute a query on a table with the following create definition:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT ,
  name VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
  PRIMARY KEY (id) 
);

A simple insert statement will look like that:

val queryResult: QueryResult = connection.sendQuery("INSERT INTO users (name) VALUES ('Boogie Man')")
queryResult.rowsAffected // 1 row affected
// for MySQL
(queryResult as MySQLQueryResult).lastInsertId // 1 (last inserted id)

A simple select statement will look like that:

val queryResult: QueryResult = connection.sendQuery("SELECT * FROM users")
val result: ResultSet = queryResult.rows
result[0]["id"] // 1
result[0][0] // 1
result[0]["name"] // 'Boogie Man'
// also possible to get type safe result
result[0].getInt("id") // 1

To see the exact data types head on to the section on the wiki: MySQL PostgreSQL

Error handling

When an error occurred, a com.github.jasync.sql.db.exceptions.DatabaseException will be thrown. CompletableFuture callback will be completed exceptionally.

Prepared Statements

A prepared statement is a statement that is sent to the database separately from the parameters (which are marked by ? in the query). The API itself is similar to regular query with an additional parameter which is the list of parameters values:

fun sendPreparedStatement(query: String, values: List<Any?>, release: Boolean): CompletableFuture<QueryResult>

An example of such a query can look like:

sendPreparedStatement("select * from posts where created_at_year > ?", listOf(2011))

Closing Prepared Statements

In Mauricio lib, there was no support to close a prepared statement. This is fine in most cases, statements are cached in the database and reused thus more performant. Also, when the connection is closed all it's prepared statements are deallocated.
However, in some cases dynamically prepared statements create a leak that prevents from creating more prepared statements. In such cases, it is advised to release the statement immediately.
Release a statement immediately is done by an additional boolean parameter to sendPreparedStatement() Otherwise, there are the following options:

  • Not release at all - this is good if all prepared statements are known in advance and will not leak.
  • Release manually via releasePreparedStatement() - this is usually not recommended and will not work with a connection pool.

More Info

Some more info on prepared statements from wikipedia:

As compared to executing statements directly, prepared statements offer two main advantages:

  • The overhead of compiling the statement is incurred only once, although the statement is executed multiple times. However not all optimization can be performed at the time the statement template is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.
  • Prepared statements are resilient against SQL injection because values which are transmitted later using a different protocol are not compiled like the statement template. If the statement template is not derived from external input, SQL injection cannot occur.

On the other hand, if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server. Implementation limitations may also lead to performance penalties; for example, some versions of MySQL did not cache the results of prepared queries. A stored procedure, which is also precompiled and stored on the server for later execution, has similar advantages. Unlike a stored procedure, a prepared statement is not normally written in a procedural language and cannot use or modify variables or use control flow structures, relying instead on the declarative database query language. Due to their simplicity and client-side emulation, prepared statements are more portable across vendors.

Transactions

Connection supports transactions with the following API:

fun <A> inTransaction(f: (Connection) -> CompletableFuture<A>): CompletableFuture<A>

Essentially f() is a method that executes queries on the connection. In case the future fails, the transaction will be rolled back.

You can see more details about the implementation here: https://medium.com/@OhadShai/just-a-small-example-of-how-kotlin-coroutines-are-great-c9774fe8434

Clone this wiki locally