Android Database Tutorial - Basic SQLite DB

UPDATE: This is superseded by the Room Database Tutorial.

Here is the new tutorial: Room Database, Fragments, RecyclerView, LiveData, ViewModel and Data Binding

Is this what you're looking for?

The areas of Android Development that are touched on in this tutorial are:

• SQLiteOpenHelper - For Database purposes,
• AsyncTask - To load the ListView without using the Main UI Thread,
• Thread - To execute queries without using the Main UI Thread,
• ViewHolder Pattern - To display the BlogPost titles in the ListView,
• Dynamic Creation of Database Row IDs - a lot of the database logic deals with the BlogPost titles, so I needed a way to handle insertion.

Introduction

I'm making this tutorial as a future reference for myself, and publishing it so it may be a basic step by step guide for others to implement Databases in their Android Application. I have Googled for "Android Database Tutorial". The Google Developer results are confusing, Android Hive is old/erroneous and Vogella's one is really detailed/long.

The App

This will be a tutorial on the basics of creating a database in Android. The end result will be an app that can create records and display them as a ListView. The reason the app is so simple is to remove the clutter, to allow the database to be the main focus of the tutorial.

Also there isn't a whole lot of data integrity checks, logging, user feedback, exception handling here, so keep your panties unbunched.

I can evolve this app in the following ways, and I will with future posts:

• Advanced Data Types in the Database,
• Content Providers,
• RecyclerView for a nice layout to the ListView,
• ActionBar commands to create and delete BlogPosts,
• Pulling from a RESTful Service, etc...

Android Database Technology

Android uses an SQLite Database when storing data on the device. You can use a remote database too, but it's best to use a RESTful Web Service to accomplish tasks in that way. Storing content locally is good if the user doesn't have a network connection and wants to view current data, or create data for synching to the cloud later.

Database Implementation Flow

I'll provide a synopsis of the steps below. As Android Database interaction is Model Driven, we'll implement our Model Class first. "Model" is just a fancy word for "POJO", which itself is a fancy word for "Plain Old Java Object". Secondly we'll implement the outline of our Database Class, to provide the foundation to build interactions on. The Database Class will be a Singleton, so only one instance will exist in our app, it's best to get into good practices early. There's two schools of thought on the next step; one, implement the queries as needed, as not to bloat functionality, two, write the CRUD logic up front to reduce development time later on and we're in the database zone at this time. I'd recommend the former for normal development, but for the purposes of keeping this tutorial streamlined, I'll take the latter approach. Finally we'll add the UI Activities to make use of the database functionality.

I have the source code posted to GitHub: Android Database Tutorial-Basic repository. Each of the steps below will be a commit.

Step 0: New App with Blank Activity

This step is optional, you may already have the app that you wish to use the database in. Create the new app, give it a name and select the "Blank Activity" to begin with. I just accepted all the defaults for the naming. As none of that matters much in the context of this tutorial. Close MainActivity.java and activity_main.xml when they open. We'll deal with them later. At this point I added my project to Source Control with git.

Commit Diff: 394ff7f

Step 1: Implement the Model Class

We'll add a new package under our app, for good practice. We'll call it "model". Next add a Java Class, which will be our model. We'll call it "BlogPost". Our app will display words that the user creates in a ListView on screen. With that in mind, here is our simple Model Class, it has the ID and the Title fields.

	public class BlogPost {
	private long id;
	private String title;
	/**
	* Empty Constructor
	*/
	public BlogPost() {
	setId(-1);
	}
	/**
	* Construcor with only the title.
	* @param title The Title of the BlogPost
	*/
	public BlogPost(String title) {
	setId(-1);
	setTitle(title);
	}
	/**
	* Full Constructor
	* @param id The ID number of the BlogPost in the Database
	* @param title The Title of the BlogPost
	*/
	public BlogPost(long id, String title) {
	setId(id);
	setTitle(title);
	}
	/**
	* Getters and Setters
	*/
	public long getId() {
	return id;
	}
	public void setId(long id) {
	this.id = id;
	}
	public String getTitle() {
	return title;
	}
	public void setTitle(String title) {
	this.title = title;
	}
	/**
	* Determines if this Instance is equal to the argument.
	* It makes the comparison on the case insensitive titles of the BlogPost Objects.
	*
	* @param o Any Object.
	* @return True, if o is the same as this Instance.
	* False, if o is not the same as this Instance.
	*/
	@Override
	public boolean equals(Object o) {
	return o instanceof BlogPost && this.title.equalsIgnoreCase(((BlogPost) o).getTitle());
	}
	}

