I’m going to skip over external file storage for now, since its pretty much the same as internal file storage, but just writing to the SDCard folder instead. Anyways, the last part of this series will cover using the DB. I’m going to assume now that you now something about SQL, as that’s going to be necessary. Also, there is a lot of material to cover, so I’m just going to do the quickest of overviews for now.
Now, if you have any sort of complexity or large numbers of records, you will want to use the SQLite database, as its going to be the most efficient in terms of sorting and such. Its fairly easy to get started. A few items about the database that I’m going to create here first:
- The database file name for now will be: tables.db
- We’ll just create two tables: Alpha and Beta
- Beta will have a foreign key to Alpha
I think that will be enough to get most of us started. First off, we should implement the interface:
android.database.sqlite.SQLiteOpenHelper
This will help us create and update or database. Here’s a sample implementation:
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
|
private static class OpenHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "tables.db";
private static final int DATABASE_VERSION = 1;
private static final String ALPHA_TABLE_NAME = "alpha";
private static final String BETA_TABLE_NAME = "beta";
OpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + ALPHA_TABLE_NAME
+ "(id INTEGER PRIMARY KEY, intValue INTEGER, name TEXT, content TEXT)");
db.execSQL("CREATE TABLE " + BETA_TABLE_NAME
+ "(id INTEGER PRIMARY KEY, intValue INTEGER, title TEXT, alpha_id INTEGER references "
+ ALPHA_TABLE_NAME + "(id), content TEXT, content2 TEXT) ");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w("Example",
"Upgrading database, this will drop tables and recreate.");
db.execSQL("DROP TABLE IF EXISTS " + ALPHA_TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + BETA_TABLE_NAME);
onCreate(db);
}
} |
So a couple of things to note here. The constructor will try to open the named database, if it does not exist, then it will be created and will call the onCreate function. If it already exists, it will compare the version numbers and if the existing database is a older version, then the onUpgrade function will be called. So here is where I create the Alpha and Beta tables. As you can see in the create table statement, a foreign key to the alpha table was created as alpha_id.
So now we have this interface implemented, how do we use it? Well, its used to help with opening a database, but we still need functions to read/write to the database. In my code, I created a DBHelper class to do this stuff. So then the constructor of the DBHelper will call the OpenHelper to help with opening the database. It looks like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
private static final String INSERT_ALPHA = "insert into " + ALPHA_TABLE_NAME
+ "(intValue, name, content) values (?,?,?)";
private static final String INSERT_BETA = "insert into " + BETA_TABLE_NAME
+ "(title, alpha_id, content, content2) values (?,?,?,?)";
public DBHelper(Context context) {
_context = context;
OpenHelper openHelper = new OpenHelper(_context);
_db = openHelper.getWritableDatabase();
_insertAlphaStmt = _db.compileStatement(INSERT_ALPHA);
_insertBetaStmt = _db.compileStatement(INSERT_BETA);
} |
So you see here how we use the OpenHelper. By calling the function getWritableDatabase, it will trigger to loading/creation of the database. Another thing to note here is that I pre-comple the insert statements for inserting into the table, this will make the actual inserting of data much quicker when you actually do it.
So now we can create and load the database, we need to know how to read/write from it. I’m just going to show a set of functions for doing this with the Alpha table for now:
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
| public long insertAlpha(int intValue, String name, String content) {
_insertAlphaStmt.bindLong(1, intValue);
_insertAlphaStmt.bindString(2, name);
_insertAlphaStmt.bindString(3, content);
long res = _insertAlphaStmt.executeInsert();
return res;
}
public void updateStory(int id, int intValue, String name, String content) {
ContentValues values=new ContentValues();
values.put("intValue", intValue);
values.put("name", namet);
values.put("content",content);
_db.update(ALPHA_TABLE_NAME, values, "id=?", new String[]{id + ""});
}
public Alpha getAlpha(String id) {
Cursor cursor = _db.query(ALPHA_TABLE_NAME,
new String[] {"id", "intValue", "name", "content"},
"id=?", new String[]{id}, null, null, "id asc");
Alpha alpha = null;
if(cursor.moveToFirst()) {
alpha = new Alpha();
alpha.id = cursor.getLong(0);
alpha.intValue = (int) cursor.getLong(1);
alpha.name = cursor.getString(2);
alpha.content= cursor.getString(3);
}
if(cursor != null && !cursor.isClosed()) {
cursor.close();
}
return alpha;
}
public Vector<Alpha> selectAllStories() {
Vector<Alpha> alphas = new Vector<Alpha>();
Cursor cursor = _db.query(ALPHA_TABLE_NAME,
new String[] {"id", "intValue", "name", "content"},
null, null, null, null, "id asc");
if(cursor.moveToFirst()) {
do {
alpha = new Alpha();
alpha.id = cursor.getLong(0);
alpha.intValue = (int) cursor.getLong(1);
alpha.name = cursor.getString(2);
alpha.content= cursor.getString(3);
alphas.add(alpha);
} while(cursor.moveToNext());
}
if(cursor != null && !cursor.isClosed()) {
cursor.close();
}
return alphas;
}
public void deleteAlpha(int id) {
_db.delete(ALPHA_TABLE_NAME, "id=?", new String[] {id + ""});
} |
So that shows how to insert, update, delete and read from the database. A couple of things to note, the database uses Long’s for ints, so you’ll notice that instead of getInt or putInt, we use getLong and putLong for the values.
So that’s it, that should give you a quick overview of how to implement your database. There’s lots more you can do, but I won’t go into that for now.
