Android development - A Micro Blogging App Part 4 - Databases

scacchicgardenSoftware and s/w Development

Dec 13, 2013 (3 years and 7 months ago)

66 views

Android development - A Micro Blogging App
Part 4 - Databases
Brink van der Merwe
10 March 2011
Databases in Android
 Android uses databases to persist data so that the data is
there even if the user kills the app or shuts down the device
and powers it back on again.
 It is useful to store data locally in order to access it faster and
have it available even when the network is down.
 In our micro blogging app a service will run in the background
and periodically update the database.
Databases in Android
 Android uses databases to persist data so that the data is
there even if the user kills the app or shuts down the device
and powers it back on again.
 It is useful to store data locally in order to access it faster and
have it available even when the network is down.
 In our micro blogging app a service will run in the background
and periodically update the database.
Databases in Android
 Android uses databases to persist data so that the data is
there even if the user kills the app or shuts down the device
and powers it back on again.
 It is useful to store data locally in order to access it faster and
have it available even when the network is down.
 In our micro blogging app a service will run in the background
and periodically update the database.
SQLite
 SQLite is an open source database that has been around for a
long time and is popular on many small devices.

Reasons why SQLite is a great t for Android app
development:
1
It's a zero-conguration database.
2
It does not have a server.There's no SQLite database process
running.
3
It's a single-le database.
4
It's open source.
 SQLite is not an alternative to a full SQL server,instead it is
an alternative to using a local le with an arbitrary format.
SQLite
 SQLite is an open source database that has been around for a
long time and is popular on many small devices.

Reasons why SQLite is a great t for Android app
development:
1
It's a zero-conguration database.
2
It does not have a server.There's no SQLite database process
running.
3
It's a single-le database.
4
It's open source.
 SQLite is not an alternative to a full SQL server,instead it is
an alternative to using a local le with an arbitrary format.
SQLite
 SQLite is an open source database that has been around for a
long time and is popular on many small devices.

Reasons why SQLite is a great t for Android app
development:
1
It's a zero-conguration database.
2
It does not have a server.There's no SQLite database process
running.
3
It's a single-le database.
4
It's open source.
 SQLite is not an alternative to a full SQL server,instead it is
an alternative to using a local le with an arbitrary format.
SQLite
 SQLite is an open source database that has been around for a
long time and is popular on many small devices.

Reasons why SQLite is a great t for Android app
development:
1
It's a zero-conguration database.
2
It does not have a server.There's no SQLite database process
running.
3
It's a single-le database.
4
It's open source.
 SQLite is not an alternative to a full SQL server,instead it is
an alternative to using a local le with an arbitrary format.
SQLite
 SQLite is an open source database that has been around for a
long time and is popular on many small devices.

Reasons why SQLite is a great t for Android app
development:
1
It's a zero-conguration database.
2
It does not have a server.There's no SQLite database process
running.
3
It's a single-le database.
4
It's open source.
 SQLite is not an alternative to a full SQL server,instead it is
an alternative to using a local le with an arbitrary format.
SQLite
 SQLite is an open source database that has been around for a
long time and is popular on many small devices.

Reasons why SQLite is a great t for Android app
development:
1
It's a zero-conguration database.
2
It does not have a server.There's no SQLite database process
running.
3
It's a single-le database.
4
It's open source.
 SQLite is not an alternative to a full SQL server,instead it is
an alternative to using a local le with an arbitrary format.
SQLite
 SQLite is an open source database that has been around for a
long time and is popular on many small devices.

Reasons why SQLite is a great t for Android app
development:
1
It's a zero-conguration database.
2
It does not have a server.There's no SQLite database process
running.
3
It's a single-le database.
4
It's open source.
 SQLite is not an alternative to a full SQL server,instead it is
an alternative to using a local le with an arbitrary format.
SQLiteOpenHelper

SQLiteOpenHelper is a helper class that provides a connection
to the database,creating the connection if it doesn't already
exist.
 The database class it returns is an instance of
SQLiteDatabase.
SQLiteOpenHelper

SQLiteOpenHelper is a helper class that provides a connection
to the database,creating the connection if it doesn't already
exist.
 The database class it returns is an instance of
SQLiteDatabase.
Schema