view raw BlogPost.java hosted with ❤ by GitHub

The ID field always needs to be present in a Model Class that we want to be in the database. The ID is the Primary Key for the database. The Database will start from 1, not from 0 as you'd expect. Hopefully, simple stuff here.

Commit Diff: 391add4

Step 2: Implement the Database Class

In this step, we will create the outline of the Database Class. Add another package, call it "database". Add a Java Class, call it "DatabaseConnection" and the kind will be Singleton.

In our code editor we make it so DatabaseConnection.java extends SQLiteOpenHelper. SQLiteOpenHelper requires that we implement its two abstract methods, onCreate() and onUpgrade(). The lifecycle method onCreate() is called when the app is first launched. In the onCreate() method implementation, we issue a CREATE TABLE command to the database. The lifecycle method onUpgrade() is called when the app is updated to a later version, i.e when an update is installed from the Google Play Store. In the onUpgrade(), you can manage the addition and removal of columns from the database table. But in our implementation, we'll just drop the old database in favour of the new version, which will be created when we call the onCreate() method manually.

	public class DatabaseConnection extends SQLiteOpenHelper {
	// Database Information.
	private static final String DATABASE_NAME = "BlogPost";
	private static final int DATABASE_VERSION = 1;
	// Table Name.
	private static final String TABLE_BLOGPOSTS = "blogposts";
	// Blog Post Table Columns, these map to the public fields on the BlogPost class.
	private static final String KEY_ID = "id";
	private static final String KEY_TITLE = "title";
	// Singleton Instance.
	private static DatabaseConnection ourInstance;
	// The ID of the next row number, used for Insertions.
	private static long NEXT_ROW_ID_NUMBER;
	/**
	* Simple Constructor for Database Connection
	*/
	private DatabaseConnection(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
	setupNextRowIdNumber();
	}
	/**
	* Allows retrieval of the Singleton Instance,
	* in the app we'll call DatabaseConnection.getInstance(getApplicationContext()) to get the
	* instance.
	*/
	public static synchronized DatabaseConnection getInstance(Context context) {
	// Initialise the instance if it's null.
	if (ourInstance == null) ourInstance = new DatabaseConnection(context);
	return ourInstance;
	}
	/**
	* Used by the Android System to Create the Database Initially.
	*
	* @param database The Database Instance from Android System.
	*/
	@Override
	public void onCreate(SQLiteDatabase database) {
	// SQL Query for creating the table.
	final String CREATE_BLOGPOSTS_TABLE = "CREATE TABLE " + TABLE_BLOGPOSTS + " ("
	+ KEY_ID + " INTEGER PRIMARY KEY, " + KEY_TITLE + " TEXT)";
	database.execSQL(CREATE_BLOGPOSTS_TABLE);
	}
	/**
	* Used by the Android System to Upgrade the database version.
	*
	* @param database The Database Instance from Android System.
	* @param oldVersion The Old version of the Database.
	* @param newVersion The New version of the Database.
	*/
	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
	// Drop the older table, if there's a newer table, and create the newer table.
	if (newVersion > oldVersion) {
	// SQL Query for Deleting (dropping) the table.
	final String DROP_BLOGPOSTS_TABLE = "DROP TABLE IF EXISTS " + TABLE_BLOGPOSTS;
	database.execSQL(DROP_BLOGPOSTS_TABLE);
	onCreate(database);
	}
	}

