Workflow working with database in Android

Working with local database in Android required when we want to save user activities and load that state soon when the app reload / started. Some new guy that just started with Android Development need to figure out what the “worklow” to working with database in Android. Okay, here is the recipe:

Knowing Model, Helper and DataSource
Mostly, when we open some open-source Android app that contains database access, we see this three categories. Yes, it might have Model, Helper and DataSource in their class, filename or Description.
What they are?

1. Model
This class will contains “getter” and “setter” function and will produce Objects that will contains “result data”. What it’s looks like?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

public class MarketingItem {
private long id;
private Date date;
private String type;
private int intensity;

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public Date getDate() {
return date;
}

public void setDate(long utcTime) {
this.date = new Date(utcTime * 1000);
}

public String getType() {
return type;
}

public void setType(String t) {
this.type = t;
}

public int getIntensity() {
return intensity;
}

public void setIntensity(int i) {
this.intensity = i;
}

@Override
public String toString() {
return "Marketing Entry for " + date.toString();
}
}

2. Helper
This class will have function to make direct access into database itself. At this example, I using SQLITE. So, every activities like “CREATE TABLE”, “DROP TABLE”, etc will be defined inside this class.
What it’s looks like?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

public class SQLiteHelper extends SQLiteOpenHelper {
public static final String COLUMN_ID = "_id";
public static final String TABLE_USER = "users";
public static final String COLUMN_USER_NAME = "name";
public static final String COLUMN_USER_GENDER = "gender";
public static final String COLUMN_USER_ADDRESS = "address";
public static final String COLUMN_USER_PHONE = "phone";

private static final String DB_NAME = "marketing.db";
private static final int DB_VERSION = 1;

private static final String DB_USER_CREATE = "create table "
+ TABLE_USER + "(" + COLUMN_ID + " integer primary key autoincrement, "
+ COLUMN_USER_NAME + " text not null, "
+ COLUMN_USER_GENDER + " integer not null, "
+ COLUMN_USER_ADDRESS + " text not null, "
+ COLUMN_USER_PHONE + " text not null);";

public SQLiteHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DB_USER_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
this.wipeDB(db);
}

public void wipeDB(SQLiteDatabase db) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
onCreate(db);
}
}

3. DataSource
The last things here is DataSource. This class will working as bridge between Model and Helper. DataSource contains queries into database and will wrap the results into Model objects that can be used in Activity or Intent or something else that need database result.

What it’s looks like ?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

public class UserDataSource {
private SQLiteDatabase db;
private SQLiteHelper dbHelper;
private String[] columns = { SQLiteHelper.COLUMN_ID,
SQLiteHelper.COLUMN_USER_NAME,
SQLiteHelper.COLUMN_USER_GENDER,
SQLiteHelper.COLUMN_USER_ADDRESS,
SQLiteHelper.COLUMN_USER_PHONE};

public UserDataSource(Context context) {
dbHelper = new SQLiteHelper(context);
}

public void open() throws SQLiteException {
db = dbHelper.getWritableDatabase();
}

public void close() {
dbHelper.close();
}

public User getUser() {
this.open();
Cursor cursor = db.query(SQLiteHelper.TABLE_USER, columns,
null, null, null, null, null);
cursor.moveToFirst();
User newUser = cursorToUser(cursor);
cursor.close();
this.close();
return newUser;
}

public boolean doesUserExist() {
Cursor cursor = db.query(SQLiteHelper.TABLE_USER, columns,
null, null, null, null, null);
int count = cursor.getCount();
return count > 0 ? true : false;
}

public User createUser(String name, int gender, String address, String phone) {
ContentValues values = new ContentValues();
values.put(SQLiteHelper.COLUMN_USER_NAME, name);
values.put(SQLiteHelper.COLUMN_USER_GENDER, gender);
values.put(SQLiteHelper.COLUMN_USER_ADDRESS, address);
values.put(SQLiteHelper.COLUMN_USER_PHONE, phone);

long insertId = db.insert(SQLiteHelper.TABLE_USER, null, values);
Cursor cursor = db.query(SQLiteHelper.TABLE_USER, columns,
SQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null);
cursor.moveToFirst();
User newUser = cursorToUser(cursor);
cursor.close();
return newUser;
}

public void deleteUser() {
this.open();
dbHelper.wipeDB(this.db);
this.close();
}

private User cursorToUser(Cursor cursor) {
User user = new User();
user.setId(cursor.getLong(0));
user.setName(cursor.getString(1));
user.setGender(cursor.getInt(2));
user.setAddress(cursor.getString(3));
user.setPhone(cursor.getString(4));
return user;
}

public User generateDummyUser() {
this.open();
ContentValues values = new ContentValues();
values.put(SQLiteHelper.COLUMN_USER_NAME, "John Doe");
values.put(SQLiteHelper.COLUMN_USER_GENDER, 0);
values.put(SQLiteHelper.COLUMN_USER_ADDRESS, "Indonesia Street");
values.put(SQLiteHelper.COLUMN_USER_PHONE, "28738923");

long insertId = db.insert(SQLiteHelper.TABLE_USER, null, values);
Cursor cursor = db.query(SQLiteHelper.TABLE_USER, columns,
SQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null);
cursor.moveToFirst();
User newUser = cursorToUser(cursor);
cursor.close();
this.close();
return newUser;
}
}

This is the diagram:

Hope this fast explanation and codes can help to understanding more about making interaction with android database.

Yodi Aditya

Workflow working with database in Android

Leave a Reply Cancel reply