Business

Technology

 

 

 

 

 

The number one benefit of information technology is that it empowers people to do what they want to do. It lets people be creative. It lets people be productive. It lets people learn things they didn't think they could learn before, and so in a sense it is all about potential.

Steve Ballmer

Android : Creating a SQLite Database

Looking for an Android Developer?

If you or your company are looking for an Android Developer contact us or read about our Android Development Service

Android : Creating a SQLite Database key visual

 

Creating a SQLite database for Android is a 3 step process. This article details the 3 steps required to create a SQLite database for Android. 

The 3 steps to creating a SQLite database for Android are:

  1. Create a DBHelper that overrides the SQLiteOpenHelper
  2. Implement the onCreate method of the SQLiteOpenHelper
  3. Call the getWritableDatabase method

Creating the DBHelper class

The DBHelper class follows the Respsitory pattern. Where the access to the underlaying data is contained in a "Repository" class. This frees up your main logic to concentrate on the business logic and pushes all the data handling responsibility to the Repository. Depending on the complexity of your application the Repository may act as a "Facade" where the calls are simply proxied through to a Repository responsible for the domain object.

The code below creates a very simple template for the DBHelper

	package au.com.finalconcept;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

        // Define the version and database file name
	private static final String DB_NAME = "example.db";
	private static final int DB_VERSION = 1;
        
        // Use a static class to defined the data structure
        // This will come in very handy if you using Agile 
        // As your development model
	private static class UserTable {
		private static final String NAME = "user";
		private static final String COL_ID = "id";
		private static final String COL_USERNAME = "username";
		private static final String COL_PASSWORD = "password";
	}

	private SQLiteDatabase db;

        // Constructor to simplify Business logic access to the repository 
	public DBHelper(Context context) {

		super(context, DB_NAME, null, DB_VERSION);
                // Android will look for the database defined by DB_NAME
                // And if not found will invoke your onCreate method
		this.db = this.getWritableDatabase();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
                
                // Android has created the database identified by DB_NAME
                // The new database is passed to you vai the db arg
                // Now it is up to you to create the Schema.
                // This schema creates a very simple user table, in order
                // Store user login credentials
		db.execSQL(String.format("CREATE TABLE %s (%s INTEGER PRIMARY KEY, %s TEXT, %s TEXT)",
						UserTable.NAME, UserTable.COL_ID,
						UserTable.COL_USERNAME, UserTable.COL_PASSWORD));

	}

	
	public String[] getUserCredentials() {

		String[] creds;
		Cursor cursor;

		creds = new String[3];

		cursor = this.db.query(UserTable.NAME, new String[] {
				UserTable.COL_USERNAME, UserTable.COL_PASSWORD}, 
                                null, null, null, null, null);

		if (cursor.moveToFirst()) {

			creds[0] = cursor.getString(0);
			creds[1] = cursor.getString(1);
			creds[2] = cursor.getString(2);
			cursor.close();
			
		} else {
			
			throw new Exception("No User Credentials Found");
			
		}

		return creds;

	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// Later when you change the DB_VERSION 
                // This code will be invoked to bring your database
                // Upto the correct specification
	}
}

Using the new Respository is now as simple as instantiating the DBHelper and calling your methods.

	DBHelper db = new DBHelper(this);
try {
    String[] creds = db.getUserCredentials();
} catch (Exception e) {
    e.printStackTrace();
    // Do something to fetch the credentials from the user
    // Maybe fire of an Activity to have the user enter them
}

charlie.collins shows an other method of using a nested private class. This has the advantage of exposing to your business logic ONLY the methods relevant to the application. 

This article was created with insperation from charlie.collins. His article can be found at the link below.

Android SQLite Basics: creating and using a database, and working with sqlite3 

JoelDub commented:

Thanks for this. Simple and clean explanation.

JoelDub commented:

Thanks for this. Simple and clean explanation. It's amazing how convoluted this is for some authors.

Best commented:

Clean and the best! read many but this is just simple and clean!

vino commented:

nice

jorge commented:

I'm new to android but in 4.0.3 throw new Exception was a no go. Used Log.v.

Thanks for this post. Finally got to understand how to create, access databases in Android.

mounika commented:

Simple and neat explanation...

Thank u. It helped me.

 

mounika commented:

Simple and neat explanation...

Thank u. It helped me.

 

mounika commented:

Simple and neat explanation...

Thank u. It helped me.

 

amit commented:

thanks for such a simple and clean explenation it helped me a lot.

amit commented:

thanks for such a simple and clean explenation it helped me a lot.

amit commented:

thanks for such a simple and clean explenation it helped me a lot.

Matthew Johnson commented:

Unfortunately, the URL you give for Charlie Collins' SQL intro is broken: even screaming-penguin itself now gives an amusing error message when you try to connect to it.

zenidasooner commented:

You made some decent points there. I checked on the net for more information about the issue and found most people will go along with your views on this website. buy real instagram followers at http://socialformula.com/ Thanks :)

Add a comment

*
*
Your email will not be published
*
*