How To Make A Database App For Android

Background

SQLite

SQLite is a relational database management system (RDBMS). If most RDBMSs such as MySQL, Oracle, etc. are standalone server processes, then SQLite is embedded because it is provided in the form of a library that is linked in applications.

Like other RDBMSs, data is accessed in a SQLite database by using Structured Query Language (SQL).

Android SQLite Java Classes

Cursor: a class provides access to the results of a database query. Its methods include:

close(): release all resources used by cursor and close it.
getCount(): returns the number of rows contained within the result set.
moveToFirst(): moves to the first row in the result set.
moveToLast(): moves to the last row in the result set.
moveToNext(): moves to the next row in the result set.
move(): moves by a specified offset from the current position in the result set.
get<type>() (such as getInt(), getDouble(), so on): returns the value of the specified <type> contained at the specified column index of the row at the current cursor position.

SQLiteDatabaseprovides the primary interface between the application code and underlying SQLite database. Its methods include:

insert(): inserts a new row into a database table.
delete(): deletes rows from a database table
query(): performs a specified database query and returns matching results via a Cursor object.
execSQL(): executes a single SQL Statement that does not return result data.
rawQuery(): executes an SQL query statement and returns matching results in the form of a Cursor object.

SQLiteOpenHelper is designed to make it easier to create and update databases. Its methods include:

onCreate(): called when the database is created for the first time.
onUpgrade(): called in the event that the application code contains a more recent database version number reference.
onOpen(): called when the database is opened.
getWritableDatabase(): opens or creates a database for reading and writing.
getReadableDatabase(): creates or opens a database for reading only.
close(): closes the database.

ContentValues allows key/value pairs to be declared consisting of table column identifiers and the values to be stored in each column. Its methods include:

put(): adds a value to the set.

Create a Database Android Application

We will create a database Android application that has a UI as follows:

Image title My application will interact with a database named StudentDB.db, which contains a single table named Student. The Student table schema will look like this:

Image title

The application will consist of an activity and a database handler class (MyDBHandler class). The database handler will be a subclass of SQLiteOpenHelper and will provide an abstract layer between the underlying SQLite database and the activity class. A third class (Student class) will need to be implemented to hold the database entry data as it is passed between the activity and the handler. My application model can be shown in the following figure:

Image title

Data Model Class

The Student class contains fields, constructors, and properties as follows:
Image title We create the Student class in the Android Studio 3.0 by selecting app > java.

Image title

Right-click the myfirstdatabase package and selecting New > Java Class.

Image title

Type Student in the Name item, maintain the default options, and click the OK button:

Image title

