Android SQLite Multiple table basics -II

In the Previous tutorial(Android SQLite Multiple table basics) we have ,developed a Multiple table in a database and seen its basic operations .Now in this post  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 cat and task data and performed the all the operations by calling the methods which we prepared in DatabaseHelper class.

 
MainActivity.java

package info.androidgreeve.sqlite;

import info.androidgreeve.sqlite.helper.DatabaseHelper;
import info.androidgreeve.sqlite.model.cat;
import info.androidgreeve.sqlite.model.task;

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 cat
        cat cat1 = new cat("Shopping");
        cat cat2 = new cat("Important");
        cat cat3 = new cat("Watchlist");
        cat cat4 = new cat("androidgreeve");

        // Inserting cat in db
        long cat1_id = db.createcat(cat1);
        long cat2_id = db.createcat(cat2);
        long cat3_id = db.createcat(cat3);
        long cat4_id = db.createcat(cat4);

        Log.d("cat Count", "cat Count: " + db.getAllcat().size());

        // Creating tasks
        task task1 = new task("iPhone 5S", 0);
        task task2 = new task("Galaxy Note II", 0);
        task task3 = new task("Whiteboard", 0);

        task task4 = new task("Riddick", 0);
        task task5 = new task("Prisoners", 0);
        task task6 = new task("The Croods", 0);
        task task7 = new task("Insidious: Chapter 2", 0);

        task task8 = new task("Don't forget to call MOM", 0);
        task task9 = new task("Collect money from John", 0);

        task task10 = new task("Post new Article", 0);
        task task11 = new task("Take database backup", 0);

        // Inserting tasks in db
        // Inserting tasks under "Shopping" cat
        long task1_id = db.createtask(task1, new long[] { cat1_id });
        long task2_id = db.createtask(task2, new long[] { cat1_id });
        long task3_id = db.createtask(task3, new long[] { cat1_id });

        // Inserting tasks under "Watchlist" cat
        long task4_id = db.createtask(task4, new long[] { cat3_id });
        long task5_id = db.createtask(task5, new long[] { cat3_id });
        long task6_id = db.createtask(task6, new long[] { cat3_id });
        long task7_id = db.createtask(task7, new long[] { cat3_id });

        // Inserting tasks under "Important" cat
        long task8_id = db.createtask(task8, new long[] { cat2_id });
        long task9_id = db.createtask(task9, new long[] { cat2_id });

        // Inserting tasks under "androidgreeve" cat
        long task10_id = db.createtask(task10, new long[] { cat4_id });
        long task11_id = db.createtask(task11, new long[] { cat4_id });

        Log.e("task Count", "task count: " + db.gettaskCount());

        // "Post new Article" - assigning this under "Important" cat
        // Now this will have - "androidgreeve" and "Important" cat
        db.createtaskcat(task10_id, cat2_id);

        // Getting all cat names
        Log.d("Get cat", "Getting All cat");

        List<cat> allcat = db.getAllcat();
        for (cat cat : allcat) {
            Log.d("cat Name", cat.getcatName());
        }

        // Getting all tasks
        Log.d("Get tasks", "Getting All tasks");

        List<task> alltasks = db.getAlltasks();
        for (task task : alltasks) {
            Log.d("task", task.getNote());
        }

        // Getting tasks under "Watchlist" cat name
        Log.d("task", "Get tasks under single cat name");

        List<task> catWatchList = db.getAlltasksBycat(cat3.getcatName());
        for (task task : catWatchList) {
            Log.d("task Watchlist", task.getNote());
        }

        // Deleting a task
        Log.d("Delete task", "Deleting a task");
        Log.d("cat Count", "cat Count Before Deleting: " + db.gettaskCount());

        db.deletetask(task8_id);

        Log.d("cat Count", "cat Count After Deleting: " + db.gettaskCount());

        // Deleting all tasks under "Shopping" cat
        Log.d("cat Count",
                "cat Count Before Deleting 'Shopping' tasks: "
                        + db.gettaskCount());

        db.deletecat(cat1, true);

        Log.d("cat Count",
                "cat Count After Deleting 'Shopping' tasks: "
                        + db.gettaskCount());

        // Updating cat name
        cat3.setcatName("Movies to watch");
        db.updatecat(cat3);

        // 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.androidgreeve.sqlite.helper;

