한국어

네트워킹

온누리070 플레이스토어 다운로드
    acrobits softphone
     온누리 070 카카오 프러스 친구추가온누리 070 카카오 프러스 친구추가친추
     카카오톡 채팅 상담 카카오톡 채팅 상담카톡
    
     라인상담
     라인으로 공유

    
공유하기
페북공유

   ◎위챗 : speedseoul


  
     PAYPAL
     
     PRICE
     

pixel.gif

    before pay call 0088 from app
글 수 67

Android SQLite Database with Multiple Tables

admin

http://www.androidhive.info/2013/09/android-sqlite-database-with-multiple-tables/




In my previous tutorial Android SQLite Database Tutorial I explained how to use SQLite database in your android application. But that covered the scenario, only when you have one table in the database. I am getting lot of queries about handling the sqlite database when it is having multiple tables.

I explained here how to handle the SQLite database when it is having multiple tables.

DOWNLOAD CODE


Use Case: Todo Application

To make it easier for you to understand, I am taking a real use case example of TODO Applicationdatabase schema in this tutorial. This article doesn’t covers how to design the application, but explains the database design, preparing database helper classes and models.


Database Design

I considered a basic Todo Application with minimal functionality like creating a todo note andassigning it under a tag(s) (category). So for this we just need three tables in the database.

The three tables are

todos – to store all todo notes
tags – to store list of tags
todo_tags – to store the tags which are assigned to a todo



Check the below diagram that explains the table structure and the relationship between tables

android sqlite with multiple tables


Let’s start a new Project

So let’s start by creating a new project in Eclipse IDE

1. Create a new project in Eclipse from File ⇒ New ⇒ Android ⇒ Application Project. I named my package name as info.androidhive.sqlite and left the main activity name as MainActivity.java

2. We need two more packages to keep helpers and model classes. Right Clicking on src ⇒ New ⇒ Package and name them as info.androidhive.sqlite.helper andinfo.androidhive.sqlite.model

Creating Model Class for Tables

Next step is to create model classes for our database tables just to make single row as an object. We need only two models for todos and tags. For todo_tags we don’t need a model class.

3. Create a new class file under info.androidhive.sqlite.helper package named Todo.java and type the code like below. This is the model class for todos table

Todo.java
package info.androidhive.sqlite.model;
 
public class Todo {
 
    int id;
    String note;
    int status;
    String created_at;
 
    // constructors
    public Todo() {
    }
 
    public Todo(String note, int status) {
        this.note = note;
        this.status = status;
    }
 
    public Todo(int id, String note, int status) {
        this.id = id;
        this.note = note;
        this.status = status;
    }
 
    // setters
    public void setId(int id) {
        this.id = id;
    }
 
    public void setNote(String note) {
        this.note = note;
    }
 
    public void setStatus(int status) {
        this.status = status;
    }
     
    public void setCreatedAt(String created_at){
        this.created_at = created_at;
    }
 
    // getters
    public long getId() {
        return this.id;
    }
 
    public String getNote() {
        return this.note;
    }
 
    public int getStatus() {
        return this.status;
    }
}

4. Create one more model class for tags table named Tag.java under the same package.

Tag.java
package info.androidhive.sqlite.model;
 
public class Tag {
 
    int id;
    String tag_name;
 
    // constructors
    public Tag() {
 
    }
 
    public Tag(String tag_name) {
        this.tag_name = tag_name;
    }
 
    public Tag(int id, String tag_name) {
        this.id = id;
        this.tag_name = tag_name;
    }
 
    // setter
    public void setId(int id) {
        this.id = id;
    }
 
    public void setTagName(String tag_name) {
        this.tag_name = tag_name;
    }
 
    // getter
    public int getId() {
        return this.id;
    }
 
    public String getTagName() {
        return this.tag_name;
    }
}


Database Helper Class

Database helper class contains all the methods to perform database operations like opening connection, closing connection, insert, update, read, delete and other things. As this class is helper class, place this under helper package.

5. So create another class named DatabaseHelper.java underinfo.androidhive.sqlite.helper package and extend the class from SQLiteOpenHelper

public class DatabaseHelper extends SQLiteOpenHelper {

6. Add required variables like database name, database version, column names. I also executed table create statements in onCreate() method. Type the following code in DatabaseHelper.java class

DatabaseHelper.java
public class DatabaseHelper extends SQLiteOpenHelper {
 
