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.
Use Case: Todo Application
To make it easier for you to understand, I am taking a real use case example of TODO Application database 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 and assigning 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
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 and info.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
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.
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 under info.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
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.
/* * 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.
/* * 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.
/* * 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.
/** * 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.
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
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 ?
An example of Todo application is coming soon … stay tuned …
Hi there! I am Founder at androidhive and programming enthusiast. My skills includes Android, iOS, PHP, Ruby on Rails and lot more. If you have any idea that you would want me to develop? Let’s talk: ravi@androidhive.info
Good Man,,,,Nice Work…
Your blog is very helpfull. Thanks you
Why you don’t use ContentProvider? Because ContentProvider – it’s Google style…
http://stackoverflow.com/questions/3350408/exact-difference-between-content-provider-and-sqlite-database
Do you have a link to a ContentProvider example with multiple tables? Would love to look at one.
Very Nice Example
How can I create a todo item in todos table of db from a parsed xml file?
Just create a todo with xml data like
Todo todo = new Todo(“your xml todo text”, 0);
insert in db
db.createToDo(todo, new long[] { tag1_id });
Should I define and use an updateToDo function afterwards??
Because every time I start my list activity, the xml file would download and parce, so every time the same todo text would be add in sqlite database matrix.
You need to find a way to stop parsing and inserting if todos are already inserted into sqlite.
Every start application, database will be recreate or not ? Because i see when start MainActivity, it run that code “db = new DatabaseHelper(getApplicationContext());”. Please explain for me. Thanks you.
Don’t worry it will create the tables only once on the first time!
can you please explain what causes the database and tables not to be recreated over and over….does the static code have something to do with the that? and how?
i cant retrieve the data from table using another application
Suppose If I have seperate DatabaseHelper(extending SQLiteOpenHelper) for each table with same DB name and version. Will it create all the tables under same database only once? As each DatabaseHelper will have CREATE TABLE statement. So will it execute?
Hello, I’m sorry for bothering but I’d like a little help. Suppose you want to change the database model… what should i call in the Activity file?
nice tutorial as usual! But little typing error :
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
I assume the class Todo has to be under the package info.androidhive.sqlite.model.
Thank you Lynn for notifying me this 🙂
I’ll do the correction.
Oh and sections 12 and 13 have the same code sample… I assume that’s a typing error as well…
Really! I’ll look into that too 🙂
awsome explanation
retrive coding are in main activity.java; how can view those in emulator?
Very good example as usual.. Retrive codings are in main acticity.java file, i want to get retrive values in to emulator. how can set those things??
why r u creating model class for every database table? It will increase no of file….can we create one single file which will contains “row object” of every table and all the method for manipulating all tables…
for example..
class Contacts extends Object {
public int _Id;
public String name;
public String email;
}
public DatabaseHelper(Context ctx) {
public void close() {
public void createContacts(String name, String email) {
public void deleteContacts(int ContactsId) {
public List fetchAllContactss() {
public Contacts fetchContacts(int ContactsId) {
public void updateContacts(int ContactsId, String name, String email) {
public Cursor GetAllContactss() {
public void onCreate(SQLiteDatabase arg0) {
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
…
because in my application i have more than 15 tables….
into some CRUD methods, using the local SQLiteDatabase, maybe occurs the null-pointer exceptions,, why didn’t we use the global SQLiteDatabase variables at the first-top of class?
Thanks very useful info. Please work more on this series.
how can I fetch the data and add it to listview?
Putting everything in one DatabaseHelper class doesn’t seem scalable as we have 10s of tables with potentially 100s of columns. Ultimately it has to come down to 1 class per table. Don’t you think?
Create a DAO Factory
In section 12 and 13, the method is supposed to update or delete a ‘tag’ of a ‘todo’. However, it gives TABLE_TODO as parameter to db.update() instead of TABLE_TODO_TAG which holds the relevant information. Is there something wrong here, or did I not understand this correctly?
Yeah, there’s so much wrong with this tutorial that I don’t know where to begin. He only inserts into TABLE_TODO_TAG and never updates that table when todos are deleted or tags are deleted.
Why aren’t you using “FOREIGN KEY” in the table declaration (in the
String CREATE_TABLE_TODO_TAG)? Isn’t that possiible?. I wish all
operation related to delete, update etc wil be managed automatically by
the dbms, here I have to take care of everything!
I’d like to know this as well… In SQL I’d just have two tables and in the todos table I’d have a tag_id as a foreign key referencing the tag_ig of the tags table. So we wouldn’t even need to have a junction table (todo_tag). So can’t we do the same here?
If you want to have a todo with multiple tags, the todo_tag table allows you to store multiple tags for for each todo. That allows the user to have a more robust categorization system for their todos.
Very, very good tutorial! Congratulations!
hello can you plz snd me the tutorial by mail on jenareshmeebye@hotmail.com
Thanks
is there any example app for this…here or github or somewhere else, if u can give me link it would be really helpful, thanks.
Ravi! Thank you for all the great articles, you are genius. BUT what is the deal with the “Todo” table have “buying IPhone 5S” ?? You must have meant “Galaxy S4″…keep up the great work.
I am not a big fan of Android phones 🙂
Cant believe it. You are making awesome tutorials for Android. and you dont like Android Phones.. uuhh really?
wouldn’t the tables get recreated everytime the activity runs
whatever you post is good and working but i created database table using your code using one application. but i cant retrieve the table data from another application. why?
What do you by retrieving the data from another application? Do you want to access database/tables of your other applications? If this is the case then you are required to implement the concept of “Content provider” same like we can access data of calendar, call logs, SMS, etc native applications!
To read other app data you have to user share app id attributre in manifest file. then your app will get same application id as of you are trying to read(app id). other wise android system will not allow you to read other app data.
thanks Ravi Tamada for your great cod.
i have question, i don’t no why it dose not work with me because when i run it nothing happens, just on sentence (hello whorled ). so what the problem please and thanks.
how do u fix that?
thanks i now what the problem now
B-E-A-utifull 🙂
Goog job. Continue the tutorial please.
thanks
Can you explain why do you close the Cursors in “getTodoCount” and live them opened in all the other methods (“getTodo”, “getAllTodoByTag”, “getAppTags”)?
thanks very useful
Hey, I just have to pitch in and tell you how useful this has been to me. Thanks a lot for it!
Thank You
Can you continue with
– enter the data with textfields on the app
– connection to mysql
thank you
I cannot download the code… =(
Hi Ravi, Thank you for the great tutorials!! I don’t know if everyone has this problem. I couldnt subscribe to your website. When I click “Subscribe” it redirects to http://download.androidhive.info/firewall/firewall.php and nothing happens
Thanks for your article, it’s very useful!
Good job here dude!!
Why is there a model package when all classes were created inside helper package?
#3 and #4 should be under model package. not helper package
Sorry, I mean Todo.java and Tag.java
In the getTodo you have a null pointer check for the cursor and if it is not null you moveToFirst. And then you use it to create a Todo object. However if it is null the c.getInt just after the new Todo() won’t throw a null pointer?
Dude, you really shouldn’t be writing online tutorials if you can’t even reply to reader’s comments, and, especially, to those that have correctly identified errors. You replied to a couple of them saying you would fix them, but you haven’t done so (10 months later). For example, Lynn’s and Baraa’s comments.
Thanks man !
Excellent tutorial.
You are welcome Robson.
“An example of Todo application is coming soon … stay tuned …” When?
Liked you paid him.. Behave yourself.
thnx 🙂
Good job Mr Ravi Tamada thinks !
Thanks Marwen 🙂
Thanks Ravi, Waiting for the Note tutorial 🙂
Hi Ravi sir,
i’m soundararajan from bangalore
i had one doubt, in sqlite during run the program i want create a table like student assignment mark sheet table the content is , student name,dob,register no.father name, address this all in one table and from the student name and reg no, based mark sheet in anther table (s.no,sub,date of summation, date of summit,mark,totalmark) column if i and the student details the in student record it’s automatically create mark sheet table and mark sheet entry separate sheet like our mark sheet is it possible ,
if i delete the student record mean it’s automatically delete the mark sheet also
(create table from the edittext input and column items all same it’s possible) by button
Hi Soundararajan.
Do u have article bout what u did? I need reference about structure and script code of Student’s Assignment Record App.
Can both of u help me pls?
Btw, thanks for Ravi. I followed almost all of ur tutor 😀 (y)
Wonderful! Thanx a lot!
I’m so glad I ran into your site.
Will definitely stay tuned 🙂
Wonderful! Thanx a lot!
I’m so glad I ran into your posts.
Will definitely follow you 🙂
How should the composite key of a table be renamed. for example
CREATE TABLE `Products-Stores` (
`product` INTEGER NOT NULL,
`store` INTEGER NOT NULL,
`price` INTEGER NOT NULL,
PRIMARY KEY(product,store)
);
It is grt8 post , Ravi . but i have some doubts here and still wondering why the is the reason to put all the schemas in one file . Is there any specific reason ?
Thanks
Hi, i want a question. Once I run this program. it show nothing and then pop out a stop working alert. what happen with this problem?