Basics of SQLITE database with Multiple table example


This tutorial will cover the creating the Multiple tables in sqlite Database for that we will need to have 2 separate packages Model and helper.

Creating the Model and Helpler Class;

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



- Create a new class file under com.shir60bhushan.sqlite.helper package named Tasks.java and type the code like below. This is the model class for Tasks table

Task.java

package shir60bhushan.sqlite.model;



public class Task {
 

 int id;
   
 String note;
    
 int status;
   
 String created_at;

   
 
// constructors
    

public Task() {
    }

    

public Task(String note, int status) {
 
       this.note = note;
    
    this.status = status;
  
  }

   
 
public Task(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 Cat table named Cat.java under the same package.


package shir60bhushan.sqlite.model;

public class cat {

    int id;
    String cat_name;

    // constructors
    public cat() {

    }

    public cat(String cat_name) {
        this.cat_name = cat_name;
    }

    public cat(int id, String cat_name) {
        this.id = id;
        this.cat_name = cat_name;
    }

    // setter
    public void setId(int id) {
        this.id = id;
    }

    public void setcatName(String cat_name) {
        this.cat_name = cat_name;
    }

    // getter
    public int getId() {
        return this.id;
    }

    public String getcatName() {
        return this.cat_name;
    }
}

cat.java
package com.shir60bhushan.sqlite.model;

public class cat {

    int id;
    String cat_name;

    // constructors
    public cat() {

    }

    public cat(String cat_name) {
        this.cat_name = cat_name;
    }

    public cat(int id, String cat_name) {
        this.id = id;
        this.cat_name = cat_name;
    }

    // setter
    public void setId(int id) {
        this.id = id;
    }

    public void setcatName(String cat_name) {
        this.cat_name = cat_name;
    }

    // getter
    public int getId() {
        return this.id;
    }

    public String getcatName() {
        return this.cat_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.
So create another class named DatabaseHelper.java under com.shir60bhushan.sqlite.helper package and extend the class from SQLiteOpenHelper

public class DatabaseHelper extends SQLiteOpenHelper {

. 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 cat
    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_tasks = "tasks";
    private static final String TABLE_cat = "cats";
    private static final String TABLE_tasks_cat = "tasks_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_tasks = "tasks";
    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_tasks_ID = "tasks_id";
    private static final String KEY_cat_ID = "cat_id";

    // Table Create Statements
    // tasks table create statement
    private static final String CREATE_TABLE_tasks = "CREATE TABLE "
            + TABLE_tasks + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_tasks
            + " 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" + ")";

    // tasks_cat table create statement
    private static final String CREATE_TABLE_tasks_cat = "CREATE TABLE "
            + TABLE_tasks_cat + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_tasks_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_tasks);
        db.execSQL(CREATE_TABLE_cat);
        db.execSQL(CREATE_TABLE_tasks_cat);
    }

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

        // create new tables
        onCreate(db);
    }



Create, Read, Update and Delete Operations

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

1. Creating a Task

The function will create a task item in tasks table. In this same function we are assigning the task to a tag name which inserts a row in task_cat table.

public long createToDo(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);

    // assigning CATS to TASK
    for (long cat_id : cat_ids) {
        createTodoTag(task_id, task_id);
    }

    return task_id;
}

2. Fetching a TASK
Following will fetch a TASK from TASKS table.
SELECT * FROM TASKS WHERE id = 1;

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

3. Fetching all TASKS

Fetching all tasks involves reading all task rows and adding them to a list array.
SELECT * FROM tasks;

public List getAlltasks() {
    List tasks = new ArrayList();
    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;
}
dfgdgsdfg




4. Fetching all Task under a cat name

 SELECT * FROM tasks td, cats tg, task_cats tt WHERE tg.cat_name = ‘Manner’ AND tg.id = tt.cat_id AND td.id = tt.task_id;


public List getAlltasksBycat(String cat_name) {
    List tasks = new ArrayList();

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

5. Updating a task

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

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()) });
}
6. Deleting a Task

Pass task_id to the following function to delete the task from db.

public void deleteTa(long tas_id) {

    SQLiteDatabase db = this.getWritableDatabase();

    db.delete(TABLE_Task, KEY_ID + " = ?",

            new String[] { String.valueOf(tas_id) });

}
Until now we are done creating the CRUD methods onto tasks table. Now we can start the methods required on cats table.


7. Creating cat

Following method will insert a row into cats table.



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;
}
8. Fetching all cat names

Performing select all statement on cats table will give you list of cat names.
SELECT * FROM cats;


public List getAllcats() {
    List cats = new ArrayList();
    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;
}

9. Updating cats
Following method will update 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()) });
}
10. Deleting cat and tasks under the cat name Following method will delete a cat from db. This also will delete all the tasks under the cat name, but this is optional.
should_delete_all_cat_tasks = Passing true will delete all the tasks under the cat name

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 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()) });
}

Post a Comment