So I recently got into Android programming and have been following this tutorial on how to insert, update, delete and view data in an SQLite database. Now, I would also like to add a search functionality to this SQLite database where I can search for a name (the columns I'm using are name, contact and DOB) and if the searched for name matches an existing one in the database, display this row/entry from the database in the application. I figured this could be done in a similar way as the viewing/updating the database, so I tried coming up with a solution for this with these as reference, however after having tried a good amount of ways that seemed reasonable to me I still haven't gotten it to work, so I'd greatly appreciate any help with this! I feel like I was close w/ some of my attempts but something with the logic didn't click completely.
MainActivity.java:
package com.example.sqliteapplication;
import ...
public class MainActivity extends AppCompatActivity {
EditText name, contact, dob;
Button insert, update, delete, view, search;
DBHelper DB;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
name = findViewById(R.id.name);
contact = findViewById(R.id.contact);
dob = findViewById(R.id.dob);
insert = findViewById(R.id.btnInsert);
update = findViewById(R.id.btnUpdate);
delete = findViewById(R.id.btnDelete);
view = findViewById(R.id.btnView);
search = findViewById(R.id.btnSearch);
DB = new DBHelper(this);
insert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String nameTXT = name.getText().toString();
String contactTXT = contact.getText().toString();
String dobTXT = dob.getText().toString();
Boolean checkinsertdata = DB.insertuserdata(nameTXT, contactTXT, dobTXT);
if(checkinsertdata==true)
Toast.makeText(MainActivity.this, "New Entry Inserted", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "New Entry Not Inserted", Toast.LENGTH_SHORT).show();
} });
update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String nameTXT = name.getText().toString();
String contactTXT = contact.getText().toString();
String dobTXT = dob.getText().toString();
Boolean checkupdatedata = DB.updateuserdata(nameTXT, contactTXT, dobTXT);
if(checkupdatedata==true)
Toast.makeText(MainActivity.this, "Entry Updated", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "New Entry Not Updated", Toast.LENGTH_SHORT).show();
} });
delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String nameTXT = name.getText().toString();
Boolean checkudeletedata = DB.deletedata(nameTXT);
if(checkudeletedata==true)
Toast.makeText(MainActivity.this, "Entry Deleted", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "Entry Not Deleted", Toast.LENGTH_SHORT).show();
} });
view.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Cursor res = DB.getdata();
if(res.getCount()==0){
Toast.makeText(MainActivity.this, "No Entry Exists", Toast.LENGTH_SHORT).show();
return;
}
StringBuffer buffer = new StringBuffer();
while(res.moveToNext()){
buffer.append("Name :"+res.getString(0)+"\n");
buffer.append("Contact :"+res.getString(1)+"\n");
buffer.append("Date of Birth :"+res.getString(2)+"\n\n");
}
AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
builder.setCancelable(true);
builder.setTitle("User Entries");
builder.setMessage(buffer.toString());
builder.show();
} });
}
}
DBHelper.java:
package com.example.sqliteapplication;
import ...
public class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context) {
super(context, "Userdata.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase DB) {
DB.execSQL("create Table Userdetails(name TEXT primary key," +
"contact TEXT, dob TEXT)");
}
@Override
public void onUpgrade(SQLiteDatabase DB, int oldVersion, int newVersion) {
DB.execSQL("drop Table if exists Userdetails");
}
public Boolean insertuserdata(String name, String contact, String dob) {
SQLiteDatabase DB = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name", name);
contentValues.put("contact", contact);
contentValues.put("dob", dob);
long result = DB.insert("Userdetails",
null, contentValues);
if (result == -1) {
return false;
} else {
return true;
}
}
public Boolean updateuserdata(String name, String contact, String dob) {
SQLiteDatabase DB = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("contact", contact);
contentValues.put("dob", dob);
Cursor cursor = DB.rawQuery(
"Select * from Userdetails where name = ?", new String[]{name});
if (cursor.getCount() > 0) {
long result = DB.update("Userdetails",
contentValues, "name=?",
new String[]{name});
if (result == -1) {
return false;
} else {
return true;
}
} else {
return false;
}
}
public Boolean deletedata(String name) {
SQLiteDatabase DB = this.getWritableDatabase();
Cursor cursor = DB.rawQuery(
"Select * from Userdetails where name = ?", new String[]{name});
if (cursor.getCount() > 0) {
long result = DB.delete("Userdetails", "name=?",
new String[]{name});
if (result == -1) {
return false;
} else {
return true;
}
} else {
return false;
}
}
public Cursor getdata () {
SQLiteDatabase DB = this.getWritableDatabase();
Cursor cursor = DB.rawQuery(
"Select * from Userdetails ", null);
return cursor;
}
}
activity_main.xml:
<?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"
android:padding="10dp"
tools:context=".MainActivity">
<TextView
android:id="@+id/texttitle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Please enter details below"
android:textSize="24dp"
android:layout_marginTop="20dp"
/>
<EditText
android:id="@+id/name"
android:hint="Name"
android:textSize="24dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_below="@+id/texttitle"
android:inputType="textPersonName"
/>
<EditText
android:id="@+id/contact"
android:hint="Contact"
android:textSize="24dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_below="@+id/name"
android:inputType="number"
/>
<EditText
android:id="@+id/dob"
android:hint="Date of Birth"
android:textSize="24dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_below="@+id/contact"
android:inputType="number"
/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btnInsert"
android:textSize="24dp"
android:text="Insert New Data"
android:layout_marginTop="30dp"
android:layout_below="@+id/dob"
/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btnUpdate"
android:textSize="24dp"
android:text="Update Data"
android:layout_below="@+id/btnInsert"
/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btnDelete"
android:textSize="24dp"
android:text="Delete Data"
android:layout_below="@+id/btnUpdate"
/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btnView"
android:textSize="24dp"
android:text="View Data"
android:layout_below="@+id/btnDelete"
/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btnSearch"
android:textSize="24dp"
android:text="Search Data"
android:layout_below="@+id/btnView"
/>
</RelativeLayout>
Sorry in advance for perhaps some bad explanations/something not making sense, I'm only a few weeks into this and got a lot to learn for sure! Thanks
You can modify getdata() so that it takes as an argument the name, or part of the name that you search for:
public Cursor getdata(String name) {
SQLiteDatabase DB = this.getWritableDatabase();
String sql = "Select * from Userdetails";
String[] selectionArgs = null;
if (name != null) {
sql += " where name LIKE '%' || ? || '%'";
selectionArgs = new String[] {name};
}
return DB.rawQuery(sql, selectionArgs);
}
and you should also modify in the listener of the button view the call to getdata() to pass null so that you get as result all the rows of the table:
Cursor res = DB.getdata(null);
Then you create the listener for the button search:
search.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String searchName = name.getText().toString().trim();
if (searchName.length() == 0) return;
Cursor res = DB.getdata(searchName);
if(res.getCount()==0){
Toast.makeText(MainActivity.this, "No Entry Exists", Toast.LENGTH_SHORT).show();
return;
}
StringBuffer buffer = new StringBuffer();
while(res.moveToNext()){
buffer.append("Name :"+res.getString(0)+"\n");
buffer.append("Contact :"+res.getString(1)+"\n");
buffer.append("Date of Birth :"+res.getString(2)+"\n\n");
}
AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
builder.setCancelable(true);
builder.setTitle("User Entries");
builder.setMessage(buffer.toString());
builder.show();
}
});
Note that there is no need to query the table before you insert, update, delete a row.
You can perform the operation that you want and then examine the result of the method insert() or update() or delete() to decide whether it was successful or not.
Remember that only insert() returns -1 if unsuccessful.
update() and delete() return the number of affected (updated/deleted) rows.
So, I would write the code like this:
package com.example.sqliteapplication;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context) {
super(context, "Userdata.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase DB) {
DB.execSQL("create Table Userdetails(name TEXT primary key, contact TEXT, dob TEXT)");
}
@Override
public void onUpgrade(SQLiteDatabase DB, int oldVersion, int newVersion) {
DB.execSQL("drop Table if exists Userdetails");
}
public Boolean insertuserdata(String name, String contact, String dob) {
SQLiteDatabase DB = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name", name);
contentValues.put("contact", contact);
contentValues.put("dob", dob);
long result = DB.insert("Userdetails", null, contentValues);
return (result != -1);
}
public Boolean updateuserdata(String name, String contact, String dob) {
SQLiteDatabase DB = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("contact", contact);
contentValues.put("dob", dob);
long result = DB.update("Userdetails", contentValues, "name = ?", new String[]{name});
return (result > 0);
}
public Boolean deletedata(String name) {
SQLiteDatabase DB = this.getWritableDatabase();
long result = DB.delete("Userdetails", "name = ?", new String[]{name});
return (result > 0);
}
public Cursor getdata(String name) {
SQLiteDatabase DB = this.getWritableDatabase();
String sql = "Select * from Userdetails";
String[] selectionArgs = null;
if (name != null) {
sql += " where name LIKE '%' || ? || '%'";
selectionArgs = new String[] {name};
}
return DB.rawQuery(sql, selectionArgs);
}
}
and for the activity class:
package com.example.sqliteapplication;
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;
import android.database.Cursor;
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 {
EditText name, contact, dob;
Button insert, update, delete, view, search;
DBHelper DB;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
name = findViewById(R.id.name);
contact = findViewById(R.id.contact);
dob = findViewById(R.id.dob);
insert = findViewById(R.id.btnInsert);
update = findViewById(R.id.btnUpdate);
delete = findViewById(R.id.btnDelete);
view = findViewById(R.id.btnView);
search = findViewById(R.id.btnSearch);
DB = new DBHelper(this);
insert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String nameTXT = name.getText().toString().trim();
String contactTXT = contact.getText().toString().trim();
String dobTXT = dob.getText().toString().trim();
Boolean checkinsertdata = DB.insertuserdata(nameTXT, contactTXT, dobTXT);
if(checkinsertdata==true)
Toast.makeText(MainActivity.this, "New Entry Inserted", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "New Entry Not Inserted", Toast.LENGTH_SHORT).show();
}
});
update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String nameTXT = name.getText().toString().trim();
String contactTXT = contact.getText().toString().trim();
String dobTXT = dob.getText().toString().trim();
Boolean checkupdatedata = DB.updateuserdata(nameTXT, contactTXT, dobTXT);
if(checkupdatedata==true)
Toast.makeText(MainActivity.this, "Entry Updated", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "New Entry Not Updated", Toast.LENGTH_SHORT).show();
}
});
delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String nameTXT = name.getText().toString().trim();
Boolean checkudeletedata = DB.deletedata(nameTXT);
if(checkudeletedata==true)
Toast.makeText(MainActivity.this, "Entry Deleted", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "Entry Not Deleted", Toast.LENGTH_SHORT).show();
}
});
view.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Cursor res = DB.getdata(null);
if(res.getCount()==0){
Toast.makeText(MainActivity.this, "No Entry Exists", Toast.LENGTH_SHORT).show();
return;
}
StringBuffer buffer = new StringBuffer();
while(res.moveToNext()){
buffer.append("Name :"+res.getString(0)+"\n");
buffer.append("Contact :"+res.getString(1)+"\n");
buffer.append("Date of Birth :"+res.getString(2)+"\n\n");
}
AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
builder.setCancelable(true);
builder.setTitle("User Entries");
builder.setMessage(buffer.toString());
builder.show();
}
});
search.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String searchName = name.getText().toString().trim();
if (searchName.length() == 0) return;
Cursor res = DB.getdata(searchName);
if(res.getCount()==0){
Toast.makeText(MainActivity.this, "No Entry Exists", Toast.LENGTH_SHORT).show();
return;
}
StringBuffer buffer = new StringBuffer();
while(res.moveToNext()){
buffer.append("Name :"+res.getString(0)+"\n");
buffer.append("Contact :"+res.getString(1)+"\n");
buffer.append("Date of Birth :"+res.getString(2)+"\n\n");
}
AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
builder.setCancelable(true);
builder.setTitle("User Entries");
builder.setMessage(buffer.toString());
builder.show();
}
});
}
}