Data Storage-SQLite Database
SQLite dabase with listviewData storage -SQLite data base
When developing mobile applications, you need to store data, sometimes lots of data, on client devices. With batch applications, this is a requirement because users will infrequently synchronize with the server to fetch data from database.Hence fetching a batch of data and storing it on the client device would provide seamless operation for the wireless applications which may be hindered by sporadic loss of connectivity due to RF interference and spotty coverage causing disrupt to the application,consequently maintain data on the client device to allow continual access to applications.
The current database tools enable you to easily replicate data on the mobile device. The client devices and server databases remain in sync, making it possible for users to continually use the mobile application. The need for this feature is what sets mobile systems aside from traditional wire-based solutions.
Avoid Flat Files
Unfortunately, many mobile software developers store data on the client device in flat files. This makes applications run very slow. Avoiding flat files and using database had 20 to 30 speed increase in performance of applications. By using effective relational database design rules, as with any other applications, relational databases also significantly improves data integrity.
Introduction to SQLite
SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).
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 saving them 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. More information about SQLite can be found on the SQLite website: http://www.sqlite.org.
SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration.
SQLite in Android
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 filesystem. This can be slow. Therefore it is recommended to perform database operations asynchronously, for example inside the
If your application creates a database, this database is by default saved in the directory AsyncTask class. 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.SQLite Architecture
The package
android.database contains all general classes for working with databases. android.database.sqlite contains the SQLite specific classes. To create and upgrade a database in your Android application you usually subclass
In this class you need to override the
Both methods receive an
The database tables should use the identifier
It is best practice to create a separate class per table. This class defines static
SQLiteOpenHelper. 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
onCreate() and onUpgrade() methods. onCreate() is called by the framework, if the database does not exists. onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema. Both methods receive an
SQLiteDatabase object as parameter which represents the database. SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() 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. It is best practice to create a separate class per table. This class defines static
onCreate() and onUpdate() methods. These methods are called in the corresponding methods of SQLiteOpenHelper. This way your implementation of SQLiteOpenHelper will stay readable, even if you have several tables. 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. The following gives an example of a
rawQuery() call. Cursor cursor = getReadableDatabase().
rawQuery("select * from todo where _id = ?", new String[] { id });
The following gives an example of a
The method
If a condition is not required you can pass
The "whereClause" is specified without the word "where", for example a "where" statement might look like: "_id=19 and summary=?".
If you specify placeholder values in the where clause via
query() call. return database.query(DATABASE_TABLE,
new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION },
null, null, null, null, null);
The method
query() has the following parameters. Table 1. Parameters of the query() method
| Parameter | Comment |
|---|---|
| String dbName | The table name to compile the query against. |
| int[] columnNames | A list of which table columns to return. Passing "null" will return all columns. |
| String whereClause | Where-clause, i.e. filter for the selection of data, null will select all data. |
| String[] selectionArgs | You may include ?s in the "whereClause"". These placeholders will get replaced by the values from the selectionArgs array. |
| String[] groupBy | A filter declaring how to group rows, null will cause the rows to not be grouped. |
| String[] having | Filter for the groups, null means no filter. |
| String[] orderBy | Table columns which will be used to order the data, null means no ordering. |
If a condition is not required you can pass
null, e.g. for the group by clause. The "whereClause" is specified without the word "where", for example a "where" statement might look like: "_id=19 and summary=?".
If you specify placeholder values in the where clause via
?, you pass them as the selectionArgs parameter to the query. A query returns a
To get the number of elements of the resulting query use the
To move between individual data rows, you can use the
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. 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. The Demonstrative project takes entry of queries and saves in to SQLite Database. Fetch the entered comments from database and display in list view and allow the user to delete the selected queries. Demonstrate accessing SQLite Database to manage data.
1.Create Android project with details as listed in table below
Property name | Property value |
Project name | SRM_SQLiteTutorial |
Package name | in.ac.srmuniv.sqlitetutorial |
Activity name | DatabaseActivity |
Layout xml name | main |
2.Copy the code to the file main.xml in res/layout folder
<?xml version="1.0"encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<EditText
android:id="@+id/txt1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="" >
</EditText>
<Button
android:id="@+id/add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:gravity="center"
android:onClick="onClick"
android:text="Add New">
</Button>
<ListView
android:id="@android:id/list"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello" />
</LinearLayout>
3.Create plain java file named SRMDataSource.java copy the code given below
package in.ac.srmuniv.sqlitetutorial;
import java.util.ArrayList;
import java.util.List;
importandroid.content.ContentValues;
importandroid.content.Context;
importandroid.database.Cursor;
importandroid.database.SQLException;
importandroid.database.sqlite.SQLiteDatabase;
public class SRMDataSource {
// Database fields
private SQLiteDatabase database;
private MySQLiteHelper dbHelper;
private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
MySQLiteHelper.COLUMN_SRMQuerys };
publicSRMDataSource(Context context) {
dbHelper = newMySQLiteHelper(context);
}
public void open() throws SQLException {
database = dbHelper.getWritableDatabase();
}
public void close() {
dbHelper.close();
}
public SRMQuerys createSRMQuerys(String SRMQuerys) {
ContentValues values = new ContentValues();
values.put(MySQLiteHelper.COLUMN_SRMQuerys, SRMQuerys);
long insertId = database.insert(MySQLiteHelper.TABLE_SRMQuerys, null,
values);
// To show how to query
Cursor cursor = database.query(MySQLiteHelper.TABLE_SRMQuerys,
allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
null, null, null);
cursor.moveToFirst();
returncursorToSRMQuerys(cursor);
}
public voiddeleteSRMQuerys(SRMQuerys SRMQuerys) {
long id = SRMQuerys.getId();
System.out.println("SRMQuerys deleted with id: " + id);
database.delete(MySQLiteHelper.TABLE_SRMQuerys, MySQLiteHelper.COLUMN_ID
+ " = "+ id, null);
}
public List<SRMQuerys> getAllSRMQuerys() {
List<SRMQuerys> SRMQueryss = new ArrayList<SRMQuerys>();
Cursor cursor = database.query(MySQLiteHelper.TABLE_SRMQuerys,
allColumns, null, null, null, null, null);
cursor.moveToFirst();
while(!cursor.isAfterLast()) {
SRMQuerys SRMQuerys = cursorToSRMQuerys(cursor);
SRMQueryss.add(SRMQuerys);
cursor.moveToNext();
}
// Make sure to close the cursor
cursor.close();
return SRMQueryss;
}
private SRMQuerys cursorToSRMQuerys(Cursor cursor) {
SRMQuerys SRMQuerys = new SRMQuerys();
SRMQuerys.setId(cursor.getLong(0));
SRMQuerys.setquery(cursor.getString(1));
return SRMQuerys;
}
}
4.Create a plain java file named SRMQuerys
package in.ac.srmuniv.sqlitetutorial;
public class SRMQuerys {
private long id;
private String query;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getquery() {
return query;
}
public void setquery(String query) {
this.query = query;
}
// Will be used by the ArrayAdapter in the ListView
@Override
public String toString() {
return query;
}
}
5.Create a java class MySQLiteHelper which extends SQLiteOpenHelper copy the code given below
package in.ac.srmuniv.sqlitetutorial;
importandroid.content.Context;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class MySQLiteHelper extends SQLiteOpenHelper {
public static final String TABLE_SRMQuerys = "SRMQuerys";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_SRMQuerys = "comment";
private static final String DATABASE_NAME = "srmqueries.db";
private static final int DATABASE_VERSION = 1;
// Database creation sql statement
private static final String DATABASE_CREATE = "create table "
+ TABLE_SRMQuerys + "( " + COLUMN_ID
+ " integer primary key autoincrement, " + COLUMN_SRMQuerys
+ " text not null);";
publicMySQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public voidonCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE);
}
@Override
public voidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(MySQLiteHelper.class.getName(),
"Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS" + TABLE_SRMQuerys);
onCreate(db);
}
}
6.Create an Activity named DatabaseActivity.java which should extend ListActivity
package in.ac.srmuniv.sqlitetutorial;
import java.util.List;
import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;
public class DatabaseActivity extends ListActivity {
private SRMDataSource datasource;
EditText commentEdit ;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
datasource = new SRMDataSource(this);
datasource.open();
commentEdit = (EditText)findViewById(R.id.txt1);
List<SRMQuerys> values = datasource.getAllSRMQuerys();
// Use the SimpleCursorAdapter to show the
// elements in a ListView
ArrayAdapter<SRMQuerys> adapter = new ArrayAdapter<SRMQuerys>(this,
android.R.layout.simple_list_item_1, values);
setListAdapter(adapter);
}
// Will be called via the onClick attribute
// of the buttons in main.xml
public void onClick(View view) {
@SuppressWarnings("unchecked")
ArrayAdapter<SRMQuerys> adapter = (ArrayAdapter<SRMQuerys>) getListAdapter();
SRMQuerys SRMQuerys = null;
// Save the new SRMQuerys to the database
SRMQuerys = datasource.createSRMQuerys(commentEdit.getText().toString());
adapter.add(SRMQuerys);
adapter.notifyDataSetChanged();
commentEdit.setText("");
}
protected void onListItemClick(ListView l, View v, int position, long id) {
ArrayAdapter<SRMQuerys> adapter = (ArrayAdapter<SRMQuerys>) getListAdapter();
SRMQuerys SRMQuerys = null;
SRMQuerys = (SRMQuerys) getListAdapter().getItem(position);
datasource.deleteSRMQuerys(SRMQuerys);
adapter.remove(SRMQuerys);
Toast.makeText(getApplicationContext()," YOU DELETED ONE ITEM on "+"'"+id+"'"+" POSITION",
Toast.LENGTH_LONG).show();
} @Override
protected void onResume() {
datasource.open();
super.onResume();
}
@Override
protected void onPause() {
datasource.close();
super.onPause();
}
}
Figure 1 Project Anatomy
7.Run the program you will get the Activity screen as shown below
Figure 4 Shows entries of Query entered in edit text is inserred in to srmqueries.db listed in list view Figure 5 Shows the deleted item from List view removes the row from database.
9 Click .DDMS perspective and view file explorer /data/.... see figure
Figure 5
10.You can pull the srmqueries.db file out of emulator and place in system desktop
11.You can open the created table through our application using SQLite can be viewed with software like Navicat Lite or SQLite browser as shown in figure
MySQLiteHelper class which extends SQLiteOpenHelper is responsible for creation of database needed to the applicatin..The constructor MySQLiteHelper() calls super() method to pass Database name and version to create database. onCreate() will be called automatically when an object for SQLiteOpenHelper class is instantiated which creates tables with sql execute qurey and onUpgrade() is callback methods called when database needs to be upgraded one has to write code to drop table and add new table or modify existing table schema of database.
User defined class SRMDataSource class creates object instance of MySQLiteOpenHelper and SQLiteDatabase provides various database handling methods such to open ,insert ,delete,query to operate on the database.SRMQueries class makes the java data object for srmqueries.db database.Following are the methods used in SRMDataSource class
public void open()
public void close()
public SRMQuerys createSRMQuerys(String SRMQuerys)
public void deleteSRMQuerys(SRMQuerys SRMQuerys)
public List<SRMQuerys> getAllSRMQuerys()
An object for SQLiteDataBase is created in open() method with code mentioned below.
database = dbHelper.getWritableDatabase();
Contentvalues are used to put values in to data base while Cursor object is used to fetch data from database.cursor object can move through the table of the selected query for each row and can fetch the queried data.used in respective function calls in our application to perform database operation when called.Insert,qurey ,delete methods of database do process the CURD operation in the database.
DatabaseActivity is an ListActivity uses SRMDataSource class object instance to call insert or delete operations on database srmqueries.db.It also uses ListView to populate the db contents of a table.srmqueries table Colum 'comment' is displayed in list view,when insertion is done through entry from edit text and "addnew" button is clicked.Figure 1 shows added elements to the database.Deletion takes place when an item is selected from list view,figure2 shows the deletion on selecting an item from the list view.We can use Navicat Lite software to view the database created.We can pull the file from file explorer in DDMS perspective from the emulator running and save in to the system for viewing it,shown through figure 4,5,6.
User defined class SRMDataSource class creates object instance of MySQLiteOpenHelper and SQLiteDatabase provides various database handling methods such to open ,insert ,delete,query to operate on the database.SRMQueries class makes the java data object for srmqueries.db database.Following are the methods used in SRMDataSource class
public void open()
public void close()
public SRMQuerys createSRMQuerys(String SRMQuerys)
public void deleteSRMQuerys(SRMQuerys SRMQuerys)
public List<SRMQuerys> getAllSRMQuerys()
An object for SQLiteDataBase is created in open() method with code mentioned below.
database = dbHelper.getWritableDatabase();
Contentvalues are used to put values in to data base while Cursor object is used to fetch data from database.cursor object can move through the table of the selected query for each row and can fetch the queried data.used in respective function calls in our application to perform database operation when called.Insert,qurey ,delete methods of database do process the CURD operation in the database.
DatabaseActivity is an ListActivity uses SRMDataSource class object instance to call insert or delete operations on database srmqueries.db.It also uses ListView to populate the db contents of a table.srmqueries table Colum 'comment' is displayed in list view,when insertion is done through entry from edit text and "addnew" button is clicked.Figure 1 shows added elements to the database.Deletion takes place when an item is selected from list view,figure2 shows the deletion on selecting an item from the list view.We can use Navicat Lite software to view the database created.We can pull the file from file explorer in DDMS perspective from the emulator running and save in to the system for viewing it,shown through figure 4,5,6.
