Android SQLite Database Tutorial - Storage Options and Implementation

1. Cincinnati Android Developers MeetupAndroid Data Storage – SQLite7/20/2011

2. 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.

3. 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

4. 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. publicclassNumbersBean { privateintstoredValue; publicstaticfinalclassNumbersColumnsimplementsBaseColumns { publicstaticfinal String STORED_VALUE = "stored_value"; } publicintgetStoredValue() { returnstoredValue; } ...

5. 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. importandroid.database.sqlite.SQLiteOpenHelper; publicclassSQLHelperextendsSQLiteOpenHelper { privatestaticfinalintDATABASE_VERSION = 1; privatestaticfinal String DATABASE_NAME = "TestDB.db"; publicstaticfinal String TABLE_NAME_NUMBERS = "numbers"; ...

6. 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. publicclassMainActionextends Activity { privatestaticSQLHelpersqlHelper; publicvoidonCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); sqlHelper = newSQLHelper(getApplicationContext()); ...

7. 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. publicclassTestSQL { ... publicNumbersBeangetNumber(int number) { NumbersBeannumbersBean = 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; SQLiteQueryBuildersqLiteQueryBuilder = newSQLiteQueryBuilder(); SQLiteDatabasesqLiteDatabase = sqLiteOpenHelper.getReadableDatabase(); sqLiteQueryBuilder.setTables(SQLHelper.TABLE_NAME_NUMBERS); ...

8. 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. publicNumbersBeangetNumber(int number) { ... Cursor cursor = null; try { cursor = sqLiteQueryBuilder.query(sqLiteDatabase, projection, selection, selectionArgs, null, null, sortOrder); if(cursor != null && cursor.moveToFirst()) {

9. 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. publicNumbersBeangetNumber(int number) { ... if(cursor != null && cursor.moveToFirst()) { numbersBean = newNumbersBean(); intstoredValueCol = cursor.getColumnIndex(NumbersBean.NumbersColumns.STORED_VALUE); numbersBean.setStoredValue(cursor.getInt(storedValueCol)); } ...

10. 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. publicNumbersBeangetNumber(int number) { ... }finally{ if(cursor != null) { cursor.close(); } } sqLiteDatabase.close(); returnnumbersBean; }

11. SQLite Database – Use the Objects Use the POJO object that was returned from the database TestSQLtestSQL = newTestSQL(sqlHelper); if(numbersBean != null) { NumbersBeannumbersBean = testSQL.getNumber(234); int value = numbersBean.getStoredValue(); // Do something with the value }

12. Topics File vs DB Performance Further Reading Cool Apps Future Meetup Topics