Author: Nitish
INSERT, SELECT, UPDATE and DELETE OPERATIONS ON SQLite DATABASE
In this article, you will learn how to use SQLite database operations in your android applications. I have used four operations in this sample android application.
I have named my android application as SQLITEdatabaseOperations. There would be one default activity(Main Activity), I have named my second activity as ShowData. One single class will be there named DBHelper.java which contains the table and operations related to the database.
File Structure
MainActivity.java
package nitish.sqlitedatabaseoperations;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends Activity implements View.OnClickListener {
private Button mSubmit;
private Button mShow;
private EditText txt_website_name;
private EditText txt_url_link;
protected DBHelper DB = new DBHelper(MainActivity.this);
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mSubmit = (Button)findViewById(R.id.btn_submit_link);
mSubmit.setOnClickListener(this);
mShow = (Button)findViewById(R.id.btn_show_link);
mShow.setOnClickListener(this);
txt_website_name = (EditText)findViewById(R.id.edit_website_name);
txt_url_link = (EditText)findViewById(R.id.edit_website_link);
}
public void onClick(View v)
{
switch(v.getId()){
case R.id.btn_show_link:
Intent i = new Intent(getBaseContext(), ShowData.class);
startActivity(i);
break;
case R.id.btn_submit_link:
String website_name = txt_website_name.getText().toString();
String url_link_name = txt_url_link.getText().toString();
boolean invalid = false;
if(website_name.equals(""))
{
invalid = true;
Toast.makeText(getApplicationContext(), "Please enter website name", Toast.LENGTH_SHORT).show();
}
else
if(url_link_name.equals(""))
{
invalid = true;
Toast.makeText(getApplicationContext(), "Please enter URL link", Toast.LENGTH_SHORT).show();
}
else
if(invalid == false)
{
addEntry(website_name, url_link_name);
mSubmit.setText("Add Another");
txt_website_name.setText("");
txt_url_link.setText("");
}
break;
}
}
private void addEntry(String website_name, String url_link_name)
{
SQLiteDatabase db = DB.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("websitename", website_name);
values.put("urllink", url_link_name);
try
{
db.insert(DBHelper.lINK_TABLE_NAME, null, values);
Toast.makeText(getApplicationContext(), "Your details saved Successfully", Toast.LENGTH_SHORT).show();
}
catch(Exception e)
{
e.printStackTrace();
}
}
public void onDestroy()
{
super.onDestroy();
DB.close();
}
}
Copy below code in the xml file of MainActivity((res->layout->activity_main).
activity_main.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
android:paddingBottom="@dimen/activity_vertical_margin"
tools:context="nitish.passwordsecurity.AddLink">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="learnwithnitish.wordpress.com"
android:id="@+id/textView"
android:layout_centerHorizontal="true"
android:layout_marginBottom="161dp"
android:textColor="#FF4081"
/>
<ScrollView
android:id="@+id/important_link_form_scroll"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:fillViewport="false"
android:layout_alignParentTop="true"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_marginTop="60dp">
<LinearLayout android:id="@+id/important_link_form"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical">
<EditText android:id="@+id/edit_website_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Website name"
android:inputType="textPersonName"
android:maxLines="1"
android:singleLine="true" />
<requestFocus />
<EditText android:id="@+id/edit_website_link"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Website Link"
android:inputType="textUri"
android:maxLines="1"
android:singleLine="true" />
<RelativeLayout android:id="@+id/relativeLayout1"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btn_submit_link"
style="?android:textAppearanceSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginLeft="5dp"
android:layout_marginRight="5dp"
android:text="Submit"
android:textStyle="bold" />
<Button
android:id="@+id/btn_show_link"
style="?android:textAppearanceSmall"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Show"
android:textStyle="bold"
android:layout_toRightOf="@id/btn_submit_link"/>
</RelativeLayout>
</LinearLayout>
</ScrollView>
</RelativeLayout>
Create a new class named DBHelper.
DBHelper.java
package nitish.sqlitedatabaseoperations;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by NitishPC on 9/17/2016.
*/
public class DBHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "registration.db";
private static final int DATABASE_VERSION = 2;
//Table names
static final String lINK_TABLE_NAME = "link";
private static final String LINK_TABLE_CREATE =
"CREATE TABLE " + lINK_TABLE_NAME + "("+
"_id_link INTEGER PRIMARY KEY AUTOINCREMENT,"+
"websitename TEXT NOT NULL, urllink TEXT NOT NULL);";
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
System.out.println("In constructor");
}
@Override
public void onCreate(SQLiteDatabase db) {
try{
db.execSQL(LINK_TABLE_CREATE);
// If you want to create more than one table then execute in same way.
}catch(Exception e){
e.printStackTrace();
}
}
public void Delete_Link(int delete_position) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(lINK_TABLE_NAME, "_id_link="+delete_position , null);
db.close(); // Closing database connection
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
public void open() {
getWritableDatabase();
}
}
Create another blank activity named as ShowData.
ShowData.java
package nitish.sqlitedatabaseoperations;
import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import java.util.ArrayList;
public class ShowData extends AppCompatActivity {
protected SQLiteDatabase DB;
protected Cursor cursor;
protected DBHelper DB_Helper = new DBHelper(ShowData.this);
private ArrayList<String> results = new ArrayList<String>();
private ArrayList<String> linkId=new ArrayList<String>();
private ArrayList<String> WebsiteName=new ArrayList<String>();
private ArrayList<String> LinkURL=new ArrayList<String>();
ArrayAdapter<String> adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_show_data);
DB = (new DBHelper(this)).getWritableDatabase();
search();
ListView listView=(ListView)findViewById(R.id.data_list);
listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
@Override
public boolean onItemLongClick(AdapterView<?> arg0, View arg1,
int position, long arg3) {
removeItemFromList(position);
return true;
}
});
}
protected void removeItemFromList(int position) {
final int deletePosition = position;
AlertDialog.Builder alert = new AlertDialog.Builder(
ShowData.this);
alert.setTitle("Delete");
alert.setMessage("Do you want delete this item?");
alert.setPositiveButton("YES", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
SQLiteDatabase db = DB_Helper.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("_id_link", deletePosition);
db.update(DBHelper.lINK_TABLE_NAME, cv, "_id_link=" + linkId.get(deletePosition), null);
DB_Helper.Delete_Link(deletePosition);
Intent refresh = new Intent(getApplicationContext(), ShowData.class);
startActivity(refresh);
}
});
alert.setNegativeButton("CANCEL", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub
dialog.dismiss();
}
});
alert.show();
}
public void search()
{
//database extraction part
cursor = DB.rawQuery("SELECT _id_link, websitename,urllink FROM link",null);
if (cursor != null ) {
if (cursor.moveToFirst()) {
do {
int id=cursor.getInt(cursor.getColumnIndex("_id_link"));
String stringLinkId=Integer.toString(id);
String webName = cursor.getString(cursor.getColumnIndex("websitename"));
String URL = cursor.getString(cursor.getColumnIndex("urllink"));
WebsiteName.add(webName);
linkId.add(stringLinkId);
LinkURL.add(URL);
results.add("ID: " + stringLinkId +"\nWebsite: " + webName + "\nLink: " + URL);
}while (cursor.moveToNext());
}
}
adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, results);
ListView lv = (ListView) findViewById(R.id.data_list);
lv.setAdapter(adapter);
}
}
Copy below code in the xml file of ShowData Activity(res->layout->activity_show_data).
activity_show_data.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >
<ListView
android:id="@+id/data_list"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="0.72">
</ListView>
</LinearLayout>
OUTPUT
Main Screen of the application will appear as shown below:
Enter the website name and link in the provided edit text as shown below:
Click on submit button to save your data successfully and then click on the Show button to view your data as shown below:
Now to delete data from the list view, long press on the item you wish to delete and then select Ok option to delete it completely from the SQLite database.
You can clearly see that one of the selected websites has been deleted from the database.
Also, read our latest blogs.
Master of Science in Computer Science (University of Cincinnati, Ohio, USA) Senior Software Developer (Surge Technology, Texas, USA) Former Senior SAP ABAP Developer (Infosys Limited, Pune, India) Research Paper: https://dl.acm.org/doi/10.1145/2905055.2905333 Microsoft Professional Certified in Database
Learn With Nitish 