Android - SQLite - Basics

A classic way to deal with database, with Android, it's to use the SQLite classes.

What's unique in SQLite is that you don't need a server where running your database.
Indeed, everything is already inside your Android application.

And of course impossible to another application to read this database.

Let's have a look in this Android SQLite tutorial to learn basics with an example.
A bit like an Hello World SQLite.

How it works

You don't need to download anything because SQLite are native Android classes from version 1.

The main purpose of this tutorial is to add users and delete them all, with 2 buttons (b1 and b2).

We're going to add these users randomly in a database, with SQLite.

At that end, we've to create SQL requests like every query for classic uses of SQL.

For example: CREATE TABLE user_table (user_id INTEGER, username TEXT).

So a very classic way.

As we don't like to repeat ourself, we created constants with static final keywords and also use a specific class only for that, in our case BadprogContract.

The BadprogCRUD is supposed to be a class with Create, Read, Update and Delete methods.

In our example, only the Create is done (of course, free to you to create them).

The delete request is directly in the code of the b2.setOnClickListener method of the MainActivity.

The way to move from a row to another one is with the Cursor class.

The way to add values in the database is with the ContentValues class.

Finally we use the changeCursor() and notifyDataSetChanged() methods to tell the database and the display to change data.

Notice that we need to recall the query() methods inside the 2 setOnClickListener().

And if you would like to change the schema of your database, adding new columns for example, you should call the onUpgrade() method.
If you do it, don't forget then to change the DBVERSION to a more recent one (2 instead of 1 for example).

ListView

With our example we're going to use a ListView.
Why?

Because it's a component especially designed to display data in a list way.

But to be used, this ListView must have an Adapter.

As we're going to use a database, this Adapter will be a SimpleCursorAdapter.

SQLite

For the SQLite basics, we need a class that extends SQLiteOpenHelper.
This class overrides at least 3 methods:

1. The constructor(context, name, factory, version)
2. onCreate(SQLiteDataBase db)
3. onUpgrade(SQLiteDataBase db, int oldVersion, int newVersion)

In our example there is another constructor with errors management (even if we're not going to see this in that tutorial).

Lets's code it

MainActivity.java

package com.badprog.android.tutorial.tuto_7_badprogtutorialsqlitebasics;

import java.util.Random;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.widget.Button;
import android.widget.ListView;

import com.badprog.android.tutorial.tuto_7_badprogtutorialsqlitebasics.BadprogContract.DBUSER;

public class MainActivity extends Activity {

    private BadprogCRUD crud;
    private SQLiteDatabase db;
    private Cursor c;
    private ContentValues values;
    int rand;
    private SimpleCursorAdapter sca;
    private String[] arrayUser = new String[] { "Alexander", "Barbara",
            "Charles", "Dave", "Eric", "Fred", "Greg", "Henry", "Iren",
            "James", "Kayle", "Lucy", "Mike", "Nao", "Olaf", "Patrik",
            "Quentin", "Roger", "Steven", "Tracy", "Ursulla", "Vera",
            "Wilfrid", "Xao", "Yoni", "Zian" };

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        Log.d("==========", "==========");

        crud = new BadprogCRUD(this);
        db = crud.getWritableDatabase();
        values = new ContentValues();
        String[] columns = { DBUSER._ID, DBUSER.NAME };
        int[] toViews = { R.id.tvId, R.id.tvName };
        c = db.query(DBUSER.TABLE, columns, null, null, null, null, DBUSER._ID
                + " DESC");
        sca = new SimpleCursorAdapter(this, R.layout.activity_main, c, columns,
                toViews, 0);
        Button b1 = new Button(this);
        b1.setText("Add User");
        Button b2 = new Button(this);
        b2.setText("Delete All");
        ListView listView = (ListView) findViewById(R.id.list1);
        listView.addHeaderView(b1);
        listView.addFooterView(b2);
        listView.setAdapter(sca);

        b1.setOnClickListener(new View.OnClickListener() { // button Add User

            @Override
            public void onClick(View v) {
                rand = new Random().nextInt(arrayUser.length);
                values.put(DBUSER.NAME, arrayUser[rand]);
                db.insert(DBUSER.TABLE, null, values);
                c.moveToLast();
                String[] columns = { DBUSER._ID, DBUSER.NAME };
                c = db.query(DBUSER.TABLE, columns, null, null, null, null,
                        DBUSER._ID + " DESC");
                sca.changeCursor(c);
                sca.notifyDataSetChanged();
            }
        });

        b2.setOnClickListener(new View.OnClickListener() { // button Delete All

            @Override
            public void onClick(View v) {
                db.delete(DBUSER.TABLE, null, null);
                c = db.query(DBUSER.TABLE, null, null, null, null, null, null,
                        null);
                sca.changeCursor(c);
                sca.notifyDataSetChanged();
            }
        });

    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

}

BadprogCRUD.java

package com.badprog.android.tutorial.tuto_7_badprogtutorialsqlitebasics;

import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.badprog.android.tutorial.tuto_7_badprogtutorialsqlitebasics.BadprogContract.DBINFO;
import com.badprog.android.tutorial.tuto_7_badprogtutorialsqlitebasics.BadprogContract.DBTYPE;
import com.badprog.android.tutorial.tuto_7_badprogtutorialsqlitebasics.BadprogContract.DBUSER;

public class BadprogCRUD extends SQLiteOpenHelper {

    /************************ Variables ************************/
    public static final String SQL_CREATE_USER = DBTYPE.CREATE_TABLE
            + DBUSER.TABLE + DBTYPE.PARENTHESIS_OPEN + DBUSER._ID
            + DBTYPE.INTEGER_PRIMARY_KEY + DBTYPE.COMMA + DBUSER.NAME
            + DBTYPE.TEXT + DBTYPE.PARENTHESIS_CLOSE;

    private static final String SQL_UPDATE_USER = DBTYPE.DROP_TABLE_IF_EXISTS
            + DBUSER.TABLE;

    /************************* Methods *************************/
    /**
     * It makes possible the Database to be create, read, write and update
     */
    public BadprogCRUD(Context context) {
        super(context, DBINFO.DBNAME, null, DBINFO.DBVERSION);
        Log.d("DB VERSION = ", String.valueOf(DBINFO.DBVERSION));
    }

    /**
     * It makes possible the Database to be create, read, write and update.
     * Manage also the Errors.
     */
    public BadprogCRUD(Context context, String name, CursorFactory factory,
            int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    /**
     * What to do on creation.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.w(BadprogCRUD.class.getName(), "DataBase created");
        db.execSQL(SQL_CREATE_USER);
    }

    /**
     * What to do on update.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(BadprogCRUD.class.getName(), "Upgrading database from version "
                + oldVersion + " to " + newVersion
                + ", the new schema is now updated.");
        db.execSQL(SQL_UPDATE_USER);
        onCreate(db);
    }

}

BadprogContract.java

package com.badprog.android.tutorial.tuto_7_badprogtutorialsqlitebasics;

import android.provider.BaseColumns;

/**
 * Contract class in order to create statically database schemas
 */
public class BadprogContract {

    /**
     * Constructor
     */
    public BadprogContract() {
    }

    /**
     * DataBase Schema Info
     */
    public static abstract class DBINFO {
        public static final String DBNAME = "badprog_user.db";
        public static final int DBVERSION = 1;
    }

    /**
     * DataBase Schema Region
     */
    public static abstract class DBUSER implements BaseColumns {
        public static final String TABLE = "user_table";
        public static final String NAME = "user_name";
    }

    /**
     * DataBase type
     */
    public static abstract class DBTYPE {
        public static final String BLOB                     = " BLOB";
        public static final String INTEGER                     = " INTEGER";
        public static final String INTEGER_PRIMARY_KEY         = " INTEGER PRIMARY KEY";
        public static final String NULL                     = " NULL";
        public static final String REAL                     = " REAL";
        public static final String TEXT                     = " TEXT";
        public static final String COMMA                     = ", ";
        public static final String PARENTHESIS_OPEN         = " (";
        public static final String PARENTHESIS_CLOSE         = " )";
        public static final String CREATE_TABLE             = "CREATE TABLE ";
        public static final String DROP_TABLE_IF_EXISTS     = "DROP TABLE IF EXISTS ";
    }

}

activity_main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity" >

    <TextView
        android:id="@+id/tvId"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:paddingRight="10dp" />

    <TextView
        android:id="@+id/tvName"
        android:layout_width="50dp"
        android:layout_height="wrap_content"
        android:layout_toRightOf="@id/tvId" />

    <ListView
        android:id="@+id/list1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

</RelativeLayout>

Conclusion

A great example to learn SQLite basics.

Well done, once again, you've made it.