Hey guys, In the last post we learned about performing the basic CRUD operation in SQLite Database. But the method we used in the previous tutorial, is not recommended. Instead, we should use SQLiteOpenHelper class. And that is why we have this SQLiteOpenHelper Tutorial. We will do the same thing that we did in the last post, but with SQLiteOpenHelper class.
If you directly came to this post, then it is highly recommended that you should go to the previous post first, as here I am going to work on the last project, so before you start, you need to get the last project. You can visit the previous post from the link given below.
Android SQLite Database Example – CRUD Operation in SQLite
Table of Contents
What is SQLiteOpenHelper?
It is a class found inside android.database.sqlite package. It is a helper class that helps in creating the database, handling the operations and also the version management.
To use sqliteopenhelper, we will create a class, and then we will extend SQLiteOpenHelper inside the class that we created.
Creating and Managing Database with SQLiteOpenHelper
- So open the project we created in the last tutorial.
- Now create a class, you can name it anything, and I have given the name as DatabaseManager.
- Inside this class we need to override onCreate() and onUpgrade() method. Inside onCreate() we will create the tables required and inside onUpgrade() we can upgrade the database (like we can add more tables).
- We will also define the methods for all the operation that we want to be performed in our database.
- Below is my DatabaseManager class having all the methods to perform CRUD. The database is same as it was in the previous tutorial.
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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | package net.simplifiedlearning.sqlitecrudexample; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by Belal on 10/20/2017. */ //The class is extending SQLiteOpenHelper public class DatabaseManager extends SQLiteOpenHelper { /* * This time we will not be using the hardcoded string values * Instead here we are defining all the Strings that is required for our database * for example databasename, table name and column names. * */ private static final String DATABASE_NAME = "EmployeesDatabase"; private static final int DATABASE_VERSION = 1; private static final String TABLE_NAME = "employees"; private static final String COLUMN_ID = "id"; private static final String COLUMN_NAME = "name"; private static final String COLUMN_DEPT = "department"; private static final String COLUMN_JOIN_DATE = "joiningdate"; private static final String COLUMN_SALARY = "salary"; /* * We need to call the super i.e. parent class constructur * And we need to pass 4 parameters * 1. Context context -> It is the context object we will get it from the activity while creating the instance * 2. String databasename -> It is the name of the database and here we are passing the constant that we already defined * 3. CursorFactory cursorFactory -> If we want a cursor to be initialized on the creation we can use cursor factory, it is optionall and that is why we passed null here * 4. int version -> It is an int defining our database version * */ DatabaseManager(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { /* * The query to create our table * It is same as we had in the previous post * The only difference here is we have changed the * hardcoded string values with String Variables * */ String sql = "CREATE TABLE " + TABLE_NAME + " (\n" + " " + COLUMN_ID + " INTEGER NOT NULL CONSTRAINT employees_pk PRIMARY KEY AUTOINCREMENT,\n" + " " + COLUMN_NAME + " varchar(200) NOT NULL,\n" + " " + COLUMN_DEPT + " varchar(200) NOT NULL,\n" + " " + COLUMN_JOIN_DATE + " datetime NOT NULL,\n" + " " + COLUMN_SALARY + " double NOT NULL\n" + ");"; /* * Executing the string to create the table * */ sqLiteDatabase.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { /* * We are doing nothing here * Just dropping and creating the table * */ String sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";"; sqLiteDatabase.execSQL(sql); onCreate(sqLiteDatabase); } /* * CREATE OPERATION * ==================== * This is the first operation of the CRUD. * This method will create a new employee in the table * Method is taking all the parameters required * * Operation is very simple, we just need a content value objects * Inside this object we will put everything that we want to insert. * So each value will take the column name and the value that is to inserted * for the column name we are using the String variables that we defined already * And that is why we converted the hardcoded string to variables * * Once we have the contentValues object with all the values required * We will call the method getWritableDatabase() and it will return us the SQLiteDatabase object and we can write on the database using it. * * With this object we will call the insert method it takes 3 parameters * 1. String -> The table name where the value is to be inserted * 2. String -> The default values of null columns, it is null here as we don't have any default values * 3. ContentValues -> The values that is to be inserted * * insert() will return the inserted row id, if there is some error inserting the row * it will return -1 * * So here we are returning != -1, it will be true of record is inserted and false if not inserted * */ boolean addEmployee(String name, String dept, String joiningdate, double salary) { ContentValues contentValues = new ContentValues(); contentValues.put(COLUMN_NAME, name); contentValues.put(COLUMN_DEPT, dept); contentValues.put(COLUMN_JOIN_DATE, joiningdate); contentValues.put(COLUMN_SALARY, salary); SQLiteDatabase db = getWritableDatabase(); return db.insert(TABLE_NAME, null, contentValues) != -1; } /* * READ OPERATION * ================= * Here we are reading values from the database * First we called the getReadableDatabase() method it will return us the SQLiteDatabase instance * but using it we can only perform the read operations. * * We are running rawQuery() method by passing the select query. * rawQuery takes two parameters * 1. The query * 2. String[] -> Arguments that is to be binded -> We use it when we have a where clause in our query to bind the where value * * rawQuery returns a Cursor object having all the data fetched from database * */ Cursor getAllEmployees() { SQLiteDatabase db = getReadableDatabase(); return db.rawQuery("SELECT * FROM " + TABLE_NAME, null); } /* * UPDATE OPERATION * ================== * Here we are performing the update operation. The proecess is same as the Create operation. * We are first getting a database instance using getWritableDatabase() method as the operation we need to perform is a write operation * Then we have the contentvalues object with the new values * * to update the row we use update() method. It takes 4 parameters * 1. String -> It is the table name * 2. ContentValues -> The new values * 3. String -> Here we pass the column name = ?, the column we want to use for putting the where clause * 4. String[] -> The values that is to be binded with the where clause * */ boolean updateEmployee(int id, String name, String dept, double salary) { SQLiteDatabase db = getWritableDatabase(); ContentValues contentValues = new ContentValues(); contentValues.put(COLUMN_NAME, name); contentValues.put(COLUMN_DEPT, dept); contentValues.put(COLUMN_SALARY, salary); return db.update(TABLE_NAME, contentValues, COLUMN_ID + "=?", new String[]{String.valueOf(id)}) == 1; } /* * DELETE OPERATION * ====================== * * This is the last delete operation. To delete again we need a writable database using getWritableDatabase() * Then we will call the delete method. It takes 3 parameters * 1. String -> Table name * 2. String -> The where clause passed as columnname = ? * 3. String[] -> The values to be binded on the where clause * */ boolean deleteEmployee(int id) { SQLiteDatabase db = getWritableDatabase(); return db.delete(TABLE_NAME, COLUMN_ID + "=?", new String[]{String.valueOf(id)}) == 1; } } |
Modifying the Last Project
- In the last tutorial we had the following classes.
- Employee.java -> The model class to store employee. We don’t need any changes here.
- EmployeeActivity .java-> The activity where we are displaying all the employees.
- EmployeeAdapter.java -> The custom list adapter for our Employees List.
- MainActivity.java -> Here we are inserting Employee to the database.
- We will modify EmployeeActivity.java, EmployeeAdapter.java and MainActivity.java.
Creating the Employee
- So let’s start with MainActivity.java. I have added the comments on the lines that is modified or added.
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 | package net.simplifiedlearning.sqlitecrudexample; import android.content.Intent; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.view.View; import android.widget.EditText; import android.widget.Spinner; import android.widget.Toast; import java.text.SimpleDateFormat; import java.util.Calendar; public class MainActivity extends AppCompatActivity implements View.OnClickListener { EditText editTextName, editTextSalary; Spinner spinnerDept; //We creating our DatabaseManager object DatabaseManager mDatabase; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); //initializing the database manager object mDatabase = new DatabaseManager(this); editTextName = (EditText) findViewById(R.id.editTextName); editTextSalary = (EditText) findViewById(R.id.editTextSalary); spinnerDept = (Spinner) findViewById(R.id.spinnerDepartment); findViewById(R.id.buttonAddEmployee).setOnClickListener(this); findViewById(R.id.textViewViewEmployees).setOnClickListener(this); } private void addEmployee() { String name = editTextName.getText().toString().trim(); String salary = editTextSalary.getText().toString().trim(); String dept = spinnerDept.getSelectedItem().toString(); if (name.isEmpty()) { editTextName.setError("Name can't be empty"); editTextName.requestFocus(); return; } if (salary.isEmpty()) { editTextSalary.setError("Salary can't be empty"); editTextSalary.requestFocus(); return; } Calendar cal = Calendar.getInstance(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss"); String joiningDate = sdf.format(cal.getTime()); //adding the employee with the DatabaseManager instance if (mDatabase.addEmployee(name, dept, joiningDate, Double.parseDouble(salary))) Toast.makeText(this, "Employee Added", Toast.LENGTH_SHORT).show(); else Toast.makeText(this, "Could not add employee", Toast.LENGTH_SHORT).show(); } @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; } } } |
Reading All Employees
- Now come to EmployeeActivity.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 | package net.simplifiedlearning.sqlitecrudexample; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.widget.ListView; import java.util.ArrayList; import java.util.List; public class EmployeeActivity extends AppCompatActivity { List<Employee> employeeList; ListView listView; //The databasemanager object DatabaseManager mDatabase; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_employee); //Instantiating the database manager object mDatabase = new DatabaseManager(this); employeeList = new ArrayList<>(); listView = (ListView) findViewById(R.id.listViewEmployees); loadEmployeesFromDatabase(); } private void loadEmployeesFromDatabase() { //we are here using the DatabaseManager instance to get all employees Cursor cursor = mDatabase.getAllEmployees(); if (cursor.moveToFirst()) { do { employeeList.add(new Employee( cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getDouble(4) )); } while (cursor.moveToNext()); //passing the databasemanager instance this time to the adapter EmployeeAdapter adapter = new EmployeeAdapter(this, R.layout.list_layout_employees, employeeList, mDatabase); listView.setAdapter(adapter); } } } |
Updating and Deleting the Employees
- Lastly come inside EmployeeAdapter.java and modify it 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 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | package net.simplifiedlearning.sqlitecrudexample; import android.content.Context; import android.content.DialogInterface; 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.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 layoutRes; List<Employee> employeeList; //the databasemanager object DatabaseManager mDatabase; //modified the constructor and we are taking the DatabaseManager instance here public EmployeeAdapter(Context mCtx, int layoutRes, List<Employee> employeeList, DatabaseManager mDatabase) { super(mCtx, layoutRes, employeeList); this.mCtx = mCtx; this.layoutRes = layoutRes; 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(layoutRes, null); TextView textViewName = view.findViewById(R.id.textViewName); TextView textViewDept = view.findViewById(R.id.textViewDepartment); TextView textViewSalary = view.findViewById(R.id.textViewSalary); TextView textViewJoinDate = view.findViewById(R.id.textViewJoiningDate); final Employee employee = employeeList.get(position); textViewName.setText(employee.getName()); textViewDept.setText(employee.getDept()); textViewSalary.setText(String.valueOf(employee.getSalary())); textViewJoinDate.setText(employee.getJoiningdate()); view.findViewById(R.id.buttonDeleteEmployee).setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { deleteEmployee(employee); } }); view.findViewById(R.id.buttonEditEmployee).setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { updateEmployee(employee); } }); return view; } private void updateEmployee(final Employee employee) { 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 AlertDialog alertDialog = builder.create(); alertDialog.show(); final EditText editTextName = view.findViewById(R.id.editTextName); final EditText editTextSalary = view.findViewById(R.id.editTextSalary); final Spinner spinner = view.findViewById(R.id.spinnerDepartment); editTextName.setText(employee.getName()); editTextSalary.setText(String.valueOf(employee.getSalary())); 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 = spinner.getSelectedItem().toString().trim(); if (name.isEmpty()) { editTextName.setError("Name can't be empty"); editTextName.requestFocus(); return; } if (salary.isEmpty()) { editTextSalary.setError("Salary can't be empty"); editTextSalary.requestFocus(); return; } //calling the update method from database manager instance if (mDatabase.updateEmployee(employee.getId(), name, dept, Double.valueOf(salary))) { Toast.makeText(mCtx, "Employee Updated", Toast.LENGTH_SHORT).show(); loadEmployeesFromDatabaseAgain(); } alertDialog.dismiss(); } }); } private void deleteEmployee(final Employee employee) { 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) { //calling the delete method from the database manager instance if (mDatabase.deleteEmployee(employee.getId())) loadEmployeesFromDatabaseAgain(); } }); builder.setNegativeButton("Cancel", new DialogInterface.OnClickListener() { @Override public void onClick(DialogInterface dialogInterface, int i) { } }); AlertDialog alertDialog = builder.create(); alertDialog.show(); } private void loadEmployeesFromDatabaseAgain() { //calling the read method from database instance Cursor cursor = mDatabase.getAllEmployees(); employeeList.clear(); if (cursor.moveToFirst()) { do { employeeList.add(new Employee( cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getDouble(4) )); } while (cursor.moveToNext()); } notifyDataSetChanged(); } } |
- That’s it now you can run your application and it will behave the same as it was in the previous tutorial.
SQLiteOpenHelper Tutorial Source Code
- If you are having troubles creating the application then don’t worry, you can download my project from the link given below.
So that’s all for this SQLiteOpenHelper Tutorial friends. Hope you liked it if you did, then please SHARE it. 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.
**Helper.kt->**
class Helper(context: Context) : SQLiteOpenHelper(context, DBNAME, null, DBVERSION) {
companion object {
private val DBNAME = "myDb"
private val DBVERSION = 1
}
–>this class will take context argument but in fragment it is showing error for “context”,”activity”,”this”.. i have tried all the things but not be able to resolve the error
**HomeFragment.kt->**
class HomeFragment : Fragment() {
companion object {
const val KEY = "data"
}
val helper = Helper(context) //getting error-> Required:Context , Found:Context?
What if i have multiple unrelated tables?
I assume that i dont want to put all crud operations on all tables in one dbhelper instance?
Would it make more sense to then create a second dbhelper?
Or to make dbhelper just responsible for maintaining the databases and opening / closing them, and make seperate classes for handeling operations on each table?
Please add ‘mDatabase.close()’ every time you are done trying to access the database. Otherwise, the same database will be appended every time you re-open your app after destroying it.
How can I check the contents or values in the tables?
Hello Belal, i wish you are fine. I can’t describe how you helped me by my project. Thank you soooooooooooooooo much and i wish you all the best.
may God bless you! this helps me a lot for my final year project!