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
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.
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
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
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.
Fetching all tasks involves reading all task rows and adding them to a list array.
SELECT * FROM tasks;
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 TASKFollowing 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 TASKSFetching 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;
Following function will update a task. It will update task values only, not the tag name.
Pass task_id to the following function to delete the task from db.
7. Creating cat
Following method will insert a row into cats table.
Performing select all statement on cats table will give you list of cat names.
SELECT * FROM cats;
9. Updating cats
Following method will update cat.
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 taskFollowing 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 TaskPass 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 namesPerforming 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()) });
}
0 comments :
Post a Comment