view raw DatabaseConnection.java hosted with ❤ by GitHub

Commit Diff: c685b65

Step 3: Basic Queries for our Use Cases

The use cases of this app are;

1. Create a new BlogPost.
2. Delete an existing BlogPost,
3. View all the BlogPosts in the database.

Still in our DatabaseConnection class, we add the following three methods after the onUpgrade() method.

	// ... after the onUpgrade() method
	/**
	* Adds a new BlogPost to the Database.
	* It performs a check to see if the BlogPost is unique.
	*
	* @param blogPost The BlogPost to add.
	*/
	public synchronized void addBlogPost(BlogPost blogPost) {
	// Abandon if new BlogPost is a duplicate.
	if (isBlogPostInDatabase(blogPost)) return;
	SQLiteDatabase database = getWritableDatabase();
	ContentValues contentValues = new ContentValues();
	contentValues.put(KEY_ID, NEXT_ROW_ID_NUMBER);
	contentValues.put(KEY_TITLE, blogPost.getTitle());
	database.insert(TABLE_BLOGPOSTS, null, contentValues);
	NEXT_ROW_ID_NUMBER++;
	database.close();
	}
	/**
	* Gets all the BlogPost objects that are in the Database.
	*
	* @return All the BlogPosts in the Database.
	*/
	public synchronized List<BlogPost> getAllBlogPosts() {
	List<BlogPost> blogPosts = new ArrayList<>();
	SQLiteDatabase database = getReadableDatabase();
	Cursor cursor = database.query(TABLE_BLOGPOSTS, null, null, null, null, null, null);
	if (cursor.moveToFirst()) {
	do {
	blogPosts.add(new BlogPost(
	cursor.getLong(cursor.getColumnIndex(KEY_ID)),
	cursor.getString(cursor.getColumnIndex(KEY_TITLE))));
	} while (cursor.moveToNext());
	}
	cursor.close();
	database.close();
	return blogPosts;
	}
	/**
	* Deletes a BlogPost from the Database.
	* It checks if the BlogPost exists in the Database before proceeding.
	*
	* @param blogPostToDelete The BlogPost to delete.
	*/
	public synchronized void deleteBlogPost(BlogPost blogPostToDelete) {
	// If the Id is not set, discover the ID;
	if (blogPostToDelete.getId() == -1) {
	long blogPostId = findBlogPostRowId(blogPostToDelete);
	if (blogPostId == -1) return; // Exit deletion process, as BlogPost is not in Database.
	else blogPostToDelete.setId(blogPostId); // Continue with deletion of existing BlogPost.
	}
	String whereClause = KEY_ID + "=?";
	String[] whereArgs = new String[]{String.valueOf(blogPostToDelete.getId())};
	SQLiteDatabase database = getWritableDatabase();
	database.delete(TABLE_BLOGPOSTS, whereClause, whereArgs);
	database.close();
	}

view raw DatabaseConnection.java hosted with ❤ by GitHub

Commit Diff: 16bf861

Step 3 Bonus: The other CRUD queries

The abbreviation CRUD stands for Create, Retrieve, Update and Delete. So far we have the Creation, Deletion and Retrieval of records implementation. Let's see how the Updating and other forms of Retrieval of records are handled with respect to our Model. Again, keeping things simple, we'll ignore the possibility of raising exceptions and handling return codes here.