We want elds for the following information about each tweet:
1
created
at - when the tweet was sent.
2
txt - text of the tweet.
3
user - the user who sent the tweet.
 Each row in our table will contain the data for one tweet,and
these items will be the columns in our schema,along with a
unique ID for each tweet.
Schema

We want elds for the following information about each tweet:
1
created
at - when the tweet was sent.
2
txt - text of the tweet.
3
user - the user who sent the tweet.
 Each row in our table will contain the data for one tweet,and
these items will be the columns in our schema,along with a
unique ID for each tweet.
Schema

We want elds for the following information about each tweet:
1
created
at - when the tweet was sent.
2
txt - text of the tweet.
3
user - the user who sent the tweet.
 Each row in our table will contain the data for one tweet,and
these items will be the columns in our schema,along with a
unique ID for each tweet.
Schema

We want elds for the following information about each tweet:
1
created
at - when the tweet was sent.
2
txt - text of the tweet.
3
user - the user who sent the tweet.
 Each row in our table will contain the data for one tweet,and
these items will be the columns in our schema,along with a
unique ID for each tweet.
Schema

We want elds for the following information about each tweet:
1
created
at - when the tweet was sent.
2
txt - text of the tweet.
3
user - the user who sent the tweet.
 Each row in our table will contain the data for one tweet,and
these items will be the columns in our schema,along with a
unique ID for each tweet.
DBHelper
 We will manage our database in the class DBHelper,which
extends SQLiteOpenHelper.
 The onCreate() and onUpgrade() methods are the only ones
in our application where we need to use SQL.
 We'll execute CREATE TABLE in onCreate() to create a table
in our database.
 In a production application,we would use ALTER TABLE in
onUpgrade() when the schema changes,but we'll use DROP
TABLE and recreate the table.
DBHelper
 We will manage our database in the class DBHelper,which
extends SQLiteOpenHelper.
 The onCreate() and onUpgrade() methods are the only ones
in our application where we need to use SQL.
 We'll execute CREATE TABLE in onCreate() to create a table
in our database.
 In a production application,we would use ALTER TABLE in
onUpgrade() when the schema changes,but we'll use DROP
TABLE and recreate the table.
DBHelper
 We will manage our database in the class DBHelper,which
extends SQLiteOpenHelper.
 The onCreate() and onUpgrade() methods are the only ones
in our application where we need to use SQL.
 We'll execute CREATE TABLE in onCreate() to create a table
in our database.
 In a production application,we would use ALTER TABLE in
onUpgrade() when the schema changes,but we'll use DROP
TABLE and recreate the table.
DBHelper
 We will manage our database in the class DBHelper,which
extends SQLiteOpenHelper.
 The onCreate() and onUpgrade() methods are the only ones
in our application where we need to use SQL.
 We'll execute CREATE TABLE in onCreate() to create a table
in our database.
 In a production application,we would use ALTER TABLE in
onUpgrade() when the schema changes,but we'll use DROP
TABLE and recreate the table.
Cursors

A query returns a set of rows along with a pointer called a
cursor.

One can retrieve results one at a time from the cursor,
causing it to advance each time to the next row.
Cursors

A query returns a set of rows along with a pointer called a
cursor.

One can retrieve results one at a time from the cursor,
causing it to advance each time to the next row.
DbHelper
public class DbHelper extends SQLiteOpenHelper {
static final String TAG ="DbHelper";
static final String DB_NAME ="timeline.db";
static final int DB_VERSION = 1;
static final String TABLE ="timeline";
static final String C_ID = Columns_ID;
static final String C_CREATED_AT ="created_at";
static final String C_TEXT ="txt";
static final String C_USER ="user";
Context context;
//Constructor
public DbHelper(Context context) {
super(context,DB_NAME,null,DB_VERSION);
this.context = context;
}
//Called only once,first time the DB is created
@Override
public void onCreate(SQLiteDatabase db) {
String sql ="create table"+ TABLE +"("+ C_ID +"int primary key,"
+ C_CREATED_AT +"int,"+ C_USER +"text,"+ C_TEXT +"text)";
db.execSQL(sql);
Log.d(TAG,"onCreated sql:"+ sql);
}
//Called whenever newVersion!= oldVersion
@Override
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion) {
//Typically do ALTER TABLE statements,but...we're just in development,
db.execSQL("drop table if exists"+ TABLE);//drops the old database
Log.d(TAG,"onUpdated");
onCreate(db);//run onCreate to get new database
}
}
Using a Service to update the database
 We update the local database by using an inner class,
Updater,in the UpdaterService class.
 Recall that UpdaterService extends the Service class.
Using a Service to update the database
 We update the local database by using an inner class,
Updater,in the UpdaterService class.
 Recall that UpdaterService extends the Service class.
Updater inner class
//Thread that performs the actual update from the online service
private class Updater extends Thread {
List<Twitter.Status> timeline;
public Updater() {
super("UpdaterService-Updater");}
@Override
public void run() {
UpdaterService updaterService = UpdaterService.this;
while (updaterService.runFlag) {
Log.d(TAG,"Updater running");
try {
try {
timeline = app.getTwitter().getFriendsTimeline();
} catch (TwitterException e) {
Log.e(TAG,"Failed to connect to twitter service",e);
}
db = dbHelper.getWritableDatabase();
//Loop over the timeline and print it out
ContentValues values = new ContentValues();
for (Twitter.Status status:timeline) {
//Insert into database
values.clear();
values.put(DbHelper.C_ID,status.id);
values.put(DbHelper.C_CREATED_AT,status.createdAt.getTime());
values.put(DbHelper.C_TEXT,status.text);
values.put(DbHelper.C_USER,status.user.name);
db.insertOrThrow(DbHelper.TABLE,null,values);
Log.d(TAG,String.format("%s:%s",status.user.name,status.text));
}
//Close the database
db.close();
Log.d(TAG,"Updater ran");
Thread.sleep(DELAY);
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
Verifying that the database got created

If the database le got created successfully,it will be located
in/data/data/package name/databases/timeline.db
 Android ships with the command line tool sqlite3.This tool
give you access to the database itself.

To see if your database schema got created properly:
1
Open up your terminal,or command line window.
2
Type adb shell to connect to your running emulator or physical
phone.
3
Change directory to where your database le is created by
typing cd/data/data/package name/databases/.
4
Connect to the database by using the sqlite3 timeline.db
command.

One can see the list of all commands by typing.help on the
sqlite3> prompt.Use.schema to verify that the schema got
created.
duplicate IDs in database
 Second time around,when your service runs,you'll notice that
it fails and that you get many SQLExceptions in logcat.
 This happens because we have duplicate IDs.

We are fetching all the data from the online service,including
IDs used online.We get the data via the getFriendsTimeline()
call,which returns the twenty most recent posts in the past
24 hours - thus you're likely to get duplicates.

One possible x is to change db.insert() to
db.insertOrThrow(),catch the SQLException and ignore it.
duplicate IDs in database
 Second time around,when your service runs,you'll notice that
it fails and that you get many SQLExceptions in logcat.
 This happens because we have duplicate IDs.

We are fetching all the data from the online service,including
IDs used online.We get the data via the getFriendsTimeline()
call,which returns the twenty most recent posts in the past
24 hours - thus you're likely to get duplicates.

One possible x is to change db.insert() to
db.insertOrThrow(),catch the SQLException and ignore it.
duplicate IDs in database
 Second time around,when your service runs,you'll notice that
it fails and that you get many SQLExceptions in logcat.
 This happens because we have duplicate IDs.

We are fetching all the data from the online service,including
IDs used online.We get the data via the getFriendsTimeline()
call,which returns the twenty most recent posts in the past
24 hours - thus you're likely to get duplicates.

One possible x is to change db.insert() to
db.insertOrThrow(),catch the SQLException and ignore it.
duplicate IDs in database
 Second time around,when your service runs,you'll notice that
it fails and that you get many SQLExceptions in logcat.
 This happens because we have duplicate IDs.

We are fetching all the data from the online service,including
IDs used online.We get the data via the getFriendsTimeline()
call,which returns the twenty most recent posts in the past
24 hours - thus you're likely to get duplicates.

One possible x is to change db.insert() to
db.insertOrThrow(),catch the SQLException and ignore it.
Summary
At this point we can pull the statuses of our friends and post them
into the local database.We still don't have a way to view this
data,but we can verify that the data is in the database.
Summary