Android | Simple SQLite Database Tutorial

android-sqliteAndroid SQLite database is an integral part “built-in” component. Any databases you create will be accessible by name to any class in the application, but not outside the application. Here we will see how to use SQLite API to perform database common operations.

 

Objectives:

  • How to start using SQLite API?
  • How to create new database & database tables?
  • How to perform CRUD “Create, Read, Update and Delete” operations?

Environment & Tools:

  • Android Developer Tools (ADT) (or Eclipse + ADT plugin)
  • AVD Nexus S Android 4.3 “emulator”
  • Min SDK 8

What we are building here?

We will build an App that can store & retrieve books title and author name.

( 1 ) Create Android Application

  • File >> New >> Android Application
  • Enter App name: SQLite App
  • Enter Project name: android-sqlite
  • Pakcage: com.hmkcode.android
  • Keep other defualt selections, click Next until you reach Finish

( 2 ) Data Model Design “Table Structure”

We want to create the following:

  • One Database instance:BookDB“.
  • One Table: “books” with three columns id, title & author

android-sqlite-table-books

( 3 ) Object Model “Book.java”

  • Create one Java bean class: Book.java
  • /src/com/hmkcode/android/model/Book.java
package com.hmkcode.android.model;

public class Book {

	private int id;
	private String title;
	private String author;

	public Book(){}

	public Book(String title, String author) {
		super();
		this.title = title;
		this.author = author;
	}

	//getters & setters

	@Override
	public String toString() {
		return "Book [id=" + id + ", title=" + title + ", author=" + author
				+ "]";
	}
}

( 4 ) extends SQLiteOpenHelper

The recommended method to create a new SQLite database is to create a subclass of SQLiteOpenHelper and override the onCreate() method, in which you can execute a SQLite command to create tables in the database.

This is the main step.

  • Create a new class MySQLiteHelper extends SQLiteOpenHelper.
  • MySQLiteHelper constructor must call the super class constructor.
  • Override onCreate() method to create the table(s)
  • Override onUpgrade() to drop old tables and create new ones.
  • /src/com/hmkcode/android/sqlite/MySQLiteHelper.java
package com.hmkcode.android.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MySQLiteHelper extends SQLiteOpenHelper {

	// Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = "BookDB";

	public MySQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);	
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// SQL statement to create book table
		String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
				"title TEXT, "+
				"author TEXT )";

		// create books table
		db.execSQL(CREATE_BOOK_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older books table if existed
        db.execSQL("DROP TABLE IF EXISTS books");

        // create fresh books table
        this.onCreate(db);
	}

}
  • This class MySQLiteHelper will just create for us Database “BookDB” with one empty table “books“.
  • Next, we will create methods to help us populate “insert”, read “select”, update and delete book(s) from this table.

( 5 ) Add, Get, Update & Delete a Book

  • In the same file “MySQLiteHelper.java” we will add 5 methods
  1. addBook(Book book)
  2. getBook(int id)
  3. getAllBooks()
  4. update(Book book)
  5. delete(Book book)
  • Some static constants 

Define static constants for table & columns names;

    // Books table name
    private static final String TABLE_BOOKS = "books";

    // Books Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_AUTHOR = "author";

    private static final String[] COLUMNS = {KEY_ID,KEY_TITLE,KEY_AUTHOR};

1. addBook(Book book)

Notice:

  • ConentValues this class is used to store a set of values.
  • Log.d() just for logging, so we can see the result later on LogCat when we run the App.
public void addBook(Book book){
                //for logging
		Log.d("addBook", book.toString()); 

		// 1. get reference to writable DB
		SQLiteDatabase db = this.getWritableDatabase();

		// 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, book.getTitle()); // get title 
        values.put(KEY_AUTHOR, book.getAuthor()); // get author

        // 3. insert
        db.insert(TABLE_BOOKS, // table
        		null, //nullColumnHack
        		values); // key/value -> keys = column names/ values = column values

        // 4. close
        db.close(); 
	}