Adding the following lines of code for the Student class:

          public class Student {  // fields  private int studentID;  private String studentName;  // constructors  public Student() {}  public Student(int id, String studentname) {    this.studentID = id;    this.studentName = studentname;   }   // properties  public void setID(int id) {   this.studentID = id;  }  public int getID() {   return this.studentID;  }   public void setStudentName(String studentname) {   this.studentName = studentname;  }  public String getStudentName() {   return this.studentName;  } }

Data Handler Class

The database handler class is a subclass of SQLiteOpenHelper class, named MyDBHandler, as in the following figure:
Image title The MyDBHandler class contains fields, constructors, and methods, as follows:

Image title

Steps for creating the MyDBHandler class like the Student class and its code can look like this:

          public class MyDBHandler extends SQLiteOpenHelper {  //information of database  private static final int DATABASE_VERSION = 1;  private static final String DATABASE_NAME = "studentDB.db";  public static final String TABLE_NAME = "Student";  public static final String COLUMN_ID = "StudentID";  public static final String COLUMN_NAME = "StudentName";  //initialize the database  public MyDBHandler(Context context, Stringname, SQLiteDatabase.CursorFactoryfactory, intversion) {   super(context, DATABASE_NAME, factory, DATABASE_VERSION);  }  @Override  public void onCreate(SQLiteDatabase db) {}  @Override  public void onUpgrade(SQLiteDatabase db, int i, int i1) {}  public String loadHandler() {}  public void addHandler(Student student) {}  public Student findHandler(String studentname) {}  public boolean deleteHandler(int ID) {}  public boolean updateHandler(int ID, String name) {} }

We must also use import statements, as follows:

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

Initialize the Database

The database can be initialized in the constructor of the MyDBHandler class. The code of this constructor looks like this:

          public MyDBHandler(Context context, Stringname,  SQLiteDatabase.CursorFactoryfactory, intversion) {  super(context, DATABASE_NAME, factory, DATABASE_VERSION); }

Create the Student Table

The SQL statement creates a table:

          CREATE TABLE table_name(  column1 datatype,  column2 datatype,  column3 datatype,  .... );

We need to convert the SQL statement to a string and implement it by using the execSQL() method of a SQLiteDatabase object. All of these statements will be put inside the onCreate method of the handler class, as follows:

          public void onCreate(SQLiteDatabase db) {  String CREATE_TABLE = "CREATE TABLE" + TABLE_NAME + "(" + COLUMN_ID +   "INTEGER PRIMARYKEY," + COLUMN_NAME + "TEXT )";  db.execSQL(CREATE_TABLE); }

Our application can load all of the students from the database, add a new student to the database, remove a student from the database, find a student from the database and modify the information of a student from the database. Therefore, we need to add corresponding methods to the handler class.

Load Data

To load data, we use a SQL query statement:

          SELECT * FROM table_name;

The result of above SQL statement is a table. We use the rawQuery() method of a SQLiteDatabase object to implement SQL statement and display result via a Cursor object. The following code will demonstrate the loadHandler method:

          public String loadHandler() {  String result = "";  String query = "Select*FROM" + TABLE_NAME;  SQLiteDatabase db = this.getWritableDatabase();  Cursor cursor = db.rawQuery(query, null);  while (cursor.moveToNext()) {   int result_0 = cursor.getInt(0);   String result_1 = cursor.getString(1);   result += String.valueOf(result_0) + " " + result_1 +    System.getProperty("line.separator");  }  cursor.close();  db.close();  return result; }

Add a New Record

To add a new record to the database, we must use the ContentValues object with the put() method that is used to assign data to ContentsValues object and then use insert() method of SQLiteDatabase object to insert data to the database. The addHandler method can look like this:

          public void addHandler(Student student) {  ContentValues values = new ContentValues();  values.put(COLUMN_ID, student.getID());  values.put(COLUMN_NAME, student.getStudentName());  SQLiteDatabase db = this.getWritableDatabase();  db.insert(TABLE_NAME, null, values);  db.close(); }

Find Information by Condition

To find information in the database by condition, we must use the SQL query statement as follows:

          SELECT * FROM table_name WHERE column_name;

In the next step, we save the result that is returned from implementation of the rawQuery() method of the SQLiteDatabase object into a Cursor object and find the matching result in this object. The code of the findHandler method looks like this:

          public Student findHandler(Stringstudentname) {  Stringquery = "Select * FROM " + TABLE_NAME + "WHERE" + COLUMN_NAME + " = " + "'" + studentname + "'";  SQLiteDatabase db = this.getWritableDatabase();  Cursor cursor = db.rawQuery(query, null);  Student student = new Student();  if (cursor.moveToFirst()) {   cursor.moveToFirst();   student.setID(Integer.parseInt(cursor.getString(0)));   student.setStudentName(cursor.getString(1));   cursor.close();  } else {   student = null;  }  db.close();  return student; }

Delete a Record by Condition

To delete a record by condition, we must use a SQL query statement, as follows:

          SELECT * FROM table_name WHERE column_name;

We will save the result that is returned from the implementation of the rawQuery() method of the SQLiteDatabase object into a Cursor object and find the matching result in this object. In the final step, we use the delete() method of the SQLiteDatabase object to delete the record. The code of the deleteHandler method looks like:

          public boolean deleteHandler(int ID) {  booleanresult = false;  Stringquery = "Select*FROM" + TABLE_NAME + "WHERE" + COLUMN_ID + "= '" + String.valueOf(ID) + "'";  SQLiteDatabase db = this.getWritableDatabase();  Cursor cursor = db.rawQuery(query, null);  Student student = new Student();  if (cursor.moveToFirst()) {   student.setID(Integer.parseInt(cursor.getString(0)));   db.delete(TABLE_NAME, COLUMN_ID + "=?",    newString[] {     String.valueOf(student.getID())    });   cursor.close();   result = true;  }  db.close();  return result; }

Update Information of a Record

To update the information of a record, we can use the ContentValues object and the update() method of the SQLiteDatabase object. Use the updateHandler() method as follows:

          public boolean updateHandler(int ID, String name) {  SQLiteDatabase db = this.getWritableDatabase();  ContentValues args = new ContentValues();  args.put(COLUMN_ID, ID);  args.put(COLUMN_NAME, name);  return db.update(TABLE_NAME, args, COLUMN_ID + "=" + ID, null) > 0; }

Main Activity

So far, let's look at our application model again:
Image title And our user interface:

Image title

Controls used in above UI include:

Controls	text atrribute	id attribute
EditText	Student ID	studentid
EditText	Student Name	studentname
Button	LOAD DATA	btnload
Button	ADD	btnadd
Button	FIND	btnfind
Button	DELETE	btndelete
Button	UPDATE	btnupdate
TextView		lst

The code for the click event of the buttons and results is as follows:

Code for the LOAD DATA button:

          public void loadStudents(View view) {  MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);  lst.setText(dbHandler.loadHanler());  studentid.setText("");  studentname.setText(""); }

In the XML file:

          <Button android:id="@+id/btnload" android:onClick="loadStudents" android:text="@string/Load" ...... />

The result looks like this:

Image title

Code for the ADD button:

          public void addStudent(View view) {  MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);  int id = Integer.parseInt(studentid.getText().toString());  String name = studentname.getText().toString();  Student student = new Student(id, name);  dbHandler.addHandler(student);  studentid.setText("");  studentname.setText(""); }

