SQLite Database in Android

What is SQLite?

SQLite is a freely available open source database provided in Android. SQLite is a lightweight and compact database that does not require any kind of server to run. It is easily integrated into any kind of mobile application. There are many libraries and classes available on Android to perform any kind of database queue on SQLite. It provides so many commands like add new data, update, read, and delete data.

SQLiteOpenHelper Class:

We can use this class for creating a database and also we can use it for version management. This class provides the onCreate() and onUpgrade() methods for performing any database operation.

Constructors of SQLiteOpenHelper Class:-

SQLiteOpenHelper class has two constructors.

1. SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version): This constructor creates an object for creating, opening, and managing the database.

2. SQLiteOpenHelper(Context context, String name,SQLiteDatabase.CursorFactory factory, int version,DatabaseErrorHandler errorHandler): This constructor creates an object for creating, opening, and managing the database. It specifies the error handler.

Methods of SQLiteOpenHelper class:

SQLiteOpenHelper class has many methods. Some of them are as follows:

1. public abstract void onCreate(SQLiteDatabase db): This method is called only when you create a database for the first time.

2. public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion): This method is called when the database needs to be upgraded.

3. public synchronized void close(): This method closes the database object.

SQLite database class:

We have different methods in this class which are used to perform some commands on SQLite databases such as create, update, delete, select, etc.

Methods of SQLiteDatabase class:

SQLite database class has many methods. Some of them are as follows:

1. void execSQL(String sql): This method executes a SQL query that is NOT a SELECT query.

2. long insert(String table, String nullColumnHack, ContentValues values): This method inserts a record on the database.

3. int update(String table,ContentValues values,String whereClause, String[]whereArgs): This method is used for updating a row.

How to Create an SQLite database:

First, we have to define how our database looks. We should create a table and implement methods.

In the AndroidManifest.xml file you add permission to access the storage.

1
2
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
android: name = "android.permission.WRITE_EXTERNAL_STORAGE" / >

activity_main.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <EditText
        android:id="@+id/etna"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="Please enter name"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/etcell"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="Please enter cell no"
        android:inputType="textPersonName" />

    <Button
        android:id="@+id/bsubmit"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="submit"
        android:text="SUBMIT" />

    <Button
        android:id="@+id/bshow"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="showdata"
        android:text="SHOW DATA" />

    <Button
        android:id="@+id/bedit"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="edit"
        android:text="EDIT DATA " />

    <Button
        android:id="@+id/bdele"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="delete"
        android:text="DELETE DATA" />
</LinearLayout>

activity_data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".Data">

    <TextView
        android:id="@+id/textView"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/data"
        android:textSize="18sp"
        android:textStyle="bold" />
</LinearLayout>

Data.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package com.example.sqlitedatabasesavedata;

import androidx.appcompat.app.AppCompatActivity;
import android.database.SQLException;
import android.os.Bundle;
import android.widget.TextView;
import android.widget.Toast;

public class Data extends AppCompatActivity {
  TextView textView;
  @Override
  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_data);
    textView = findViewById(R.id.textView);
    try {
      ContactsDB db = new ContactsDB(this);
      db.open();
      textView.setText(db.returndata());
      db.close();
    } catch (SQLException e) {
      Toast.makeText(Data.this, e.getMessage(), Toast.LENGTH_LONG)
        .show();
    }
  }
}

Create SQLiteOpenHelper class.

