TUTORIAL

SQLite Database Connectivity in Android

Posted by Chintan Rathod Tutorials | Android Programming December 23, 2011
This article talks about connecting with SQLite database, create database, create table, insert records in tables and display records from table in an Android application.
Reader Level:

Introduction

In this article, we will see how to create a SQLite database in an Android application. We will also see how to add records to the database and read and display in an application.

SQLiteDatabase

In Android, the SQLiteDatabase namespace defines the functionality to connect and manage a database. It provides functionality to create, delete, manage and display database content.

Create a Database

Simple steps to create a database and handle are as following.

  1. Create "SQLiteDatabase" object.
  2. Open or Create database and create connection.
  3. Perform insert, update or delete operation.
  4. Create Cursor to display data from table of database.
  5. Close the database connectivity.

Following tutorial helps you to create database and insert records in it.

Step 1: Instantiate "SQLiteDatabase" object

SQLiteDatabase db;

Before you can use the above object, you must import the android.database.sqlite.SQLiteDatabase namespace in your application.

db=openOrCreateDatabase(String path, int mode, SQLiteDatabase.CursorFactory factory)

This method is used to create/open database. As the name suggests, it will open a database connection if it is already there, otherwise it will create a new one.

Example,

db=openOrCreateDatabase("XYZ_Database",SQLiteDatabase.CREATE_IF_NECESSARY,null);

Arguments:

String path

Name of the database

Int mode

operating mode. Use 0 or "MODE_PRIVATE" for the default operation, or "CREATE_IF_NECESSARY"  if you like to give option that "if database is not there, create it"

CursorFactory factory

An optional factory class that is called to instantiate a cursor when query is called

Step 2: Execute DDL command

db.execSQL(String sql) throws SQLException

This command is used to execute single SQL statement which doesn't return any data means other than SELECT or any other.

db.execSQL("Create Table Temp (id Integer, name Text)");

In the above example, it takes "CREATE TABLE" statement of SQL. This will create a table of "Integer" & "Text" fields.

Try and Catch block is require while performing this operation. An exception that indicates there was an error with SQL parsing or execution.

Step 3: Create object of "ContentValues" and Initiate it.

ContentValues values=new ContentValues();

This class is used to store a set of values. We can also say, it will map ColumnName and relavent ColumnValue.

values.put("id", eid.getText().toString());         
values.put("name", ename.getText().toString()); 

String Key

Name of field as in table. Ex. "id", "name"

String Value

Value to be inserted.

Step 4: Perform Insert Statement.

insert(String table, String nullColumnHack, ContentValues values)

String table

Name of table related to database.

String nullColumnHack

If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty.

ContentValues values

This map contains the initial column values for the row.

This method returns a long. The row ID of the newly inserted row, or -1 if an error occurred.

Example,

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

Step 5: Create Cursor

This interface provides random read-write access to the result set returned by a database query.

Cursor c=db.rawQuery(String sql, String[] selectionArgs)
 

Strign sql

The SQL query

String []selectionArgs

You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

Example,

Cursor c=db.rawQuery("SELECT * FROM temp",null);

Methods
 

moveToFirst

Moves cursor pointer at first position of result set

moveToNext

Moves cursor pointer next to current position.

isAfterLast

Returs false, if cursor pointer is not at last position of result set.

Example,

c.moveToFirst();
while(!c.isAfterLast())
{
     //statement…
c.moveToNext();
}


Step 6: Close Cursor and Close Database connectivity

It is very important to release our connections before closing our activity. It is advisable to release the Database connectivity in "onStop" method. And Cursor connectivity after use it.

DatabaseDemoActivity.java

