SQLite native functions X SugarRecord

Developing applications with Sugar ORM can be useful for those who need to persist information in the local database and want to take advantage of the ORM model, not needing to use SQL for creating and manipulating tables.

Sugar ORM is a database persistence library that provides a simple and consistent way to integrate application templates into an SQLite database. The main advantage of Sugar ORM is its simplicity, but it allows you to perform somewhat more complex tasks such as using one-to-many relationships.

We can see this "simplicity" in Sugar ORM CRUD :

Save :

Book book = new Book("Title here", "2nd edition")
book.save();

Upload :

Book book = Book.findById(Book.class, 1);

Update :

Book book = Book.findById(Book.class, 1);
book.title = "updated title here"; // modify the values
book.edition = "3rd edition";
book.save(); // updates the previous entry with new values.

Delete :

Book book = Book.findById(Book.class, 1);
book.delete();

Bulk operations :

List<Book> books = Book.listAll(Book.class);

Book.deleteAll(Book.class);

No longer SQLlite :

Creation :

/*
 * Creating a todo
 */
public long createToDo(Todo todo, long[] tag_ids) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_TODO, todo.getNote());
    values.put(KEY_STATUS, todo.getStatus());
    values.put(KEY_CREATED_AT, getDateTime());

    // insert row
    long todo_id = db.insert(TABLE_TODO, null, values);

    // assigning tags to todo
    for (long tag_id : tag_ids) {
        createTodoTag(todo_id, tag_id);
    }

    return todo_id;
}

Search :

Search for type SELECT * FROM todos WHERE id = 1;

/*
 * get single todo
 */
public Todo getTodo(long todo_id) {
    SQLiteDatabase db = this.getReadableDatabase();

    String selectQuery = "SELECT  * FROM " + TABLE_TODO + " WHERE "
            + KEY_ID + " = " + todo_id;

    Log.e(LOG, selectQuery);

    Cursor c = db.rawQuery(selectQuery, null);

    if (c != null)
        c.moveToFirst();

    Todo td = new Todo();
    td.setId(c.getInt(c.getColumnIndex(KEY_ID)));
    td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
    td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

    return td;
}

Search for type SELECT * FROM todos;

/*
 * getting all todos
 * */
public List<Todo> getAllToDos() {
    List<Todo> todos = new ArrayList<Todo>();
    String selectQuery = "SELECT  * FROM " + TABLE_TODO;

    Log.e(LOG, selectQuery);

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (c.moveToFirst()) {
        do {
            Todo td = new Todo();
            td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
            td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
            td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

            // adding to todo list
            todos.add(td);
        } while (c.moveToNext());
    }

    return todos;
}

Update :

/*
 * Updating a todo
 */
public int updateToDo(Todo todo) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_TODO, todo.getNote());
    values.put(KEY_STATUS, todo.getStatus());

    // updating row
    return db.update(TABLE_TODO, values, KEY_ID + " = ?",
            new String[] { String.valueOf(todo.getId()) });
}

Delete :

/*
 * Deleting a todo
 */
public void deleteToDo(long tado_id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_TODO, KEY_ID + " = ?",
            new String[] { String.valueOf(tado_id) });
}

The downside is that it is still in development and bugs can be found. According to this site : "If you have a more complex database with more than that just text fields and integers, or if you have to implement a database migration, which can not be done by simple SQL statements, you're unfortunately lost. Anything besides access to database objects simple data will result in writing manual code with limited SQL statements. "