import info.androidgreeve.sqlite.model.cat;
import info.androidgreeve.sqlite.model.task;

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 cat
    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_task = "tasks";
    private static final String TABLE_cat = "cats";
    private static final String TABLE_task_cat = "task_cats";

    // 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_task = "task";
    private static final String KEY_STATUS = "status";

    // catS Table - column names
    private static final String KEY_cat_NAME = "cat_name";

    // NOTE_catS Table - column names
    private static final String KEY_task_ID = "task_id";
    private static final String KEY_cat_ID = "cat_id";

    // Table Create Statements
    // task table create statement
    private static final String CREATE_TABLE_task = "CREATE TABLE "
            + TABLE_task + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_task
            + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
            + " DATETIME" + ")";

    // cat table create statement
    private static final String CREATE_TABLE_cat = "CREATE TABLE " + TABLE_cat
            + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_cat_NAME + " TEXT,"
            + KEY_CREATED_AT + " DATETIME" + ")";

    // task_cat table create statement
    private static final String CREATE_TABLE_task_cat = "CREATE TABLE "
            + TABLE_task_cat + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_task_ID + " INTEGER," + KEY_cat_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_task);
        db.execSQL(CREATE_TABLE_cat);
        db.execSQL(CREATE_TABLE_task_cat);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_task);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_cat);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_task_cat);

        // create new tables
        onCreate(db);
    }

    // ------------------------ "tasks" table methods ----------------//

     /**
     * Creating a task
     */
    public long createtask(task task, long[] cat_ids) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_task, task.getNote());
        values.put(KEY_STATUS, task.getStatus());
        values.put(KEY_CREATED_AT, getDateTime());

        // insert row
        long task_id = db.insert(TABLE_task, null, values);

        // insert cat_ids
        for (long cat_id : cat_ids) {
            createtaskcat(task_id, cat_id);
        }

        return task_id;
    }

    /**
     * get single task
     */
    public task gettask(long task_id) {
        SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT  * FROM " + TABLE_task + " WHERE "
                + KEY_ID + " = " + task_id;

        Log.e(LOG, selectQuery);

        Cursor c = db.rawQuery(selectQuery, null);

        if (c != null)
            c.moveToFirst();

        task td = new task();
        td.setId(c.getInt(c.getColumnIndex(KEY_ID)));
        td.setNote((c.getString(c.getColumnIndex(KEY_task))));
        td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

        return td;
    }

    /**
     * getting all tasks
     * */
    public List<task> getAlltasks() {
        List<task> tasks = new ArrayList<task>();
        String selectQuery = "SELECT  * FROM " + TABLE_task;

        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 {
                task td = new task();
                td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                td.setNote((c.getString(c.getColumnIndex(KEY_task))));
                td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

                // adding to task list
                tasks.add(td);
            } while (c.moveToNext());
        }

        return tasks;
    }

    /**
     * getting all tasks under single cat
     * */
    public List<task> getAlltasksBycat(String cat_name) {
        List<task> tasks = new ArrayList<task>();

        String selectQuery = "SELECT  * FROM " + TABLE_task + " td, "
                + TABLE_cat + " tg, " + TABLE_task_cat + " tt WHERE tg."
                + KEY_cat_NAME + " = '" + cat_name + "'" + " AND tg." + KEY_ID
                + " = " + "tt." + KEY_cat_ID + " AND td." + KEY_ID + " = "
                + "tt." + KEY_task_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 {
                task td = new task();
                td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                td.setNote((c.getString(c.getColumnIndex(KEY_task))));
                td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

                // adding to task list
                tasks.add(td);
            } while (c.moveToNext());
        }

        return tasks;
    }

    /**
     * getting task count
     */
    public int gettaskCount() {
        String countQuery = "SELECT  * FROM " + TABLE_task;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);

        int count = cursor.getCount();
        cursor.close();

        // return count
        return count;
    }

    /**
     * Updating a task
     */
    public int updatetask(task task) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_task, task.getNote());
        values.put(KEY_STATUS, task.getStatus());

        // updating row
        return db.update(TABLE_task, values, KEY_ID + " = ?",
                new String[] { String.valueOf(task.getId()) });
    }

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

    // ------------------------ "cats" table methods ----------------//

    /**
     * Creating cat
     */
    public long createcat(cat cat) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_cat_NAME, cat.getcatName());
        values.put(KEY_CREATED_AT, getDateTime());

        // insert row
        long cat_id = db.insert(TABLE_cat, null, values);

        return cat_id;
    }

    /**
     * getting all cats
     * */
    public List<cat> getAllcats() {
        List<cat> cats = new ArrayList<cat>();
        String selectQuery = "SELECT  * FROM " + TABLE_cat;

        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 {
                cat t = new cat();
                t.setId(c.getInt((c.getColumnIndex(KEY_ID))));
                t.setcatName(c.getString(c.getColumnIndex(KEY_cat_NAME)));

                // adding to cats list
                cats.add(t);
            } while (c.moveToNext());
        }
        return cats;
    }

    /**
     * Updating a cat
     */
    public int updatecat(cat cat) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_cat_NAME, cat.getcatName());

        // updating row
        return db.update(TABLE_cat, values, KEY_ID + " = ?",
                new String[] { String.valueOf(cat.getId()) });
    }

    /**
     * Deleting a cat
     */
    public void deletecat(cat cat, boolean should_delete_all_cat_tasks) {
        SQLiteDatabase db = this.getWritableDatabase();

        // before deleting cat
        // check if tasks under this cat should also be deleted
        if (should_delete_all_cat_tasks) {
            // get all tasks under this cat
            List<task> allcattasks = getAlltasksBycat(cat.getcatName());

            // delete all tasks
            for (task task : allcattasks) {
                // delete task
                deletetask(task.getId());
            }
        }

        // now delete the cat
        db.delete(TABLE_cat, KEY_ID + " = ?",
                new String[] { String.valueOf(cat.getId()) });
    }

    // ------------------------ "task_cats" table methods ----------------//

    /**
     * Creating task_cat
     */
    public long createtaskcat(long task_id, long cat_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_task_ID, task_id);
        values.put(KEY_cat_ID, cat_id);
        values.put(KEY_CREATED_AT, getDateTime());

        long id = db.insert(TABLE_task_cat, null, values);

        return id;
    }

    /**
     * Updating a task cat
     */
    public int updateNotecat(long id, long cat_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_cat_ID, cat_id);

        // updating row
        return db.update(TABLE_task, values, KEY_ID + " = ?",
                new String[] { String.valueOf(id) });
    }

    /**
     * Deleting a task cat
     */
    public void deletetaskcat(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_task, 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);
    }
}


This Completes the Basic for Sqlite multiple Table Basics

Yeah,Next?
In the  next we will see a complete example with source code for Subscribers and twitter follower.

Guys Like our Page Facebook:fb/Androidgreeve and  
twitter :@Androidgreeve