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.rawQuery
s.
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 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 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
.
The database schema is essentially
Users {
User {
Name
Password
}
... // Zero or more users
}
Users
will be our table, and each row will represent anUser
.- Each
User
has aName
andPassword
.
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 theWHERE
itself) - selectionArgs: Replaces
?
in theselection
String. - groupBy: An
SQL GROUP BY
clause (excluding theGROUP BY
itself). - having: An
SQL HAVING
clause (excluding theHAVING
itself). - orderBy: An
SQL ORDER BY
clause (excluding theORDER 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 String
s 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; }
}
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. 👍 💯