http://hmkcode.com/android-simple-sqlite-database-tutorial/
http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/
http://chintankhetiya.wordpress.com/2013/06/01/sqlite-database-example/
http://www.techotopia.com/index.php/An_Android_SQLite_Database_Tutorial <<== this is perfect
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
( 3 ) Object Model “Book.java”
- Create one Java bean class: Book.java
- /src/com/hmkcode/android/model/Book.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 ofSQLiteOpenHelper 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | 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
- addBook(Book book)
- getBook(int id)
- getAllBooks()
- update(Book book)
- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 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
LogCat view