How To Perform CRUD Operations SQLite In Android

Users' data can be saved on the application in different ways. And we can store data on the user's device in SQLite tables, shared preferences, and in many different ways. 

In this blog, we will take a look at the SQLite database overview and its different supporting data.

What Is SQLite?

SQLite is a software library that provides a relational database management system. SQLite means light weight in terms of setup.

When We Need SQLite?

If you are developing embedded software for devices like mobile phones, televisions, home electronic devices, cameras, etc., then sqlite is a good choice.

It is free and easy to use.

SQLite is open source, no commercial license required to use.

SQLite does not require a server to perform operations.

No extra setup or administration needed, it is embedded with Android.

SQLite Supporting Data Types:

• TEXT
• INTEGER
• REAL

SQLite Database Overview:

SQLite Open Helper:

When the application runs the first time – this point, we do not have a database, so here in this class, you can create a database.

When the application is upgraded to a new schema – our database will still be on the old schema from the older edition version of the app. We will have the option to alter or upgrad the database schema to match the needs of the app.

1    public DatabaseHelper(Context context) {
2    
3    super(context, DB_NAME, null, DB_VERSION);
4    
5    }

View Raw

• on Create(SQLiteDatabase db) :

It’s called when there is no database and the app needs one and to create a new database.

• on Upgrade(SQLiteDatabase db, int oldVersion, int newVersion) :

It’s called when the schema version we need does not match the new schema version of the database.

Example:

See all examples described here.

DBManager.Java

Using this class user can perform addition operation with database like insert, update, delete, select.

1    package com.n.sqlitecruddemo;
2    
3    
4    import android.content.ContentValues;
5    import android.content.Context;
6    import android.database.Cursor;
7    import android.database.SQLException;
8    import android.database.sqlite.SQLiteDatabase;
9    
10    public class DBManager {
11    
12    private DatabaseHelper dbHelper;
13    
14    private Context context;
15    
16    private SQLiteDatabase database;
17    
18    public DBManager(Context c) {
19    context = c;
20    }
21    
22    public DBManager open() throws SQLException {
23    dbHelper = new DatabaseHelper(context);
24    database = dbHelper.getWritableDatabase();
25    return this;
26    }
27    
28    public void close() {
29    dbHelper.close();
30    }
31    
32    public void insert(String name, String desc) {
33    ContentValues contentValue = new ContentValues();
34    contentValue.put(DatabaseHelper.SUBJECT, name);
35    contentValue.put(DatabaseHelper.DESC, desc);
36    database.insert(DatabaseHelper.TABLE_NAME, null, contentValue);
37    }
38    
39    public Cursor fetch() {
40    String[] columns = new String[] { DatabaseHelper._ID, DatabaseHelper.SUBJECT, DatabaseHelper.DESC };
41    Cursor cursor = database.query(DatabaseHelper.TABLE_NAME, columns, null, null, null, null, null);
42    if (cursor != null) {
43    cursor.moveToFirst();
44    }
45    return cursor;
46    }
47    
48    public int update(long _id, String name, String desc) {
49    ContentValues contentValues = new ContentValues();
50    contentValues.put(DatabaseHelper.SUBJECT, name);
51    contentValues.put(DatabaseHelper.DESC, desc);
52    int i = database.update(DatabaseHelper.TABLE_NAME, contentValues, DatabaseHelper._ID + " = " + _id, null);
53    return i;
54    }
55    
56    public void delete(long _id) {
57    database.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper._ID + "=" + _id, null);
58    }
59    
60    }

View Raw

DatabaseHelper.Java

Using this class users can create and alter database tables.

1    package com.n.sqlitecruddemo;
2    
3    import android.content.Context;
4    import android.database.sqlite.SQLiteDatabase;
5    import android.database.sqlite.SQLiteOpenHelper;
6    
7    public class DatabaseHelper extends SQLiteOpenHelper {
8    
9    // Table Name
10    public static final String TABLE_NAME = "Subject";
11    
12    // Table columns
13    public static final String _ID = "_id";
14    public static final String SUBJECT = "subjectName";
15    public static final String DESC = "description";
16    
17    // Database Information
18    static final String DB_NAME = "StudentSubject.DB";
19    
20    // database version
21    static final int DB_VERSION = 1;
22    
23    // Creating table query
24    private static final String CREATE_TABLE = "create table " + TABLE_NAME + "(" + _ID
25    #ERROR!
26    
27    public DatabaseHelper(Context context) {
28    super(context, DB_NAME, null, DB_VERSION);
29    }
30    
31    @Override
32    public void onCreate(SQLiteDatabase db) {
33    db.execSQL(CREATE_TABLE);
34    }
35    
36    @Override
37    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
38    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
39    onCreate(db);
40    }
41    }

View Raw

AddSubjectsActivity.Java

Using this activity user perform insert operation with database.

