Hey, friends here is an Android SQLite Database Example. You may already know that we have SQLite database in android that we can use as a local SQL Database for our android application.
This Android SQLite Database Example will cover Creating Database, Creating Tables, Creating Records, Reading Records, Updating Records and Deleting Records in Android SQLite Database. After going through this post you will be having a complete idea about using SQLite database for your Android Application. So let’s start.
Table of Contents
What is SQLite?
SQLite is an SQL Database. I am assuming here that you are familiar with SQL databases. So in SQL database, we store data in tables. The tables are the structure of storing data consisting of rows and columns. We are not going in depth of what is an SQL database and how to work in SQL database. If you are going through this post, then you must know the Basics of SQL.
What is CRUD?
As the heading tells you here, we are going to learn the CRUD operation in SQLite Database.
But what is CRUD? CRUD is nothing but an abbreviation for the basic operations that we perform in any database. And the operations are
- Create
- Read
- Update
- Delete
Android SQLite Database Example App Apk
- Before moving ahead on this tutorial if you want to know what we will be building, you can get the final apk of this tutorial from the link given below.
Android SQLite Database Example App Apk Download
Android SQLite Database Example
Creating a new Android Studio Project
- As always we will create a new Android Studio Project. For this example, I have a new project named SQLiteCRUDExample.
- Once your project is loaded, we can start working on it.
The Database Structure
- The first thing needed is the database structure. We create database structure according to the system. But here we are not building an application, and it is only an example demonstrating the use of SQLite Database. So for this, I will use the following table structure.
- Now we have only a single table, but in real-world scenarios, you will have multiple tables with some complex relationships. Also, remember one thing whenever you create a table create a column named id with int as PRIMARY KEY and AUTOINCREMENT. (If you are confused don’t worry we will see now how do we create tables in database using SQL).
SQL Queries
- Now let’s see how we can create the above table in our SQLite database.
Creating the Table
1 2 3 4 5 6 7 8 9 | CREATE TABLE employees ( id INTEGER NOT NULL CONSTRAINT employees_pk PRIMARY KEY AUTOINCREMENT, name varchar(200) NOT NULL, department varchar(200) NOT NULL, joiningdate datetime NOT NULL, salary double NOT NULL ); |
Creating a new Record
1 2 3 4 5 6 | INSERT INTO employees (name, department, joiningdate, salary) VALUES ('Belal Khan', 'Technical', '2017-09-30 10:00:00', '40000'); |
Reading All Existing Records
1 2 3 | SELECT * FROM employees; |
Reading Specific Record
1 2 3 | SELECT * FROM employees WHERE id = 1; |
Note: * means selecting all the columns, if you want a specific column or multiple columns but not all you can write names of the columns like SELECT name, department.
Updating a Record
1 2 3 4 5 6 7 8 | UPDATE employees SET name = 'Belal Haque', department = 'Research and Development', salary = '100000' WHERE id = 1; |
Deleting a Record
1 2 3 | DELETE FROM employees WHERE id = 1; |
These are just some simple basics operations, but we can perform many tasks in our database. For this, you need to learn SQL in detail.
Note: SQL Queries are not case sensitive.
User Interface Design
- To implement all the above-given queries in our application, we need an Interface from where we can accomplish these tasks. Now, lets think about the screens that we need to make an app that will perform all the above-given queries with user interaction.
Adding a new Employee
- The first thing is adding a new record to our database, and for this, we can use the following screen.
- As you can see we have EditText, Button, Spinner and some TextViews. To create the above interface, you can use the following XML code. You need to paste the following code inside activity_main.xml which is generated by default in any project because this will be the first screen for our application.
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 | <?xml version="1.0" encoding="utf-8"?> <RelativeLayout 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" tools:context="net.simplifiedlearning.sqlitecrudexample.MainActivity"> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_centerVertical="true" android:orientation="vertical" android:padding="16dp"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginBottom="12dp" android:text="Add a new Employee" android:textAlignment="center" android:textAppearance="@style/Base.TextAppearance.AppCompat.Large" /> <EditText android:id="@+id/editTextName" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="Enter Employee Name" /> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginTop="10dp" android:paddingLeft="6dp" android:text="Select Department" /> <Spinner android:id="@+id/spinnerDepartment" android:layout_width="match_parent" android:layout_height="wrap_content" android:entries="@array/departments" /> <EditText android:id="@+id/editTextSalary" android:layout_width="match_parent" android:layout_height="wrap_content" android:digits="0123456789" android:hint="Enter Employee Salary" android:inputType="number" /> <Button android:id="@+id/buttonAddEmployee" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Add Employee" /> <TextView android:id="@+id/textViewViewEmployees" android:layout_width="match_parent" android:layout_height="wrap_content" android:padding="16dp" android:text="View Employees" android:textAlignment="center" android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium" android:textStyle="bold" /> </LinearLayout> </RelativeLayout> |
- For the spinner that we used in the above screen, we need to define an Array as the entries for the spinner. So go inside values->strings.xml and modify it as below.
1 2 3 4 5 6 7 8 9 10 11 12 13 | <resources> <string name="app_name">SQLiteCRUDExample</string> <array name="departments"> <item>Technical</item> <item>Support</item> <item>Research and Development</item> <item>Marketing</item> <item>Human Resource</item> </array> </resources> |
Fetching All the Employees
- Now after storing employee to the database, we also need to see all the stored employee from the database. For this, we can use a ListView.
- So, to create a new EmptyActivity in your project named EmployeeActivity. It will create a java file named EmployeeActivity.java and a layout file called activity_employee.xml.
- For this screen we can use the following design.
- This screen contains a only a ListView. The xml for the above screen is below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <?xml version="1.0" encoding="utf-8"?> <RelativeLayout 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" tools:context="net.simplifiedlearning.sqlitecrudexample.EmployeeActivity"> <ListView android:id="@+id/listViewEmployees" android:layout_width="match_parent" android:layout_height="wrap_content" /> </RelativeLayout> |
- But here we will not use a simple ListView as we need to display multiple items in the List we will be using a Custom ListView. You can learn about creating custom ListView from this tutorial -> Creating a CustomListview.
- And for the ListView item we need one more layout as well, so create one more layout file named list_layout_employee.xml and write the following xml code.
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 | <?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="horizontal" android:padding="8dp"> <LinearLayout android:layout_width="230dp" android:layout_height="wrap_content" android:orientation="vertical"> <TextView android:id="@+id/textViewName" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginBottom="5dp" android:layout_marginTop="10dp" android:text="Belal Khan" android:textAppearance="@style/Base.TextAppearance.AppCompat.Large" /> <TextView android:id="@+id/textViewDepartment" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Technical" android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium" /> <TextView android:id="@+id/textViewSalary" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="INR 40000" android:textAppearance="@style/Base.TextAppearance.AppCompat.Medium" /> <TextView android:id="@+id/textViewJoiningDate" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginTop="8dp" android:text="2017-09-30 10:00:00" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <Button android:id="@+id/buttonEditEmployee" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:background="@color/colorPrimary" android:text="Edit" /> <Button android:id="@+id/buttonDeleteEmployee" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:background="@color/colorAccent" android:text="Delete" /> </LinearLayout> </LinearLayout> |
- The above xml code will generate the following layout.
Updating the Employee
- Now we will create one more layout file (not a new activity only a layout file because we will edit the employee in an alert dialog) to update the employee data. So create a new layout file named dialog_update_employee.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 | <?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" android:padding="16dp"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginBottom="12dp" android:text="Edit Employee" android:textAlignment="center" android:textAppearance="@style/Base.TextAppearance.AppCompat.Large" /> <EditText android:id="@+id/editTextName" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="Enter Employee Name" /> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginTop="10dp" android:paddingLeft="6dp" android:text="Select Department" /> <Spinner android:id="@+id/spinnerDepartment" android:layout_width="match_parent" android:layout_height="wrap_content" android:entries="@array/departments" /> <EditText android:id="@+id/editTextSalary" android:layout_width="match_parent" android:layout_height="wrap_content" android:digits="0123456789" android:hint="Enter Employee Salary" android:inputType="number" /> <Button android:id="@+id/buttonUpdateEmployee" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Update" /> </LinearLayout> |
- This code will generate the following design.
- We don’t need a separate design for deleting the employee as we will be doing it from the button that we created on the List.
- So we are done with the interface design. Now lets start coding.
Coding the Application
Adding an Employee
- We will perform this operation inside MainActivity.java so inside this file write the following code.
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 | package net.simplifiedlearning.sqlitecrudexample; import android.content.Intent; import android.database.sqlite.SQLiteDatabase; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.EditText; import android.widget.Spinner; import android.widget.TextView; public class MainActivity extends AppCompatActivity implements View.OnClickListener { public static final String DATABASE_NAME = "myemployeedatabase"; TextView textViewViewEmployees; EditText editTextName, editTextSalary; Spinner spinnerDepartment; SQLiteDatabase mDatabase; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); textViewViewEmployees = (TextView) findViewById(R.id.textViewViewEmployees); editTextName = (EditText) findViewById(R.id.editTextName); editTextSalary = (EditText) findViewById(R.id.editTextSalary); spinnerDepartment = (Spinner) findViewById(R.id.spinnerDepartment); findViewById(R.id.buttonAddEmployee).setOnClickListener(this); textViewViewEmployees.setOnClickListener(this); //creating a database mDatabase = openOrCreateDatabase(DATABASE_NAME, MODE_PRIVATE, null); } //this method will validate the name and salary //dept does not need validation as it is a spinner and it cannot be empty private boolean inputsAreCorrect(String name, String salary) { if (name.isEmpty()) { editTextName.setError("Please enter a name"); editTextName.requestFocus(); return false; } if (salary.isEmpty() || Integer.parseInt(salary) <= 0) { editTextSalary.setError("Please enter salary"); editTextSalary.requestFocus(); return false; } return true; } //In this method we will do the create operation private void addEmployee() { } @Override public void onClick(View view) { switch (view.getId()) { case R.id.buttonAddEmployee: addEmployee(); break; case R.id.textViewViewEmployees: startActivity(new Intent(this, EmployeeActivity.class)); break; } } } |
- In the above code you see we used a method openOrCreateDatabase().
- This method takes 3 parameters.
First parameter is the database name as String, that we need to open. Lets assume we passed some name “xyz” as the first parameter then if there exist a database named “xyz” it will open it, if no database found with the specified name it will create a new database named “xyz” and will open it.
Second Parameter is the open mode as int. We have some predefined values for it, right now we are using MODE_PRIVATE and it simply means that only this application can access this database. - Now we will create a method to create the Employee table. So create a method named createEmployeeTable().
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | //this method will create the table //as we are going to call this method everytime we will launch the application //I have added IF NOT EXISTS to the SQL //so it will only create the table when the table is not already created private void createEmployeeTable() { mDatabase.execSQL( "CREATE TABLE IF NOT EXISTS employees (\n" + " id int NOT NULL CONSTRAINT employees_pk PRIMARY KEY,\n" + " name varchar(200) NOT NULL,\n" + " department varchar(200) NOT NULL,\n" + " joiningdate datetime NOT NULL,\n" + " salary double NOT NULL\n" + ");" ); } |
- Now just call this method just after calling the openOrCreateDatabase() method. In the above method we are just calling the method execSQL() to create our database table. The execSQL() method takes String as a parameter and the String is actually the SQL query that we need to execute.
- Remember we use execSQL() method only for creating table, inserting or updating records. We cannot use it to retrieve values.
- Till now we have the database, the table now we need to insert the employee in the table and we need to do this inside addEmployee() method.
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 | private void addEmployee() { String name = editTextName.getText().toString().trim(); String salary = editTextSalary.getText().toString().trim(); String dept = spinnerDepartment.getSelectedItem().toString(); //getting the current time for joining date Calendar cal = Calendar.getInstance(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss"); String joiningDate = sdf.format(cal.getTime()); //validating the inptus if (inputsAreCorrect(name, salary)) { String insertSQL = "INSERT INTO employees \n" + "(name, department, joiningdate, salary)\n" + "VALUES \n" + "(?, ?, ?, ?);"; //using the same method execsql for inserting values //this time it has two parameters //first is the sql string and second is the parameters that is to be binded with the query mDatabase.execSQL(insertSQL, new String[]{name, dept, joiningDate, salary}); Toast.makeText(this, "Employee Added Successfully", Toast.LENGTH_SHORT).show(); } } |
- Now you can test your application for the Create Operation.
- Now lets move towards fetching the stored employees which we call the Read Operation.
Retrieving All Employees
- We will do this operation inside EmployeeActivity.java. But before fetching the employees, we need two more java classes to display employees in the list. The first class is a regular java class to store the employee as an object and a CustomAdapter class for the ListView.
Employee Model
- Create a simple java class named Employee.java and write the following code.
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 | package net.simplifiedlearning.sqlitecrudexample; /** * Created by Belal on 9/30/2017. */ public class Employee { int id; String name, dept, joiningDate; double salary; public Employee(int id, String name, String dept, String joiningDate, double salary) { this.id = id; this.name = name; this.dept = dept; this.joiningDate = joiningDate; this.salary = salary; } public int getId() { return id; } public String getName() { return name; } public String getDept() { return dept; } public String getJoiningDate() { return joiningDate; } public double getSalary() { return salary; } } |
- Now we will create a custom adapter class for our ListView.
Custom ListView Adapter
- Create a java class named EmployeeAdapter and write the following code.
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 | package net.simplifiedlearning.sqlitecrudexample; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.support.annotation.NonNull; import android.support.annotation.Nullable; import android.support.v7.app.AlertDialog; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.ArrayAdapter; import android.widget.Button; import android.widget.EditText; import android.widget.Spinner; import android.widget.TextView; import android.widget.Toast; import java.util.List; /** * Created by Belal on 9/30/2017. */ public class EmployeeAdapter extends ArrayAdapter<Employee> { Context mCtx; int listLayoutRes; List<Employee> employeeList; SQLiteDatabase mDatabase; public EmployeeAdapter(Context mCtx, int listLayoutRes, List<Employee> employeeList, SQLiteDatabase mDatabase) { super(mCtx, listLayoutRes, employeeList); this.mCtx = mCtx; this.listLayoutRes = listLayoutRes; this.employeeList = employeeList; this.mDatabase = mDatabase; } @NonNull @Override public View getView(int position, @Nullable View convertView, @NonNull ViewGroup parent) { LayoutInflater inflater = LayoutInflater.from(mCtx); View view = inflater.inflate(listLayoutRes, null); //getting employee of the specified position Employee employee = employeeList.get(position); //getting views TextView textViewName = view.findViewById(R.id.textViewName); TextView textViewDept = view.findViewById(R.id.textViewDepartment); TextView textViewSalary = view.findViewById(R.id.textViewSalary); TextView textViewJoiningDate = view.findViewById(R.id.textViewJoiningDate); //adding data to views textViewName.setText(employee.getName()); textViewDept.setText(employee.getDept()); textViewSalary.setText(String.valueOf(employee.getSalary())); textViewJoiningDate.setText(employee.getJoiningDate()); //we will use these buttons later for update and delete operation Button buttonDelete = view.findViewById(R.id.buttonDeleteEmployee); Button buttonEdit = view.findViewById(R.id.buttonEditEmployee); return view; } } |
- Now we can fetch the employees to display them in the ListView.
Retrieving and Displaying in ListView
- Come inside EmployeeActivity.java and write the following code.
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 | package net.simplifiedlearning.sqlitecrudexample; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.widget.ListView; import java.util.ArrayList; import java.util.List; public class EmployeeActivity extends AppCompatActivity { List<Employee> employeeList; SQLiteDatabase mDatabase; ListView listViewEmployees; EmployeeAdapter adapter; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_employee); listViewEmployees = (ListView) findViewById(R.id.listViewEmployees); employeeList = new ArrayList<>(); //opening the database mDatabase = openOrCreateDatabase(MainActivity.DATABASE_NAME, MODE_PRIVATE, null); //this method will display the employees in the list showEmployeesFromDatabase(); } private void showEmployeesFromDatabase() { //we used rawQuery(sql, selectionargs) for fetching all the employees Cursor cursorEmployees = mDatabase.rawQuery("SELECT * FROM employees", null); //if the cursor has some data if (cursorEmployees.moveToFirst()) { //looping through all the records do { //pushing each record in the employee list employeeList.add(new Employee( cursorEmployees.getInt(0), cursorEmployees.getString(1), cursorEmployees.getString(2), cursorEmployees.getString(3), cursorEmployees.getDouble(4) )); } while (cursorEmployees.moveToNext()); } //closing the cursor cursorEmployees.close(); //creating the adapter object adapter = new EmployeeAdapter(this, R.layout.list_layout_employee, employeeList); //adding the adapter to listview listViewEmployees.setAdapter(adapter); } } |
- Now try running the application to see the Read Operation.
Updating an Employee
- We have done with Create and Read, now lets do the Update Operation. We will perform it inside the EmployeeAdapter class.
- For the Update operation we need two new methods inside the EmployeeAdapter.java. One is to update the employee and the second one is to reload the employee from database to show updated information.
- So inside the class you need to make below given two methods named updateEmployee() and reloadEmployeesFromDatabase() method.
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 | private void updateEmployee(final Employee employee) { final AlertDialog.Builder builder = new AlertDialog.Builder(mCtx); LayoutInflater inflater = LayoutInflater.from(mCtx); View view = inflater.inflate(R.layout.dialog_update_employee, null); builder.setView(view); final EditText editTextName = view.findViewById(R.id.editTextName); final EditText editTextSalary = view.findViewById(R.id.editTextSalary); final Spinner spinnerDepartment = view.findViewById(R.id.spinnerDepartment); editTextName.setText(employee.getName()); editTextSalary.setText(String.valueOf(employee.getSalary())); final AlertDialog dialog = builder.create(); dialog.show(); view.findViewById(R.id.buttonUpdateEmployee).setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { String name = editTextName.getText().toString().trim(); String salary = editTextSalary.getText().toString().trim(); String dept = spinnerDepartment.getSelectedItem().toString(); if (name.isEmpty()) { editTextName.setError("Name can't be blank"); editTextName.requestFocus(); return; } if (salary.isEmpty()) { editTextSalary.setError("Salary can't be blank"); editTextSalary.requestFocus(); return; } String sql = "UPDATE employees \n" + "SET name = ?, \n" + "department = ?, \n" + "salary = ? \n" + "WHERE id = ?;\n"; mDatabase.execSQL(sql, new String[]{name, dept, salary, String.valueOf(employee.getId())}); Toast.makeText(mCtx, "Employee Updated", Toast.LENGTH_SHORT).show(); reloadEmployeesFromDatabase(); dialog.dismiss(); } }); } private void reloadEmployeesFromDatabase() { Cursor cursorEmployees = mDatabase.rawQuery("SELECT * FROM employees", null); if (cursorEmployees.moveToFirst()) { employeeList.clear(); do { employeeList.add(new Employee( cursorEmployees.getInt(0), cursorEmployees.getString(1), cursorEmployees.getString(2), cursorEmployees.getString(3), cursorEmployees.getDouble(4) )); } while (cursorEmployees.moveToNext()); } cursorEmployees.close(); notifyDataSetChanged(); } |
- The update operation is same as the insert operation, we changed the query only to update from insert. If you are having any confusion understanding the codes please comment and I will help you.
- Now we need to modify the getView() method of EmployeeAdapter as below.
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 | public View getView(int position, @Nullable View convertView, @NonNull ViewGroup parent) { LayoutInflater inflater = LayoutInflater.from(mCtx); View view = inflater.inflate(listLayoutRes, null); final Employee employee = employeeList.get(position); TextView textViewName = view.findViewById(R.id.textViewName); TextView textViewDept = view.findViewById(R.id.textViewDepartment); TextView textViewSalary = view.findViewById(R.id.textViewSalary); TextView textViewJoiningDate = view.findViewById(R.id.textViewJoiningDate); textViewName.setText(employee.getName()); textViewDept.setText(employee.getDept()); textViewSalary.setText(String.valueOf(employee.getSalary())); textViewJoiningDate.setText(employee.getJoiningDate()); Button buttonDelete = view.findViewById(R.id.buttonDeleteEmployee); Button buttonEdit = view.findViewById(R.id.buttonEditEmployee); //adding a clicklistener to button buttonEdit.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { updateEmployee(employee); } }); return view; } |
- Now you can test the Update Operation.
Deleting an Employee
- We have done with Create, Read and Update. Now the last operation is the Delete Operation. So lets do this to complete the CRUD for this tutorial.
- We will do the deletion also in the EmployeeAdapter class. The operation is same we just need to change the SQL String to perform delete operation the rest part is exactly the same.
- So to make the delete operation again modify the getView() of EmployeeAdapter as below.
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 | @NonNull @Override public View getView(int position, @Nullable View convertView, @NonNull ViewGroup parent) { LayoutInflater inflater = LayoutInflater.from(mCtx); View view = inflater.inflate(listLayoutRes, null); final Employee employee = employeeList.get(position); TextView textViewName = view.findViewById(R.id.textViewName); TextView textViewDept = view.findViewById(R.id.textViewDepartment); TextView textViewSalary = view.findViewById(R.id.textViewSalary); TextView textViewJoiningDate = view.findViewById(R.id.textViewJoiningDate); textViewName.setText(employee.getName()); textViewDept.setText(employee.getDept()); textViewSalary.setText(String.valueOf(employee.getSalary())); textViewJoiningDate.setText(employee.getJoiningDate()); Button buttonDelete = view.findViewById(R.id.buttonDeleteEmployee); Button buttonEdit = view.findViewById(R.id.buttonEditEmployee); //adding a clicklistener to button buttonEdit.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { updateEmployee(employee); } }); //the delete operation buttonDelete.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { AlertDialog.Builder builder = new AlertDialog.Builder(mCtx); builder.setTitle("Are you sure?"); builder.setPositiveButton("Yes", new DialogInterface.OnClickListener() { @Override public void onClick(DialogInterface dialogInterface, int i) { String sql = "DELETE FROM employees WHERE id = ?"; mDatabase.execSQL(sql, new Integer[]{employee.getId()}); reloadEmployeesFromDatabase(); } }); builder.setNegativeButton("Cancel", new DialogInterface.OnClickListener() { @Override public void onClick(DialogInterface dialogInterface, int i) { } }); AlertDialog dialog = builder.create(); dialog.show(); } }); return view; } |
- So the operation is complete and now you can test all the operation in your application and you should see something like this.
Android SQLite Database Example Source Code
- If you are facing any troubles building, the application then don’t worry here is my source code for you.
Android SQLite Database Example Source Code Download
So that’s all for this Android SQLite Database Example friends. I hope you liked it. For any confusions or questions just leave your comments below. Please write your real name in the comment box as I don’t approve comments with spam names.
You can share this post to help us. Thank You 🙂
Hi, my name is Belal Khan and I am a Google Developers Expert (GDE) for Android. The passion of teaching made me create this blog. If you are an Android Developer, or you are learning about Android Development, then I can help you a lot with Simplified Coding.
Your way of guiding is amazing. I really learnt making db so easily. I tried many sites but did not understand so quickly. One question, how to save values for checkboxes and radio buttons in db. If you have other such tutorial pls advice. Otherwise request you to guide.
Hi, your tutorial is great. but i have a doubt, can i know what is the coding you wrote for your execSQL in your DatabaseHelper? If i had that code then my delete button might work.
Just awesome tutorial . I have no words to appreciate your work. I am getting an error in my log cat can you please please sort out this
Nice and clear.
Thank you very much
Very good and easy to understand ……
Please also create CRUD tutorial using Volley library…..
sir can u tell where to paste ” private void createEmployeeTable() ” function in code …or pls send full code on my mail
You can download the full source code I have given the link in the post
Hello Belal,
I have subscribed your channel but I am not able to download the code.. can you please send the code to deepakbellale@gmail.com
Regards,
Deepak
the link is not accessible
and on thing where createEmployeeTable() method called
Link is working, check again. And call the method inside onCreate().
sir Belal khan, i hope you are doing well i have requested on for the access please give the source code i need it becasue the i have tired it by my self but when i run my project it crashes so i need source code please.
Email:humasjan26@gmail.com
Belal Bro .
Nice work…….
sir one question if we want to add duplicate data and when we click add button the show data is duplicate how to implement this?
Hi,
After updating the control goes to reloadDataFromDatabase(), from this the control should go to getView() if I am correct. But there is no call to that method. Does the control defaultly goes to getView()? Please explain the flow and what is the use of notifyDataSetChanged()?
Thank you.
I am getting this error: SQLiteException: no such table
the line of code –
mDatabase.execSQL(insertSQL, new String[]{name, dept, joiningDate, salary});
I hope their is error in creating table.
Please suggest to fix this error.
No such table means.. in the table where you are trying to insert the value not exist.. try downloading my source code.. Or you can watch the video of this tutorial in youtube for more clarification.
Thank you. The error was fixed. Great work
can you upload another example with multiple table?
looks fine
Thanks a lot
Hi sir,
when i click the button add Employee ,i got the following error:Application has stopped
how can i fix it pleaase ?
Please check the error on the logcat.
I get the following error:
Error:(57, 19) error: constructor EmployeeAdapter in class EmployeeAdapter cannot be applied to given types;
required: Context,int,List,SQLiteDatabase
found: EmployeeActivity,int,List
reason: actual and formal argument lists differ in length
add mDatabase to the parameters like below:
adapter = new EmployeeAdapter(this, R.layout.list_layout_employee, employeeList,mDatabase);
Hi, I would like to create Depertment table. Two tables (employee & depertment). How to use one to many relationship (PK,FK) between two tables? One Depart have Many Employees. And then, if users delete depart, also delete relative employee. How to do that? 🙁
coming error on error: SQLiteException: no such table
the line of code –
mDatabase.execSQL(insertSQL, new String[]{name, dept, joiningDate, salary});
this line please send me link of source code… where to i get solve this
…..
Hello sir,your way of guiding is good.But sir i have some problem in my code,i taken fragment instead of EmployeeActivity that was in ur code .i got error in this line,
adapter=new CustomPatientListView(this,R.layout.listview_patient,patientList));
In the word of “this” i got error ,so what i have to write in the place “this”.
please give me solution sir…
Very straightforward tutorial.
Thanks for investing time.
Can you please tell which tool you are using to create videos.
Hello,
thanks for your tutorials. They are quite easy to understand and it is a huge job.
I am new in Android and Java and I spent months to learn about this.
Sorry, but I am a little bit upset reading that you only showing basic queries to access SQLite. I understand this is a first approach but I would be happier if you had mentioned other way to to query Android database (as ContentProvider for instance).
Just to say, when I began on Android I spent a lot of time learning Android SQLite basics until I discovered Content providers that changed my life :-)). You do not need to build CRUD for each table, just have to add it to the content provider. Content providers are widely used in android.
Hope this tutorial will get a bother with ContentProvider!
Thanks.
if we have external db what modifications have to do?
Nice tutor ! Thanks
Bro,nice tuts.Where can i download the source code from?
Bro my add button is not working. whenever I press add, it, unfortunately, stopped the app…
Thanks for the this post sir… I have a problem to display the content when i click on view employee it stopped working …how to solve it
not null constarint failed error while adding the emplyee
Good work. Thank you. Why not display the query result as a simple string? and show that in a simple EditView? Will it work? I will try your code and let you know. Thanks again.
hope someday i will master these concepts. Thank you so much for helping people like me who are looking for help. It was a real help. Thank you again.
Best tutorial! Thank you very much for sharing here!
i love it thank you …………..
please can you tell me how i create a connection between my wamp database and sdk?
code is not working, crashing
I’m unable to download your source code.
Can you plzz send it to my Email: bharatvora814@gmail.com
The best explanation of SQLite. AWesome 🙂
Unlock the link by sharing the post, the link is working
please help for download ;its not coming
sir,please help me for down loading ;
Application crashing. Code not working & not getting the full source code also.
Hello Sir,
Thank you for teaching me a great lesson freely, without a penny.
I have grasped the whole thing very well, I able to create the DB and start the app and the GUIs have come well. However, when I tried to insert data the app crushes. I tried a lot of means. But could not resolve the issue. Do you have some advice for me, sir?
Thank You
Check this video please
https://www.youtube.com/watch?v=FwUV1R8nKXk&t=7s
package com.sliit.model2018;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.view.View;
public class DBHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = “userInfo”;
private static final String createTable = “CREATE TABLE ” + UserProfile.Users.TABLE_NAME + ” (” + UserProfile.Users._ID + ” INTEGER PRIMARY KEY,” + UserProfile.Users.COLUMN_NAME_USERNAME + ” TEXT NOT NULL ,” + UserProfile.Users.COLUMN_NAME_DOB + ” TEXT ,” + UserProfile.Users.COLUMN_NAME_PWD + ” TEXT ,” + UserProfile.Users.COLUMN_NAME_GENDER + ” TEXT )”;
private static final String deleteTable = “DROP TABLE IF EXISTS ” + UserProfile.Users.TABLE_NAME;
public DBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
SQLiteDatabase db = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(createTable);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(deleteTable);
onCreate(db);
}
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
onUpgrade(db, oldVersion, newVersion);
}
public Boolean addInfo(String uName, String dob, String pwd, String gender) {
long newRowId = 0;
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(UserProfile.Users.COLUMN_NAME_USERNAME, uName);
values.put(UserProfile.Users.COLUMN_NAME_DOB, dob);
values.put(UserProfile.Users.COLUMN_NAME_PWD, pwd);
values.put(UserProfile.Users.COLUMN_NAME_GENDER, gender);
newRowId = db.insert(UserProfile.Users.TABLE_NAME, null, values);
if (newRowId == -1) {
return false;
} else {
return true;
}
}
public Boolean updateInfor( String uName, String dob, String pwd, String gender) {
ContentValues values = new ContentValues();
SQLiteDatabase db = this.getWritableDatabase();
values.put(UserProfile.Users.COLUMN_NAME_USERNAME, uName);
values.put(UserProfile.Users.COLUMN_NAME_DOB, dob);
values.put(UserProfile.Users.COLUMN_NAME_PWD, pwd);
values.put(UserProfile.Users.COLUMN_NAME_GENDER, gender);
String condition = UserProfile.Users.COLUMN_NAME_USERNAME + ” LIKE ?”;
String[] condition_value = {uName};
int result = db.update(UserProfile.Users.TABLE_NAME, values, condition, condition_value);
if (result == -1) {
return false;
} else {
return true;
}
}
public Cursor readAllInfor() {
SQLiteDatabase db = getWritableDatabase();
Cursor data = db.rawQuery(“SELECT * FROM ” + UserProfile.Users.TABLE_NAME, null);
return data;
}
public Cursor readAllInfor(String uName) {
SQLiteDatabase db = getWritableDatabase();
Cursor data = db.rawQuery(“SELECT * FROM ” + UserProfile.Users.TABLE_NAME + ” WHERE ” + UserProfile.Users.COLUMN_NAME_USERNAME+ ” = ‘” + uName+”‘”, null);
return data;
}
public Integer deleteInfo(String uName) {
SQLiteDatabase db = getWritableDatabase();
int result = db.delete(UserProfile.Users.TABLE_NAME, UserProfile.Users.COLUMN_NAME_USERNAME + ” LIKE ?”, new String[]{uName});
return result;
}
}
………………..profile management………
package com.sliit.model2018;
import android.content.Context;
import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.Toast;
public class ProfileManagement extends AppCompatActivity {
private Button registerBtn;
private EditText getUName;
private EditText getDob;
private EditText getPwd;
private int compareId;
private RadioGroup getGender;
private RadioButton radioBtnGender;
private DBHandler dbHandler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_profile_management);
registerBtn = (Button) findViewById(R.id.register_btn_two);
dbHandler = new DBHandler(this);
registerBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getUName = (EditText) findViewById(R.id.user_Name_txt);
getDob = (EditText) findViewById(R.id.dob_txt);
getPwd = (EditText) findViewById(R.id.pwd_txt);
getGender = (RadioGroup) findViewById(R.id.gender_radio_group);
compareId =getGender.getCheckedRadioButtonId();
radioBtnGender = (RadioButton) findViewById(compareId);
Boolean id = dbHandler.addInfo(getUName.getText().toString(),getDob.getText().toString(),getPwd.getText().toString(),radioBtnGender.getText().toString());
// Boolean id = dbHandler.addInfo(“Tharindu”,”94/09/30″,”Secret”,”male”);
if(id==false)
{
Toast.makeText(ProfileManagement.this, “The Database is empty :(.”, Toast.LENGTH_LONG).show();
} else
{
Toast.makeText(ProfileManagement.this, “Successfully added..!”, Toast.LENGTH_LONG).show();
}
}
});
}
}
……………………………Edit profile…………………………..
package com.sliit.model2018;
import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.TextView;
import android.widget.Toast;
public class EditProfile extends AppCompatActivity {
Button search;
Button edit;
Button delete;
EditText uName;
EditText dob;
EditText pwd;
RadioButton male;
RadioButton female;
DBHandler db ;
Cursor data;
String x;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_edit_profile);
uName = (EditText) findViewById(R.id.search_u_name);
search = (Button) findViewById(R.id.search_btn);
dob = (EditText) findViewById(R.id.get_dob);
pwd = (EditText) findViewById(R.id.get_pwd);
male = (RadioButton) findViewById(R.id.edit_male_radio_btn);
female = (RadioButton) findViewById(R.id.edit_female_radio_btn);
edit = (Button) findViewById(R.id.edit_btn);
delete = (Button) findViewById(R.id.delete_btn);
db = new DBHandler(this);
search.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
data = db.readAllInfor(uName.getText().toString());
while (data.moveToNext()) {
dob.setText(data.getString(2));
pwd.setText(data.getString(3));
//x = data.getString(4);
if(“male”.equals(data.getString(4)) || “Male”.equals(data.getString(4))) {
male.setChecked(true);
}else {
female.setChecked(true);
}
}
}
});
edit.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Boolean result = db.updateInfor(uName.getText().toString(),dob.getText().toString(),pwd.getText().toString(),x);
if(result==false)
{
Toast.makeText(EditProfile.this, “Update was failed..! :(.”, Toast.LENGTH_LONG).show();
} else
{
Toast.makeText(EditProfile.this, “Successfully updated..!”, Toast.LENGTH_LONG).show();
}
}
});
delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
int val = db.deleteInfo(uName.getText().toString());
if(val != 1)
{
Toast.makeText(EditProfile.this, “Not updated..! :(“, Toast.LENGTH_LONG).show();
} else
{
Toast.makeText(EditProfile.this, “Successfully updated..!”, Toast.LENGTH_LONG).show();
}
}
});
}
}
…………………..home……………………..
package com.sliit.model2018;
import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
public class Home extends AppCompatActivity {
private Button updateProfileBtn;
private Button registerBtn;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_home);
registerBtn = (Button) findViewById(R.id.register_btn);
registerBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Intent intent = new Intent(Home.this,ProfileManagement.class);
startActivity(intent);
}
});
updateProfileBtn = (Button) findViewById(R.id.update_profile_btn);
updateProfileBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Intent intent = new Intent(Home.this,EditProfile.class);
startActivity(intent);
}
});
}
}
Bro i am not able to find below method anywhere in the code. Can you/anyone tell me where it is.
private void createEmployeeTable() {
mDatabase.execSQL(
“CREATE TABLE IF NOT EXISTS employees (\n” +
” id int NOT NULL CONSTRAINT employees_pk PRIMARY KEY,\n” +
” name varchar(200) NOT NULL,\n” +
” department varchar(200) NOT NULL,\n” +
” joiningdate datetime NOT NULL,\n” +
” salary double NOT NULL\n” +
“);”
);
}
got it!!!! thanks anyways. downloaded the source code.
Hey Billal i would like to give you a suggestion. But somewhere i think this will be applicable for only small tutorials not for big or i can say long line coding projects. Already sorry if i am wrong.
1. Don’t write unnecessary code while explaining the code ex. imported packages, variable initialisation, findviewbyid and so on
2. Write the “Complete Last and best final code” after finishing the all the explanations. This would be in sequence as first the .java file then all the .xml files associated with it. You can post code before the download link.
Hope it will be useful and/or helpful to you and if not please ignore my suggestion and forgive me if am wrong. The thought of giving suggestion came in my mind because of the issue i faced.
Sorry for spelling mistake in your name Belal.
Hello sir
In my case whenever I am call from sqlite method in work good in emulator.
But whenever I am build in my new phone method get suppresed or never used showing.
Please help me out 😭
Thanks for this great tutorial.
I have a question, though, why the database seems to not have any created table?
I mean, I copy the file and when I open it in the sqlitemanager, it will not show any data.
Thanks in advance for your time.
I’m unable to download your source code.
Can you plzz send it to my Email: jabbary2014@yahoo.com
with my heart thanks
Click the download link,
You will see the download page where link is locked, now follow these steps
#1 Click on Subscribe Button, it will open a new tab, in this tab subscribe if you are not already subscribed. If you are already subscribed ignore this tab and go back to the download page. Make sure you don’t open the download page again and go back to the already opened page.
#2 Wait for few seconds and the download link will unlock.
employee id not null, but employee id not found on insert statement, is query correct?
Hi Sir,
Could you please explain SQLite database tutorial in Kotlin language. I am new to this. Please….
Can you create a video on saving data from recylerview (used volley)to Sqlite
Can you please do this with kotlin or is there any method to do same this program without sql. please reply.
Thanks in advance
Check this playlist: https://www.youtube.com/watch?v=9iF2qEF28Do&list=PLk7v1Z2rk4hg_ywZffPgRTmJoy2XWs02d
I have issue in delete record, All records are deleting smoothly but when it comes to last record it deleted successfully but record still visible unlit I go to other activity and come back. Can you please help me ??
It may be a bug in my code only as I have not tested this situation. But I guess you can easily fix it, check in the last item we are updating the list or not. (If I get sometime I will update the code with the fix.)
hi, Mr. Belal, does this code still applied to the latest android studio? so that I can edit this, thanks.
Yes ofcourse.
Thank you very much you help me to learn easily.
I am new in Android and Java.
So, can you help me for more basic concepts on this?
View EMployee not working.. it crash after i click it
check logs to see the exact reason of the crash
hello sir i am facing and error with this code and i am the beginner so please help me to get out of this errors and the code not working properly and i also download your code to execute the whole program but i got failed to execute so give me the solution
Hi ,
Dear Belal Khan
Greatly appreciating your commitment and interest on share the knowledge to others by providing the tutorial . Very helpful for beginners and working without any error .
Thanks and love from Kerala,India
Asalam o alikum sir i need source code of this project sir i am requesting for the unlock please allow me access to the soucre code as i am geeting so many errors by it by my self please sir i need this today