Post
Ask Question

SQLite Database Connectivity in Android

Introduction

 
In this article, we will see how to create an 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 follows.
  1. Create "SQLiteDatabase" object.
  2. Open or Create a database and create a connection.
  3. Perform insert, update or delete operation.
  4. Create a Cursor to display data from the table of the database.
  5. Close the database connectivity.
Following tutorial helps you to create a database and insert records in it.
 
Step 1: Instantiate "SQLiteDatabase" object
  1. SQLiteDatabase db;  
Before you can use the above object, you must import the android.database.sqlite.SQLiteDatabase namespace in your application.
  1. 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,
  1. 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 an option that "if a database is not there, create it"
CursorFactory factory
An optional factory class that is called to instantiate a cursor when a query is called
 
Step 2: Execute DDL command
  1. db.execSQL(String sql) throws SQLException 
This command is used to execute a single SQL statement that doesn't return any data means other than SELECT or any other.
  1. 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 required while performing this operation. An exception that indicates there was an error with SQL parsing or execution.
 
Step 3: Create an object of "ContentValues" and Initiate it.
  1. ContentValues values=new ContentValues(); 
This class is used to store a set of values. We can also say, it will map ColumnName and relevant ColumnValue.
  1. values.put("id", eid.getText().toString());           
  2. values.put("name", ename.getText().toString());  
String Key
Name of the field as in table. Ex. "id", "name"
String Value
Value to be inserted.
 
Step 4: Perform Insert Statement.
  1. insert(String table, String nullColumnHack, ContentValues values)  
String table
Name of table related to the 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,
  1. 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.
  1. 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,
  1. Cursor c=db.rawQuery("SELECT * FROM temp",null); 
Methods
 
moveToFirst
Moves cursor pointer at a first position of a result set
moveToNext
Moves cursor pointer next to the current position.
isAfterLast
Returns false, if the cursor pointer is not atlast position of a result set.
 
Example,
  1. c.moveToFirst();  
  2. while(!c.isAfterLast())  
  3. {  
  4.      //statement…  
  5. c.moveToNext();  
  6. } 
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
  1. package com.DataBaseDemo;  
  2. import android.app.Activity;  
  3. import android.content.ContentValues;  
  4. import android.database.Cursor;  
  5. import android.database.SQLException;  
  6. import android.database.sqlite.SQLiteDatabase;  
  7. import android.os.Bundle;  
  8. import android.view.View;  
  9. import android.widget.Button;  
  10. import android.widget.EditText;  
  11. import android.widget.Toast;  
  12. public class DataBaseDemoActivity extends Activity {  
  13.     /** Called when the activity is first created. */  
  14.       SQLiteDatabase db;  
  15. Button btnInsert;  
  16.       @Override  
  17.       public void onCreate(Bundle savedInstanceState) {  
  18.       super.onCreate(savedInstanceState);  
  19.       setContentView(R.layout.main);  
  20.       btnInsert=(Button)findViewById(R.id.button1);  
  21.       try{  
  22.       db=openOrCreateDatabase("StudentDB",SQLiteDatabase.CREATE_IF_NECESSARY,null);  
  23.       db.execSQL("Create Table Temp(id integer,name text)");  
  24.       }catch(SQLException e)  
  25.       {  
  26.       }  
  27.       btnInsert.setOnClickListener(new View.OnClickListener() {  
  28.            @Override  
  29.            public void onClick(View v) {  
  30.            // TODO Auto-generated method stub  
  31.            EditText eid=(EditText) findViewById(R.id.editText1);  
  32.            EditText ename=(EditText)findViewById(R.id.editText2);  
  33.            ContentValues values=new ContentValues();  
  34.            values.put("id", eid.getText().toString());  
  35.            values.put("name", ename.getText().toString());  
  36.            if((db.insert("temp", null, values))!=-1)  
  37.            {  
  38.            Toast.makeText(DataBaseDemoActivity.this, "Record Successfully Inserted", 2000).show();  
  39.            }  
  40.            else  
  41.            {  
  42.            Toast.makeText(DataBaseDemoActivity.this, "Insert Error", 2000).show();  
  43.            }  
  44.            eid.setText("");  
  45.            ename.setText("");  
  46.            Cursor c=db.rawQuery("SELECT * FROM temp",null);  
  47.            c.moveToFirst();  
  48.            while(!c.isAfterLast())  
  49.            {  
  50.            Toast.makeText(DataBaseDemoActivity.this,c.getString(0)+ " "+c.getString(1),1000).show();  
  51.            c.moveToNext();  
  52.            }  
  53.            c.close();  
  54.            }  
  55.         });  
  56.     }  
  57.     @Override  
  58.     protected void onStop() {  
  59.       // TODO Auto-generated method stub  
  60.       db.close();  
  61.       super.onStop();  
  62.     }  
  63. }  
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 provides 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:


Similar Articles