    // Logcat tag
    private static final String LOG = "DatabaseHelper";
 
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "contactsManager";
 
    // Table Names
    private static final String TABLE_TODO = "todos";
    private static final String TABLE_TAG = "tags";
    private static final String TABLE_TODO_TAG = "todo_tags";
 
    // Common column names
    private static final String KEY_ID = "id";
    private static final String KEY_CREATED_AT = "created_at";
 
    // NOTES Table - column nmaes
    private static final String KEY_TODO = "todo";
    private static final String KEY_STATUS = "status";
 
    // TAGS Table - column names
    private static final String KEY_TAG_NAME = "tag_name";
 
    // NOTE_TAGS Table - column names
    private static final String KEY_TODO_ID = "todo_id";
    private static final String KEY_TAG_ID = "tag_id";
 
    // Table Create Statements
    // Todo table create statement
    private static final String CREATE_TABLE_TODO = "CREATE TABLE "
            + TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO
            + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
            + " DATETIME" + ")";
 
    // Tag table create statement
    private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG
            + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
            + KEY_CREATED_AT + " DATETIME" + ")";
 
    // todo_tag table create statement
    private static final String CREATE_TABLE_TODO_TAG = "CREATE TABLE "
            + TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
            + KEY_CREATED_AT + " DATETIME" + ")";
 
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
 
        // creating required tables
        db.execSQL(CREATE_TABLE_TODO);
        db.execSQL(CREATE_TABLE_TAG);
        db.execSQL(CREATE_TABLE_TODO_TAG);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO_TAG);
 
        // create new tables
        onCreate(db);
    }


CRUD (Create, Read, Update and Delete) Operations

From now on we are going to add one by one method into DatabaseHelper.class


1. Creating a Todo

The function will create a todo item in todos table. In this same function we are assigning the todo to a tag name which inserts a row in todo_tags table.

/*
 * Creating a todo
 */
public long createToDo(Todo todo, long[] tag_ids) {
    SQLiteDatabase db = this.getWritableDatabase();
 
    ContentValues values = new ContentValues();
    values.put(KEY_TODO, todo.getNote());
    values.put(KEY_STATUS, todo.getStatus());
    values.put(KEY_CREATED_AT, getDateTime());
 
    // insert row
    long todo_id = db.insert(TABLE_TODO, null, values);
 
    // assigning tags to todo
    for (long tag_id : tag_ids) {
        createTodoTag(todo_id, tag_id);
    }
 
    return todo_id;
}


2. Fetching a Todo

Following will fetch a todo from todos table.

SELECT * FROM todos WHERE id = 1;
/*
 * get single todo
 */
public Todo getTodo(long todo_id) {
    SQLiteDatabase db = this.getReadableDatabase();
 
    String selectQuery = "SELECT  * FROM " + TABLE_TODO + " WHERE "
            + KEY_ID + " = " + todo_id;
 
    Log.e(LOG, selectQuery);
 
    Cursor c = db.rawQuery(selectQuery, null);
 
    if (c != null)
        c.moveToFirst();
 
    Todo td = new Todo();
    td.setId(c.getInt(c.getColumnIndex(KEY_ID)));
    td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
    td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
 
    return td;
}


3. Fetching all Todos

Fetching all todos involves reading all todo rows and adding them to a list array.

SELECT * FROM todos;
/*
 * getting all todos
 * */
public List<Todo> getAllToDos() {
    List<Todo> todos = new ArrayList<Todo>();
    String selectQuery = "SELECT  * FROM " + TABLE_TODO;
 
    Log.e(LOG, selectQuery);
 
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);
 
    // looping through all rows and adding to list
    if (c.moveToFirst()) {
        do {
            Todo td = new Todo();
            td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
            td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
            td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
 
            // adding to todo list
            todos.add(td);
        } while (c.moveToNext());
    }
 
    return todos;
}


4. Fetching all Todos under a Tag name

This is also same as reading all the rows but it filters the todos by tag name. Check the following select query which fetches the todos under Watchlist tag name.

SELECT * FROM todos td, tags tg, todo_tags tt WHERE tg.tag_name = ‘Watchlist’ AND tg.id = tt.tag_id AND td.id = tt.todo_id;
/*
 * getting all todos under single tag
 * */
