UNITY UNIVERSITY
Department of computer science
CHAPTER FOUR
Basic Database Programming in
Android using SQLite
Performing CRUD
(Create, Read, Update, and Delete)
operation in SQLite
Procedure:
PART I: TOGGLE BUTTON EXAMPLE
Step 1. Create Android Application
The first step is to create a simple Android Application using Android studio. Search
for android in the program menu. When you click on Android studio icon, then click
“Start a new Android Studio Project”it will show screen as shown below:
Step 2: Type the application name
You can start your application development by calling start a new android studio
project. in a new installation frame should ask Application name, package
information and location of the project. Type the application name
“SQLiteExample ”
Step 3: Select Target Android Device
After entered application name, it going to be called select the form factors your
application runs on, here need to specify Minimum SDK, in our tutorial, I have
declared as API23: Android 6.0(Mashmallow) −
Step 4: Choose an Activity
The next level of installation should contain selecting the activity to mobile, it
specifies the default layout for Applications.
Step 5: Getting Ready for writing the application code
At the final stage it going to be open development tool to write the application code.
Step 6: After Click on a virtual device icon, it going to be shown by default virtual
devices which are present on your SDK, or else need to create a virtual device by
clicking Create new Virtual device button
If your AVD is created successfully it means your environment is ready for Android
application development.
Step 7: Design the User Interface
Before Writing a Hello word code, you must know about XML [Link] write hello
word code, you should redirect to App>res>layout>Activity_main.xml. Drag a
LinearLayout and (4) Button on the Layout XML.
Step 8: Add a New Activity (Empty Activity) and Design the User Interface
Add a new activity and name it Add_Employee. After adding the new activity, you
should redirect to App>res>layout>Activity_add_employee.xml. Drag a (2)
TextView, (2) Plain Text and (1) Button on the Linear Layout XML.
Objects ID
editName
PlainText - Name
ditCompany
PlainText – Company
btn_Add
Button: Add Record
textView1
TextView1: Enter Username
textView2
TextView1: Enter Company
Step 9: Add a New Activity (Empty Activity) and Design the User Interface
Add a new activity and name it Search_Employee. After adding the new activity, you
should redirect to App>res>layout>Activity_search_employee.xml. Drag some
objects/widgets on the Linear Layout XML as shown below.
Step 10: Add a New Activity (Empty Activity) and Design the User Interface
Add a new activity and name it Login_Employee. After adding the new activity, you
should redirect to App>res>layout>Activity_login_employee.xml. Drag some
objects/widgets on the Linear Layout XML as shown below.
Step 11: Add a New Activity (Empty Activity) and Design the User Interface
Add a new activity and name it ShowAll_Employee. After adding the new activity,
you should redirect to App>res>layout>Activity_showall_employee.xml. Drag
some objects/widgets on the Linear Layout XML as shown below.
Step 12: Modify the [Link]
A. Import necessary libraries
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
B. Declare and Create a variable. Add the following code to main function.
private Button btnInsert, btnSearch, btnLogin, btnShowAll;
C. Create an object instance by adding the code in the onCreate() Function
btnInsert = (Button) findViewById([Link].insert_btn);
btnSearch = (Button) findViewById([Link].search_btn);
btnShowAll = (Button) findViewById([Link].showall_btn);
btnLogin = (Button) findViewById([Link]);
D. Still on the onCreate() function, add the actionListener() method to the button objects
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_add();
}
});
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_search();
}
});
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_show();
}
});
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_login();
}
});
E. Add the function to open a new activity when the menu button is click
private void launchActivity_add() {
Intent intent = new Intent(this,
Add_Employee.class); startActivity(intent);
}
private void launchActivity_show() {
Intent intent = new Intent(this,
Tanan_All.class); startActivity(intent);
}
private void launchActivity_search() {
Intent intent = new Intent(this,
[Link]); startActivity(intent);
}
private void launchActivity_login() {
Intent intent = new Intent(this, [Link]);
startActivity(intent);
}
Full [Link] file code
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class MainActivity extends AppCompatActivity {
private Button btnInsert, btnSearch, btnLogin, btnShowAll;
@Override
protected void onCreate(Bundle savedInstanceState)
{ [Link](savedInstanceState);
setContentView([Link].activity_main);
btnInsert = (Button) findViewById([Link].insert_btn);
btnSearch = (Button) findViewById([Link].search_btn);
btnShowAll = (Button) findViewById([Link].showall_btn);
btnLogin = (Button) findViewById([Link]);
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_add();
}
});
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_search();
}
});
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_show();
}
});
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_login();
}
});
}
private void launchActivity_add() {
Intent intent = new Intent(this,
Add_Employee.class); startActivity(intent);
}
private void launchActivity_show() {
Intent intent = new Intent(this,
Tanan_All.class); startActivity(intent);
}
private void launchActivity_search() {
Intent intent = new Intent(this, [Link]);
startActivity(intent);
}
private void launchActivity_login() {
Intent intent = new Intent(this, [Link]);
startActivity(intent);
}
Step 13: Create a Class for DB Handler named it SQL “DBHelper” and add the
following code:
package [Link];
/**
* Created by bah on 5/2/2017.
*/
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class DBHelper extends SQLiteOpenHelper {
public static final String TAG = "DBHelper";
public static final String DATABASE_NAME = "[Link]";
public static final String EMP_TABLE_NAME = "EmpTable";
public static final String EMP_COLUMN_ID = "id";
public static final String EMP_COLUMN_NAME = "name"; public
static final String EMP_COLUMN_COMPANY = "company";
public static final String idnila = "2";
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
[Link](
"create table IF NOT EXISTS " + EMP_TABLE_NAME + "
(id integer primary key, name text, company text)"
);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
/* Insert a row into the database */
public boolean insert(String name, String company) {
SQLiteDatabase db = [Link]();
ContentValues contentValues = new
ContentValues(); [Link]("name", name);
[Link]("company", company);
[Link](EMP_TABLE_NAME, null, contentValues);
return true;
}
/* Get the first row ID from the table
*/ public int getFirstId() {
int idToUpdate = 0;
String query = "select id from " + EMP_TABLE_NAME + " LIMIT 1";
SQLiteDatabase db = [Link]();
Cursor res = [Link](query, null);
if (null != res && [Link]() > 0)
{ [Link]();
idToUpdate = [Link](0);
}
return idToUpdate;
}
/* Get all employees records from the table based on search criteria
*/ public ArrayList<Employee> getAllEmployees(String searchName) {
ArrayList<Employee> employeeList = new ArrayList<Employee>();
String query;
if (searchName == null) {
query = "select * from " + EMP_TABLE_NAME;
} else
query = "select * from " + EMP_TABLE_NAME + " where name like
'%" + searchName + "%'";
SQLiteDatabase db = [Link]();
Cursor res = [Link](query, null);
[Link]();
while ([Link]() == false) {
Employee emp = new Employee();
int id = [Link](0);
String name = [Link](1);
String company = [Link](2);
[Link](id, name, company);
[Link](emp);
[Link]();
}
return employeeList;
public ArrayList<Employee> loginCredentials(String searchName,
String searchCompany) {
ArrayList<Employee> employeeList = new ArrayList<Employee>();
String query;
query = "select * from " + EMP_TABLE_NAME + " where name like '%" +
searchName + "%'" + " AND company like '%" + searchCompany + "%'" ;
SQLiteDatabase db = [Link]();
Cursor res = [Link](query, null);
[Link]();
while ([Link]() == false) {
Employee emp = new Employee();
int id = [Link](0);
String name = [Link](1);
String company = [Link]( 2);
[Link](id, name, company);
[Link](emp);
[Link]();
}
return employeeList;
/* Update the table row with ID - id */
public boolean updateDB(Integer id, String name, String company) {
Log.i(TAG, "Updating ID : " + id);
SQLiteDatabase db = [Link]();
ContentValues contentValues = new ContentValues();
[Link]("name", name);
[Link]("company", company);
[Link](EMP_TABLE_NAME, contentValues, "id = ? ", new
String[]{[Link](id)});
return true;
}
/* Delete the row with ID - id from the employees table
*/ public Integer deleteRow(Integer id) {
SQLiteDatabase db = [Link]();
return [Link](EMP_TABLE_NAME, "id = ? ", new
String[]{[Link](id)});
}
Step 14: Create a Model Class named it SQL “Employee” and add the following
code:
package [Link];
/**
* Created by bah on 5/2/2017.
*/
public class Employee {
public int id;
public String name, company;
public void setAll(int id, String name, String company)
{ [Link] = id;
[Link] = name;
[Link] = company;
}
public Employee(){
public long getEmpId()
{ return id;
}
public void setEmpId(long empId)
{ [Link] = id;
}
public String getName() {
return name;
}
public void setName(String firstname)
{ [Link] = name;
}
public String getCompany() {
return company;
}
public void setCompany (String lastname)
{ [Link] = company;
}
Step 14: Modify the Search_Employee.java
[Link] file code
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class SeearchEmployee extends AppCompatActivity {
private TextView mTv;
private EditText empSearch, editName, editCompany;
private Button btnSearchRecords, btnUpdateRecords, btnDeleteRecords;
private DBHelper myDb;
ArrayList<Employee> allEmployees;
@Override
protected void onCreate(Bundle savedInstanceState) {
[Link](savedInstanceState);
setContentView([Link].activity_seearch_employee);
empSearch = (EditText) findViewById([Link]);
editName = (EditText) findViewById([Link]);
editCompany = (EditText) findViewById([Link]);
mTv = (TextView) findViewById([Link]);
btnSearchRecords = (Button) findViewById([Link]);
btnUpdateRecords = (Button) findViewById([Link]);
btnDeleteRecords = (Button) findViewById([Link]);
myDb = new DBHelper(this);
[Link](new [Link]() {
@Override
public void onClick(View view) {
// allEmployees = [Link](null);
String searchVal = [Link]().toString().trim();
allEmployees = [Link](searchVal);
//String basta = [Link](1);
//[Link](allEmployees);
for (Employee emp : allEmployees) {
//[Link]("[ " + [Link] + " ] [ " + [Link]
+ " ] [ " + [Link] + " ]\n");
[Link]([Link]());
[Link]([Link]());
}
// printTable(allEmployees);
// Log.i(TAG, "Searched Size : " + [Link]());
if ([Link]() == 0) {
showToastMessage("No Results");
}
}
});
[Link](new [Link]() {
@Override
public void onClick(View view) {
// allEmployees = [Link](null);
String searchVal = [Link]().toString().trim();
allEmployees = [Link](searchVal);
//String basta = [Link](1);
//[Link](allEmployees);
for (Employee emp : allEmployees) {
//[Link]("[ " + [Link] + " ] [ " + [Link]
+ " ] [ " + [Link] + " ]\n");
//[Link]([Link]());
//[Link]([Link]());
[Link]([Link], [Link]().toString(),
[Link]().toString());
allEmployees = [Link](null);
}
showUpdateToastMessage("Updated the record
" +[Link]().toString().trim());
launchActivity_Main();
// printTable(allEmployees);
// Log.i(TAG, "Searched Size : " + [Link]());
if ([Link]() == 0) {
showToastMessage("No Results");
}
}
});
[Link](new [Link]() {
@Override
public void onClick(View view) {
// allEmployees = [Link](null);
String searchVal = [Link]().toString().trim();
allEmployees = [Link](searchVal);
//String basta = [Link](1);
//[Link](allEmployees);
for (Employee emp : allEmployees) {
//[Link]("[ " + [Link] + " ] [ " +
[Link] + " ] [ " + [Link] + " ]\n");
//[Link]([Link]());
//[Link]([Link]());
[Link]([Link]);
allEmployees = [Link](null);
// printTable(allEmployees);
// Log.i(TAG, "Searched Size : " + all [Link]());
showDeleteToastMessage("Deleted the record "
+[Link]().toString().trim());
launchActivity_Main();
if ([Link]() == 0) {
showToastMessage("No Results");
}
}
});
}
void showToastMessage(String message) {
[Link](this, message, Toast.LENGTH_LONG).show();
}
void showUpdateToastMessage(String message) {
[Link](this, message, Toast.LENGTH_LONG).show();
}
void showDeleteToastMessage(String message) {
[Link](this, message, Toast.LENGTH_LONG).show();
}
private void launchActivity_Main() {
Intent intent = new Intent(this,
[Link]); startActivity(intent);
}
}
Step 15: Modify the [Link]
[Link] file code
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class Tanan_All extends AppCompatActivity {
TextView mTv;
private DBHelper myDb;
ArrayList<Employee> allEmployees;
@Override
protected void onCreate(Bundle savedInstanceState)
{ [Link](savedInstanceState);
setContentView([Link].activity_tanan__all);
mTv = (TextView) findViewById([Link].textView2);
//allEmployees = [Link](null);
myDb = new DBHelper(this);
allEmployees = [Link](null);
printTable(allEmployees);
}
void printTable(ArrayList<Employee> allEmployees) {
// Empty text in the TextView to show new
values [Link]("");
StringBuffer stringBuffer = new StringBuffer();
// Append table values in a String buffer
for (Employee emp : allEmployees) {
[Link]("[ " + [Link] + " ] [ " + [Link] + " ] [ "
+ [Link] + " ]\n");
}
// Show the table values
[Link](stringBuffer);
}
}
Step 16: Modify the [Link]
[Link] file code
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class Login extends AppCompatActivity {
private EditText editName, editCompany;
private Button btnLoginRecords, btnCancelRecords;
private DBHelper myDb;
ArrayList<Employee> allEmployees;
@Override
protected void onCreate(Bundle savedInstanceState) {
[Link](savedInstanceState);
setContentView([Link].activity_login);
editName = (EditText) findViewById([Link]);
editCompany = (EditText) findViewById([Link]);
btnLoginRecords= (Button) findViewById([Link].btnLogin1);
btnCancelRecords = (Button) findViewById([Link]. btnCancel1);
myDb = new DBHelper(this);
[Link](new [Link]() {
@Override
public void onClick(View view) {
// allEmployees = [Link](null);
String searchVal1 = [Link]().toString().trim();
String searchVal2 = [Link]().toString().trim();
allEmployees = [Link](searchVal1, searchVal2);
//String basta = [Link](1);
//[Link](allEmployees);
for (Employee emp : allEmployees) {
//[Link]("[ " + [Link] + " ] [ " +
[Link] + " ] [ " + [Link] + " ]\n");
//[Link]([Link]());
//[Link]([Link]());
showLoginMessage("Login Successful for "
+[Link]().toString().trim());
launchActivity_Main();
}
// printTable(allEmployees);
// Log.i(TAG, "Searched Size : " + [Link]());
if ([Link]() == 0) {
showLoginUnsuccessfulMessage("Login Not Successful");
}
}
});
[Link](new [Link]() {
// @Override
public void onClick(View view) {
launchActivity_Main();
}
});
void showLoginMessage(String message) {
[Link](this, message, Toast.LENGTH_LONG).show();
}
void showLoginUnsuccessfulMessage(String message) {
[Link](this, message, Toast.LENGTH_LONG).show();
}
private void launchActivity_Main() {
Intent intent = new Intent(this,
[Link]); startActivity(intent);
}