Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to manage a SQLite Database for Data Storage in Android.

See in this article how to manage, in Android Development, SQLite Databases for Data Storage.

An SQLite database is preferred for mobile platforms and apps requiring RDBMS capabilities. It has various advantages, such as efficient use of memory, disk space, and disk bandwidth, which make it different from other databases. In addition, it is highly reliable and requires no maintenance by a database administrator.

On the Android platform, SQLite helps in creating independent databases for apps and helps to store and manage complex, structured app data. All such databases are stored in the /data/data/<package_name>/databases folder on the device. By default, all databases are private and are accessible only by the app that created them. Some important considerations that should be kept in mind while creating databases are:

  • Files, such as image and audio files, should not be stored in databases. Instead, the path of the file should be stored using a string, preferably a fully qualified content provider URI.
  • All tables should include an auto-increment key field that will function as a unique index value for every row. Although it is optional to include this key while creating tables, it becomes mandatory if you later want to share the table using a content provider.
  • A design should be created for the database and tables that are required for the app. It is a good practice to identify the column structure of each table to ensure data integrity in the database.

Creating a Database

An Android app may need to use databases to store the private app data. For example, a Contacts Manager app may need to use a database to store contact details. You can create an SQLite database either programmatically or by using the Linux shell.

Creating a Database Programmatically

In Android, you can create a database programmatically by using the

android.database.sqlite.SQLiteDatabase class. The SQLiteDatabase class
exposes various methods to manage an SQLite database. The SQLiteDatabase class has methods to create and delete databases, execute SQL commands on the database, and perform other common database management tasks.

Once a database is created, the

execSQL(String sql)method of the SQLiteDatabase class
can be used to execute SQL statements for creating tables in the database. The following code illustrates how to create a database and a table within that database:

// Declare static variables to hold string values for the names private 
  static final string DATABASE_NAME = "MyContacts.db";
   private static final String TABLE_NAME = "ContactDetails"; 
  // Declare an object of SQLiteDatabase
   private SQLiteDatabase sqlDB;
   // Write the following code inside the onCreate() method 
  // Open the database if it exists otherwise create the MyContacts database 
  sqlDB=openOrCreateDatabase (DATABASE_NAME, SQLiteDatabase.CREATE_IF_NECESSARY, null);
  // Create a table with three columns: columns id, name, and phone
  sqlDB.execSQL("CREATE TABLE "+TABLE_NAME+"(id INTEGER PRIMARY KEY, Name TEXT, Phone LONG)");

The preceding code will create a database named MyContacts and a table named ContactDetails.

The database MyContacts will be stored in the data\data\<package_name>\databases folder of the device/ emulator.

However, the preferred method to create a database is by using the SQLiteOpenHelper class. The SQLiteOpenHelper class is an abstract class used for creating, opening, and upgrading databases, and managing database versions.

For example, you are creating a login activity for an app that asks the users to register their usernames and password, which will be stored in a database. Later, when the user logs in, the database checks for the user information provided, confirms it, and allows the user access to the app. After some time, you modify this app to extend its functionality on the latest version of Android. However, your login form has not changed and hence, the database is also not modified. But since the app that uses this login activity has been taken to the new Android version, the database also needs to be upgraded. This is done with the help of the SQLiteOpenHelper class, which would create the new version of the same database easily. In order to use the SQLiteOpenHelper class, you need to create another class that inherits this class and overrides the following callback methods of the SQLiteOpenHelper class:

  • onCreate(SQLiteDatabase db): This method is invoked when the database is created for the first time. In this method, you can create tables.
  • onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion): This method is invoked when any modification is done on the database, such as creating, altering, and dropping tables. This method takes three parameters: the database object, the old database version, and the new database version.
  • onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion): This method is called when the database needs to be downgraded. This is similar to the onUpgrade() method but is called whenever the version of the database is newer than the app requesting it. However, this method is not abstract and therefore, it is not mandatory to override it. If not overridden, the default implementation will reject downgrade and throw an SQLiteException exception.
  • onOpen(SQLiteDatabase db): This method is called when the database has been opened. This method is not abstract. Therefore, it is not mandatory to override this method.

