Class Database

java.lang.Object

com.codename1.backend.Database

public final class Database
extends Object

One database API over SQLite, PostgreSQL and MySQL, chosen by URL.

Database.open("/var/lib/app/app.db")
Database.open(":memory:")
Database.open("postgres://user:secret@db.internal:5432/app?sslmode=require")
Database.open("mysql://user:secret@db.internal/app?sslmode=require"
              + "&sslrootcert=/etc/ssl/rds-ca.pem")

The point of the single type is that a handler cannot tell which engine answered it. Rows come back as column-name to value maps whose values are always Long, Double, String, byte[] or null, whichever engine produced them -- so the same code developed against a local SQLite file runs against a managed PostgreSQL without a branch. Parameters are always bound, never interpolated, on all three.

TLS has three settings, and none of them is "encrypted but unverified". sslmode=require demands TLS whose certificate chains to a trusted root AND carries the host's name; sslmode=disable is plaintext, deliberately; sslmode=prefer uses TLS when the server offers it and fails loudly rather than silently downgrading when verification does not hold. A managed instance or a development container that presents a private CA is reached by naming it: sslrootcert=/path/to/ca.pem.

SQLite goes through Db, which is per-target: the engine is linked into the binary on the translated side and reached through a JDBC driver on the local Java SE side. PostgreSQL and MySQL are the SAME code on both targets -- they speak the wire protocol over Tcp, so there is no driver to install and nothing that can behave differently between the two.

Statements are written ONCE, in the portable form: ? for every parameter and plain unquoted names. PostgreSQL binds $1 rather than ?, and that difference stops inside execute(String, Object[]) and query(String, Object[]) -- see Dialect.bind(String, int) -- rather than at every call site. SQL already written for one engine keeps working: a statement carrying no ? at all is passed through untouched, so hand-written $1 is left alone.

What differs between engines and used to be papered over by the caller: lastInsertId() is meaningful for SQLite and MySQL and always 0 for PostgreSQL, which has no such concept. insert(String, Object[], String) is the portable form of that question -- it asks whichever way this engine answers -- and dialect exposes the rest of the differences for code that generates schema.

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
static interface	Database.Work	A unit of work run inside transaction(Database.Work).

Method Summary

Modifier and Type	Method	Description
Db	asSqlite()	The underlying SQLite handle, or null when this is a server engine.
void	beginExclusiveTransaction()	Begins a transaction reserved to the calling thread until commit, rollback, or close.
void	beginTransaction()	Begins a transaction reserved to the calling thread until commit, rollback, or close.
void	close()	SYNCHRONIZED, like execute, query and transaction on this class.
void	commitTransaction()	Commits the transaction opened through this API.
Dialect	dialect()	How this connection's engine spells what the three of them spell differently: parameter placeholders, identifier quoting, column types, the declaration of a generated key.
int	execute(String sql, Object[] params)	Synchronized, like the two below, because a shared session is not safe to interleave -- and for the network engines it is worse than interleaved transactions.
long	insert(String sql, Object[] params, String idColumn)	Runs an INSERT and answers the key the database generated for it.
boolean	isInTransaction()	Whether a transaction opened through this Database API is active.
boolean	isOpen()	Whether this connection is still usable, which a pool has to know.
long	lastInsertId()	The id the most recent insert produced, or 0 where the engine has no such concept.
static Database	of(Db db)	Wraps an already-open SQLite handle, for code that opened one directly.
static Database	open(String url)	Opens the database the URL names.
List	query(String sql, Object[] params)	Runs a query and returns every row as a column-name to value map.
Map	queryOne(String sql, Object[] params)	The one row a query is expected to return, or null when it returns none.
void	rollbackTransaction()	Rolls back the transaction opened through this API.
String	toString()	Returns a string representation of the object.
Object	transaction(Database.Work body)
void	tuneForConcurrency(int busyTimeoutMillis)	SQLite-only tuning, ignored elsewhere.

Methods inherited from class Object

clone, equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Method Details

open

public static Database open(String url)
                     throws IOException

Opens the database the URL names. Anything that is not a recognised scheme is taken as a SQLite path, so an ordinary file name keeps working.

Throws:

IOException

of

public static Database of(Db db)
                   throws IOException

Wraps an already-open SQLite handle, for code that opened one directly.

Throws:

IOException

execute

public int execute(String sql,
 Object[] params)
            throws IOException

Synchronized, like the two below, because a shared session is not safe to interleave -- and for the network engines it is worse than interleaved transactions.

Postgres and MySql each own a Wire, and a Wire owns ONE 16KB buffer with a position and a limit, plus one output stream it builds every message in. MySql also carries the packet sequence number. Two handlers calling at once therefore write into the same message buffer, move each other's parse position and desynchronize the sequence: that is protocol corruption, not merely one request's work committed by another's COMMIT.

The SQLite path delegates to Db, which is synchronized on its own monitor. Holding this one first is safe -- the order is always Database then Db, never the reverse -- and Db's monitor is reentrant for the callbacks.

Throws:

IOException

query

public List query(String sql,
 Object[] params)
           throws IOException

