We do SQLite operations in Android by extending the SQLiteOpenHelper Class.

I will create an Employee table with three fields

id
name
company.

I have a model class same as the database table Employee.

Employee.java

public class Employee {

    public int id;
    public String name, company;

    public void setAll(int id, String name, String company) {
        this.id = id;
        this.name = name;
        this.company = company;
    }
}

Lets see How we can do the CRUD (Create, Read, Update and Delete) Operations.

CREATE

We create a class that extends the SQLiteOpenHelper.
Override the “onCreate” method to write the Table create Query.


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

import java.util.ArrayList;
import java.util.HashMap;

public class DBHelper extends SQLiteOpenHelper {

    public static final String TAG = "DBHelper";

    public static final String DATABASE_NAME = "EmpDB.db";
    public static final String EMP_TABLE_NAME = "EmpTable";
    public static final String EMP_COLUMN_ID = "id";
    public static final String EMP_COLUMN_NAME = "name";
    public static final String EMP_COLUMN_COMPANY = "company";

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "CREATE TABLE IF NOT EXISTS " + EMP_TABLE_NAME + " (" + EMP_COLUMN_ID + " integer primary key, " + EMP_COLUMN_NAME + " text, " + EMP_COLUMN_COMPANY + " text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
    
}

READ

The below function gets all records from the SQLite Database, but it has a parameter which is used to search the Database. if the search string is null then fetch all the records from the database.


 public ArrayList<Employee> getAllEmployees(String searchName) {
 
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        String query;

        if (searchName == null) {
            query = "select * from "  + EMP_TABLE_NAME;
        } else
            query = "select * from " + EMP_TABLE_NAME  + " where name like '%" + searchName + "%'";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {

            Employee emp = new Employee();

            int id = res.getInt(0);
            String name = res.getString(1);
            String company = res.getString(2);

            emp.setAll(id, name, company);
            employeeList.add(emp);

            res.moveToNext();
        }
        return employeeList;
        
}

UPDATE

Below function updates the table row belonging to a particular ID.


 public boolean updateDB(Integer id, String name, String company) {
	SQLiteDatabase db = this.getWritableDatabase();
	ContentValues contentValues = new ContentValues();
	contentValues.put("name", name);
	contentValues.put("company", company);
	db.update(EMP_TABLE_NAME, contentValues, "id = ? ", new String[]{Integer.toString(id)});
	return true;
}

DELETE

Function deletes the row with the passed ID.

 public Integer deleteRow(Integer id) {
	SQLiteDatabase db = this.getWritableDatabase();
	return db.delete(EMP_TABLE_NAME, "id = ? ", new String[]{Integer.toString(id)});
}

The complete DBHelper class looks like this.


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

import java.util.ArrayList;
import java.util.HashMap;

public class DBHelper extends SQLiteOpenHelper {

    public static final String TAG = "DBHelper";

    public static final String DATABASE_NAME = "EmpDB.db";
    public static final String EMP_TABLE_NAME = "EmpTable";
    public static final String EMP_COLUMN_ID = "id";
    public static final String EMP_COLUMN_NAME = "name";
    public static final String EMP_COLUMN_COMPANY = "company";

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "create table IF NOT EXISTS " + EMP_TABLE_NAME + " (id integer primary key, name text, company text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /* Insert a row into the database */
    public boolean insert(String name, String company) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("company", company);
        db.insert(EMP_TABLE_NAME, null, contentValues);
        return true;
    }

    /* Get the first row ID from the table */
    public int getFirstId() {

        int idToUpdate = 0;
        String query = "select id from " + EMP_TABLE_NAME + " LIMIT 1";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);

        if (null != res && res.getCount() > 0) {
            res.moveToFirst();
            idToUpdate = res.getInt(0);
        }
        return idToUpdate;
    }

    /* Get all employees records from the table based on search criteria */
    public ArrayList<Employee> getAllEmployees(String searchName) {
 
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        String query;

        if (searchName == null) {
            query = "select * from "  + EMP_TABLE_NAME;
        } else
            query = "select * from " + EMP_TABLE_NAME  + " where name like '%" + searchName + "%'";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {

            Employee emp = new Employee();

            int id = res.getInt(0);
            String name = res.getString(1);
            String company = res.getString(2);

            emp.setAll(id, name, company);
            employeeList.add(emp);

            res.moveToNext();
        }
        return employeeList;
        
   }

    /* Update the table row with ID - id */
    public boolean updateDB(Integer id, String name, String company) {
        Log.i(TAG, "Updating ID : " + id);
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("company", company);
        db.update(EMP_TABLE_NAME, contentValues, "id = ? ", new String[]{Integer.toString(id)});
        return true;
    }

    /* Delete the row with ID - id from the employees table */
    public Integer deleteRow(Integer id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(EMP_TABLE_NAME, "id = ? ", new String[]{Integer.toString(id)});
    }

}