The following partial code accesses data from an SQLite database by using the SQLiteOpenHelperclass:

// Declare an instance of the DataHelper class in the main activity.
  DataHelper dh; 
  @Override public void onCreate(Bundle savedInstanceState) 
  {
  // Pass the current context to the DataHelper object.
   dh = new DataHelper(this);
  } 
  // Create the DataHelper class in a separate .java file.
   public class DataHelper 
  {
   private static final String DATABASE_NAME = "ContactsInfo.db"; 
  private static final int DATABASE_VERSION = 1; 
  private static final String TABLE_NAME = "ContactDetails"; 
  // Creating context object.
  private Context context; 
  // Declaring SQLiteDatabase object .
  private SQLiteDatabase sqlDB; 
  public DataHelper(Context context) // constructor 
  {
   // Accepts the context of the invoking class.
   this.context = context; 
  // Create an instance of the OpenHelper class.
   OpenHelper openHelper = new OpenHelper (this.context);
   // Create and/or opens a database that will be used for reading // and writing records 
  this.sqlDB = openHelper.getWritableDatabase();
  // Code for querying, inserting, updating, or deleting data 
  }
   
   // Create an OpenHelper class by extending the SQLiteOpenHelper.
  class private static class OpenHelper extends SQLiteOpenHelper 
  {
  OpenHelper(Context context) // constructor 
  {
   super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }
   // Create the table 
  @Override public void onCreate(SQLiteDatabase db) 
  {
   // Execute the create table query when an object of the //OpenHelper class is created.
   db.execSQL("CREATE TABLE " + TABLE_NAME + "(Name Text, PhoneNo INTEGER, Email_ID TEXT, PhoneType TEXT)");
  } 
  // Upgrade the table. This statement will drop the table and 
  // recreate it 
  @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); 
  onCreate(db);
   }
  }

Creating a Database Through Shell Commands

To create a database through shell commands, you need to use the ADB tool. You need to use the adb shell command to enter the emulator’s shell, as shown in the following figure:

Figure 1 : The Use of the adb shell Command

To create a new database, you need to specify the path where you want to store the database in the emulator/ device. The cd <path> command is used to navigate from the current working directory.

For example, the command

cd /data/data/databases/

will change the current working directory to /data/data/databases/.

After specifying the location, you can create the database using the

sqlite3 <database name>command.

For example, the sqlite3 Mydatabase command creates a database file named, MyDatabase.db, in the data/data/databases folder.

The following figure displays Mydatabase.db in the File Explorer tab of the DDMS perspective:

Figure 2 : The Mydatabase.db in the File Explorer Tab

Once you are inside the SQLite shell of the working database to create a table inside the database, the create table command is used, as shown in the following code snippet:

create table Contacts(id INTERGER PRIMARY KEY, name TEXT, phone numeric);

In the preceding code snippet, Contacts refers to the table name. The parameters id, name, and phone refer to the columns within that table.

To list or verify whether the table has been created or not, you can use the .tables command on the sqlite prompt.

Note : Before executing shell commands, you have to ensure that the emulator is running or else the device not found error will be displayed on the command prompt.

Note : You can also use other SQL statements, such as insert, delete, update, query at the sqlite prompt. All SQL statements must be terminated with a semicolon.

You can use the .help command to list all the supported commands on the sqlite prompt. You can use the .quit command to return to the Linux shell. Finally, you can execute the exit command on the Linux shell to exit from the ADB’s Linux shell and return to the OS’s terminal window or the Windows command prompt window depending on the platform being used for developing the Android apps.

Storing and Retrieving Data in Android with sqlite

After creating the database and tables, you need to populate the tables with records. You may also need to delete and update the records stored in tables. In addition, you may want to retrieve the records stored in a table.

