articles

Home / DeveloperSection / Articles / SQLite in Android

SQLite in Android

Chris Anderson 20720 26-Oct-2011

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:

SQLite in Android

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, (WindowOpen PerspectiveOtherDDMS).

·         Then in the File Explorer Tab you will follow the path:
datadatayour package namedatabases – 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.


Updated 03-Jan-2020
hi I am software developer at mindstick software pvt. ltd.

Leave Comment

Comments

Liked By