Cincinnati Android Developers Meetup

apatheticyogurtSoftware and s/w Development

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

66 views

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android Developers
Meetup


Android Data Storage


SQLite





7/20/2011

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Android Data Storage Options



Shared Preferences



Simple key
-
value storage for user preferences



Internal Storage


Files that are stored to internal storage are only accessible
to the application by default.



External Storage


Files stored here can be stored to the SD card or the internal
memory on the device and are readable by all applications



SQLite

Databases


Data stored in
SQLite

is only accessible from within the
application that created the database.



Network Connection


Data can be retrieved over a network and parsed using
the included XML or JSON libraries.

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database on Android

To use the
SQLite

Database within Android the following steps must be preformed
using the framework native to the platform.



Create the Result Object


Open the Database


Construct the SQL Statement


Execute the Statement


Use the Cursor


Close the Cursor

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database


Create the Result Object

To ensure the database cursor is correctly closed the results from the cursor are
copied into object(s) and returned or returned in a list. In order to facilitate this a
POJO is created to store the data.


public

class

NumbersBean

{


private

int

storedValue
;



public

static

final

class

NumbersColumns

implements

BaseColumns



{



public

static

final

String
STORED_VALUE

=
"
stored_value
"
;


}



public

int

getStoredValue
()


{



return

storedValue
;


}


...

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database
-

Open the Database

The easiest way to open the database is to create a class that extends the
SQLiteOpenHelper

class which contains the database name, version, and table
names. This class will ensure that the version of the database that is installed on
the device is up to date.


import

android.database.sqlite.SQLiteOpenHelper
;


public

class

SQLHelper

extends

SQLiteOpenHelper

{


private

static

final

int

DATABASE_VERSION

= 1;



private

static

final

String
DATABASE_NAME

=
"
TestDB.db
"
;



public

static

final

String
TABLE_NAME_NUMBERS

=
"numbers"
;


...

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database
-

Open the Database (Cont)

Create an instance of the
SQLHelper

class in a non
-
UI thread using standard
threading or an
AsynchTask
. In our example we’re doing everything in the UI
thread for simplicity, but this is not recommended.


public

class

MainAction

extends

Activity

{


private

static

SQLHelper

sqlHelper
;



public

void

onCreate
(Bundle
savedInstanceState
)


{



super
.onCreate
(
savedInstanceState
);



setContentView
(
R.layout.
main
);




sqlHelper

=
new

SQLHelper
(
getApplicationContext
());



...

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database
-

Construct the SQL Statement


Create a class that can be used to access the database and convert the cursor
rows into objects or list of objects using the
SQLiteQueryBuilder

class.


public

class

TestSQL

{


...



public

NumbersBean

getNumber
(
int

number)


{



NumbersBean

numbersBean

=
null
;



String[] projection =
new

String[]{








NumbersBean.NumbersColumns.
STORED_VALUE

}
;




String selection =
NumbersBean.NumbersColumns.
STORED_VALUE

+
" = ?"
;



String[]
selectionArgs

=
new

String[]{
Integer.
toString
(number)};



String
sortOrder

=
null
;




SQLiteQueryBuilder

sqLiteQueryBuilder

=
new

SQLiteQueryBuilder
();



SQLiteDatabase

sqLiteDatabase

=
sqLiteOpenHelper
.getReadableDatabase
();



sqLiteQueryBuilder.setTables
(
SQLHelper.
TABLE_NAME_NUMBERS
);



...

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database


Execute the
Statement


Execute the SQL statement that was created by the
SQLiteQueryBuilder

and
prepare to use the cursor.


The projection is an array of column names that will be available via the cursor.

The selection is the where clause where all parameters are represented with a “?”.

The
selectionArgs

contain the values that are passed into the prepared statement.


public

NumbersBean

getNumber
(
int

number)

{


...


Cursor
cursor

=
null
;



try


{



cursor =
sqLiteQueryBuilder.query
(
sqLiteDatabase
, projection,




selection,
selectionArgs
,
null
,
null
,
sortOrder
);



if
(cursor !=
null

&&
cursor.moveToFirst
())



{

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database


Use the Cursor


Copy the values from the cursor into a POJO by:



Instantiating a class where the values from the cursor can be stored.


Getting the column index since columns cannot be accessed by name.


Getting the value from the cursor and store it in the POJO.


public

NumbersBean

getNumber
(
int

number)

{


...


if
(cursor !=
null

&&
cursor.moveToFirst
())


{



numbersBean

=
new

NumbersBean
();






int

storedValueCol

=







cursor.getColumnIndex
(
NumbersBean.NumbersColumns.
STORED_VALUE
);



numbersBean.setStoredValue
(
cursor.getInt
(
storedValueCol
));


}


...


Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database


Close the Cursor & DB


Create a class that can be used to access the database and convert the cursor
rows into objects or list of objects using the
SQLiteQueryBuilder

class.


public

NumbersBean

getNumber
(
int

number)

{


...


}
finally
{



if
(cursor !=
null
)



{




cursor.close
();



}


}


sqLiteDatabase.close
();



return

numbersBean
;

}

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

SQLite

Database


Use the Objects


Use the POJO object that was returned from the database



TestSQL

testSQL

=
new

TestSQL
(
sqlHelper
);



if
(
numbersBean

!=
null
)


{




NumbersBean

numbersBean

=
testSQL.getNumber
(234);



int

value =
numbersBean.getStoredValue
();



// Do something with the value


}

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Cincinnati Android
Developers

http://www.meetup.com/cincy
-
android/

DotLoop, LLC.
-

4445 Lake Forest Drive Suite 430
-

Blue Ash, OH 45242

Topics



File
vs

DB Performance


Further Reading


Cool Apps


Future
Meetup

Topics