ContactsDB.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
package com.example.sqlitedatabasesavedata;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class ContactsDB {
  public static final String Key_RowId = "_id";
  public static final String Key_Name = "person_name";
  public static final String Key_Cell = "_cell";

  private final String Database_Name = "ContactsDB"; //Database Name
  private final String Database_Table = "ContactsTavle";
  private final int Database_Version = 1;

  private DBHelper ourHelper;
  private final Context ourContext;
  private SQLiteDatabase ourdatabase;

  public ContactsDB(Context context) {
    ourContext = context;
  }

  private class DBHelper extends SQLiteOpenHelper {
    public DBHelper(Context context) {
      super(context, Database_Name, null, Database_Version);

    }
    @Override
    public void onCreate(SQLiteDatabase db) {
      String sqlcode = "CREATE TABLE ContactsTable(_id INTEGER PRIMARY KEY AUTOTNCREMENT, person_name TEXT NOT NULL, _cell TEXT NOT NULL);";
      db.execSQL(sqlcode);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
      db.execSQL("DROP TABLE IF EXISTS " + Database_Table);
      onCreate(db);
    }
  }
  public ContactsDB open() throws SQLException {
    ourHelper = new DBHelper(ourContext);
    ourdatabase = ourHelper.getWritableDatabase();
    return this;
  }
  public void close() {
    ourHelper.close();
  }
  public long creat(String name, String cell) {
    ContentValues cv = new ContentValues();
    cv.put(Key_Name, name);
    cv.put(Key_Cell, cell);
    return ourdatabase.insert(Database_Table, null, cv);
  }
  public String returndata() {
    String[] column = new String[] {
      Key_RowId,
      Key_Name,
      Key_Cell
    };
    Cursor c = ourdatabase.query(Database_Table, column, null, null, null, null, null);
    String resu = "";
    int irowid = c.getColumnIndex(Key_RowId);
    int iname = c.getColumnIndex(Key_Name);
    int icell = c.getColumnIndex(Key_Cell);
    for (c.moveToFirst(); c.isAfterLast(); c.moveToNext()) {
      resu = resu + c.getString(irowid) + ":" + c.getString(iname) + " " + c.getString(icell) + "\n";
    }
    c.close();
    return resu;
  }
  public long deleteEnter(String rowId) {
    return ourdatabase.delete(Database_Table, Key_RowId + "=?", new String[] {
      rowId
    });
  }

  public long update(String rowId, String cell, String name) {
    ContentValues cu = new ContentValues();
    cu.put(Key_Name, name);
    cu.put(Key_Cell, cell);
    return ourdatabase.update(Database_Table, cu, Key_RowId + "=?", new String[] {
      rowId
    });
  }
}

MainActivity.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
package com.example.sqlitedatabasesavedata;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.database.SQLException;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
import android.widget.Toolbar;

public class MainActivity extends AppCompatActivity {
  EditText etname, etcell;
  @Override
  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    etname = findViewById(R.id.etna);
    etcell = findViewById(R.id.etcell);
  }
  public void showdata(View v) {
    startActivity(new Intent(this, Data.class));
  }

  public void submit(View v) {
    String name = etname.getText()
      .toString()
      .trim();
    String cell = etcell.getText()
      .toString()
      .trim();
    try {
      ContactsDB db = new ContactsDB(this);
      db.open();
      db.creat(name, cell);
      db.close();
      Toast.makeText(MainActivity.this, "Successfully saved ", Toast.LENGTH_LONG)
        .show();
      etname.setText("");
      etcell.setText("");
    } catch (SQLException e) {
      Toast.makeText(MainActivity.this, e.getMessage(), Toast.LENGTH_LONG)
        .show();
    }
  }

  public void edit(View v) {
    try {
      ContactsDB db = new ContactsDB(this);
      db.open();
      db.update("1", "John", "24334421");
      db.close();
      Toast.makeText(MainActivity.this, "Successfully updated", Toast.LENGTH_LONG)
        .show();
    } catch (SQLException e) {
      Toast.makeText(MainActivity.this, e.getMessage(), Toast.LENGTH_LONG)
        .show();
    }
  }

  public void delete(View v) {
    try {
      ContactsDB db = new ContactsDB(this);
      db.open();
      db.deleteEnter("1");
      Toast.makeText(MainActivity.this, "Successfully delete", Toast.LENGTH_LONG)
        .show();
      db.close();
    } catch (SQLException e) {
      Toast.makeText(MainActivity.this, e.getMessage(), Toast.LENGTH_LONG)
        .show();
    }
  }
}

Output: