SQLiteOpenHelper and Database Inspector in Android
Let's dive into using SQLite and Database Inspector for your next Android app.
Join the DZone community and get the full member experience.
Join For FreeWhat Is SQLite?
SQLite is an open-source relational database, similar to MySQL. Android includes a SQLite library in its standard library that implements a serverless, zero-configuration, and transactional SQL database engine. SQLite does not run on a database server, but stores all its data in simple files. A complete SQLite database with many tables, triggers, indices, and views is contained in a single disk file. SQLite is built into all mobile phones and most computers and comes bundled inside. It carries lightweight data and does not require any administration or setup procedure of the database.
The package android.database.sqlite.SQLiteDatabase consists of all the APIs to perform CRUD operations from our android application.
SQLiteOpenHelper
SQLiteOpenHelper is an in-built class of android.database.sqlite.SQLiteDatabase package. It is a helper class to manage SQLite database creation and version management. The helper class manages the creation of the database, handling database manipulations, and also the version management. We need to create a subclass extending from SQLiteOpenHelper class for database manipulations. In this class, we will implement two overridden methods onCreate( ) and onUpgrade( ). These classes take care of opening the database if it exists, creating it if it does not, and upgrading it as necessary.
onCreate( ) method
The onCreate( ) is called when the database is created for the first time. It is called only once throughout the entire application lifecycle. It will be called whenever there is a first call to getReadableDatabase( ) or getWritableDatabase( ) function. These functions are available in the super SQLiteOpenHelper class.
The below statement instantiates an object of the SQLiteOpenHelper class and calls its constructor.
xxxxxxxxxx
DatabaseHelper dbh= new DatabaseHelper(getApplicationContext());
xxxxxxxxxx
public DatabaseHelper(Context context) {
super(context,dbName,null,version);
context=this.context;
}
SQLiteOpenHelper class call the onCreate( ) method after creating database and instantiate SQLiteDatabase object. The onCreate( ) method is called only once when the database is created for the first time.
xxxxxxxxxx
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(CREATE_TABLE);
} catch (Exception e) {
}
}
onUpgrade( ) method
The onUpgrade( ) method is called when the database needs to be upgraded. It is called when the database file already exists, and we want to upgrade its version.
xxxxxxxxxx
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_TABLE);
onCreate(db);
}
Now we will create a simple Android application to demonstrate the SQLiteOPneHelper class. This application inserts employee records into a table 'Empdata' in a SQLite database 'Company.db' using the SQLiteOpenHelper class.
We have a very simple UI designed as below in the activity_main.xml
MainActivity.java
xxxxxxxxxx
package www.sqliteopenhelper.sqliteopenhelper;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity {
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
EditText edtEmpName = findViewById(R.id.edtName);
EditText edtDesig = findViewById(R.id.edtDesig);
EditText edtSalary = findViewById(R.id.edtSalary);
Button btnSave = findViewById(R.id.btnSave);
DatabaseHelper dbh= new DatabaseHelper(getApplicationContext());
btnSave.setOnClickListener(new View.OnClickListener() {
public void onClick(View view) {
Employee objEmp = new Employee(edtEmpName.getText().toString(),edtDesig.getText().toString(),Integer.parseInt(edtSalary.getText().toString()));
if(dbh.InsertEmployee(objEmp))
Toast.makeText(getApplicationContext(),"Record inserted successfully",Toast.LENGTH_LONG).show();
else
Toast.makeText(getApplicationContext(),"Record not inserted",Toast.LENGTH_LONG).show();
}
});
}
}
Employee.java
xxxxxxxxxx
package www.sqliteopenhelper.sqliteopenhelper;
public class Employee {
private String employeeName;
private String employeeDesig;
private int employeeSalary;
public Employee() {
}
public Employee(String employeeName, String employeeDesig, int employeeSalary) {
this.employeeName = employeeName;
this.employeeDesig = employeeDesig;
this.employeeSalary = employeeSalary;
}
public String getEmployeeName() {
return employeeName;
}
public String getEmployeeDesig() {
return employeeDesig;
}
public int getEmployeeSalary() {
return employeeSalary;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public void setEmployeeDesig(String employeeDesig) {
this.employeeDesig = employeeDesig;
}
public void setEmployeeSalary(int employeeSalary) {
this.employeeSalary = employeeSalary;
}
}
DatabaseHelper.java
package www.sqliteopenhelper.sqliteopenhelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
public static final int version = 1;
public static String dbName="Company.db";
public static final String TABLE_NAME ="Empdata";
public static final String COL1 = "id";
public static final String COL2 = "name";
public static final String COL3 = "designation";
public static final String COL4 = "salary";
private static final String CREATE_TABLE="create table if not exists "+ TABLE_NAME + "(" + COL1 + " INTEGER PRIMARY KEY AUTOINCREMENT,"+COL2+" TEXT NOT NULL,"
+ COL3 + " TEXT, " +COL4 + " INTEGER);";
private static final String DROP_TABLE = "DROP TABLE IF EXISTS "+ TABLE_NAME;
private Context context;
public DatabaseHelper(Context context) {
super(context,dbName,null,version);
context=this.context;
}
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(CREATE_TABLE);
} catch (Exception e) {
}
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_TABLE);
onCreate(db);
}
public boolean InsertEmployee(Employee objEmp)
{
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(COL2,objEmp.getEmployeeName());
cv.put(COL3,objEmp.getEmployeeDesig());
cv.put(COL4,objEmp.getEmployeeSalary());
long result = db.insert(TABLE_NAME,null,cv);
if(result == -1)
return false;
else
return true;
}
}
We can see that the DatabaseHelper class is extended from SQLiteOpenHelper. The SQLiteOpenHelper class has different in-built methods for inserting, updating, reading, and deleting records.
After the database has been created we need to create and update fields. We can use a ContentValues object, which stores keys and values. ContentValues is a name-value pair, used to insert or update values into database tables. ContentValues object will be passed to SQLiteDataBase objects insert( ) and update( ) functions.
Database Inspector in Android Studio
Database Inspector allows us to inspect, query, and modify our app's databases while our app is running. The database inspector is available in Android Studio 4.1 and higher. This is especially useful for database debugging. The Database Inspector works with plain SQLite and with libraries built on top of SQLite, such as Room. The Database Inspector only works with the SQLite library included in the Android operating system on API level 26 and higher.
To open a database in the Database Inspector, do the following:
- Run the app on an emulator or connected device running API level 26 or higher.
- Select View > Tool Windows > Database Inspector from the menu bar.
- Select the running app process from the dropdown menu.
- The databases in the currently running app appear in the Databases pane. Expand the node for the database that we need to inspect.
Note: A known issue related to the Android 11 emulator causes apps to crash when connecting to the DB Inspector. To fix the issue, follow these steps.
Opinions expressed by DZone contributors are their own.
Comments