android 數據庫sqllite示例

這篇文章寫得好, 行文簡潔, 清晰, 內容詳盡. 一個例子可將你一腳踢進android數據庫大門. 剩下的就是搭積木了.

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 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

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

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.

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

 

轉自http://hmkcode.com/android-simple-sqlite-database-tutorial/