2. getBook(int id)

	public Book getBook(int id){

		// 1. get reference to readable DB
		SQLiteDatabase db = this.getReadableDatabase();

		// 2. build query
        Cursor cursor = 
        		db.query(TABLE_BOOKS, // a. table
        		COLUMNS, // b. column names
        		" id = ?", // c. selections 
                new String[] { String.valueOf(id) }, // d. selections args
                null, // e. group by
                null, // f. having
                null, // g. order by
                null); // h. limit

        // 3. if we got results get the first one
        if (cursor != null)
            cursor.moveToFirst();

        // 4. build book object
        Book book = new Book();
        book.setId(Integer.parseInt(cursor.getString(0)));
        book.setTitle(cursor.getString(1));
        book.setAuthor(cursor.getString(2));

        //log 
	Log.d("getBook("+id+")", book.toString());

        // 5. return book
        return book;
	}

3. getAllBooks()

 public List<Book> getAllBooks() {
        List<Book> books = new LinkedList<Book>();

        // 1. build the query
        String query = "SELECT  * FROM " + TABLE_BOOKS;

    	// 2. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);

        // 3. go over each row, build book and add it to list
        Book book = null;
        if (cursor.moveToFirst()) {
            do {
            	book = new Book();
                book.setId(Integer.parseInt(cursor.getString(0)));
                book.setTitle(cursor.getString(1));
                book.setAuthor(cursor.getString(2));

                // Add book to books
                books.add(book);
            } while (cursor.moveToNext());
        }

		Log.d("getAllBooks()", books.toString());

        // return books
        return books;
    }

4. update(Book book)

    public int updateBook(Book book) {

    	// 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

		// 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put("title", book.getTitle()); // get title 
        values.put("author", book.getAuthor()); // get author

        // 3. updating row
        int i = db.update(TABLE_BOOKS, //table
        		values, // column/value
        		KEY_ID+" = ?", // selections
                new String[] { String.valueOf(book.getId()) }); //selection args

        // 4. close
        db.close();

        return i;

    }

5. delete(Book book)

public void deleteBook(Book book) {

    	// 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

        // 2. delete
        db.delete(TABLE_BOOKS, //table name
        		KEY_ID+" = ?",  // selections
                new String[] { String.valueOf(book.getId()) }); //selections args

        // 3. close
        db.close();

        //log
	Log.d("deleteBook", book.toString());

    }

Complete MySQLiteHelper.java Code:

package com.hmkcode.android.sqlite;

import java.util.LinkedList;
import java.util.List;

