Creating SQLite database & Tables in Android

SQLite is an open-source server-less database engine. SQLite supports transacations and has no configuration required. SQLite adds powerful data storage to mobile and embedded apps.

Creating and connecting to a database

Import android.database.sqlite.SQLiteDatabase into the application.

Use openOrCreateDatabase() method to create or connect to a database.

Sample code pasted below,

package example.SQLiteTest;

import android.app.Activity;
import android.os.Bundle;
import android.database.sqlite.SQLiteDatabase;

public class SQLiteTest extends Activity {
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
SQLiteDatabase db;
db = openOrCreateDatabase(
"TestData.db"
, SQLiteDatabase.CREATE_IF_NECESSARY
, null
);
}
}

Run the above code snippet, to create a database called TestData.

Android stores the above database in,

/data/data/[application package name]/databases.

Emulator’s status and port can be found out by giving the following command,

C:\[..path..]\android-sdk-windows\tools>adb devices

List of devices attached
emulator-5554 device

To view the data base created above, we need to log in to the adb shell. (I’m using windows),

In the command prompt navigate to your android-sdk folder,

C:\[..path..]\android-sdk-windows\tools>adb -s emulator-5554 shell

# ls /data/data/example.SQLiteTest/databases
TestData.db

# sqlite3 /data/data/example.SQLiteTest/databases/TestData.db

SQLite version 3.5.9

Enter “.help” for instructions

sqlite> .tables

android_metadata

sqlite>

Creating Tables

execSQL() statement is used to execute queries.

Before creating tables, certain database properties should be set using the following methods,

setVersion(), setLocale(), and setLockingEnabled().

Append this code to the previous example for creating tables (tbl_countries and tbl_states)..

db.setVersion(1);

db.setLocale(Locale.getDefault());

db.setLockingEnabled(true);

final String CREATE_TABLE_COUNTRIES =

"CREATE TABLE tbl_countries ("

+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"

+ "country_name TEXT);";

final String CREATE_TABLE_STATES =

"CREATE TABLE tbl_states ("

+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"

+ "state_name TEXT);";

db.execSQL(CREATE_TABLE_COUNTRIES);

db.execSQL(CREATE_TABLE_STATES);

Now in shell if you execute the following command you should see these two tables that we’ve created.

sqlite> .tables

.tables

android_metadata tbl_countries tbl_states

sqlite>

Inserting Records

import android.content.ContentValues;

Use ContentValues instance to create a series of table field to data matchings that will be passed into an insert() method.

Code snipped pasted below..

ContentValues values = new ContentValues();

values.put("country_name", "India");

db.insert("tbl_countries", null, values);

sqlite> select * from tbl_countries;

select * from tbl_countries;

1|India

15 comments on “Creating SQLite database & Tables in Android

  • suryagopal says:

    Hi,
    I am new to android, i am trying out the small small applications in android. but i am trying to do database applications i am not able to run it. so plz help me out with proper step by step procedure how to do a small database application using sqlite and inbuikt database also…
    help me out plz…
    wating for repliesss

  • I could not find any good tutorials for sqlite with android…
    Really a good job, fantastic tutorial. :)

  • guys any one over here can help me?? as am doing a project and i want to access the data entered in text box in the layout.. so can any 1 suggest me what dom api’s to use ??

  • Thanks a lot. It was really helpful. Is there any example of extracting data from the database?

    Thanks

  • Rajesh Tiwari says:

    Hello Friends,

    I am new to android. I implemented same steps to create a sqlite database,

    public void onCreate(Bundle icicle){
    super.onCreate(icicle);
    setContentView(R.layout.main);
    SQLiteDatabase db;
    db = openOrCreateDatabase(“TestData.db”, SQLiteDatabase.CREATE_IF_NECESSARY, null);

    }

    but the the database not made i searched the database at every location, also checked with commands specified in the tutorial.

  • @Rajesh Tiwari

    The database will be created in the device or emulator not in the project location. The database will available at
    /data/data//databases/

    specifically for you
    /data/data//databases/TestData.db

  • I had tried too any things to create databases. But each I failed. Am really very much thankful to you for sharing this article.

  • Hello Ajith

    I am new to android uptill now i am trying to small app. in android its working but now i am trying to use SQlite3 datbase in my application but iam not able to get it.So plz tell me how to do that.Plz tell me all the processes step by step.

    Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *


*

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