public List<Todo> getAllToDosByTag(String tag_name) {
    List<Todo> todos = new ArrayList<Todo>();
 
    String selectQuery = "SELECT  * FROM " + TABLE_TODO + " td, "
            + TABLE_TAG + " tg, " + TABLE_TODO_TAG + " tt WHERE tg."
            + KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
            + " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
            + "tt." + KEY_TODO_ID;
 
    Log.e(LOG, selectQuery);
 
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);
 
    // looping through all rows and adding to list
    if (c.moveToFirst()) {
        do {
            Todo td = new Todo();
            td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
            td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
            td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
 
            // adding to todo list
            todos.add(td);
        } while (c.moveToNext());
    }
 
    return todos;
}


5. Updating a Todo

Following function will update a todo. It will update Todo values only, not the tag name.

/*
 * Updating a todo
 */
public int updateToDo(Todo todo) {
    SQLiteDatabase db = this.getWritableDatabase();
 
    ContentValues values = new ContentValues();
    values.put(KEY_TODO, todo.getNote());
    values.put(KEY_STATUS, todo.getStatus());
 
    // updating row
    return db.update(TABLE_TODO, values, KEY_ID + " = ?",
            new String[] { String.valueOf(todo.getId()) });
}


6. Deleting a Todo

Pass todo id to the following function to delete the todo from db.

/*
 * Deleting a todo
 */
public void deleteToDo(long tado_id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_TODO, KEY_ID + " = ?",
            new String[] { String.valueOf(tado_id) });
}



Until now we are done creating the CRUD methods onto todos table. Now we can start the methods required on tags table.

7. Creating Tag

Following method will insert a row into tags table.

/*
 * Creating tag
 */
public long createTag(Tag tag) {
    SQLiteDatabase db = this.getWritableDatabase();
 
    ContentValues values = new ContentValues();
    values.put(KEY_TAG_NAME, tag.getTagName());
    values.put(KEY_CREATED_AT, getDateTime());
 
    // insert row
    long tag_id = db.insert(TABLE_TAG, null, values);
 
    return tag_id;
}


8. Fetching all Tag names

Performing select all statement on tags table will give you list of tag names.

SELECT * FROM tags;
/**
 * getting all tags
 * */
public List<Tag> getAllTags() {
    List<Tag> tags = new ArrayList<Tag>();
    String selectQuery = "SELECT  * FROM " + TABLE_TAG;
 
    Log.e(LOG, selectQuery);
 
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);
 
    // looping through all rows and adding to list
    if (c.moveToFirst()) {
        do {
            Tag t = new Tag();
            t.setId(c.getInt((c.getColumnIndex(KEY_ID))));
            t.setTagName(c.getString(c.getColumnIndex(KEY_TAG_NAME)));
 
            // adding to tags list
            tags.add(t);
        } while (c.moveToNext());
    }
    return tags;
}


9. Updating Tags

Following method will update tag.

/*
 * Updating a tag
 */
public int updateTag(Tag tag) {
    SQLiteDatabase db = this.getWritableDatabase();
 
    ContentValues values = new ContentValues();
    values.put(KEY_TAG_NAME, tag.getTagName());
 
    // updating row
    return db.update(TABLE_TAG, values, KEY_ID + " = ?",
            new String[] { String.valueOf(tag.getId()) });
}


10. Deleting Tag and Todos under the Tag name

Following method will delete a tag from db. This also will delete all the todos under the tag name, but this is optional.

should_delete_all_tag_todos = Passing true will delete all the todos under the tag name

/*
 * Deleting a tag
 */
public void deleteTag(Tag tag, boolean should_delete_all_tag_todos) {
    SQLiteDatabase db = this.getWritableDatabase();
 
    // before deleting tag
    // check if todos under this tag should also be deleted
    if (should_delete_all_tag_todos) {
        // get all todos under this tag
        List<Todo> allTagToDos = getAllToDosByTag(tag.getTagName());
 
        // delete all todos
        for (Todo todo : allTagToDos) {
            // delete todo
            deleteToDo(todo.getId());
        }
    }
 
    // now delete the tag
    db.delete(TABLE_TAG, KEY_ID + " = ?",
            new String[] { String.valueOf(tag.getId()) });
}



Below are the methods to access the rows from todo_tags table

11. Assigning a Tag to Todo

Following method will assign a todo under a tag name. You can also assign multiple tags to a todo by calling this function multiple times.

/*
     * Creating todo_tag
     */
    public long createTodoTag(long todo_id, long tag_id) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TODO_ID, todo_id);
        values.put(KEY_TAG_ID, tag_id);
        values.put(KEY_CREATED_AT, getDateTime());
 
        long id = db.insert(TABLE_TODO_TAG, null, values);
 
        return id;
    }


