# Class Database

java.lang.Object
com.codename1.db.Database

Direct Known Subclasses:
  `ThreadSafeDatabase`

public abstract class Database extends Object

Allows access to SQLite specifically connecting to a database and executing sql queries on the data. There is more thorough coverage of the `Database API here`.

The Database class abstracts the underlying SQLite of the device if available.

Notice that this might not be supported on all platforms in which case the `Database` will be null.

SQLite should be used for very large data handling, for small storage refer to `com.codename1.io.Storage` which is more portable.

##### Example

    Database db = null;
    Cursor cur = null;
    try {
        db = Database.openOrCreate("MyDB.db");
        db.execute("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY, name TEXT)");
        db.execute("INSERT INTO people (name) VALUES (?)", new Object[] {"Alice"});

        cur = db.executeQuery("SELECT id, name FROM people ORDER BY id");
        while (cur.next()) {
            Row row = cur.getRow();
            System.out.println(row.getInteger(0) + " " + row.getString(1));
        }
    } finally {
        if (cur != null) {
            cur.close();
        }
        if (db != null) {
            db.close();
        }
    }

##### Encryption

Pass a `DatabaseConfig` to `#openOrCreate(java.lang.String, com.codename1.db.DatabaseConfig)` to encrypt the database at rest. Check `#isEncryptionSupported()` first, and read the security notes on `DatabaseConfig` before choosing how to key it.

- ## Field Summary

  Fields
  Modifier and Type
  Field
  Description
  `protected boolean`
  `inTransaction`
  Tracks whether a transaction is open on this instance, so the flat-transaction rules in the package documentation are enforced identically on every port rather than being re-derived from each engine's very different native semantics.

- ## Constructor Summary

  Constructors
  Constructor
  Description
  `Database()`

- ## Method Summary

  Modifier and Type
  Method
  Description
  `protected IOException`
  `abandonFailedCommit(Throwable cause)`
  Discards a transaction whose commit failed, and builds the exception to report it with.
  `static void`
  `beforeFirst(Cursor cursor)`
  Rewinds a cursor to before its first row.
  `abstract void`
  `beginTransaction()`
  Starts a transaction.
  `void`
  `changeKey(DatabaseConfig config)`
  Changes the key of this open database, or removes it entirely.
  `protected void`
  `checkBeginTransaction()`
  Rejects a nested `#beginTransaction()`, then records that one is open.
  `protected void`
  `checkEndTransaction()`
  Rejects a commit or rollback with no open transaction.
  `abstract void`
  `close()`
  Closes the database
  `protected static String[]`
  `coerceToText(Object[] params, String operation)`
  Renders parameters as text for ports that have not implemented typed binding.
  `abstract void`
  `commitTransaction()`
  Commits current transaction
  `static int`
  `count(Cursor cursor)`
  Returns the number of rows a cursor holds, or -1 when that is not cheaply knowable.
  `static void`
  `decrypt(String databaseName, DatabaseConfig config)`
  Decrypts an existing encrypted database in place, leaving a plain SQLite file.
  `static void`
  `delete(String databaseName)`
  Deletes database
  `static void`
  `encrypt(String databaseName, DatabaseConfig config)`
  Encrypts an existing plaintext database in place.
  `abstract void`
  `execute(String sql)`
  Execute an update query.
  `void`
  `execute(String sql, Object... params)`
  Execute an update query with params.
  `abstract void`
  `execute(String sql, String[] params)`
  Execute an update query with params.
  `abstract Cursor`
  `executeQuery(String sql)`
  This method should be called with SELECT type statements that return row set.
  `Cursor`
  `executeQuery(String sql, Object... params)`
  This method should be called with SELECT type statements that return row set it accepts object with params.
  `abstract Cursor`
  `executeQuery(String sql, String[] params)`
  This method should be called with SELECT type statements that return row set.
  `static boolean`
  `exists(String databaseName)`
  Indicates weather a database exists
  `static boolean`
  `forgetManagedKey(String keyAlias)`
  Removes the stored managed key for an alias.
  `static String`
  `getDatabasePath(String databaseName)`
  Returns the file path of the Database if exists and if supported on the platform.
  `static boolean`
  `isBlobQueryParameterSupported()`
  Indicates whether `#executeQuery(java.lang.String, java.lang.Object[])` accepts `byte[]` parameters on this platform.
  `static boolean`
  `isCustomPathSupported()`
  Checks if this platform supports custom database paths.
  `static boolean`
  `isEncrypted(String databaseName)`
  Indicates whether a database file appears to be encrypted.
  `static boolean`
  `isEncryptionSupported()`
  Indicates whether this platform can open encrypted databases.
  `boolean`
  `isInTransaction()`
  Reports whether a transaction is currently open on this database.
  `static boolean`
  `isLegacyBehavior()`
  Returns whether the database API is running in legacy compatibility mode.
  `protected void`
  `markTransactionEnded()`
  Records that a transaction has actually ended.
  `static Database`
  `openOrCreate(String databaseName)`
  Opens a database or create one if not exists.
  `static Database`
  `openOrCreate(String databaseName, DatabaseConfig config)`
  Opens an encrypted database, creating it if it does not exist.
  `abstract void`
  `rollbackTransaction()`
  Rolls back current transaction
  `static void`
  `setLegacyBehavior(boolean legacy)`
  Turns legacy compatibility mode on or off.
  `static boolean`
  `supportsWasNull(Row row)`
  Checks to see if the given row supports `#wasNull(com.codename1.db.Row)`.
  `protected static String`
  `toPragmaLiteral(String keyMaterial)`
  Renders a key literal for use as a `PRAGMA` argument.
  `static boolean`
  `wasNull(Row row)`
  Checks if the last value accessed from a given row was null.

  ### Methods inherited from class Object

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

- ## Field Details

  - ### inTransaction

    protected boolean inTransaction
    Tracks whether a transaction is open on this instance, so the flat-transaction rules in the package documentation are enforced identically on every port rather than being re-derived from each engine's very different native semantics.

- ## Constructor Details

  - ### Database

    public Database()

- ## Method Details

  - ### isCustomPathSupported

    public static boolean isCustomPathSupported()

    Checks if this platform supports custom database paths. On platforms that support this, you can pass a file path to `#openOrCreate(java.lang.String)`, `#exists(java.lang.String)`, `#delete(java.lang.String)`, and `#getDatabasePath(java.lang.String)`.

    ###### Returns

    True on platorms that support custom database paths.

  - ### isLegacyBehavior

    public static boolean isLegacyBehavior()

    Returns whether the database API is running in legacy compatibility mode.

    The behaviour of this API used to differ substantially between platforms. Those differences have been reconciled into the single contract documented in the `com.codename1.db` package, but applications written against the old, divergent behaviour may depend on it. Legacy mode restores each platform's previous behaviour exactly, and is intended as a transition aid rather than a permanent setting.

    Enable it with the `db.legacy` build hint, or from code before the first database call:

        Database.setLegacyBehavior(true);

    The package documentation lists precisely which behaviours the flag covers. Fixes for outright defects, and capabilities that previously threw and now work, are not covered, because no application can depend on those.

    ###### Returns

    true when the pre-normalization behaviour is in effect

  - ### setLegacyBehavior

    public static void setLegacyBehavior(boolean legacy)

    Turns legacy compatibility mode on or off.

    Call this before opening any database; cursors and connections capture the mode as they are created, so flipping it mid-session gives inconsistent results.

    ###### Parameters

    - `legacy`: true to restore the pre-normalization behaviour

    ###### See also

    - #isLegacyBehavior()

  - ### openOrCreate

    public static Database openOrCreate(String databaseName) throws IOException

    Opens a database or create one if not exists.

    ###### Parameters

    - `databaseName`: @param databaseName the name of the database. Platforms that support custom database paths (i.e. `#isCustomPathSupported()` return true), will also accept a file path here.

    ###### Returns

    Database Object or null if not supported on the platform

    ###### Throws

    - `IOException`: if database cannot be created
    Throws:
      `IOException`

  - ### exists

    public static boolean exists(String databaseName)

    Indicates weather a database exists

    NOTE: Not supported in the Javascript port. Will always return false.

    ###### Parameters

    - `databaseName`: @param databaseName the name of the database. Platforms that support custom database paths (i.e. `#isCustomPathSupported()` return true), will also accept a file path here.

    ###### Returns

    true if database exists

  - ### delete

    public static void delete(String databaseName) throws IOException

    Deletes database

    NOTE: This method is not supported in the Javascript port. Will silently fail.

    ###### Parameters

    - `databaseName`: @param databaseName the name of the database. Platforms that support custom database paths (i.e. `#isCustomPathSupported()` return true), will also accept a file path here.

    ###### Throws

    - `IOException`: if database cannot be deleted
    Throws:
      `IOException`

  - ### getDatabasePath

    public static String getDatabasePath(String databaseName)

    Returns the file path of the Database if exists and if supported on the platform.

    ###### Parameters

    - `databaseName`: @param databaseName The name of the database. Platforms that support custom database paths (i.e. `#isCustomPathSupported()` return true), will also accept a file path here.

    NOTE: Where `#isCustomPathSupported()` is false the databases are not filesystem backed, so what comes back identifies the database inside the platform's storage but is not a path `com.codename1.io.FileSystemStorage` can open.

    ###### Returns

    the file path of the database

  - ### openOrCreate

    public static Database openOrCreate(String databaseName, DatabaseConfig config) throws IOException

    Opens an encrypted database, creating it if it does not exist.

    The database is encrypted at rest using the key described by `config`. Every platform that supports encryption writes the same on-disk format, so a database created on one device can be opened on another and in the simulator.

    If `config` is null or describes a plaintext database this behaves exactly like `#openOrCreate(java.lang.String)`.

    ###### Example

        if (!Database.isEncryptionSupported()) {
            throw new IOException("This build cannot store data securely");
        }
        DatabaseConfig config = DatabaseConfig.managed();
        Database db = Database.openOrCreate("secure.db", config);
        config.wipe();

    ###### Parameters

    - `databaseName`: @param databaseName the name of the database. Platforms that support custom database paths (see `#isCustomPathSupported()`) also accept a file path.

    - `config`: how to key the database, or null for plaintext

    ###### Returns

    the open database

    ###### Throws

    - `DatabaseEncryptionException`: @throws DatabaseEncryptionException with `DatabaseEncryptionException#NOT_SUPPORTED` if encryption was requested on a platform that cannot provide it, or with `DatabaseEncryptionException#WRONG_KEY` if the key does not decrypt an existing database

    - `IOException`: if the database cannot be opened or created

    Throws:
      `IOException`

  - ### isEncryptionSupported

    public static boolean isEncryptionSupported()

    Indicates whether this platform can open encrypted databases.

    ###### Returns

    true if `#openOrCreate(java.lang.String, com.codename1.db.DatabaseConfig)` accepts an encrypting config

  - ### isEncrypted

    public static boolean isEncrypted(String databaseName)

    Indicates whether a database file appears to be encrypted.

    This inspects the file header: an unencrypted SQLite database begins with the ASCII bytes `SQLite format 3` followed by a zero byte, and an encrypted one does not. It is therefore a header sniff, not a cryptographic assertion -- a truncated or corrupt file also reports true, and a false result only means the file is a readable plaintext SQLite database.

    ###### Parameters

    - `databaseName`: the name of the database

    ###### Returns

    false if the file exists and starts with a plaintext SQLite header, true otherwise

  - ### encrypt

    public static void encrypt(String databaseName, DatabaseConfig config) throws IOException

    Encrypts an existing plaintext database in place.

    The conversion is performed by the database engine as a single transaction, so an interruption leaves the original file intact rather than half-converted. Schema metadata such as `PRAGMA user_version` is preserved.

    ###### Parameters

    - `databaseName`: the name of an existing plaintext database

    - `config`: how the encrypted database should be keyed

    ###### Throws

    - `IOException`: if the database cannot be converted
    Throws:
      `IOException`

  - ### decrypt

    public static void decrypt(String databaseName, DatabaseConfig config) throws IOException

    Decrypts an existing encrypted database in place, leaving a plain SQLite file.

    ###### Parameters

    - `databaseName`: the name of an existing encrypted database

    - `config`: the config that currently opens the database

    ###### Throws

    - `IOException`: if the database cannot be converted
    Throws:
      `IOException`

  - ### forgetManagedKey

    public static boolean forgetManagedKey(String keyAlias)

    Removes the stored managed key for an alias.

    `#delete(java.lang.String)` deliberately leaves the managed key in place, because deleting and recreating a database is a normal thing to do and should not discard the identity that protects it. Call this explicitly when the key really should be forgotten -- after which any remaining database encrypted with it is permanently unreadable.

    ###### Parameters

    - `keyAlias`: @param keyAlias the alias passed to `DatabaseConfig#managed(java.lang.String)`, or the database name when `DatabaseConfig#managed()` was used

    ###### Returns

    true if a key was removed

  - ### beforeFirst

    public static void beforeFirst(Cursor cursor) throws IOException

    Rewinds a cursor to before its first row.

    Uses `CursorExt#beforeFirst()` when the cursor provides it, and falls back to `Cursor#position(int)` with -1 otherwise.

    ###### Parameters

    - `cursor`: the cursor to rewind

    ###### Throws

    - `IOException`: if the cursor is closed or the rewind fails
    Throws:
      `IOException`

  - ### count

    public static int count(Cursor cursor) throws IOException

    Returns the number of rows a cursor holds, or -1 when that is not cheaply knowable.

    ###### Parameters

    - `cursor`: the cursor to measure

    ###### Returns

    the row count, or -1 when unknown

    ###### Throws

    - `IOException`: if the cursor is closed
    Throws:
      `IOException`

  - ### isBlobQueryParameterSupported

    public static boolean isBlobQueryParameterSupported()

    Indicates whether `#executeQuery(java.lang.String, java.lang.Object[])` accepts `byte[]` parameters on this platform.

    Blob values can always be written with `#execute(java.lang.String, java.lang.Object[])`. Using one as a query parameter, for example in `WHERE digest = ?`, needs engine support that not every port can provide.

    ###### Returns

    true if blobs may be used as query parameters

  - ### changeKey

    public void changeKey(DatabaseConfig config) throws IOException

    Changes the key of this open database, or removes it entirely.

    Passing a plaintext config decrypts the database. The engine performs the conversion as a single transaction and preserves schema metadata such as `PRAGMA user_version`.

    Ports that support encryption override this. The default implementation reports that the platform cannot do it; it is deliberately concrete rather than abstract, because `Database` is public and is subclassed outside this repository.

    ###### Parameters

    - `config`: the new key, or `DatabaseConfig#plain()` to decrypt

    ###### Throws

    - `IOException`: if the key cannot be changed
    Throws:
      `IOException`

  - ### wasNull

    public static boolean wasNull(Row row) throws IOException

    Checks if the last value accessed from a given row was null. Not all platforms support wasNull(). If the platform does not support it, this will just return false.

    Check `#supportsWasNull(com.codename1.db.Row)` to see if the platform supports wasNull().

    Currently wasNull() is supported on UWP, iOS, Android, and JavaSE (Simulator).

    ###### Parameters

    - `row`: The row to check.

    ###### Returns

    True if the last value accessed was null.

    ###### Throws

    - `IOException`

    ###### See also

    - RowExt#wasNull()

    - #supportsWasNull(com.codename1.db.Row)

    Throws:
      `IOException`

  - ### supportsWasNull

    public static boolean supportsWasNull(Row row) throws IOException

    Checks to see if the given row supports `#wasNull(com.codename1.db.Row)`.

    ###### Parameters

    - `row`: The row to check.

    ###### Returns

    True if the row supports wasNull().

    ###### Throws

    - `IOException`

    ###### See also

    - #wasNull(com.codename1.db.Row)

    - RowExt#wasNull()

    Throws:
      `IOException`

  - ### isInTransaction

    public boolean isInTransaction()

    Reports whether a transaction is currently open on this database.

    ###### Returns

    true between a successful `#beginTransaction()` and its commit or rollback

  - ### toPragmaLiteral

    protected static String toPragmaLiteral(String keyMaterial)

    Renders a key literal for use as a `PRAGMA` argument.

    Raw keys are already the blob literal `x'...'`, which has to reach the engine unquoted as a literal rather than as a string. Passphrases are arbitrary text, so they are single quoted with any embedded single quote doubled. Interpolating a passphrase directly would let one containing a quote change the statement.

    ###### Parameters

    - `keyMaterial`: the value from `DatabaseConfig#resolveKeyMaterial(java.lang.String)`

    ###### Returns

    the text to place after `PRAGMA key =` or `PRAGMA rekey =`

  - ### checkBeginTransaction

    protected void checkBeginTransaction() throws IOException

    Rejects a nested `#beginTransaction()`, then records that one is open.

    Transactions are flat: only that model is expressible on all of the engines behind this API. Ports call this at the top of `#beginTransaction()`. In legacy mode the check is skipped, because a nested begin used to be accepted on Android.

    ###### Throws

    - `IOException`: if a transaction is already open
    Throws:
      `IOException`

  - ### checkEndTransaction

    protected void checkEndTransaction() throws IOException

    Rejects a commit or rollback with no open transaction.

    Ports call this at the top of `#commitTransaction()` and `#rollbackTransaction()`, and `#markTransactionEnded()` once the engine has ended it. The two are separate so that a port can end the transaction on a path that does not commit it, which is what `#abandonFailedCommit(Throwable)` does.

    In legacy mode the check is skipped.

    ###### Throws

    - `IOException`: if no transaction is open
    Throws:
      `IOException`

  - ### markTransactionEnded

    protected void markTransactionEnded()
    Records that a transaction has actually ended. Call only after the engine has committed or rolled back successfully.

  - ### abandonFailedCommit

    protected IOException abandonFailedCommit(Throwable cause)

    Discards a transaction whose commit failed, and builds the exception to report it with.

    A commit that fails cannot be retried, so the only remaining outcome is a rollback. The engines disagree about what they leave behind: Android has already ended the transaction by the time it reports the failure, while the SQLite C API and JDBC leave it open. Ports call this from the failure path of `#commitTransaction()`, after making a best effort to roll back, so that callers see one behavior everywhere -- no transaction is open, and `#beginTransaction()` works again.

    ###### Parameters

    - `cause`: the failure the engine reported

    ###### Returns

    the exception the caller should throw

  - ### beginTransaction

    public abstract void beginTransaction() throws IOException

    Starts a transaction.

    Transactions are flat. Calling this while a transaction is already open throws, and committing or rolling back returns the connection to autocommit. Closing a database with an open transaction rolls it back.

    ###### Throws

    - `IOException`: if the database is not open, or a transaction is already in progress
    Throws:
      `IOException`

  - ### commitTransaction

    public abstract void commitTransaction() throws IOException

    Commits current transaction

    NOTE: Not supported in Javascript port. This method will do nothing when running in Javascript.

    ###### Throws

    - `IOException`: if database is not opened or transaction was not started
    Throws:
      `IOException`

  - ### rollbackTransaction

    public abstract void rollbackTransaction() throws IOException

    Rolls back current transaction

    NOTE: Not supported in Javascript port. This method will do nothing when running in Javascript.

    ###### Throws

    - `IOException`: if database is not opened or transaction was not started
    Throws:
      `IOException`

  - ### close

    public abstract void close() throws IOException

    Closes the database

    ###### Throws

    - `IOException`
    Throws:
      `IOException`

  - ### execute

    public abstract void execute(String sql) throws IOException

    Execute an update query. Used for INSERT, UPDATE, DELETE and similar sql statements.

    ###### Parameters

    - `sql`: the sql to execute

    ###### Throws

    - `IOException`
    Throws:
      `IOException`

  - ### execute

    public abstract void execute(String sql, String[] params) throws IOException

    Execute an update query with params. Used for INSERT, UPDATE, DELETE and similar sql statements. The sql can be constructed with '?' and the params will be binded to the query

    ###### Parameters

    - `sql`: the sql to execute

    - `params`: to bind to the query where the '?' exists

    ###### Throws

    - `IOException`
    Throws:
      `IOException`

  - ### execute

    public void execute(String sql, Object... params) throws IOException

    Execute an update query with params. Used for INSERT, UPDATE, DELETE and similar sql statements. The sql can be constructed with '?' and the params will be binded to the query

    ###### Parameters

    - `sql`: the sql to execute

    - `params`: @param params to bind to the query where the '?' exists, supported object types are String, byte[], Double, Long and null

    ###### Throws

    - `IOException`
    Throws:
      `IOException`

  - ### coerceToText

    protected static String[] coerceToText(Object[] params, String operation) throws IOException

    Renders parameters as text for ports that have not implemented typed binding.

    This is the fallback path only. Ports that can bind by type override the varargs methods and never reach here, which is why hitting a `byte[]` is an error rather than something to paper over: silently storing the result of `byte[].toString()` would write the array's identity hash into the database.

    ###### Parameters

    - `params`: the parameters supplied by the caller

    - `operation`: the calling method name, used in the error message

    ###### Returns

    the parameters rendered as text, preserving nulls

    ###### Throws

    - `IOException`: if a parameter is a `byte[]` and this port cannot bind blobs
    Throws:
      `IOException`

  - ### executeQuery

    public abstract Cursor executeQuery(String sql, String[] params) throws IOException

    This method should be called with SELECT type statements that return row set.

    ###### Parameters

    - `sql`: the sql to execute

    - `params`: to bind to the query where the '?' exists

    ###### Returns

    a cursor to iterate over the results

    ###### Throws

    - `IOException`
    Throws:
      `IOException`

  - ### executeQuery

    public Cursor executeQuery(String sql, Object... params) throws IOException

    This method should be called with SELECT type statements that return row set it accepts object with params.

    ###### Parameters

    - `sql`: the sql to execute

    - `params`: @param params to bind to the query where the '?' exists, supported object types are String, byte[], Double, Long and null

    ###### Returns

    a cursor to iterate over the results

    ###### Throws

    - `IOException`
    Throws:
      `IOException`

  - ### executeQuery

    public abstract Cursor executeQuery(String sql) throws IOException

    This method should be called with SELECT type statements that return row set.

    ###### Parameters

    - `sql`: the sql to execute

    ###### Returns

    a cursor to iterate over the results

    ###### Throws

    - `IOException`
    Throws:
      `IOException`