Runs a query and returns every row as a column-name to value map.

Throws:

IOException

queryOne

public Map queryOne(String sql,
 Object[] params)
             throws IOException

The one row a query is expected to return, or null when it returns none.

Its own method because the alternative is written at every call site and is wrong in the same way each time: reading get(0) off a list without looking at its size, which is an IndexOutOfBoundsException on the day the row is missing rather than the null the code above it is already written to handle. More than one row is a bug in the statement, and it is reported as one rather than silently discarded.

Throws:

IOException

insert

public long insert(String sql,
 Object[] params,
 String idColumn)
            throws IOException

Runs an INSERT and answers the key the database generated for it.

This is the operation the engines disagree about most and the one an application needs most often. SQLite and MySQL assign the key and hold it until asked -- lastInsertId() -- while PostgreSQL has no such concept at all, and the only way to learn the key there is to ask the INSERT itself for it with a RETURNING clause. Written by hand that is a branch on the engine at every insert; here the dialect knows which it is.

idColumn names the generated column, which is what RETURNING needs. It is quoted for the engine, so a column named "order" or one whose case matters is spelled correctly rather than folded.

ONE ROW. A statement whose VALUES clause names more than one tuple is refused before it runs, because the three engines key a multi-row insert differently and there is no answer that means the same thing on all of them. A statement whose row count its text does NOT show -- an INSERT ... SELECT -- is refused on PostgreSQL, where RETURNING counts the rows for certain, and answers with the engine's last-insert-id on the other two, where asking would mean trusting MySQL's affected-row count, which says two for an upsert that touched one row. Use execute() for a statement that inserts an unknown number of rows.

Parameters:

sql - an INSERT in the portable form, with no RETURNING of its own

Returns:

the generated key, or 0 where the statement inserted no row -- an ignored conflict, most often -- or where the engine generated none

Throws:

IOException

dialect

public Dialect dialect()

How this connection's engine spells what the three of them spell differently: parameter placeholders, identifier quoting, column types, the declaration of a generated key.

Statements passed to execute(String, Object[]) and query(String, Object[]) are already rendered through it, so ordinary code never needs this. Schema generation does -- it has to ask what this engine calls a 64-bit integer.

beginExclusiveTransaction

public void beginExclusiveTransaction()
                               throws IOException

Begins a transaction reserved to the calling thread until commit, rollback, or close. Other threads' database operations wait for that boundary, as they do while transaction(Database.Work) holds the monitor around its callback. The owning thread must complete this transaction; do not hand it to another thread.

Throws:

IOException

isInTransaction

public boolean isInTransaction()

Whether a transaction opened through this Database API is active. Waits for another thread's transaction to finish before checking, like other operations on this connection.

beginTransaction

public void beginTransaction()
                      throws IOException

Begins a transaction reserved to the calling thread until commit, rollback, or close. Other threads wait before using this connection. The initiating thread must complete the transaction; it cannot be handed to another thread. SQLite's write lock is acquired immediately.

Throws:

IOException

commitTransaction

public void commitTransaction()
                       throws IOException

Commits the transaction opened through this API.

Throws:

IOException

rollbackTransaction

public void rollbackTransaction()
                         throws IOException

Rolls back the transaction opened through this API.

Throws:

IOException

transaction

public Object transaction(Database.Work body)
                   throws Exception

Throws:

Exception

lastInsertId

public long lastInsertId()

The id the most recent insert produced, or 0 where the engine has no such concept. PostgreSQL is the case that has none: use INSERT ... RETURNING.

SYNCHRONIZED like every other operation on this class, which is the point: it was the one that was not. A close on another thread -- a pool shutting down, a reconnect -- could therefore run between this reading the engine and the engine reading its own state.

tuneForConcurrency

public void tuneForConcurrency(int busyTimeoutMillis)
                        throws IOException

SQLite-only tuning, ignored elsewhere. Write-ahead logging is what lets readers run while a writer is active, and it has no counterpart on a server engine that already does.

Throws:

IOException

asSqlite

public Db asSqlite()

The underlying SQLite handle, or null when this is a server engine.

close

public void close()

SYNCHRONIZED, like execute, query and transaction on this class.

Without it a shutdown or a reconnect could close the engine while another handler was inside an operation, so Postgres.close or MySql.close wrote its termination packet into a Wire another thread was mid-exchange on -- and on the packaged TLS path freed the native session while that thread was reading through it. The engines' own close() methods were given this lock already; the facade that fronts them was not, which left the same race one level up.

isOpen

public boolean isOpen()

Whether this connection is still usable, which a pool has to know.

toString

public String toString()

Description copied from class: Object

Returns a string representation of the object. In general, the toString method returns a string that "textually represents" this object. The result should be a concise but informative representation that is easy for a person to read. It is recommended that all subclasses override this method. The toString method for class Object returns a string consisting of the name of the class of which the object is an instance, the at-sign character `@', and the unsigned hexadecimal representation of the hash code of the object. In other words, this method returns a string equal to the value of: getClass().getName() + '@' + Integer.toHexString(hashCode())

Overrides:

toString in class Object