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).
Using SQLite in Android does not
require any database setup or administration. We have to specify the SQL for
working with the database and the database is automatically managed for us.
SQLite supports 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 on of these fields before saving them in the
database.
A SQLite database is private to
the application which creates it. If you want to share data with other
applications you can use a Content Provider.
SQLiteOpenHelper in Android
To create and upgrade a database
in an Android application we have to subclass the
SQLiteOpenHelper. In this class we
need to override the methods onCreate()
to create the database and onUpgrade()
to upgrade the database in case of changes in the database schema. Both methods
receive a SQLiteDatabase object.
SQLiteOpenHelper provides the
methods getReadableDatabase() and
getWriteableDatabase() to get access
to a SQLiteDatabase object which allows database access either in read or write
mode. For the primary key of the database you should always use the identifier
_id as some of the Android functions
rely on this standard.
SQLiteDatabase and Cursor in
Android
SQLiteDatabase provides the
methods insert(),
update() and
delete() and execSQL() method
which allows to execute directly SQL. The object
ContentValues allow to define key/values for insert and update. The key is
the column and the value is the value of this column.
Queries can be created via the
method rawQuery() which accepts
SQL or query() which provides
an interface for specifying dynamic data or SQLiteQueryBuilder.
SQLiteBuilder is similar to the interface of a content provider therefore it
is typically used in ContentProviders.
A query returns always a Cursor.
Create a SQLite Database in
Android
Now I am going to explain how
create a database in an Android application and how to find path of database
file in Android emulator and how use Android tools to see the database.
Start a new project named DatabaseDemo.
Open the res/layout/main.xml and
insert the following:
<?xml
version="1.0"
encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent">
<TextView
android:id="@+id/txtView"
android:layout_marginLeft="100dp"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello"/>
</LinearLayout>
Write a code for creating a database in the Activity file as shown below:
import java.util.Locale;
import
android.app.Activity;
import
android.database.sqlite.SQLiteDatabase;
import
android.os.Bundle;
import
android.widget.TextView;
public
class DatabaseActivity
extends Activity {
@Override
public
void onCreate(Bundle
savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
SQLiteDatabase db;
db = openOrCreateDatabase("Test.db", SQLiteDatabase.CREATE_IF_NECESSARY,
null);
db.setVersion(1);
db.setLocale(Locale.getDefault());
db.setLockingEnabled(true);
TextView txtView=(TextView)findViewById(R.id.txtView);
txtView.setText("Database
created");
}
}
Run the application.
Your output should look like below:
A Database created message will
display in the Textview.
Find Database in Android
Emulator
In order to check the created
database (Test.db), you have to be on
the DDMS perspective on your Eclipse IDE. Your application must be running so
you can see your project folder.
Go to the following location, (Window
Open Perspective
Other
DDMS).
Then in the File Explorer Tab you
will follow the path:
data
data your package name
databases your-database-file (Test.db).
Find Database and Database Info
in ADB shell
In order to check the tables and
database information from the database, you can use adb shell provided by
Android SDK:
Run the cmd (command prompt).
Go to the following location:
d:\android-sdk_r13-windows\android-sdk-windows\platform-tools>
(you have to user your own location).
Type adb shell and press enter, a
# symbol will display.
Type sqlite3
/data/data/ [package name]/databases/database-name (Test.db) and press
Enter.
For e.g. sqlite3 /data/data/com.android.databaseDemo/databases/Test.db
Then type .tables in order to see the
table present in the database.
It will show you a default table (android_metadata)
first time in the database.
Thanks for reading this article. I think this will help you a lot.