import com.hmkcode.android.model.Book;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

	// Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = "BookDB";

	public MySQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);	
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// SQL statement to create book table
		String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
				"title TEXT, "+
				"author TEXT )";

		// create books table
		db.execSQL(CREATE_BOOK_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older books table if existed
        db.execSQL("DROP TABLE IF EXISTS books");

        // create fresh books table
        this.onCreate(db);
	}
	//---------------------------------------------------------------------

	/**
     * CRUD operations (create "add", read "get", update, delete) book + get all books + delete all books
     */

	// Books table name
    private static final String TABLE_BOOKS = "books";

    // Books Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_AUTHOR = "author";

    private static final String[] COLUMNS = {KEY_ID,KEY_TITLE,KEY_AUTHOR};

	public void addBook(Book book){
		Log.d("addBook", book.toString());
		// 1. get reference to writable DB
		SQLiteDatabase db = this.getWritableDatabase();

		// 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, book.getTitle()); // get title 
        values.put(KEY_AUTHOR, book.getAuthor()); // get author

        // 3. insert
        db.insert(TABLE_BOOKS, // table
        		null, //nullColumnHack
        		values); // key/value -> keys = column names/ values = column values

        // 4. close
        db.close(); 
	}

	public Book getBook(int id){

		// 1. get reference to readable DB
		SQLiteDatabase db = this.getReadableDatabase();

		// 2. build query
        Cursor cursor = 
        		db.query(TABLE_BOOKS, // a. table
        		COLUMNS, // b. column names
        		" id = ?", // c. selections 
                new String[] { String.valueOf(id) }, // d. selections args
                null, // e. group by
                null, // f. having
                null, // g. order by
                null); // h. limit

        // 3. if we got results get the first one
        if (cursor != null)
            cursor.moveToFirst();

        // 4. build book object
        Book book = new Book();
        book.setId(Integer.parseInt(cursor.getString(0)));
        book.setTitle(cursor.getString(1));
        book.setAuthor(cursor.getString(2));

		Log.d("getBook("+id+")", book.toString());

        // 5. return book
        return book;
	}

	// Get All Books
    public List<Book> getAllBooks() {
        List<Book> books = new LinkedList<Book>();

        // 1. build the query
        String query = "SELECT  * FROM " + TABLE_BOOKS;

    	// 2. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);

        // 3. go over each row, build book and add it to list
        Book book = null;
        if (cursor.moveToFirst()) {
            do {
            	book = new Book();
                book.setId(Integer.parseInt(cursor.getString(0)));
                book.setTitle(cursor.getString(1));
                book.setAuthor(cursor.getString(2));

                // Add book to books
                books.add(book);
            } while (cursor.moveToNext());
        }

		Log.d("getAllBooks()", books.toString());

        // return books
        return books;
    }

	 // Updating single book
    public int updateBook(Book book) {

    	// 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

		// 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put("title", book.getTitle()); // get title 
        values.put("author", book.getAuthor()); // get author

        // 3. updating row
        int i = db.update(TABLE_BOOKS, //table
        		values, // column/value
        		KEY_ID+" = ?", // selections
                new String[] { String.valueOf(book.getId()) }); //selection args

        // 4. close
        db.close();

        return i;

    }

    // Deleting single book
    public void deleteBook(Book book) {

    	// 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

        // 2. delete
        db.delete(TABLE_BOOKS,
        		KEY_ID+" = ?",
                new String[] { String.valueOf(book.getId()) });

        // 3. close
        db.close();

		Log.d("deleteBook", book.toString());

    }
}

( 6 ) Using MySQLiteHelper in Activity

  • /src/com/hmkcode/android/MainActivity.java
package com.hmkcode.android;

import java.util.List;
import com.hmkcode.android.model.Book;
import com.hmkcode.android.sqlite.MySQLiteHelper;
import android.os.Bundle;
import android.app.Activity;

public class MainActivity extends Activity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);

		MySQLiteHelper db = new MySQLiteHelper(this);

        /**
         * CRUD Operations
         * */
        // add Books
        db.addBook(new Book("Android Application Development Cookbook", "Wei Meng Lee"));   
        db.addBook(new Book("Android Programming: The Big Nerd Ranch Guide", "Bill Phillips and Brian Hardy"));       
        db.addBook(new Book("Learn Android App Development", "Wallace Jackson"));

        // get all books
        List<Book> list = db.getAllBooks();

        // delete one book
        db.deleteBook(list.get(0));

        // get all books
        db.getAllBooks();

	}

}
  • Deploy the App on the emulator and run it.
  • Look for the results in LogCat!

( 7 ) Where to See the Results? LogCat!

  • Log function Log.d() used within the addBook(), getBook()…etc. will log database operations on LogCat Eclipse (ADT) window.
  • If you don’t see LogCat window, you need to add it.
  • go to Window >> Show View >> Others… (on Show View window) Android >> LogCat

android-logcat

LogCat view

Source Code @ GitHub

Related posts from Android Tutorial

One thought on “Android | Simple SQLite Database Tutorial

  1. Ferdinando Cecchini

    Very interesting post!
    I’ve a question: is there any method to save the SQLite db on a remote server (for backup purpose) or import the data from a remote server instead of insert records directly from the application?
    Thanks

Comments are closed.