Android Lab essentials

DatabaseHelper.md

The SQLiteOpenHelper is a

A helper class to manage database creation and version management.

It might feel complicated, but once you understand how to use it, you would (hopefully) prefer SQLiteOpenHelper to multiple db.rawQuerys.

I'll explain with the help of Login and Signup activities. We'll probably get something similar for the assess. So, two birds in one stone. :)

---

The (only) proper way of using SQLiteOpenHelper is to extend and override some of its methods namely - onCreate, onUpgrade

onCreate - We'll create our tables here.

onUpgrade - Destroy all tables and create them again with new version number.

We'll also add custom functions for our use.

Signup

Signup is essentially entering values into the database (after checking to see if they exist already).

So, it reduces down to a SELECT query followed by an optional INSERT query.

Login

Login is essentially checking to see if certain values exist in the database.

So, it reduces down to a SELECT query. Infact, it'll be the same query used by Signup.

Schema

The database schema is essentially

Users {
    User {
        Name
        Password
    }
    ... // Zero or more users
}

• Users will be our table, and each row will represent an User.
• Each User has a Name and Password.

---

DatabaseHelper

This will be the class extended from SQLiteOpenHelper and will be responsible for all database related tasks.

Here is a basic stub:

public class DatabaseHelper extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "lab_assess";

    // Tables name
    private static final String TABLE_USERS = "users";

    // Table Columns names
    private static final String ID = "_id";
    private static final String COL_NAME = "name";
    private static final String COL_PASS = "password";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE = String.format("CREATE TABLE %s (%s INTEGER PRIMARY KEY, %s TEXT, %s TEXT)", ID, COL_NAME, COL_PASS);

        db.execSQL(CREATE_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);

        // Create tables again
        onCreate(db);
    }
}

As said earlier, the onCreate contains the query to create the table.

We'll ignore onUpgrade for the time being.

We still can't do anything with this helper yet. We'll change that by adding two custom functions - exists() and add()

exists() takes an User object and returns a boolean indicating whether that User exists in the database.

add() takes an User object and inserts it into the database.

Here's an implementation.

public void add(User user) {
    ContentValues values = new ContentValues()
        .put(COL_NAME, user.getName())
        .put(COL_PASSWORD, user.getPassword());

    SQLiteDatabase db = this.getWritableDatabase();
    db.insert(TABLE_USERS, null, values);
    db.close();
}

public boolean exists(User user) {
    SQLiteDatabase db = this.getReadableDatabase();

    String[] columns = { COL_NAME };
    String selection = String.format("%s LIKE ? ", COL_NAME);
    String[] selectionArgs = { user.getName() };

    Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs, null, null ,null);
        
    boolean exists = cursor.getCount() == 1;
    db.close();
    return exists;
}

Some explanations are in order.

this.getReadableDatabase and this.getWritableDatabase are to be used wherever appropriate. If you're gonna INSERT / UPDATE / DELETE, use the latter otherwise the former.

Always db.close at the end of the function. Has to do with performance and whatnot.

SQLiteDatabase has some convenience methods for some queries i.e db.insert, db.delete and db.update (optionally, check out db.replace)

db.query is the important function. This is the signature of the function

Cursor query (String table, 
                String[] columns, 
                String selection, 
                String[] selectionArgs, 
                String groupBy, 
                String having, 
                String orderBy)

• table: The table to query.
• columns: A list of which columns to return. Passing null will return all columns.
• selection: An SQL WHERE clause (excluding the WHERE itself)
• selectionArgs: Replaces ? in the selection String.
• groupBy: An SQL GROUP BY clause (excluding the GROUP BY itself).
• having: An SQL HAVING clause (excluding the HAVING itself).
• orderBy: An SQL ORDER BY clause (excluding the ORDER BY itself).

For checking if an user exists, the query we would use is

SELECT name FROM users WHERE name LIKE 'name'

This is represented in db.query as

db.query(TABLE_USERS, columns, selection, selectionArgs, null, null ,null);

The final piece is writing the User object. We can pass around Strings instead of objects, but abstracting the values into a POJO (Plain Old Java Object) such as User is much more idiomatic.

Here's an example implementation

public class User {

    private String name;
    private String password;

    public User() {}

    public User(String name, String password) {
        this.name = name;
        this.password = password;
    }

    public String getName() { return name; }

    public void setName(String name) { this.name = name; }

    public String getPassword() { return password; }

    public void setPassword(String password) { this.password = password; }
}

Using in your code

Suppose you have LoginActivity. You could use it like this

public LoginActivity extends Activity {
 
    private DatabaseHelper db;

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

        db = new DatabaseHelper(this);
        // .... other stuff
    }

    public void onLoginClick(View loginView) {
        String name;
        String password;

        // bind values from the EditTexts

       if (db.exists(new User(name, password)) {
           // Login success!
       }
    }
}

You could employ a similar logic for SignupActivity too.

P.S. If you are lazy, you can just use SharedPreferences ;).

Hope this helped.

Brilliant write up. 👍 💯

Just WOW.!!! 👍 :)