AndroidHive
Database

Android SQLite Database with Multiple Tables

By July 12, 2017 0 Comments

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
android sqlite database with 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

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 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.

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
	 * */
	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 …

Ravi Tamada
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

Related Posts

  • Ashish Mishra

    Good Man,,,,Nice Work…

  • Your blog is very helpfull. Thanks you

  • NONE

    Why you don’t use ContentProvider? Because ContentProvider – it’s Google style…

  • sukumar

    Very Nice Example

  • Costas

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

      • Costas

        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!

      • chris

        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?

      • Vijayakumar

        i cant retrieve the data from table using another application

      • Guest

        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?

      • Maria Pedroto

        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?

  • Lynn Houthuys

    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.

      • Lynn Houthuys

        Oh and sections 12 and 13 have the same code sample… I assume that’s a typing error as well…

  • rishad

    awsome explanation

  • Guest

    retrive coding are in main activity.java; how can view those in emulator?

  • Sharaa

    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??

  • vinit

    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….

  • Larry Ritchie

    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?

  • Jh

    Thanks very useful info. Please work more on this series.

  • how can I fetch the data and add it to listview?

  • Fayez

    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?

    • WiseFundManager

      Create a DAO Factory

  • Baraa Orabi

    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?

    • Marco

      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.

  • theangi

    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!

    • PrincessLilly

      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?

      • Kendell Fabricius

        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.

  • Andre F G Jr

    Very, very good tutorial! Congratulations!

  • Jena Sookdin Reshmeebye

    hello can you plz snd me the tutorial by mail on jenareshmeebye@hotmail.com
    Thanks

  • tekno freek

    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.

  • Lucky Reed

    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 🙂

      • Ronit

        Cant believe it. You are making awesome tutorials for Android. and you dont like Android Phones.. uuhh really?

  • chris

    wouldn’t the tables get recreated everytime the activity runs

  • Vijayakumr

    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.

  • Faisal

    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.

    • amat

      how do u fix that?

  • Faisal

    thanks i now what the problem now

  • Bharat Jiyani

    B-E-A-utifull 🙂

  • João Quinteira

    Goog job. Continue the tutorial please.

  • carty

    thanks

  • Nick P

    Can you explain why do you close the Cursors in “getTodoCount” and live them opened in all the other methods (“getTodo”, “getAllTodoByTag”, “getAppTags”)?

  • Gunsu Koksal

    thanks very useful

  • Lee Davison

    Hey, I just have to pitch in and tell you how useful this has been to me. Thanks a lot for it!

  • rosh3000

    Thank You

  • Steven

    Can you continue with

    – enter the data with textfields on the app
    – connection to mysql

    thank you

  • Шйгук

    I cannot download the code… =(

  • Umarani

    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

  • dj-chen.com

    Thanks for your article, it’s very useful!

  • Dani

    Good job here dude!!

  • Rayko

    Why is there a model package when all classes were created inside helper package?

    • JOptionPane

      #3 and #4 should be under model package. not helper package

    • JOptionPane

      Sorry, I mean Todo.java and Tag.java

  • Andreas

    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?

  • Marco

    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.

  • Robson Douglas

    Thanks man !
    Excellent tutorial.

  • Peter

    “An example of Todo application is coming soon … stay tuned …” When?

    • Hi

      Liked you paid him.. Behave yourself.

  • Marwen

    Good job Mr Ravi Tamada thinks !

  • Aya M Salama

    Thanks Ravi, Waiting for the Note tutorial 🙂

  • soundararajan s

    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

    • savitririshma

      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)

  • Guest

    Wonderful! Thanx a lot!
    I’m so glad I ran into your site.
    Will definitely stay tuned 🙂

  • Guest

    Wonderful! Thanx a lot!
    I’m so glad I ran into your posts.
    Will definitely follow you 🙂

  • Gatunox

    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)

    );

  • Sanny Singhs

    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

  • Janice Heah

    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?

  • kyl

    great example! thanks a lot.

  • jack

    hi thnx for the tutorial, its helping a lot but i have a small problemabout creating tables i have only one table in the db and i am doing the exactly same thng to create the second one but it doesnt, i cant create it no any error when i try to insert it says no such a table if you have any idea pls i would like to hear ty again

    • jack

      Solved it Thnx Anyway 🙂

      • Tommy

        How you solved it?

  • Ferrin Benjamen Katz

    “3. Create a new class file under info.androidhive.sqlite.helper package”

    I believe you meant info.androidhive.sqlite.model here.

  • karathb

    I like that you always write full working examples (not just the main parts), so we can learn from it. Thanks for the 1000th times!

  • Waqas Wagan

    if i get current co ordinate how can i store in into database can u help me

  • Waqas Wagan

    if database is created how can i conform that DB is created

  • Waqas Wagan

    while creating database its not in assest where it should be ?

  • Alex

    There’s a mistake at the beginning :
    “3. Create a new class file under info.androidhive.sqlite.helper”, it should be info.androidhive.sqlite.model

  • Marcos Bérgamo

    Just a little question… Why you put the CRUD on the SQLDatabaseHelper? Why not write it on the model classes?

  • 1. Do all table operations in one single DbHandlerClass (just like you done) .
    2. Do all table operation in multiple DbHandlerClasses according to tables.

    WHICH ONE IS BETTER APPROACH ??

    • It depends on what youre targeting, I guess…

  • Important: The code below reduces the time of execution by about 100x (from 200ms to 1-2ms, tested multiple times with about 150 database-entries) 😉
    cards.add(new Card(c.getInt(COL_ID),
    c.getString(COL_CARD_TITLE),
    c.getString(COL_CARD_CREA),
    c.getInt(COL_CARD_FAVOR),
    c.getInt(COL_CARD_ICON)));

    @Ravi: Thanks for your nice tutorial, it’s well written and very informative. 🙂

  • Денис Шовгеня

    I think that you missed in Database design image in todo_tags table created_at column.
    And I want to say thanks for your tutorials, they are very helpfull.

  • KEY_CREATED_AT + ” DATETIME” + “)”;

    I think DATETIME is not a datatype in SQLite.

  • Gerry

    Good job man, you help us a lot

  • Yosub lee

    I had a problem with getting an idea about how people design tables and your post helped me alot!!! Thanks!

  • Ibrahim Saputra

    hi … thanks for this wonderful tutorial … can i ask something?

    it just a simple question … can any one explain in the tutorial why we must have status variabel at Todo model even though in the table design it doesnt appear …

    sorry for bad english

  • Jojo

    Hi friends, I need to sync my database from my own server to make the app work in OFFLINE.

    Please help me.
    How can i do this.???
    i have more than 50 tables.. to sync for offline.

  • Michael Obi

    Nice tutorial as always. This is cool for 3 tables but then the files may get really confusing when we have over 10 tables. This has been giving me a headache for some time.

    • Jim Lu

      I cannot agree nor more. This is a good example~

      • If you have more tables try using Content Providers.

        • Maheswaran Mohan

          If i have more tables ..what’s the advantage of using content providers. How i can overcome complexity using content providers. can u plz provide detail explanation.

  • dreamgs1

    Simply not working.
    Deletion is not working with above code.
    Example:
    Create 2 tags and add 3 todos each to both tags
    So each tag will have 3 todos.

    Delete one todo of tag2 and see you will find the list of todos are 5. Under tag1 its 3 and under tag2 its 2 entries.

    I have modified the db not to create tags every time and rerun the app. At this point while loading 2nd time, total todos count is 5 (3 for tag1 and 2 for tag2).
    Now add 3 more todos each to both tag1 and tag2.

    Note*: checked that tag ids of tag1 and tag2 are same as first run

    Delete some todos from tag2.

    Try to get todos for each tag (tag1 and tag2) and see result.

    Seeing big difference.Not sure if there is no deletion is happening or tods are not associated with tags

    But thanks for the post to know basics. But I am not able to find where the problem is after deleting of todos (not tags) respective tag todo list is not appropriate.

    any body can help me in this?

    • Welcome, Stranger

      Hi, didi you resolve this issue?
      I also had problems deleting tags and todos

  • Amita Jain

    the worst example

  • Anshul

    Hi All,

    The tutorial is good for an beginner. I want to go in depth with all methods in sqlite lik beginTransaction() etc. methods. I want to know How we can use an SingleTone Instance class for this. Please help me with these str. of databse.

  • arpit

    Is there any way to import existing .sql file in our application ??? If Anyone know please help me.

  • rammohan

    thank you for your helping

  • Kousar

    thankx u fr the great work
    is there any refrence book to go in detail with android SQLite database
    plz suggest me

  • Jonny

    This is a great article. Do we have any idea when the next instalment will be released? I have completed this tutorial so far but am REALLY struggling to bind the data to a GridView. Can anyone else suggest how I would do this?

    Thanks

    • Once you are good at SQLite, try to learn SQLite with Cursor Loader and Content Providers.

  • Mart

    How do I add data to the SQL from my MainActivity? I understand how to do this if I am only adding one field to the db (dao.createTodo(todoTextValue); or something similar) where todoTextValue is a string. But how do I do it when there is more than one item, like in your example when you add a Todo you add a note, status and time. TY

  • harin kaklotar

    @ravi8x:disqus download button is not working for this project

  • Nice and clean post. But in real world, models can be much more complicated. Let’s assume Todo has relationships with Project(a project has many todos) and User(a member in a team, might be working on the same project).
    Now models look like this

    class Todo {
    User user;
    Project project;
    }

    class User{
    List todoList;
    List projects;

    Role role; // admin, user, …
    }

    class Project {
    List todoList;
    User creator;
    ….
    }

    Now if we want to save these things into sqlite database, what might be a better and elegant solution?

    Do we have to create DAOs to do CRUD on objects? Such as

    TodoDao.insertOrUpdateUser(Todo todo) {
    db.insertOrUpdateTodo(todo);
    db.insertOrUpdateUser(todo.user)
    db.insertOrUpdateProject(todo.project)
    }

    And so forth…

    But that seems ugly. Do you guys have better solutions? Enlighten me please. 😉

  • Anit X

    hi can u teach

    Android PHP Read From MySQL DB Using Volley with RecyclerView like userlist with image and other details

    • Jermaine

      Hello,
      Can you used multiple class file for this code? one class file for each table insert/update/delete with dbhelper

  • Arihant Jain

    thanks for awesome tutorials please let me know how to insert bulk data in sqlite through json i want to insert 1000000 lacs of rows

  • Manuel

    this is great but where are the source code for the layout?

  • Renz

    This really helped me in my mobile development. I also hope you’ve got blogs about how mobile app connect to web database. Anyway, thanks so much!

  • Živjo Doberdan

    thanks man, you really helped me on my work with android app… thanks again 😀

    • 😀 Try learning Content Providers as they are much better with SQlite.

  • Sanyog Nishandar

    // NOTES Table – column nmaes
    private static final String KEY_TODO = “todo”;
    private static final String KEY_STATUS = “status”;

    I dint undesrtand the above two columsn in TODOS TABLE, fields of first table are ID ,NOTE , CREATED at.
    pls help

  • it is not required to close cursor ?

    • It’s required. I might be forgot in some places.

  • I thought that a attribute with “_id” name was necessary for android system to use in CursorAdapter.

  • Abdulkadir Yiğit

    How to create two tables and joın with sqlite ?

  • narges

    hi-sorry i want have this source code. i am biginner android programmer.

  • Allen Rowland

    Hi Ravi Tamada
    Your project Android SQLite Database with Multiple Tables mainactivity.xml file
    says Hello World. Is that all the code needed for the project success to run?

  • Sacha

    I love this tutorial, thank you so much Ravi.

  • Kanika Sagar Dheman

    Thanks Ravi, great tutorial. What if one wanted to insert this data into tables in table layout?

  • CM Pundhir

    hello bro we need a mentor for our team , our team is selected in samrt india hackathon, we have to make an android app on passenger facilitation , can you help us ??

    • Hi

      Right now I am very much occupied with other works.

      • CM Pundhir

        ok, no problem
        thanks

  • Don Mills

    Ravi Tamada,

    Your tutorials have really helped me as a beginning developer. I have learned a great deal. If you were to write a book, I would buy it.

    As I was looking for a Sqlite tutorial/framework to follow and customize to make my own app, I also stumbled across someone else who is copying and pasting your tutorials as if they are his own. I can send you the link, if you are interested. I would post it here, but I don’t want to help the guy out like that.

    Thanks Again,
    Don Mills

    • Hi Don

      Please give me the link of the website that is cloning the article. The help would be much appreciated.

      Thank You

  • ghaith zouglami

    hello ravi , i need your help i work with android studio with gtfs files use attach database sqlite i want to show in listeview arrival and departure time for trip i don’t how use multiple tables in android studion and show one result and where and write this instruction:
    SELECT t.trip_id, trip_headsign, departure_time, direction_id, s.stop_name
    FROM stops s, routes r, stop_times st, calendar c, trips t
    WHERE departure_time > “00:00:00” and departure_time < "23:59:59"
    AND r.route_id=1 and s.stop_id = 42
    AND s.stop_id = st.stop_id
    AND st.trip_id = t.trip_id
    AND c.service_id = t.service_id
    AND c.monday=1 and direction_id=1;

  • Paul Tilson

    I followed your example very carefully and all it says is “Hello World”. When I search for “Hello World” in the project it says “No occurrences of “hello world” found in Project with mask ‘git.exe’. Do I need to install git? I already have. This is a great example for an old retired programmer but I have never programmed phone apps before. Any help would be appreciated. By the way, this is the 3rd app its happened on. Must be a setting or an add-on or something.

  • sulistyo adi

    Can I create more than one class DBHelper, because the data input of each table very much ???

  • عمر عيسى

    hi Ravi, excellent tutorial, am still waiting for the complete Todo example desperately, thanks bro fro this.

  • Poli

    can you help me in Activity class

    InsertActivity.java
    private long insertData(Subject subject){
    setContentView(R.layout.content_insert);
    String sub_code = et_sub_code.getText().toString();
    String sub_name = et_sub_name.getText().toString();
    String datestart = et_datestart.getText().toString();
    String dateend = et_dateend.getText().toString();

    if(!sub_code.isEmpty()&&!sub_name.isEmpty()&&!datestart.isEmpty()&&!dateend.isEmpty()){
    DatabaseHelper helper = new DatabaseHelper(this);
    helper.createSubject(???????); <——— This
    finish();
    }else{
    Toast.makeText(this,"put it all ",Toast.LENGTH_SHORT).show();
    }
    }

    Database.java
    public long createSubject(Subject subject,long[] student_ids) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_SUBCODE, subject.getSub_code());
    values.put(KEY_SUBNAME, subject.getSub_name());
    values.put(KEY_TIMESTART, subject.getTimestart());
    values.put(KEY_TIMEEND, subject.getTimeend());
    values.put(KEY_CREATED_AT, getDateTime());
    subject.getId();
    // insert row
    long subject_id = db.insert(TABLE_SUBJECT, null, values);

    // insert student_ids
    for (long student_id : student_ids) {
    createSubjectStudent(subject_id, student_id);
    }

    return subject_id;
    }

  • Dinesh EPDV

    I need to drop the database …can i acheive it programattically???

    • Check the SQL statements in onUpgrade() method. Why do you want to delete database instead of tables?

  • sridhar sridhar

    hi ravi,
    there is an error occur during the debug time

    in Debug clip board “Could not connect to remote process. Aborting debug session.”
    will be came what to do?

  • PP7

    tag3.setTagName(“Movies to watch”);
    db.updateTag(tag3); //doesn’t work

  • Suraj Chavan

    Does DATETIME is supported data type in sqlite?

    • Vratislav Jindra

      No, I suggest you to use INTEGER and save the datetime as amount of millis since the epoch (1. 1. 1970, 00:00:00.000). Although the amount of millis is long, the Integer data type in SQLite isn’t strictly 32 bit (you can save 64 bit long to it). This representation of date/time is widely used in Android programming, and I believe it’s the best way of storing date/time in SQLite as well.

  • Francis Dewara

    what if i will make it an online app that connects to mysql? what to do ?

  • Andhica Pratama

    thanks for the tutorial. I’ve using this code and change it and make layout.
    But how can i select the table and store it into array string for the tag_name. because i wanna make drop down menu. thanks

  • Mohammad Ali Kadiwala

    Code is awesome i’ll implement perfectly and also i getting all data but i have one question
    How to Get All data DateTime wise ?

    ThankYou 🙂

    • You need to add order by datetime in your query.

      • Mohammad Ali Kadiwala

        Please can show me how to i implement order by ?

  • Cheers!

  • Remonda

    what will happen if i didn’t close the database {db.close()}??

  • Aman Shekhar

    when should we use cursor.close() as I saw it is advised in many stackoverflow links.
    try {
    // perform your querry
    }
    catch (SQLException e) {
    e.printStackTrace();
    } finally {
    if (cursor != null) {
    cursor.close();
    }
    }

  • Adrian Abune

    Nice. Just upgraded my app to suport SQLite databases. For every update I was making a HTTP request ( every … 2 .. 3 seconds ) . Now, all updates go in a table, and once every 2 minutes I send them all with one HTTP request.

  • Lingam mohan krishnasen

    How To add A fourth table X in into same database with reinstalling the application or clearing the app data

  • Do we really need “created_at” in the “Tag” column?! to goal of the “tags” is to sort other, not to be sorted, right?

    • What if you have a screen to show all the tags sorted in created order.

  • Haya Sultan

    Asslam O Alaikum…I’m beginner…I’m confused why we use two constructor here?

    • Haya Sultan

      Constructors*

  • Wane Denis

    hi, i have make all the step of the tutorial but, i always get this error:

    “android.database.sqlite.SQLiteException: no such table: table_name (code 1): , while compiling: SELECT * FROM table_name”

    How can i fix this error?
    Thanks.

  • Wijen

    What if I want to show all object from todo_tags table, but instead of showing todo_id and tags_id, I want it to show note and tagname instead.. I’m so confused.. @@ravi8x:disqus

  • Bert Paul Eric

    I have a problem with the getTagName class how can I fix it

  • Parth Singh

    I think that the updateNoteTag method is incorrect, because todo table does not have a tag_id column. please check and correct it.

  • Kishorsinh Chudasama

    Can you make design part also…

  • Anas Limouri

    Why is there no Foreign Key statement upon creation for the todo_tag table?

  • AbelardoLG

    Hi everyone!

    Strictly speaking, a “todo” is related to a “tag” with a relationship 1:N since a “todo” only belongs to a tag. The table “todo” should contain a foreign id from “Tag” entity.

    This idea is the most simple one about a “todo” but you can extend it to a relationship N:M (as explained here) in order to meet with this use case: an appointment with a doctor could be tagged as “health” but also with “job”.

    Good work!

    • AbelardoLG

      The above example provided by @ravi8x:disqus is clearly 1:N and therefore SHOULDN’T proceed by creating an extra table to represent their relations.

      If we would like to represent the above example, it would be enough with a FK inside the “todo” table.

      Nevertheless, it’s a matter of personal taste to choose a strategy or another one. 🙂

  • AbelardoLG

    @ravi8x:disqus , there is a typo:
    Create a new class file under info.androidhive.sqlite.helper…
    when it should tell:
    Create a new class file under info.androidhive.sqlite.MODEL
    to create TODO and TAG class to model their entities. 🙂

    Best regards.

  • Reema Gupta Goyal

    Hi @ravi8x:disqus , If my applications have many tables, i need to call DataManager again and again for every activity or do i need to create singleton class of DataManager and i can use instance of it. If i create singleton calss when will i close the connection
    . Please Reply . Thanks in advance

    • Hi Reema

      You can try Realm database for easier implementation. When you have too many tables, SQLite gets complicated.

      • Reema Gupta Goyal

        Thanks for your reply ,

        I have alredy used Sqlite in my live application. I wonder creating many instances of DataManager is using memory, So is singleton method or using dagger is correct approach.

  • Murtaza

    Hi @ravi8x:disqus
    I would really appreciate if you help me with something, in Database Helper class, createTodo Method has long[] parameter passed to it, I was re creating your code in an actual app, I could not figure out as to how to pass that second parameter in Main Activity while saving the Data to Database using a Submit button.

    • just hold the string in variable and call new tag to insert data into DB and then do the rest of your business.. just sample prototype based on this tutorials and your request..

      myButton.setOnclickListener …

      String text1 = textEdit1.getText().toString();
      … and so on ..
      db = new DatabaseHelper(this);
      Tag tag1 = new Tag(text1);
      boolean test = db.createTag(tag1);
      if (test != false) {
      // do something when proccess true ..
      // maybe call function for refresh the list after added
      }

      …

  • Murtaza

    HI@ravi8x:disqus
    Once again i am writing to you, cause i have tried your code made into a practice application, the biggest problem i am facing is, everytime i add a note, nothing is being added, it only shows data inserted at 0 position. i do not know why ? can you help with another code.

    • Hi Murtaza

      Do you want to try Realm database?

  • Murtaza