12. Removing Tag of Todo

Following method will remove the tag assigned to a todo

/*
 * Updating a todo tag
 */
public int updateNoteTag(long id, long tag_id) {
    SQLiteDatabase db = this.getWritableDatabase();
 
    ContentValues values = new ContentValues();
    values.put(KEY_TAG_ID, tag_id);
 
    // updating row
    return db.update(TABLE_TODO, values, KEY_ID + " = ?",
            new String[] { String.valueOf(id) });
}


13. Changing the tag of todo

Following simply replaces the tag name of a todo

/*
 * Updating a todo tag
 */
public int updateNoteTag(long id, long tag_id) {
    SQLiteDatabase db = this.getWritableDatabase();
 
    ContentValues values = new ContentValues();
    values.put(KEY_TAG_ID, tag_id);
 
    // updating row
    return db.update(TABLE_TODO, values, KEY_ID + " = ?",
            new String[] { String.valueOf(id) });
}


14. Closing Database Connection

Importantly don’t forget to close the database connection once you done using it. Call following method when you don’t need access to db anymore.

// closing database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }


How to Use / Testing

As this tutorial already seems lengthy I am not considering giving an example with a sample application. In upcoming tutorial I will give you a simple todo application which will give you complete picture of using multiple SQLite tables in your android apps.

For now we will test the class just by printing the data to Logcat.

Open your main activity class and type the following. In the below I just created sample tags and todo data and performed the all the operations by calling the methods which we prepared in DatabaseHelper class.

MainActivity.java
package info.androidhive.sqlite;
 
import info.androidhive.sqlite.helper.DatabaseHelper;
import info.androidhive.sqlite.model.Tag;
import info.androidhive.sqlite.model.Todo;
 
import java.util.List;
 
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
 
public class MainActivity extends Activity {
 
    // Database Helper
    DatabaseHelper db;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        db = new DatabaseHelper(getApplicationContext());
 
        // Creating tags
        Tag tag1 = new Tag("Shopping");
        Tag tag2 = new Tag("Important");
        Tag tag3 = new Tag("Watchlist");
        Tag tag4 = new Tag("Androidhive");
 
        // Inserting tags in db
        long tag1_id = db.createTag(tag1);
        long tag2_id = db.createTag(tag2);
        long tag3_id = db.createTag(tag3);
        long tag4_id = db.createTag(tag4);
 
        Log.d("Tag Count", "Tag Count: " + db.getAllTags().size());
 
        // Creating ToDos
        Todo todo1 = new Todo("iPhone 5S", 0);
        Todo todo2 = new Todo("Galaxy Note II", 0);
        Todo todo3 = new Todo("Whiteboard", 0);
 
        Todo todo4 = new Todo("Riddick", 0);
        Todo todo5 = new Todo("Prisoners", 0);
        Todo todo6 = new Todo("The Croods", 0);
        Todo todo7 = new Todo("Insidious: Chapter 2", 0);
 
        Todo todo8 = new Todo("Don't forget to call MOM", 0);
        Todo todo9 = new Todo("Collect money from John", 0);
 
        Todo todo10 = new Todo("Post new Article", 0);
        Todo todo11 = new Todo("Take database backup", 0);
 
        // Inserting todos in db
        // Inserting todos under "Shopping" Tag
        long todo1_id = db.createToDo(todo1, new long[] { tag1_id });
        long todo2_id = db.createToDo(todo2, new long[] { tag1_id });
        long todo3_id = db.createToDo(todo3, new long[] { tag1_id });
 
        // Inserting todos under "Watchlist" Tag
        long todo4_id = db.createToDo(todo4, new long[] { tag3_id });
        long todo5_id = db.createToDo(todo5, new long[] { tag3_id });
        long todo6_id = db.createToDo(todo6, new long[] { tag3_id });
        long todo7_id = db.createToDo(todo7, new long[] { tag3_id });
 
        // Inserting todos under "Important" Tag
        long todo8_id = db.createToDo(todo8, new long[] { tag2_id });
        long todo9_id = db.createToDo(todo9, new long[] { tag2_id });
 
        // Inserting todos under "Androidhive" Tag
        long todo10_id = db.createToDo(todo10, new long[] { tag4_id });
        long todo11_id = db.createToDo(todo11, new long[] { tag4_id });
 