Inserting, Deleting, and Updating Records in a Table :

You can insert values into a table by using the execSQL()method, as shown in the following code snippet:

/* As id is a primary key, the value of this attribute will be auto-incremented and need not be specified in the insert statement */
  sqlDB.execSQL("INSERT INTO ContactDetails(Name, Phone)values ('SAM',7771110022)");

In the preceding code snippet, the value SAMgets inserted in the Name column and the value 7771110022 gets inserted into the Phone column of the ContactDetails table.

You can also update records by using the execSQL() method, as shown in the following code snippet:

//Updating values in a table
  sqlDB.execSQL("UPDATE ContactDetails Set Name='JACK' where id=1");

In the preceding code snippet, the Name column of the ContactDetails table gets updated with the value JACK where the value of the id column is 1. Similarly, you can delete specific records from the table by using the execSQL()method, as shown in the following code snippet:

//Deleting value from a table
  sqlDB.execSQL("DELETE from ContactDetails where id=1");

In Android, objects of the ContentValues class are used to insert new rows into the database. Every ContentValues object represents a single row, mapping column names to their values.

The following code illustrates how to create and implement content values:

import android.content.ContentValues;
  // Code for the onCreate() method of the Activity class.
  // This code uses the database, MyContacts and the table, ContactDetails.
   
  try
  {
   // Create an instance cv of the ContentValues class that will be used // to insert rows in the table. 
  ContentValues cv = new ContentValues (); 
  // To insert values in a ContentValues object, you need to use the 
  // put() method. The put() method is supplied two values. The first 
  // value is the column name as it exists in the table and the second // value is the value for the column to be inserted. For example, the // first value is the Name column of the ContactDetails table and 
  // second value is the string, TOM.
  cv.put("Name", "TOM"); 
  cv.put("Phone", 999231);
  // The sqlDB.insertOrThrow() method inserts a record in a table.
  sqlDB.insertOrThrow(ContactDetails, null, cv);
   cv.put("Name", "SAM"); 
  cv.put("Phone", 777111); 
  sqlDB.insertOrThrow(ContactDetails, null, cv); 
  cv.put("Name", "CAROL"); 
  cv.put("Phone", 777812); 
  sqlDB.insertOrThrow(ContactDetails, null, cv); 
  cv.put("Name", "RON"); 
  cv.put("Phone", 777115); 
  sqlDB.insertOrThrow(ContactDetails, null, cv); 
  } 
  catch(Exception ex) 
  { 
  Log.e("Issue",ex.toString()); 
  }

You can also update the records using the SQLiteDatabase.update(table, values, whereClause, whereArgs)method. The following code snippet shows the implementation of the update() method:

ContentValues cvupdate = new ContentValues(); cvupdate.put("Name","JACK");
  //Update the Name column of the table with the value "Jack" WHERE the id column has the value 1.
  sqlDB.update(ContactDetails, cvupdate, "id=1", null);

Similarly, to delete a record from the table, the delete(table, whereClause, whereArgs)method is used. This method takes three parameters: the table name, an optional where clause, and an argument array for the where clause. The argument array provides a list of values to replace the question marks in the query, if used, in the same order that they appear in the query. The following code snippet explains the implementation of the delete()method:

//Delete the record from the table WHERE the id column has the value 1.
  sqlDB.delete(ContactDetails, "id=1", null);

Retrieving Records from a Table

The SQLiteDatabase class also provides the query ()method to retrieve data from a table. This method returns a Cursor object, which helps to iterate and retrieve data from the result set. A result set refers to the collection of rows retrieved by a SELECT statement. The following code snippet explains the syntax of the query()method:

public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