In the XML file:

          <Button android:id="@+id/btnadd" android:onClick="addStudent" android:text="@string/Add" .... />

Code for the FIND button:

          public void findStudent(View view) {  MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);  Studentstudent =   dbHandler.findHandler(studentname.getText().toString());  if (student != null) {   lst.setText(String.valueOf(student.getID()) + " " + student.getStudentName() + System.getProperty("line.separator"));   studentid.setText("");   studentname.setText("");  } else {   lst.setText("No Match Found");   studentid.setText("");   studentname.setText("");  } }

In the XML file:

          <Button android:id="@+id/btnfind" android:onClick="findStudent" android:text="@string/Find"..../>

The result looks like this:

Image title Code for the DELETE button:

          public void removeStudent(View view) {  MyDBHandler dbHandler = new MyDBHandler(this, null,   null, 1);  boolean result = dbHandler.deleteHandler(Integer.parseInt(   studentid.getText().toString()));  if (result) {   studentid.setText("");   studentname.setText("");   lst.setText("Record Deleted");  } else   studentid.setText("No Match Found"); }

In the XML file:

          <Button android:id="@+id/btndelete" android:onClick="removeStudent" android:text="@string/Delete" ........ />

The result looks like this:

Image title Code for the UPDATE button:

          public void updateStudent(View view) {  MyDBHandler dbHandler = new MyDBHandler(this, null,   null, 1);  boolean result = dbHandler.updateHandler(Integer.parseInt(   studentid.getText().toString()), studentname.getText().toString());  if (result) {   studentid.setText("");   studentname.setText("");   lst.setText("Record Updated");  } else   studentid.setText("No Match Found"); }

In the XML file:

          <Button android:id="@+id/btnupdate" android:onClick="updateStudent" android:text="@string/Update" .... />

The result looks like this:

Conclusion

In this article, I have introduced how to create a simple database Android application in Android Studio 3.0. I hope that this article is useful for beginners who are learning Android programming.

Topics:

android, mobile, mobile app development, database, android studio

Opinions expressed by DZone contributors are their own.