        Log.e("Todo Count", "Todo count: " + db.getToDoCount());
 
        // "Post new Article" - assigning this under "Important" Tag
        // Now this will have - "Androidhive" and "Important" Tags
        db.createTodoTag(todo10_id, tag2_id);
 
        // Getting all tag names
        Log.d("Get Tags", "Getting All Tags");
 
        List<Tag> allTags = db.getAllTags();
        for (Tag tag : allTags) {
            Log.d("Tag Name", tag.getTagName());
        }
 
        // Getting all Todos
        Log.d("Get Todos", "Getting All ToDos");
 
        List<Todo> allToDos = db.getAllToDos();
        for (Todo todo : allToDos) {
            Log.d("ToDo", todo.getNote());
        }
 
        // Getting todos under "Watchlist" tag name
        Log.d("ToDo", "Get todos under single Tag name");
 
        List<Todo> tagsWatchList = db.getAllToDosByTag(tag3.getTagName());
        for (Todo todo : tagsWatchList) {
            Log.d("ToDo Watchlist", todo.getNote());
        }
 
        // Deleting a ToDo
        Log.d("Delete ToDo", "Deleting a Todo");
        Log.d("Tag Count", "Tag Count Before Deleting: " + db.getToDoCount());
 
        db.deleteToDo(todo8_id);
 
        Log.d("Tag Count", "Tag Count After Deleting: " + db.getToDoCount());
 
        // Deleting all Todos under "Shopping" tag
        Log.d("Tag Count",
                "Tag Count Before Deleting 'Shopping' Todos: "
                        + db.getToDoCount());
 
        db.deleteTag(tag1, true);
 
        Log.d("Tag Count",
                "Tag Count After Deleting 'Shopping' Todos: "
                        + db.getToDoCount());
 
        // Updating tag name
        tag3.setTagName("Movies to watch");
        db.updateTag(tag3);
 
        // Don't forget to close database connection
        db.closeDB();
         
    }
}

Run the application and the check the Logcat.


Complete Code of DatabaseHelper.java Class

DatabaseHelper.java
package info.androidhive.sqlite.helper;
 
