Android 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
( 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
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 ap
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;
}
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
No comments:
Post a Comment