Android SQLite Database

Android SQLite Database

1. What is SQLite?

SQLite is an Open Source database. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. The database requires limited memory at runtime (approx. 250 KByte) which makes it a good candidate from being embedded into other runtimes.

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before getting saved in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.

2. SQLite in Android

SQLite is embedded into every Android device. Using an SQLite database in Android does not require a setup procedure or administration of the database.

You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.

Access to an SQLite database involves accessing the file system. This can be slow. Therefore it is recommended to perform database operations asynchronously.

If your application creates a database, this database is by default saved in the directory  DATA/data/APP_NAME/databases/FILENAME.

The parts of the above directory are constructed based on the following rules. DATA is the path which the Environment.getDataDirectory() method returns. APP_NAME is your application name. FILENAME is the name you specify in your application code for the database.

3. SQLite architecture

3.1. Packages

The android.database package contains all necessary classes for working with databases. The android.database.sqlite package contains the SQLite specific classes.

3.2. Creating and updating database with SQLiteOpenHelper

To create and upgrade a database in your Android application you create a subclass of the SQLiteOpenHelper class. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.

In this class you need to override the following methods to create and update your database.

  • onCreate() – is called by the framework, if the database is accessed but not yet created.
  • onUpgrade() – called, if the database version is increased in your application code. This method allows you to update an existing database schema or to drop the existing database and recreate it via the onCreate() method.

Both methods receive an SQLiteDatabase object as parameter which is the Java representation of the database.

The SQLiteOpenHelper class provides the getReadableDatabase() and getWriteableDatabase() methods to get access to an SQLiteDatabase object either in read or write mode.

The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.

3.3. SQLiteDatabase

SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.

More specifically SQLiteDatabase provides the insert(), update() and delete() methods.

In addition it provides the execSQL() method, which allows to execute an SQL statement directly.

The object ContentValues allows to define key/values. The key represents the table column identifier and the value represents the content for the table record in this column. ContentValues can be used for inserts and updates of database entries.

Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .

rawQuery() directly accepts an SQL select statement as input.

query() provides a structured interface for specifying the SQL query.

SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

3.4. rawQuery() Example

The following gives an example of a rawQuery() call.

3.5. query() Example

The following gives an example of a query() call.

 

3.6. Cursor

A query returns a Cursor object. A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory.

To get the number of elements of the resulting query use the getCount() method.

To move between individual data rows, you can use the moveToFirst() and moveToNext() methods. The isAfterLast()method allows to check if the end of the query result has been reached.

Cursor provides typed get*() methods, e.g. getLong(columnIndex), getString(columnIndex) to access the column data for the current position of the result. The “columnIndex” is the number of the column you are accessing.

Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table.

A Cursor needs to be closed with the close() method call.

3.7. ListViews, ListActivities and SimpleCursorAdapter

ListViews are Views which allow to display a list of elements.

ListActivities are specialized activities which make the usage of ListViews easier.

To work with databases and ListViews you can use the SimpleCursorAdapter. The SimpleCursorAdapter allows to set a layout for each row of the ListViews.

You also define an array which contains the column names and another array which contains the IDs of Views which should be filled with the data.

The SimpleCursorAdapter class will map the columns to the Views based on the Cursor passed to it.

To obtain the Cursor you should use the Loader class.

Example

In this example i will be demonstrating how to work on SQLite Database i.e. local database for storing data and fetching data again.

VIDEO DEMO

Let’s get start by creating a project in Eclipse IDE.

1. Create a new project in Eclipse by navigating to File ⇒ New Android ⇒ Application Project and fill required details. (I kept my main activity name as MainActivity.java)

2. Open your your AndroidManifest.xml file and make your “MainActivity” as Launcher activity.

3. Create a new folder in res folder naming drawable by right clicking on res ⇒ New ⇒ Folder . In that folder create a new xml  file for button selector naming button_selector.xml and set it as background to buttons. We can go beyond just displaying image, where Android allows us to differentiate different states of the selected button.

4. Create a layout file for MainActivtiy.java under res ⇒ layout folder. I named the layout file as activity_main.xml. This layout contains some edittexts, buttons and a listview for displaying saved data.

 5. Create a java class naming Data_Model.java for getter and setter and add the following code. I am storing three things so I used three strings it can be changed according to need.

6. Now make a Database_Helper.java file for SQLite Database for storing the data and reading data back. For this class we extend the class with  SQLiteOpenHelper.

 7. Now, add the following code in MainActivity.java. In this class we save the data in database on button click and read back the data on button click.

8. For displaying data in ListView i make a custom layout file under res ⇒ layout folder. I named the layout file as customview_for_listview.xml.

9. Finally make a Custom_Adapter.java  for ListView and add the following code.

10. Run the application and you will get the output as shown in video and you are done.

Thanks. 🙂

Download Source Code – >

 

Leave a Comment

Comment (required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Name (required)
Email (required)