Android

Working With SQLite In Android Apps

Introduction

Android is one of the most popular operating systems for mobile. You can create the database and manipulate the data in Android apps using SQLite. The database is used to store and retrieve the data. Here, I will show you how to work with SQLite in Android application using Android Studio.

Requirements

Steps to be followed

Carefully follow the below steps to work with SQLite Android applications using Android Studio, and I have included the source code below.

Step 1

Open Android Studio and start a new project.

Android

Step 2

Put the application name and company domain. If you wish to use C++ for coding the project, mark the "Include C++ support" checkbox and click Next.

Android

Step 3

Select the Android minimum SDK version. After you chose the minimum SDK, it will show approximate percentage of people using that SDK. Then, click Next.

Android

Step 4

Choose "Basic Activity" and click Next.

Android

Step 5

Put the activity name and layout name. Android Studio basically takes the java class name that you provide as activity name. Click Finish.

Android

Step 6

Go to activity_main.xml and click the text botton. This XML file contains the designing code for Android app. Into the activity_main.xml, copy and paste the below code.

Activity_main.xml code

  1. <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  2.     xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"  
  3.     android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"  
  4.     android:paddingRight="@dimen/activity_horizontal_margin"  
  5.     android:paddingTop="@dimen/activity_vertical_margin"  
  6.     android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">  
  7.   
  8.     <EditText  
  9.         android:layout_width="wrap_content"  
  10.         android:layout_height="wrap_content"  
  11.         android:id="@+id/user_Input"  
  12.         android:layout_alignParentTop="true"  
  13.         android:layout_centerHorizontal="true"  
  14.         android:layout_marginTop="69dp"  
  15.         android:width="300dp"  
  16.         android:inputType=""  
  17.         tools:ignore="LabelFor" />  
  18.   
  19.     <Button  
  20.         android:layout_width="wrap_content"  
  21.         android:layout_height="wrap_content"  
  22.         android:text="@string/add"  
  23.         android:id="@+id/add_Button"  
  24.         android:layout_below="@+id/user_Input"  
  25.         android:layout_alignStart="@+id/user_Input"  
  26.         android:layout_marginTop="40dp"  
  27.         android:onClick="addButtonClicked" />  
  28.   
  29.     <Button  
  30.         android:layout_width="wrap_content"  
  31.         android:layout_height="wrap_content"  
  32.         android:text="@string/delete"  
  33.         android:id="@+id/delete_Button"  
  34.         android:layout_alignTop="@+id/add_Button"  
  35.         android:layout_alignEnd="@+id/user_Input"  
  36.         android:onClick="deleteButtonClicked" />  
  37.   
  38.     <TextView  
  39.         android:layout_width="wrap_content"  
  40.         android:layout_height="wrap_content"  
  41.         android:textAppearance="?android:attr/textAppearanceLarge"  
  42.         android:text="Large Text"  
  43.         android:id="@+id/records_TextView"  
  44.         android:layout_centerVertical="true"  
  45.         android:layout_centerHorizontal="true" />  
  46. </RelativeLayout>  

Android

Step 7

In the MainActivity.java file, copy and paste the below code. Java programming is the back-end language for Android. Do not replace your package name, otherwise the app will not run.

MainActivity.java code

  1. package ganeshannt.sqlite;  
  2.   
  3. import android.app.Activity;  
  4. import android.os.Bundle;  
  5. import android.view.View;  
  6. import android.widget.EditText;  
  7. import android.widget.TextView;  
  8.   
  9. public class MainActivity extends Activity {  
  10. // Declare references  
  11.   
  12.     EditText userInput;  
  13.     TextView recordsTextView;  
  14.     MyDBHandler dbHandler;  
  15.   
  16.     @Override  
  17.     protected void onCreate(Bundle savedInstanceState) {  
  18.         super.onCreate(savedInstanceState);  
  19.         setContentView(R.layout.activity_main);  
  20.   
  21.         userInput = (EditText) findViewById(R.id.user_Input);  
  22.         recordsTextView = (TextView) findViewById(R.id.records_TextView);  
  23.           
  24.         dbHandler = new MyDBHandler(this, null, null, 1);  
  25.         printDatabase();  
  26.     }  
  27.   
  28.     //Print the database  
  29.     public void printDatabase(){  
  30.         String dbString = dbHandler.databaseToString();  
  31.         recordsTextView.setText(dbString);  
  32.         userInput.setText("");  
  33.     }  
  34.   
  35.     //add your elements onclick methods.  
  36.     //Add a product to the database  
  37.     public void addButtonClicked(View view){  
  38.         // dbHandler.add needs an object parameter.  
  39.         Products product = new Products(userInput.getText().toString());  
  40.         dbHandler.addProduct(product);  
  41.         printDatabase();  
  42.     }  
  43.   
  44.     //Delete items  
  45.     public void deleteButtonClicked(View view){  
  46.         // dbHandler delete needs string to find in the db  
  47.         String inputText = userInput.getText().toString();  
  48.         dbHandler.deleteProduct(inputText);  
  49.         printDatabase();  
  50.     }  
  51.   
  52. }  