The following methods are pretty self explanatory, they allow for the retrieval of one record, allow updating of one record, count the records, determine if a record exists, and find the ID of an existing record in the database.

	// ... after deleteBlogPost() method
	/**
	* Locates a BlogPost using it's Id.
	* Useful for ListView's OnClickListener, when dealing with positions that correspond to the
	* indices in the database, e.g. FragmentListView in a DrawerPattern.
	*
	* @param id The Id of the BlogPost to search the Database for.
	* @return The BlogPost corresponding to the Id
	*/
	public synchronized BlogPost getBlogPost(int id) {
	BlogPost blogPost = null;
	String selection = KEY_ID + "=?";
	String[] selectionArgs = new String[]{String.valueOf(id)};
	String limit = String.valueOf(1);
	SQLiteDatabase database = getReadableDatabase();
	Cursor cursor = database.query(
	TABLE_BLOGPOSTS, null, selection, selectionArgs, null, null, null, limit);
	if (cursor.moveToFirst()) {
	blogPost = new BlogPost(
	cursor.getLong(cursor.getColumnIndex(KEY_ID)),
	cursor.getString(cursor.getColumnIndex(KEY_TITLE)));
	}
	cursor.close();
	database.close();
	return blogPost;
	}
	/**
	* Updates the BlogPost in the Database. Uses the BlogPost's Id to do the lookup.
	*
	* @param updatedBlogPost The BlogPost to update.
	*/
	public synchronized void updateBlogPost(BlogPost updatedBlogPost) {
	SQLiteDatabase database = getWritableDatabase();
	ContentValues contentValues = new ContentValues();
	contentValues.put(KEY_ID, updatedBlogPost.getId());
	contentValues.put(KEY_TITLE, updatedBlogPost.getTitle());
	String whereClause = KEY_ID + "=?";
	String[] whereArgs = new String[]{String.valueOf(updatedBlogPost.getId())};
	database.update(TABLE_BLOGPOSTS, contentValues, whereClause, whereArgs);
	database.close();
	}
	/**
	* Gets the number of BlogPosts in the database.
	* Useful for Array sizing.
	*
	* @return The number of BlogPosts in the database.
	*/
	public synchronized int getBlogPostsCount() {
	String[] columns = new String[]{KEY_ID};
	SQLiteDatabase database = getReadableDatabase();
	Cursor cursor = database.query(TABLE_BLOGPOSTS, columns, null, null, null, null, null);
	int numberOfBlogPosts = cursor.getCount();
	cursor.close();
	database.close();
	return numberOfBlogPosts;
	}
	/**
	* Searches the database for the BlogPost.
	* This Search uses the BlogPost's equals() method.
	*
	* @param blogPost The BlogPost to search for.
	* @return True, if the BlogPost is in the database.
	* False, if the BlogPost is not in the database.
	*/
	private synchronized boolean isBlogPostInDatabase(BlogPost blogPost) {
	List<BlogPost> blogPosts = getAllBlogPosts();
	for (BlogPost blogPostInList : blogPosts) {
	if (blogPostInList.equals(blogPost))
	return true;
	}
	return false;
	}
	/**
	* Finds the Row Id of the BlogPost in the database.
	* This Search uses the BlogPost's equals() method.
	*
	* @param blogPost The BlogPost to search for.
	* @return The Row Id of the BlogPost, if it exists.
	* -1, if the BlogPost does not exist.
	*/
	private synchronized long findBlogPostRowId(BlogPost blogPost) {
	List<BlogPost> blogPosts = getAllBlogPosts();
	for (BlogPost blogPostInList : blogPosts) {
	if (blogPostInList.equals(blogPost))
	return blogPostInList.getId();
	}
	return -1;
	}
	/**
	* Sets up the value for the next Row ID.
	* This is required for Insertion.
	* This is used at creation of the DatabaseConnection Instance.
	*/
	private synchronized void setupNextRowIdNumber() {
	Long maxBlogPostId = determineMaxBlogPostId();
	if (maxBlogPostId == 0)
	NEXT_ROW_ID_NUMBER = 0;
	else
	NEXT_ROW_ID_NUMBER = ++maxBlogPostId;
	}
	/**
	* Determines the largest value of the ID column for the BlogPosts table.
	*
	* @return The maximum value of the ID columm in the BlogPosts table.
	*/
	private Long determineMaxBlogPostId() {
	long maxBlogPostId = 0;
	String[] columns = new String[]{"MAX(" + KEY_ID + ")"};
	SQLiteDatabase database = getReadableDatabase();
	Cursor cursor = database.query(
	TABLE_BLOGPOSTS, columns, null, null, null, null, null, null);
	if (cursor.moveToFirst()) maxBlogPostId = cursor.getLong(0);
	cursor.close();
	database.close();
	return maxBlogPostId;
	}
	} // End DatabseConnection.java

view raw DatabaseConnection.java hosted with ❤ by GitHub

Commit Diff: 2340692

Step 4: UI Activity to Insert, Delete and Display Records

As this tutorial is not about UI programming, I'll just leave code for calling the queries the commit ID, for the rest of the code, here for you to examine. The AsyncTask and ViewHolder Pattern is defined in that Commit ID. The UI is an EditText to input BlogPost titles, then two buttons to Add and Delete the BlogPosts, based on their titles, in the ListView based on their titles matching the text in the EditText.

Loading https://gist.github.com/8488564....

	public class MainActivity extends Activity {
	private EditText editText;
	private Button addButton, deleteButton;
	private ListView blogPostsListView;
	/**
	* The OnClickListener to add a BlogPost to the database.
	* It Spawns a thread that inserts the new unique BlogPost into the database.
	* It finally refreshes the ListView.
	*/
	private OnClickListener addBlogPostOnClickListener = new OnClickListener() {
	@Override
	public void onClick(View v) {
	Thread addNewBlogPostThread = new Thread() {
	@Override
	public void run() {
	BlogPost blogPostToAdd = new BlogPost(editText.getText().toString().trim());
	DatabaseConnection databaseConnection = DatabaseConnection.getInstance(getApplicationContext());
	databaseConnection.addBlogPost(blogPostToAdd);
	refreshBlogPostsListView();
	}
	};
	addNewBlogPostThread.start();
	}
	};
	/**
	* The OnClickListener to remove a BlogPost from the database.
	* It Spawns a new Thread to delete the BlogPost from the database.
	* Finally it refreshes the ListView.
	*/
	private OnClickListener deleteBlogPostOnClickListener = new OnClickListener() {
	@Override
	public void onClick(View v) {
	Thread deleteBlogPostThread = new Thread() {
	@Override
	public void run() {
	BlogPost blogPostToDelete = new BlogPost(editText.getText().toString().trim());
	DatabaseConnection databaseConnection = DatabaseConnection.getInstance(getApplicationContext());
	databaseConnection.deleteBlogPost(blogPostToDelete);
	refreshBlogPostsListView();
	}
	};
	deleteBlogPostThread.start();
	}
	};
	@Override
	protected void onCreate(Bundle savedInstanceState) {
	super.onCreate(savedInstanceState);
	setContentView(R.layout.activity_main);
	setupUiResources();
	}
	private void setupUiResources() {
	editText = (EditText) findViewById(R.id.editText);
	addButton = (Button) findViewById(R.id.addButton);
	addButton.setOnClickListener(addBlogPostOnClickListener);
	deleteButton = (Button) findViewById(R.id.deleteButton);
	deleteButton.setOnClickListener(deleteBlogPostOnClickListener);
	blogPostsListView = (ListView) findViewById(R.id.blogPostListView);
	refreshBlogPostsListView();
	}
	/**
	* Refreshes the ListView using an AsyncTask.
	* This reduces the load on the UI Thread.
	*/
	private void refreshBlogPostsListView() {
	LoadBlogPostListViewTask loadBlogPostListViewTask =
	new LoadBlogPostListViewTask(getApplicationContext(), blogPostsListView);
	loadBlogPostListViewTask.execute();
	}
	}

view raw MainActivity.java hosted with ❤ by GitHub

Commit Diff: 7b59a0e, this one is a bit of a mish-mash of updates.

Conclusion

I hope this tutorial was basic enough for novice programmers to understand and comprehensive for intermediate programmers to get ideas of this possibilities of storing data locally on the Android device.

All the code is in the GitHub Repository, AndroidDatabaseTutorial-Basic. If the app fails to build on your IDE, start by changing the "buildToolsVersion" in your "app/build.gradle" to match the version in your SDK Manager.