1    package com.n.sqlitecruddemo;
2    import android.app.Activity;
3    import android.content.Intent;
4    import android.os.Bundle;
5    import android.view.View;
6    import android.view.View.OnClickListener;
7    import android.widget.Button;
8    import android.widget.EditText;
9    
10    public class AddSubjectsActivity extends Activity implements OnClickListener {
11    
12    private Button addTodoBtn;
13    private EditText subjectEditText;
14    private EditText descEditText;
15    
16    private DBManager dbManager;
17    
18    @Override
19    protected void onCreate(Bundle savedInstanceState) {
20    super.onCreate(savedInstanceState);
21    
22    setTitle("Add Record");
23    
24    setContentView(R.layout.activity_add_record);
25    
26    subjectEditText = (EditText) findViewById(R.id.subject_edittext);
27    descEditText = (EditText) findViewById(R.id.description_edittext);
28    
29    addTodoBtn = (Button) findViewById(R.id.add_record);
30    
31    dbManager = new DBManager(this);
32    dbManager.open();
33    addTodoBtn.setOnClickListener(this);
34    }
35    
36    @Override
37    public void onClick(View v) {
38    switch (v.getId()) {
39    
40    case R.id.add_record:
41    
42    final String name = subjectEditText.getText().toString();
43    final String desc = descEditText.getText().toString();
44    
45    dbManager.insert(name, desc);
46    
47    Intent main = new Intent(AddSubjectsActivity.this, SubjectListActivity.class)
48    .setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
49    
50    startActivity(main);
51    break;
52    }
53    }
54    
55    }

View Raw

Activity_add_record.Xml

Design file for add new recored.

1    ?xml version="1.0" encoding="utf-8"?
2    LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
3    android:layout_width="match_parent"
4    android:layout_height="match_parent"
5    android:orientation="vertical"
6    android:padding="20dp" 
7    
8    EditText
9    android:id="@+id/subject_edittext"
10    android:layout_width="match_parent"
11    android:layout_height="wrap_content"
12    android:ems="10"
13    android:hint="@string/enter_title" 
14    
15    requestFocus /
16    /EditText
17    
18    EditText
19    android:id="@+id/description_edittext"
20    android:layout_width="match_parent"
21    android:layout_height="wrap_content"
22    android:ems="10"
23    android:hint="@string/enter_desc"
24    android:inputType="textMultiLine"
25    android:minLines="5" 
26    /EditText
27    
28    Button
29    android:id="@+id/add_record"
30    android:layout_width="wrap_content"
31    android:layout_height="wrap_content"
32    android:layout_gravity="center"
33    android:text="@string/add_record" /
34    
35    /LinearLayout

View Raw

SubjectListActivity.Java

In this file, users can view the list of data available in the database. Here user can perform the select operation.

1    package com.n.sqlitecruddemo;
2    
3    import android.support.v7.app.AppCompatActivity;
4    import android.os.Bundle;
5    
6    import android.content.Intent;
7    import android.database.Cursor;
8    import android.support.v4.widget.SimpleCursorAdapter;
9    import android.view.Menu;
10    import android.view.MenuItem;
11    import android.view.View;
12    import android.widget.AdapterView;
13    import android.widget.ListView;
14    import android.widget.TextView;
15    
16    public class SubjectListActivity extends AppCompatActivity {
17    
18    private DBManager mDBManager;
19    
20    private ListView mListView;
21    
22    private SimpleCursorAdapter mAdaper;
23    
24    final String[] from = new String[] { DatabaseHelper._ID,
25    DatabaseHelper.SUBJECT, DatabaseHelper.DESC };
26    
27    final int[] IDS = new int[] { R.id.id, R.id.title, R.id.desc };
28    
29    @Override
30    protected void onCreate(Bundle savedInstanceState) {
31    super.onCreate(savedInstanceState);
32    
33    setContentView(R.layout.fragment_subject_list);
34    
35    mDBManager = new DBManager(this);
36    mDBManager.open();
37    Cursor cursor = mDBManager.fetch();
38    
39    mListView = (ListView) findViewById(R.id.subject_list_view);
40    mListView.setEmptyView(findViewById(R.id.empty_view));
41    
42    mAdaper = new SimpleCursorAdapter(this, R.layout.activity_view_all_subject, cursor, from, IDS, 0);
43    mAdaper.notifyDataSetChanged();
44    
45    mListView.setAdapter(mAdaper);
46    
47    // OnCLickListiner For List Items
48    mListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
49    @Override
50    public void onItemClick(AdapterView parent, View view, int position, long viewId) {
51    TextView idTextView = (TextView) view.findViewById(R.id.id);
52    TextView titleTextView = (TextView) view.findViewById(R.id.title);
53    TextView descTextView = (TextView) view.findViewById(R.id.desc);
54    
55    String id = idTextView.getText().toString();
56    String title = titleTextView.getText().toString();
57    String desc = descTextView.getText().toString();
58    
59    Intent modify_intent = new Intent(getApplicationContext(), ModifySubjectActivity.class);
60    modify_intent.putExtra("name", title);
61    modify_intent.putExtra("desc", desc);
62    modify_intent.putExtra("id", id);
63    
64    startActivity(modify_intent);
65    }
66    });
67    }
68    
69    @Override
70    public boolean onCreateOptionsMenu(Menu menu) {
71    getMenuInflater().inflate(R.menu.main, menu);
72    return true;
73    }
74    
75    @Override
76    public boolean onOptionsItemSelected(MenuItem item) {
77    
78    int id = item.getItemId();
79    if (id == R.id.add_record) {
80    
81    Intent add_mem = new Intent(this, AddSubjectsActivity.class);
82    startActivity(add_mem);
83    
84    }
85    return super.onOptionsItemSelected(item);
86    }
87    
88    }