import info.androidhive.sqlite.model.Tag;
import info.androidhive.sqlite.model.Todo;
 
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
 
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 DatabaseHelper extends SQLiteOpenHelper {
 
    // Logcat tag
    private static final String LOG = DatabaseHelper.class.getName();
 
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "contactsManager";
 
    // Table Names
    private static final String TABLE_TODO = "todos";
    private static final String TABLE_TAG = "tags";
    private static final String TABLE_TODO_TAG = "todo_tags";
 
    // Common column names
    private static final String KEY_ID = "id";
    private static final String KEY_CREATED_AT = "created_at";
 
    // NOTES Table - column nmaes
    private static final String KEY_TODO = "todo";
    private static final String KEY_STATUS = "status";
 
    // TAGS Table - column names
    private static final String KEY_TAG_NAME = "tag_name";
 
    // NOTE_TAGS Table - column names
    private static final String KEY_TODO_ID = "todo_id";
    private static final String KEY_TAG_ID = "tag_id";
 
    // Table Create Statements
    // Todo table create statement
    private static final String CREATE_TABLE_TODO = "CREATE TABLE "
            + TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO
            + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
            + " DATETIME" + ")";
 
    // Tag table create statement
    private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG
            + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
            + KEY_CREATED_AT + " DATETIME" + ")";
 
    // todo_tag table create statement
    private static final String CREATE_TABLE_TODO_TAG = "CREATE TABLE "
            + TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
            + KEY_CREATED_AT + " DATETIME" + ")";
 
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
 
        // creating required tables
        db.execSQL(CREATE_TABLE_TODO);
        db.execSQL(CREATE_TABLE_TAG);
        db.execSQL(CREATE_TABLE_TODO_TAG);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO_TAG);
 
        // create new tables
        onCreate(db);
    }
 
    // ------------------------ "todos" table methods ----------------//
 
     /**
     * Creating a todo
     */
    public long createToDo(Todo todo, long[] tag_ids) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TODO, todo.getNote());
        values.put(KEY_STATUS, todo.getStatus());
        values.put(KEY_CREATED_AT, getDateTime());
 
        // insert row
        long todo_id = db.insert(TABLE_TODO, null, values);
 
        // insert tag_ids
        for (long tag_id : tag_ids) {
            createTodoTag(todo_id, tag_id);
        }
 
        return todo_id;
    }
 
    /**
     * get single todo
     */
    public Todo getTodo(long todo_id) {
        SQLiteDatabase db = this.getReadableDatabase();
 
        String selectQuery = "SELECT  * FROM " + TABLE_TODO + " WHERE "
                + KEY_ID + " = " + todo_id;
 
        Log.e(LOG, selectQuery);
 
        Cursor c = db.rawQuery(selectQuery, null);
 
        if (c != null)
            c.moveToFirst();
 
        Todo td = new Todo();
        td.setId(c.getInt(c.getColumnIndex(KEY_ID)));
        td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
        td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
 
        return td;
    }
 
    /**
     * getting all todos
     * */
    public List<Todo> getAllToDos() {
        List<Todo> todos = new ArrayList<Todo>();
        String selectQuery = "SELECT  * FROM " + TABLE_TODO;
 
        Log.e(LOG, selectQuery);
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                Todo td = new Todo();
                td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
                td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
 
                // adding to todo list
                todos.add(td);
            } while (c.moveToNext());
        }
 
        return todos;
    }
 
    /**
     * getting all todos under single tag
     * */
    public List<Todo> getAllToDosByTag(String tag_name) {
        List<Todo> todos = new ArrayList<Todo>();
 
        String selectQuery = "SELECT  * FROM " + TABLE_TODO + " td, "
                + TABLE_TAG + " tg, " + TABLE_TODO_TAG + " tt WHERE tg."
                + KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
                + " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
                + "tt." + KEY_TODO_ID;
 
        Log.e(LOG, selectQuery);
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                Todo td = new Todo();
                td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
                td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
 
                // adding to todo list
                todos.add(td);
            } while (c.moveToNext());
        }
 
        return todos;
    }
 
    /**
     * getting todo count
     */
    public int getToDoCount() {
        String countQuery = "SELECT  * FROM " + TABLE_TODO;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
 
        int count = cursor.getCount();
        cursor.close();
 
        // return count
        return count;
    }
 
    /**
     * Updating a todo
     */
    public int updateToDo(Todo todo) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TODO, todo.getNote());
        values.put(KEY_STATUS, todo.getStatus());
 
        // updating row
        return db.update(TABLE_TODO, values, KEY_ID + " = ?",
                new String[] { String.valueOf(todo.getId()) });
    }
 
    /**
     * Deleting a todo
     */
    public void deleteToDo(long tado_id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_TODO, KEY_ID + " = ?",
                new String[] { String.valueOf(tado_id) });
    }
 
    // ------------------------ "tags" table methods ----------------//
 
    /**
     * Creating tag
     */
    public long createTag(Tag tag) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TAG_NAME, tag.getTagName());
        values.put(KEY_CREATED_AT, getDateTime());
 
        // insert row
        long tag_id = db.insert(TABLE_TAG, null, values);
 
        return tag_id;
    }
 
    /**
     * getting all tags
     * */
    public List<Tag> getAllTags() {
        List<Tag> tags = new ArrayList<Tag>();
        String selectQuery = "SELECT  * FROM " + TABLE_TAG;
 
        Log.e(LOG, selectQuery);
 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                Tag t = new Tag();
                t.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                t.setTagName(c.getString(c.getColumnIndex(KEY_TAG_NAME)));
 
                // adding to tags list
                tags.add(t);
            } while (c.moveToNext());
        }
        return tags;
    }
 
    /**
     * Updating a tag
     */
    public int updateTag(Tag tag) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TAG_NAME, tag.getTagName());
 
        // updating row
        return db.update(TABLE_TAG, values, KEY_ID + " = ?",
                new String[] { String.valueOf(tag.getId()) });
    }
 
    /**
     * Deleting a tag
     */
    public void deleteTag(Tag tag, boolean should_delete_all_tag_todos) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        // before deleting tag
        // check if todos under this tag should also be deleted
        if (should_delete_all_tag_todos) {
            // get all todos under this tag
            List<Todo> allTagToDos = getAllToDosByTag(tag.getTagName());
 
            // delete all todos
            for (Todo todo : allTagToDos) {
                // delete todo
                deleteToDo(todo.getId());
            }
        }
 
        // now delete the tag
        db.delete(TABLE_TAG, KEY_ID + " = ?",
                new String[] { String.valueOf(tag.getId()) });
    }
 
    // ------------------------ "todo_tags" table methods ----------------//
 
    /**
     * Creating todo_tag
     */
    public long createTodoTag(long todo_id, long tag_id) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TODO_ID, todo_id);
        values.put(KEY_TAG_ID, tag_id);
        values.put(KEY_CREATED_AT, getDateTime());
 
        long id = db.insert(TABLE_TODO_TAG, null, values);
 
        return id;
    }
 
    /**
     * Updating a todo tag
     */
    public int updateNoteTag(long id, long tag_id) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_TAG_ID, tag_id);
 
        // updating row
        return db.update(TABLE_TODO, values, KEY_ID + " = ?",
                new String[] { String.valueOf(id) });
    }
 
    /**
     * Deleting a todo tag
     */
    public void deleteToDoTag(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_TODO, KEY_ID + " = ?",
                new String[] { String.valueOf(id) });
    }
 
    // closing database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }
 
    /**
     * get datetime
     * */
    private String getDateTime() {
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        Date date = new Date();
        return dateFormat.format(date);
    }
}


