한국어

네트워킹

온누리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