The SQLiteDatabase.query()method takes the following parameters:

  • String table: The table name to retrieve the data from.
  • String[] columns: A list of columns to retrieve. Passing null will return all columns.
  • String selection: A filter used to specify which rows to return, similar to the where clause. Passing null will return all rows.
  • String[] selectionArgs: A list of values to replace the question marks in the query in the same order that they appear in the query.
  • String groupBy: A filter used to specify how to group the rows similar to the GROUP BY clause. Passing null will return ungrouped rows.
  • String having: A filter used to specify which row groups to retrieve in the cursor, if row grouping is being used similar to the HAVING clause. Passing null will return all row groups.
  • String orderBy: A clause used to order the rows similar to the ORDER BY clause. Passing null will return unordered rows.
  • String limit: A parameter used to specify the number of rows to be retrieved by the query similar to the LIMIT clause. Passing null denotes no limit for the number of rows.

When any query is executed, cursors are used to store the results of that query in rows. Cursors act as a pointer to the subset of the underlying data and are used to control the position of the current row in the result set of a database query.

Cursors are implemented using the Cursor interface through the android.database package, which contains various methods to access and iterate through records. When a cursor is no longer required, it can be closed.

The following code illustrates how to create and implement cursors:

import android.database.Cursor; 
  // Code for the onCreate() method of the Activity class 
  // This code uses the database, MyContacts and the table, ContactDetails 
  try{
  // To create a Cursor object, you need to use the query() method of the 
  // SQLiteDatabase object. This will retrieve the values from the 
  // ContactDetails table in the Cursor object. 
  Cursor c = sqlDB.query(ContactDetails, null, null, null, null, null, null); 
  // The moveToFirst() method is used to set the cursor on the first row //in the result set. 
  if( c.moveToFirst()){ 
  // The isAfterLast() method checks whether the cursor is pointing to // the position after the last row. If the cursor is not pointing // after the last row, keep on iterating the cursor using the 
  // moveToNext() method and display the records in the TextView. 
  while (c.isAfterLast()==false) {
  /* tv is the reference of the Text View with id textView1 in main.xml file */ 
  tv.append("\n"+c.getString(1) +"\n"+c.getString(2)); 
  c.moveToNext(); 
  } 
  } 
  // If the cursor // is not empty or closed, close the cursor. 
  if (c != null && !c.isClosed()) 
  {                           
   c.close(); 
  }

You can also use the methods available in the SQLiteQueryBuilder class of android.database.sqlite package to retrieve data from an SQLite database. The SQLiteQueryBuilder class offers rich query building options like the union of multiple subquery results.

The SQLiteQueryBuilder class allows you to provide the query in a broken-up format, such as separate string values for table name, selection criteria, ordering sequence, and so on. Once you provide the SQLiteQueryBuilder class with these values, the SQLiteQueryBuilder class automatically builds the SQL query. This prevents you from keeping track of the syntax for SQL statements and is useful when you are not fully aware of the correct format for writing the queries. The following table lists methods of the SQLiteQueryBuilder class.

The following code illustrates the use of the preceding methods:

// Create an instance of the SQLiteQueryBuilder class.
  SQLiteQueryBuilder sqlBuilder = new SQLiteQueryBuilder(); 
  // Specify the table to be queried sqlBuilder.
  setTables("MyTable"); 
  /* Create an array of strings that includes the names of the columns to be queried */ 
  String columns[] = new String[] {"name", "age"}; 
  /* Perform the query and store the result in a Cursor object */ 
  Cursor c = sqlBuilder.query(myDB, columns, null, null, null, null, null);

Conclusion

Well, this article explains all the processes of database related transaction like Insert, Update, Delete and Select. Using this article you can easily get the process / idea about how you can manage those stuffs. Android also allows you to create SQL statements that can be precompiled against the SQLiteDatabase object and can be reused once they have been compiled. In future my few articles will come up with some complex database transaction process with lots of steps to complete the same. Check out this space for the same.

Happy Development!



Certified Trainer for Windows 8 Mobile App Development, IBM CE Project Trainer With IBM DB2, RAD, RSA, Certified Trainer for ZEND (PHP), Certified PHP and MySql trainer, Certified trainer of Diploma in Oracle 10g (DBA) as per Orac...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login