Here is the MainActivity that implements the database functions.
Please create a layout xml with the below UI elements in the MainActivity and link them.


import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.inputmethod.InputMethodManager;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    public static final String TAG = "MainActivity";

    TextView mTv;
    EditText empName, empCompany, edtSearch;
    Button btnInsert, btnUpdate, btnSearch, btnDelete, btnShowAll;

    private DBHelper myDb;
    ArrayList<Employee> allEmployees;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // Edittext
        empName = (EditText) findViewById(R.id.empName);
        empCompany = (EditText) findViewById(R.id.empCompany);
        edtSearch = (EditText) findViewById(R.id.edtSearch);

        // Buttons
        btnInsert = (Button) findViewById(R.id.insert_btn);
        btnUpdate = (Button) findViewById(R.id.update_btn);
        btnSearch = (Button) findViewById(R.id.search_btn);
        btnDelete = (Button) findViewById(R.id.delete_btn);
        btnShowAll = (Button) findViewById(R.id.showall_btn);

        // TextView
        mTv = (TextView) findViewById(R.id.listTv);

        // Add Click Listeners
        btnInsert.setOnClickListener(this);
        btnUpdate.setOnClickListener(this);
        btnSearch.setOnClickListener(this);
        btnDelete.setOnClickListener(this);
        btnShowAll.setOnClickListener(this);

        // Create an instance of our DB Helper Class
        myDb = new DBHelper(this);

        allEmployees = myDb.getAllEmployees(null);
        printTable(allEmployees);

    }

    void printTable(ArrayList<Employee> allEmployees) {

        // Empty text in the TextView to show new values
        mTv.setText("");

        StringBuffer stringBuffer = new StringBuffer();
        // Append table values in a String buffer
        for (Employee emp : allEmployees) {
            stringBuffer.append("[ " + emp.id + " ] [ " + emp.name + " ] [ " + emp.company + " ]\n");
        }

        // Show the table values
        mTv.setText(stringBuffer);
    }

    @Override
    public void onClick(View v) {

        if (v == btnInsert) {
            String name = empName.getText().toString().trim();
            String company = empCompany.getText().toString().trim();

            if (name.length() > 0 && company.length() > 0) {
                myDb.insert(name, company);
                allEmployees = myDb.getAllEmployees(null);
            } else {
                showToastMessage("Name or Company is Empty");
            }
        }

        if (v == btnSearch) {
            String searchVal = edtSearch.getText().toString().trim();
            allEmployees = myDb.getAllEmployees(searchVal);
            Log.i(TAG, "Searched Size : " + allEmployees.size());
            if (allEmployees.size() == 0) {
                showToastMessage("No Results");
            }
        }

        if (v == btnDelete) {
            int idToDelete = myDb.getFirstId();
            if (idToDelete > 0) {
                myDb.deleteRow(idToDelete);
                allEmployees = myDb.getAllEmployees(null);
            }
        }

        if (v == btnUpdate) {
            int idToUpdate = myDb.getFirstId();
            if (idToUpdate > 0) {
                myDb.updateDB(idToUpdate, "Updated Name", "Updated Company");
                allEmployees = myDb.getAllEmployees(null);
            }
        }

        if (v == btnShowAll) {
            edtSearch.setText("");
            allEmployees = myDb.getAllEmployees(null);
        }

        hideKeyBoard();
        printTable(allEmployees);

    }

    void hideKeyBoard() {
        // Check if no view has focus:
        View view = this.getCurrentFocus();
        if (view != null) {
            InputMethodManager imm = (InputMethodManager) getSystemService(INPUT_METHOD_SERVICE);
            imm.hideSoftInputFromWindow(view.getWindowToken(), 0);
        }
    }

    void showToastMessage(String message) {
        Toast.makeText(this, message, Toast.LENGTH_LONG).show();
    }
}

All Done.

Please make your layout with the buttons and textviews mentioned in the MainActivity.

You can send your queries and comments to coderzheaven@gmail.com.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

 

 

 

Theme by HermesThemes

Copyright © 2018 CoderzHeaven. All Rights Reserved

Please wait...

Subscribe to our newsletter

Want to be notified when our article is published? Enter your email address and name below to be the first to know.