Skip to content

Updating

Jump to bottom
Siim Kinks edited this page Mar 21, 2017 · 6 revisions

Generally, there are two ways to update database records. Either call generated methods directly on objects or use UPDATE statement builder.

SqliteMagic generates the following builder methods on @Table annotated classes:

  • update() Builder for updating object in database using UPDATE SQLite statement. Execution methods produce boolean result indicating the success of the operation.

    • conflictAlgorithm(@ConflictAlgorithm int) Builder method for defining a conflict algorithm for operation.
    • usingConnection(DbConnection) Define which database connection to use for executing this operation. Whithout defining it SqliteMagic uses the default connection.

  • update(Iterable<T>) Static builder for updating multiple objects in database insfide a transaction using UPDATE SQLite statement. Execution methods produce boolean result indicating the success of the operation.

    • usingConnection(DbConnection) Define which database connection to use for executing this operation. Whithout defining it SqliteMagic uses the default connection.

Important! Generated methods are starting points to building database operations. Each builder contains several operation options and must end with an "executive" method - either execute() for synchronous execution or observe() for starting point into reactive world.

Example:

Synchronous RxJava 
Author author = new Author(
        73, "Foo", "Bar");

boolean success = author
    .update()
    .execute();
    
// Bulk operation
success = Author
    .update(someAuthors)
    .execute();
 
Author author = new Author(
        73, "Foo", "Bar");

Completable update = author
    .update()
    .observe();
    
// Bulk operation
Completable bulkUpdate = Author
    .update(someAuthors)
    .observe();

Update Statement Builder

Other option to update database is to use the UPDATE statement builder. The UPDATE statement is used to modify one or several pre-existing records in a database table. UPDATE statements are only possible on single table.

UPDATE statement builder starts with any static method on com.siimkinks.sqlitemagic.Update object and must end with an "executive" method - either execute() for synchronous execution or observe() for starting point into the reactive world.

The builder API is typesafe and follows SQL very closely. UPDATE statement builder has a guiding builder API, meaning that it is impossible to call methods in the wrong order.

Example:

SQL SqliteMagic 
UPDATE AUTHOR
   SET FIRST_NAME = 'Foo',
       LAST_NAME = 'Bar'
 WHERE ID = 42;
 
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;

int updatedRowCount = Update
        .table(AUTHOR)
        .set(AUTHOR.FIRST_NAME, "Foo")
        .set(AUTHOR.LAST_NAME, "Bar")
        .where(AUTHOR.ID.is(42L))
        .execute();

SQLite also allows using scalar subselect in UPDATE statements:

SQL SqliteMagic 
UPDATE AUTHOR
   SET FIRST_NAME = (
         SELECT FIRST_NAME
         FROM USER
         WHERE USER.ID = AUTHOR.ID
       )
 WHERE ID = 42;
 
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;
import static com.siimkinks.sqlitemagic.UserTable.USER;

int updatedRowCount = Update
        .table(AUTHOR)
        .set(AUTHOR.FIRST_NAME,
            Select
            .column(USER.FIRST_NAME)
            .from(USER)
            .where(USER.ID.is(AUTHOR.ID)
        )
        .where(AUTHOR.ID.is(42L))
        .execute();

See next

Clone this wiki locally