View Raw

Fragment_subject_list.Xml

Design file to show list data.

1    ?xml version="1.0" encoding="utf-8"?
2    RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
3    android:layout_width="fill_parent"
4    android:layout_height="fill_parent" 
5    
6    ListView
7    android:id="@+id/subject_list_view"
8    android:layout_width="match_parent"
9    android:layout_height="wrap_content"
10    android:dividerHeight="1dp"
11    android:padding="10dp" >
12    /ListView
13    
14    TextView
15    android:id="@+id/empty_view"
16    android:layout_width="wrap_content"
17    android:layout_height="wrap_content"
18    android:layout_centerInParent="true"
19    android:text="@string/empty_list_text" /
20    
21    /RelativeLayout 

View Raw

ModifySubjectActivity.Java

Using this file user can perform modify or delete operation with the database.

1    package com.n.sqlitecruddemo;
2    
3    
4    import android.app.Activity;
5    import android.content.Intent;
6    import android.os.Bundle;
7    import android.view.View;
8    import android.view.View.OnClickListener;
9    import android.widget.Button;
10    import android.widget.EditText;
11    
12    public class ModifySubjectActivity extends Activity implements OnClickListener {
13    
14    private EditText mTitletext;
15    private Button updateBtn, mDeleteButton;
16    private EditText mDescText;
17    
18    private long mID;
19    
20    private DBManager dbManager;
21    
22    @Override
23    protected void onCreate(Bundle savedInstanceState) {
24    super.onCreate(savedInstanceState);
25    
26    setTitle("Modify Record");
27    
28    setContentView(R.layout.activity_modify_record);
29    
30    dbManager = new DBManager(this);
31    dbManager.open();
32    
33    mTitletext = (EditText) findViewById(R.id.subject_edittext);
34    mDescText = (EditText) findViewById(R.id.description_edittext);
35    
36    updateBtn = (Button) findViewById(R.id.btn_update);
37    mDeleteButton = (Button) findViewById(R.id.btn_delete);
38    
39    Intent intent = getIntent();
40    String id = intent.getStringExtra("id");
41    String name = intent.getStringExtra("name");
42    String desc = intent.getStringExtra("desc");
43    
44    mID = Long.parseLong(id);
45    
46    mTitletext.setText(name);
47    mDescText.setText(desc);
48    
49    updateBtn.setOnClickListener(this);
50    mDeleteButton.setOnClickListener(this);
51    }
52    
53    @Override
54    public void onClick(View v) {
55    switch (v.getId()) {
56    case R.id.btn_update:
57    String title = mTitletext.getText().toString();
58    String desc = mDescText.getText().toString();
59    
60    dbManager.update(mID, title, desc);
61    this.returnHome();
62    break;
63    
64    case R.id.btn_delete:
65    dbManager.delete(mID);
66    this.returnHome();
67    break;
68    }
69    }
70    
71    public void returnHome() {
72    Intent home_intent = new Intent(getApplicationContext(), SubjectListActivity.class)
73    .setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
74    startActivity(home_intent);
75    }
76    }

View Raw

Activity_modify_record.Xml

Design file for update records.

1    ?xml version="1.0" encoding="utf-8"?
2    LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
3    android:layout_width="match_parent"
4    android:layout_height="match_parent"
5    android:orientation="vertical"
6    android:padding="10dp" 
7    
8    EditText
9    android:id="@+id/subject_edittext"
10    android:layout_width="match_parent"
11    android:layout_height="wrap_content"
12    android:layout_marginBottom="10dp"
13    android:ems="10"
14    android:hint="@string/enter_title" /
15    
16    EditText
17    android:id="@+id/description_edittext"
18    android:layout_width="match_parent"
19    android:layout_height="wrap_content"
20    android:ems="10"
21    android:hint="@string/enter_desc"
22    android:inputType="textMultiLine"
23    android:minLines="5" 
24    /EditText
25    
26    
27    LinearLayout
28    android:layout_width="fill_parent"
29    android:layout_height="wrap_content"
30    android:weightSum="2"
31    android:gravity="center_horizontal"
32    android:orientation="horizontal" 
33    
34    Button
35    android:id="@+id/btn_update"
36    android:layout_width="wrap_content"
37    android:layout_height="wrap_content"
38    android:layout_weight="1"
39    android:text="@string/btn_update" /
40    
41    Button
42    android:id="@+id/btn_delete"
43    android:layout_width="wrap_content"
44    android:layout_height="wrap_content"
45    android:layout_weight="1"
46    android:text="@string/btn_delete" /
47    /LinearLayout
48    
49    /LinearLayout

View Raw

Conclusion:

This guide is all about the SQLite database in Android. Learn and try to develop the best application for your clients. And if you get stuck at any point, use the source code given in this blog. An app development company can help you as well in creating the best applications.