AndroidSketchpad

Welcome to my Blog.
Today our tutorial is covered Android database.




For database we use SQLite. It's a light weight. There is a lot of benefits to using SQLite database. you can find on online about why developers use SQLite. But In this post, I am not going to tell you about it, I am just showing it uses.

Let's start-
First, we create a Java Class and extend it by SQLiteOpenHelper class-
In this case, my class name is DatabaseHelper.

class DatabaseHelper extends SQLiteOpenHelper {
}

Now we have to override two methods
1.onCreate

2.onUpgrade
Let's do this

class DatabaseHelper extends SQLiteOpenHelper {

  @Override
  public void onCreate(SQLiteDatabase db) {

  }
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  
  }
}

One more thing left that we have to add- Constructor.
But In the constructor, we just take one parameter that is Context. To fill super we create two global variables of this class
1.DATABASE_NAME
2.VERSION
and CursorFactory is null in this case
Let's add this-

// The name of the database
private static final String DATABASE_NAME = "tasks.db";

// database version
private static final int VERSION = 1;

// Constructor
DatabaseHelper(Context context) {
  super(context, DATABASE_NAME, null, VERSION);
}

Before working on OnCreate methods we are going to add a new Class that name is Contract Class. what is Contract Class? Contract Class is a class that contains all the columns name variable of Database. It's really helpful. By adding this class we can reference it from any class so we don't think about correct column names. If we want to modify a column name then we easily modify this class. we don't need to search for the whole project.
Ok, Let's add a contract class.
In this case, Name is DB_Contract

public class DB_Contract {
}

Now we add an another inner class and implements Base columns-

public class DB_Contract {

  public static class Entry implements BaseColumns{
  }
}

Now time to create table name and column name-

public class DB_Contract {

  public static class Entry implements BaseColumns{

  //column name
  public static final String COLUMN_TASK_NAME= "task_name";
  public static final String COLUMN_TASK_SOLUTION= "task_solution";
  public static final String COLUMN_TASK_TYPE= "task_type";
  public static final String COLUMN_TASK_STATUS= "task_status";
  public static final String COLUMN_TASK_DUE= "task_due";
  public static final String COLUMN_TASK_DATE= "task_date";
  public static final String COLUMN_TASK_TIME= "task_time";
  
  //task type table name
  static final String TASK_TABLE_NAME = "tasks";

  }

}

Now time to write SQL Command-

final String CREATE_TABLE = "CREATE TABLE " + DB_Contract.Entry.TASK_TABLE_NAME + " (" +
  DB_Contract.Entry._ID+ " INTEGER PRIMARY KEY, " +
  DB_Contract.Entry.COLUMN_TASK_NAME + " TEXT NOT NULL, " +
  DB_Contract.Entry.COLUMN_TASK_SOLUTION + " TEXT, " +
  DB_Contract.Entry.COLUMN_TASK_TYPE + " TEXT, " +
  DB_Contract.Entry.COLUMN_TASK_STATUS + " BOOLEAN, " +
  DB_Contract.Entry.COLUMN_TASK_DUE + " BOOLEAN, " +
  DB_Contract.Entry.COLUMN_TASK_TIME + " TEXT, " +
  DB_Contract.Entry.COLUMN_TASK_DATE+ " TEXT, "+
  " UNIQUE (" + DB_Contract.Entry.COLUMN_TASK_NAME + ") ON CONFLICT REPLACE);";

Note: In the last line we add a unique keyword and make COLUMN_TASK_NAME as unique. and at last, we write ON CONFLICT REPLACE. For this line of code, If any task name is same as previous then it will be replaced.

now we add a log statement to print SQL command and also execute SQL command-

Log.e("TABLE",CREATE_TABLE);
db.execSQL(CREATE_TABLE);

oh! we finish onCreate method.
Now we are going to write onUpgrade method-
if our database update then we drop the previous table and create a new table.
For creating new table we just call again OnCreate-

db.execSQL("DROP TABLE IF EXISTS " + DB_Contract.Entry.TASK_TABLE_NAME);
onCreate(db);

That's it. we just finish our DatabaseHelper Class.
Now we have a database but we don't have any methods to access this database. To access database we are going to use Content Provider. In the next post, we will learn how to use the content provider.

Full code of DatabaseHelper-

class DatabaseHelper extends SQLiteOpenHelper {


    // The name of the database
    private static final String DATABASE_NAME = "tasks.db";

    //database version
    private static final int VERSION = 2;

    // Constructor
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, VERSION);
    }



    /**
     * Called when the tasks database is created for the first time.
     */

    @Override
    public void onCreate(SQLiteDatabase db) {

        // Create tasks table (careful to follow SQL formatting rules)
        final String CREATE_TABLE = "CREATE TABLE "  + DB_Contract.Entry.TASK_TABLE_NAME + " (" +
                DB_Contract.Entry._ID+ " INTEGER PRIMARY KEY, " +
                DB_Contract.Entry.COLUMN_TASK_NAME + " TEXT NOT NULL, " +
                DB_Contract.Entry.COLUMN_TASK_SOLUTION + " TEXT, " +
                DB_Contract.Entry.COLUMN_TASK_TYPE + " TEXT, " +
                DB_Contract.Entry.COLUMN_TASK_STATUS + " BOOLEAN, " +
                DB_Contract.Entry.COLUMN_TASK_DUE + " BOOLEAN, " +
                DB_Contract.Entry.COLUMN_TASK_TIME + " TEXT, " +
                DB_Contract.Entry.COLUMN_TASK_DATE+ " TEXT, "+
                " UNIQUE (" + DB_Contract.Entry.COLUMN_TASK_NAME + ") ON CONFLICT REPLACE);";


        //Log.e("TABLE",CREATE_TABLE);
        db.execSQL(CREATE_TABLE);
    }


    /**
     * This method discards the old table of data and calls onCreate to recreate a new one.
     * This only occurs when the version number for this database (DATABASE_VERSION) is incremented.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DB_Contract.Entry.TASK_TABLE_NAME);
        onCreate(db);
    }
}

Thank you for reading.
Hope you will learn how to create a database in Android.
Next, we will learn how to access the database.
Happy coding.