package com.DataBaseDemo;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class DataBaseDemoActivity extends Activity {
   
/** Called when the activity is first created. */
      SQLiteDatabase db;
Button
btnInsert;

      @Override
      public void onCreate(Bundle savedInstanceState) {
     
super.onCreate(savedInstanceState);
      setContentView(R.layout.main);
     
btnInsert=(Button)findViewById(R.id.button1);
     
try{
     
db=openOrCreateDatabase("StudentDB",SQLiteDatabase.CREATE_IF_NECESSARY,null);
      db.execSQL("Create Table Temp(id integer,name text)");
      }
catch(SQLException e)
      {
      }
     
btnInsert.setOnClickListener(new View.OnClickListener() {
          
@Override
           public void onClick(View v) {
          
// TODO Auto-generated method stub
           EditText eid=(EditText) findViewById(R.id.editText1);
           EditText ename=(EditText)findViewById(R.id.
editText2);
           ContentValues values=
new ContentValues();
           values.put(
"id", eid.getText().toString());
           values.put(
"name", ename.getText().toString());
          
if((db.insert("temp", null, values))!=-1)
           {
           Toast.makeText(DataBaseDemoActivity.
this, "Record Successfully Inserted", 2000).show();
           }
          
else
           {
           Toast.makeText(DataBaseDemoActivity.
this, "Insert Error", 2000).show();
           }
           eid.setText(
"");
           ename.setText(
"");

           Cursor c=db.rawQuery("SELECT * FROM temp",null);
           c.moveToFirst();
          
while(!c.isAfterLast())
           {
           Toast.makeText(DataBaseDemoActivity.
this,c.getString(0)+ " "+c.getString(1),1000).show();
           c.moveToNext();
           }
           c.close();
           }
        });
    }
   
@Override
    protected void onStop() {
     
// TODO Auto-generated method stub
      db.close();
      super.onStop();
    }
}

SQLAnd1.jpeg

SQLAnd2.jpeg

SQLAnd3.jpeg

Note:


If you want to see where your database stored? Follow below instruction.

  1. Start Your Emulator ( It is necessary to start Emulator to see File Explorer content)
     

  2. Open "File Explorer"

    SQLAnd4.jpeg
     

  3. Data -> Data -> find your "package" -> databases -> "database"

Summary

SQLiteDatabase provide so much functionality to create and manage databases. In this article, we learned how to create a new database, connect to it and read and display data.

Resources

Here are some useful related resources:

Article Extensions
Contents added by Chintan Rathod on Sep 01, 2012
Download File: DatabaseListViewApp.zip
Hi,

Here, I am posting a new source code which will display you content in list view. I have implemented only simple code.
in this code,

1) Table will be created with two fields, first name and last name (table will be created if not exist, and if exists)
2) records are inserted after table creation.
3) those records are fetched from table and displayed in list view.

Code :
***************

package com.listview;

import android.app.ListActivity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class ListViewActivity extends ListActivity {
    /** Called when the activity is first created. */
    SQLiteDatabase mDatabase;
    static String CREATE_STUDENT_TABLE;
    final static String STUDENT_TABLE = "tblStudents";
    final String[] items = {"item 1","item 2","item 3"};
    String[] data;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
      
        mDatabase = openOrCreateDatabase("Students.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);
       
        try{
        CREATE_STUDENT_TABLE = "CREATE TABLE IF NOT EXISTS "+STUDENT_TABLE+"(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "firstname TEXT," +
                "lastname TEXT" +
                ")";
           
            /** creates a new table */
            mDatabase.execSQL(CREATE_STUDENT_TABLE);
           
            /* delete old records */
            //mDatabase.delete(STUDENT_TABLE, null, null);
           
           
        }catch(Exception e)
        {
           
        }
       
        insertData("c-sharp","corner");
        insertData("chintan","rathod");
       
        Cursor c = mDatabase.query(STUDENT_TABLE, null, null, null, null, null, null);
        data = new String[c.getCount()];
       
        c.moveToFirst();
        int i=0;
        while(c.isAfterLast()==false){
            String str = c.getString(1)+" "+c.getString(2);
            data[i++]=str;
            c.moveToNext();
        }
       
        ArrayAdapter<String> adapter =
            new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, data);
       
        setListAdapter(adapter);
       
    }
   
    public void insertData(String fn,String ln){
        ContentValues values = new ContentValues();
        values.put("firstname",fn);
        values.put("lastname",ln);
        mDatabase.insert(STUDENT_TABLE, null, values);
    }
   
    protected void onListItemClick(ListView l, View v, int position, long id){
        super.onListItemClick(l, v, position, id);
        String str = ((TextView) v).getText().toString();
        Toast.makeText(this, str, 1000).show();
    }
}




And its done. You can add your own logic to add records dynamically and display it in different view.

Thanks & Regards
------------------
Chintan Rathod
Contents added by Chintan Rathod on Dec 23, 2011
Download File: DataBaseDemo.zip
COMMENT USING

Trending up