Database - Android Simple Clean Good SQLite Database Tutorial 잘된 설명

글 수 67

Android Simple Clean Good SQLite Database Tutorial 잘된 설명

admin

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

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

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.

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 ( " +
                

네트워킹