What’s Next ?

이 게시물을...
조회 수 :
53083
등록일 :
2014.02.13
09:47:57 (*.251.139.148)
엮인글 :
http://webs.co.kr/index.php?document_srl=38741&act=trackback&key=6c3
게시글 주소 :
http://webs.co.kr/index.php?document_srl=38741
목록
List of Articles
번호 제목 글쓴이 날짜 조회 수
» Android SQLite Database with Multiple Tables admin 2014-02-13 53083
36 Android Simple Clean Good SQLite Database Tutorial 잘된 설명 admin 2014-02-13 35739
35 android sqlite 사용하기 admin 2014-02-10 91666
34 SQLite 개발가이드 데이터베이스의 성능 admin 2014-02-10 97125
33 android - 다수의 Insert 수행시 속도 향상을 위한 팁 sQlite admin 2014-02-10 86651
32 Oracle Linux 에 Oracle DB 설치하기 admin 2013-12-03 109064
31 PreparedStatement mysql java 깔끔한설명 admin 2013-10-26 110371
30 Connect Excel VBA to a MySQL database file admin 2013-09-05 47116
29 Configuring Oracle ASM disks in Oracle Enterprise Linux admin 2013-04-20 39740
28 OS에따른 Oracle 설치버전 admin 2013-04-08 33433
27 RHEL4 + 10g 설치 _silent mode admin 2013-04-08 85200
26 OLE5 + 11G 설치 _silent mode admin 2013-04-08 88471
25 WHERE 조건절 검색시 서브쿼리는 어떻게? admin 2013-04-01 38652
24 CDR 추출 저장 Inner Join 사용 Sql 문 admin 2013-02-05 39207
23 SUPER OCM 1.8club admin 2012-12-18 32414
22 MySQL Java tutorial admin 2012-09-30 50472
21 Oracle 10g Articles admin 2012-06-24 33531
20 기본 10g 설치의 리눅스 세팅에서 추가 해줘야하는 사항(윈도우) admin 2012-06-24 40992
19 SUSE Linux Enterprise Server 10 (Oracle 10g R2 (10.2.0.1)) file admin 2012-03-09 38934
18 Upgrade Oracle from 10.2.0.1 To 10.2.0.4 (Windows) admin 2012-03-06 170468
17 Upgrade Oracle 10g Release 2 from 10201 to 10204 admin 2012-03-05 115620
16 centos 6.2 oracle 10g 설치 admin 2012-03-05 113056
15 Oracle RHEL4+10G 10.2.0.1 설치 10.2.0.5 패치 admin 2012-03-03 99389
14 Oracle Backup & restore with RMAN 기본 admin 2012-02-12 44054
13 오라클 ACE가 해설하는 Oracle Backup & Recovery admin 2012-02-07 42758
12 Oracle Backup & Restore admin 2012-02-07 94770
11 http://www.hoons.kr/ admin 2011-12-19 36217
10 Java && Excel 연동 JAVA 자바로 엑셀을 핸들링 할 수 있는 방법 admin 2011-12-19 200218
9 (C#) ASP.NET MySQL Check Already Exists Add/Insert Record 프로그래밍 코드 admin 2011-12-19 35996
8 xhost and display admin 2011-12-16 36253
목록
쓰기

Powered by (주)제이에스솔루션 인터넷전화 070 가입자 가입 소프트웨어 개발