Step 8

Create new MyDBHelper.java file (File ⇒ New ⇒Java class).

In MyDBHelper.java, copy and paste the below code. Java programming contains SQLite query. Do not replace your package name, otherwise the app will not run.

MyDBHelper.java code

  1. package ganeshannt.sqlite;  
  2.   
  3. // This class handles all the database activities  
  4. import android.database.sqlite.SQLiteDatabase;  
  5. import android.database.sqlite.SQLiteOpenHelper;  
  6. import android.database.Cursor;  
  7. import android.content.Context;  
  8. import android.content.ContentValues;  
  9.   
  10. public class MyDBHandler extends SQLiteOpenHelper{  
  11.     private static final int DATABASE_VERSION = 1;  
  12.     private static final String DATABASE_NAME = "productDB.db";  
  13.     public static final String TABLE_PRODUCTS = "products";  
  14.     public static final String COLUMN_ID = "_id";  
  15.     public static final String COLUMN_PRODUCTNAME = "productname";  
  16.   
  17.     //We need to pass database information along to superclass  
  18.     public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {  
  19.         super(context, DATABASE_NAME, factory, DATABASE_VERSION);  
  20.     }  
  21.   
  22.     @Override  
  23.     public void onCreate(SQLiteDatabase db) {  
  24.         String query = "CREATE TABLE " + TABLE_PRODUCTS + "(" +  
  25.                 COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +  
  26.                 COLUMN_PRODUCTNAME + " TEXT " +  
  27.                 ");";  
  28.         db.execSQL(query);  
  29.     }  
  30.     //Lesson 51  
  31.     @Override  
  32.     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
  33.         db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);  
  34.         onCreate(db);  
  35.     }  
  36.   
  37.     //Add a new row to the database  
  38.     public void addProduct(Products product){  
  39.         ContentValues values = new ContentValues();  
  40.         values.put(COLUMN_PRODUCTNAME, product.get_productname());  
  41.         SQLiteDatabase db = getWritableDatabase();  
  42.         db.insert(TABLE_PRODUCTS, null, values);  
  43.         db.close();  
  44.     }  
  45.   
  46.     //Delete a product from the database  
  47.     public void deleteProduct(String productName){  
  48.         SQLiteDatabase db = getWritableDatabase();  
  49.         db.execSQL("DELETE FROM " + TABLE_PRODUCTS + " WHERE " + COLUMN_PRODUCTNAME + "=\"" + productName + "\";");  
  50.     }  
  51.   
  52.     // this is goint in record_TextView in the Main activity.  
  53.     public String databaseToString(){  
  54.         String dbString = "";  
  55.         SQLiteDatabase db = getWritableDatabase();  
  56.         String query = "SELECT * FROM " + TABLE_PRODUCTS + " WHERE 1";// why not leave out the WHERE  clause?  
  57.   
  58.         //Cursor points to a location in your results  
  59.         Cursor recordSet = db.rawQuery(query, null);  
  60.         //Move to the first row in your results  
  61.         recordSet.moveToFirst();  
  62.   
  63.         //Position after the last row means the end of the results  
  64.         while (!recordSet.isAfterLast()) {  
  65.             // null could happen if we used our empty constructor  
  66.             if (recordSet.getString(recordSet.getColumnIndex("productname")) != null) {  
  67.                 dbString += recordSet.getString(recordSet.getColumnIndex("productname"));  
  68.                 dbString += "\n";  
  69.             }  
  70.             recordSet.moveToNext();  
  71.         }  
  72.         db.close();  
  73.         return dbString;  
  74.     }  
  75.   
  76. }  

Step 9

Create new Products.java file (File ⇒ New ⇒Java class).

In the Products.java file, copy and paste the below code. Do not replace your package name otherwise app will not run.

Products.java code

  1. package ganeshannt.sqlite;  
  2.   
  3. public class Products {  
  4.     private int _id;  
  5.     private String _productname;  
  6.   
  7.     //Added this empty constructor in lesson 50 in case we ever want to create the object and assign it later.  
  8.     public Products(){  
  9.   
  10.     }  
  11.     public Products(String productName) {  
  12.         this._productname = productName;  
  13.     }  
  14.   
  15.     public int get_id() {  
  16.         return _id;  
  17.     }  
  18.   
  19.     public void set_id(int _id) {  
  20.         this._id = _id;  
  21.     }  
  22.   
  23.     public String get_productname() {  
  24.         return _productname;  
  25.     }  
  26.   
  27.     public void set_productname(String _productname) {  
  28.         this._productname = _productname;  
  29.     }  
  30. }  
Step 10

Click the "Make Project" option and run the project.

Android

Deliverables

Here, we have successfully created Text entry application.

Android

Add data

Android
Android

Delete data

Android

Deleted

Android

Don’t forgot to like and follow me. If you have any doubt, just comment below.

Source code

https://github.com/GaneshanNT/sqlite

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs