What is SQLite database?
SQLite database is open source relational database management system in android. It is self-contained, serverless, zero configuration and transactional SQL database engine.
SQLite is a SQL database that stores data to a text file on a device. Android comes in with built in SQLite database implementation. SQLite is a lightweight database, it does not require any server configuration like other database. SQLite is available as embedded in the android by default, so requirements of a server or installation not required.
Why SQLite?
- SQLite does not require a separate server process installation. That’s why we can say that SQLIte is serverless.
- No setup or administration needed for SQLite database.
- SQLite is not an client-server database management system.
- A complete SQLite database is stored as a single text file.
- SQLite is very small and light weight.
- SQLite is self-contained, no external dependencies exist in the database.
- SQLite transactions are fully ACID (atomic, consistent, isolated, and durable) compatible.
- SQLite supports most of the query language features of SQL standard.
- SQLite is available on UNIX and Windows.
- SQLite is Written in ANSI-C.
SQLite Database Tutorial
The main package is android.database.sqlite that contains the classes to manage your own databases. The methods used to connect the SQLite databse is shown below.
openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags)
String path : This is the database name. It cannot be null.
SQLiteDatabase.CursorFactory factory : Used to allow returning sub-classes of Cursor when calling query. This value may be null.
int flags : Open the database according to the flags OPEN_READWRITE, OPEN_READONLY, CREATE_IF_NECESSARY.
DatabaseErrorHandler errorHandler : This is defined to take an action when database corruption is detected. This value may be null.
Another method to establish a connection with SQLite database is openOrCreateDatabase() . It not only opens but creates the database if it not exists. The syntax is
- path String This value must never be null.
- factory SQLiteDatabase.CursorFactory This value may be null.
- errorHandler DatabaseErrorHandler This value may be null.
The tutorial on SQLite database discussing about students record handling. Here there is an option for adding students , Delete student record based on roll number, modify student details based on roll number, view students details based on roll number and we can view all students details in the database .
Layout for handling student record is as shown below.
The layout design of the above layout is as follows.
activity_main.xml
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:id="@+id/activity_main" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical"> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:gravity="center" android:id="@+id/l1" android:orientation="horizontal"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Students Record Handling" android:gravity="center" android:textSize="20sp" android:textColor="@color/colorAccent" android:textAlignment="center" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="60dp" android:id="@+id/l2" android:orientation="horizontal" android:layout_below="@+id/l1"> <TextView android:layout_width="200dp" android:layout_height="50dp" android:text="Enter Student Roll No:" android:textSize="20sp"/> <EditText android:layout_width="200dp" android:layout_height="50dp" android:id="@+id/e1" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="60dp" android:id="@+id/l3" android:orientation="horizontal" android:layout_below="@+id/l2"> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Enter Student Name:" android:textSize="20sp" /> <EditText android:layout_width="200dp" android:layout_height="50dp" android:id="@+id/e2"/> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="60dp" android:id="@+id/l4" android:layout_below="@+id/l3" android:orientation="horizontal"> <TextView android:layout_width="200dp" android:layout_height="50dp" android:text="Enter Student Mark:" android:textSize="20sp"/> <EditText android:layout_width="200dp" android:layout_height="50dp" android:id="@+id/e3"/> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="80dp" android:layout_below="@+id/l4" android:id="@+id/l5" android:orientation="horizontal" android:gravity="center"> <Button android:layout_width="100dp" android:layout_height="40dp" android:id="@+id/b1" android:text="Add" /> <Button android:layout_width="100dp" android:layout_height="40dp" android:id="@+id/b2" android:text="Delete" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="80dp" android:id="@+id/l6" android:orientation="horizontal" android:gravity="center" android:layout_below="@+id/l5"> <Button android:layout_width="100dp" android:layout_height="40dp" android:id="@+id/b3" android:text="Modify" /> <Button android:layout_width="100dp" android:layout_height="40dp" android:id="@+id/b4" android:text="View" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="80dp" android:layout_below="@+id/l6" android:id="@+id/l7" android:orientation="horizontal" android:gravity="center"> <Button android:layout_width="100dp" android:layout_height="40dp" android:id="@+id/b5" android:text="ViewAll" /> <Button android:layout_width="100dp" android:layout_height="40dp" android:id="@+id/b6" android:text="SQLite Details" /> </LinearLayout> </RelativeLayout>
MainActivity.java
package com.example.sqliteexample1;
import android.app.AlertDialog;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
public class MainActivity extends AppCompatActivity {
EditText editRollno;
EditText editName;
EditText editMarks;
Button btnAdd;
Button btnDelete;
Button btnModify;
Button btnView;
Button btnViewAll;
Button btnShowInfo;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// Initializing controls
editRollno = (EditText) findViewById(R.id.e1);
editName = (EditText) findViewById(R.id.e2);
editMarks = (EditText) findViewById(R.id.e3);
btnAdd = (Button) findViewById(R.id.b1);
btnDelete = (Button) findViewById(R.id.b2);
btnModify = (Button) findViewById(R.id.b3);
btnView = (Button) findViewById(R.id.b4);
btnViewAll = (Button) findViewById(R.id.b5);
btnShowInfo = (Button) findViewById(R.id.b6);
// Creating database and table
final SQLiteDatabase db = openOrCreateDatabase("StudentDB", Context.MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS student(rollno VARCHAR,name VARCHAR,marks VARCHAR);");
// Registering event handlers
btnAdd.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
// Checking empty fields
if(editRollno.getText().toString().trim().length()==0||editName.getText().toString().trim().length()==0||editMarks.getText().toString().trim().length()==0)
{
showMessage("Error", "Please enter all values");
return;
}
// Inserting record
db.execSQL("INSERT INTO student VALUES('" + editRollno.getText() + "','" +
editName.getText() + "','" + editMarks.getText() + "');");
showMessage("Success", "Record added");
clearText();
}
});
btnView.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
// Checking empty roll number
if(editRollno.getText().toString().trim().length()==0)
{
showMessage("Error", "Please enter Rollno");
return;
}
// Searching roll number
Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editRollno.getText()+"'", null);
if(c.moveToFirst())
{
editName.setText(c.getString(1));
editMarks.setText(c.getString(2));
}
else
{
showMessage("Error", "Invalid Rollno");
clearText();
}
}
});
btnDelete.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
// Checking empty roll number
if(editRollno.getText().toString().trim().length()==0)
{
showMessage("Error", "Enter Rollno");
return;
}
// Searching roll number
Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editRollno.getText()+"'", null);
if(c.moveToFirst())
{
// Deleting record if found
db.execSQL("DELETE FROM student WHERE rollno='"+editRollno.getText()+"'");
showMessage("Success", "Record Deleted");
}
else
{
showMessage("Error", "Invalid Rollno");
}
clearText();
}
});
btnModify.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
// Code here executes on main thread after user presses button
if(editRollno.getText().toString().trim().length()==0)
{
showMessage("Error", "Enter Rollno");
return;
}
// Searching roll number
Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editRollno.getText()+"'", null);
if(c.moveToFirst())
{
// Modifying record if found
db.execSQL("UPDATE student SET name='"+editName.getText()+"',marks='"+editMarks.getText()+
"' WHERE rollno='"+editRollno.getText()+"'");
showMessage("Success", "Record Modified");
}
else
{
showMessage("Error", "Invalid Rollno");
}
clearText();
}
});
btnViewAll.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
// Code here executes on main thread after user presses button
// Checking empty roll number
Cursor c=db.rawQuery("SELECT * FROM student", null);
if(c.getCount()==0)
{
showMessage("Error", "No records found");
return;
}
StringBuffer buffer=new StringBuffer();
while(c.moveToNext())
{
buffer.append("Rollno: "+c.getString(0)+"\n");
buffer.append("Name: "+c.getString(1)+"\n");
buffer.append("Marks: "+c.getString(2)+"\n\n");
}
showMessage("Student Details", buffer.toString());
}
});
btnShowInfo.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
showMessage("Students Information Handling", "https://codeunplug.com/");
}
});
}
public void showMessage(String title,String message)
{
AlertDialog.Builder builder=new AlertDialog.Builder(this);
builder.setCancelable(true);
builder.setTitle(title);
builder.setMessage(message);
builder.show();
}
public void clearText()
{
editRollno.setText("");
editName.setText("");
editMarks.setText("");
editRollno.requestFocus();
}
}
AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.example.sqliteexample1" > <application android:allowBackup="true" android:icon="@mipmap/ic_launcher" android:label="@string/app_name" android:roundIcon="@mipmap/ic_launcher_round" android:supportsRtl="true" android:theme="@style/AppTheme" > <activity android:name=".MainActivity" > <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> </application> </manifest>
Output :
