Manipulating SQLite Database From UI

Discussion in 'Android' started by faribasiddiq, Oct 15, 2014.

  1. faribasiddiq

    faribasiddiq New Member

    Joined:
    Dec 22, 2013
    Messages:
    10
    Likes Received:
    12
    Trophy Points:
    0
    Now a day, there are lots of android applications of different categories, and lots of them need to store some data. For that purpose, it is a common requirement to use a system to store data in our application. There are several systems which perform this data storing task. Out of these, Sqlite database is one of the most efficient systems. In previous tutorial, we have discussed about creating Sqlite database and performing operation. In this tutorial, we will discuss how to perform Sqlite operations from user interface.

    At first, we are going to review some concept that we have covered in the previous Sqlite tutorial for our convenience and synchronization with the current topic.

    Employee database:

    Let’s consider about an employee database. An employee database can be used to store information (i.e id, name, address, designation, salary) of the employees of any organization.
    So, we are going to create an employee database named employeeDb.

    For simplicity, we will consider some general information like id, name and designation of an employee. For this we will create a table which will store this information of an employee. The table name is: employeeInfo.

    Table structure:

    Think about a situation: Two employees work with same name and same designation in an organization, how could we differentiate between them? For that purpose, every employee will be given a unique id, which will be the differentiator for the above case. So our table structure will be like this:

    [​IMG]

    EmployeeInfo:

    We need a class to store and retrieve employee information. Now we will create a class named EmployeeInfo which will meet our purpose. Here is a detail of the class.

    Variables:

    [​IMG]

    Constructor:

    This class contains a constructor with this signature:
    Code:
    public EmployeeInfo(int id, String name, String designation)
    
    This constructor takes an int and two string values on initialization and stores these values in its class variables.

    Functions:

    EmployeeInfo class contains following functions:

    [​IMG]

    EmployeeInfo Class:

    Here is the code block for EmployeeInfo class:

    EmployeeInfo.java

    Code:
    package com.example.sqlitedb;
    
    public class EmployeeInfo {
    	int employeeId;
    	String employeeName;
    	String employeeDesignation;
    	
    	public EmployeeInfo(){
    		
    	}
    	public EmployeeInfo(int id, String name, String designation){
    		this.employeeId = id;
    		this.employeeName = name;
    		this.employeeDesignation = designation;
    	}
    	
    	public void setEmployeeId(int id)
    	{
    		this.employeeId = id;
    	}
    	
    	public void setEmployeeName(String name)
    	{
    		this.employeeName = name;
    	}
    	
    	public void setEmployeeDesignation(String designation)
    	{
    		this.employeeDesignation = designation;
    	}
    	
    	public int getEmployeeId(){
    		return this.employeeId;
    	}
    	
    	public String getEmployeeName(){
    		return this.employeeName;
    	}
    	
    	public String getEmployeeDesignation(){
    		return this.employeeDesignation;
    	}
    }
    
    DbHandler:

    Now we are going to create a class which will be responsible to perform the database operations. This class extends SQLiteOpenHelper class.

    Here, we will define the following items:
    • Database Version
    • Database Name
    • Table Name
    • Fields of the table
    In OnCreate function, we will execute a SQLite command to create our table in the database. To create a table, the command structure is:

    Create table Table Name (Field1 name, Field1 data type, Field2 name, Field2 data type).

    In place of table name, field name and field data type, we have to specify our desired value. If the table contains any primary or foreign key, you must specify along with the table field. To create our expected table, we have to execute the following command:

    Create table employeeInfo(id integer primary key, name text, designation text).

    The above command is used to create a table name employeeInfo, which has one integer field named id and it is the primary key of the table, one text field named name and one text field named designation.

    OnUpgrade function is used to upgrade the database with a new version. In this tutorial, we are not going to make use of it.

    addInfo function takes a EmployeeInfo object as parameter. The following operations are performed:
    • Extract id, name and designation information from the EmployeeInfo object
    • Create a Contentvalues object
    • Put extracted information in the Contentvalue object
    • Create a SQLiteDatabase object
    • Insert the values using Contentvalues object into the desired table
    getEmployeeList function returns the list of the employees from the database. It is used for performing following operations:
    • Create a List of EmployeeInfo
    • Take a SQLiteDatabase object
    • Use Cursor object to perform selection query
    • Get values of individual row entry of the table and add it in the list until the end of the table is reached
    • Return the List to the caller function
    Here is the code block for our DbHandler class:

    DbHandler.java

    Code:
    package com.example.sqlitedb;
    
    import java.util.ArrayList;
    import java.util.List;
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    
    public class DbHandler extends SQLiteOpenHelper {
    	
    	private static final int DATABASE_VERSION = 1;
    
    	private static final String DATABASE_NAME = "employeeDb";
    
    	private static final String TABLE_EMPLOYEE = "employeeInfo";
    
    	private static final String KEY_ID = "id";
    	private static final String KEY_NAME = "name";
    	private static final String KEY_DESIGNATION = "designation";
    	
    	public DbHandler(Context context) {
    		// TODO Auto-generated constructor stub
    		super(context, DATABASE_NAME, null, DATABASE_VERSION);		
    	}
    
    	@Override
    	public void onCreate(SQLiteDatabase db) {
    		String EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "("
    				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
    				+ KEY_DESIGNATION + " TEXT" + ")";
    		db.execSQL(EMPLOYEE_TABLE);
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    		// TODO Auto-generated method stub
    		db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMPLOYEE);
    
    		// Create tables again
    		onCreate(db);
    		
    	}
    	
    	void addEmployeeInfo(EmployeeInfo info) {
    		SQLiteDatabase db = this.getWritableDatabase();
    
    		ContentValues values = new ContentValues();
    		
    		values.put(KEY_ID, info.getEmployeeId()); 
    		values.put(KEY_NAME, info.getEmployeeName()); 
    		values.put(KEY_DESIGNATION, info.getEmployeeDesignation()); 
    
    		db.insert(TABLE_EMPLOYEE, null, values);
    		db.close(); // Closing database connection
    	}
    	
    	public List<EmployeeInfo> getEmployeeList() {
    		List<EmployeeInfo> employeeList = new ArrayList<EmployeeInfo>();
    
    		String query = "SELECT  * FROM " + TABLE_EMPLOYEE;
    
    		SQLiteDatabase db = this.getWritableDatabase();
    		Cursor cursor = db.rawQuery(query, null);
    
    		if (cursor.moveToFirst()) {
    			do {
    				EmployeeInfo employee = new EmployeeInfo();
    				employee.setEmployeeId(Integer.parseInt(cursor.getString(0)));
    				employee.setEmployeeName(cursor.getString(1));
    				employee.setEmployeeDesignation(cursor.getString(2));
    				employeeList.add(employee);
    			} while (cursor.moveToNext());
    		}
    		return employeeList;
    	}
    }
    

    Multiple Activities:



    In this example, we will use 3(three) activities to perform sqlite operations. The activites are:
    • SqliteMainActivity: Initial activity which shows add and view options
    • AddInfoActivity: Provide option to add information of new employee
    • ViewInfoActivity: Provide option to display information of all employees of the table
    The activity names are listed in AndroidManifest.xml file in <activity> tag. The launcher activity has another tag <intent-filter> that provides the extra information. For example, the SqliteMainActivity, which is the launcher activity, is listed as:
    Code:
            <activity
                android:name="com.example.sqlitedb.SqliteMainActivity"
                android:label="@string/title_activity_sqlite_main" >
                <intent-filter>
                    <action android:name="android.intent.action.MAIN" />
    
                    <category android:name="android.intent.category.LAUNCHER" />
                </intent-filter>
            </activity>
    
    Here are the details of AndroidManifest.xml file.

    AndroidManifest.xml
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <manifest xmlns:android="http://schemas.android.com/apk/res/android"
        package="com.example.sqlitedb"
        android:versionCode="1"
        android:versionName="1.0" >
    
        <uses-sdk
            android:minSdkVersion="8"
            android:targetSdkVersion="15" />
    
        <application
            android:icon="@drawable/ic_launcher"
            android:label="@string/app_name"
            android:theme="@style/AppTheme" >
            <activity
                android:name="com.example.sqlitedb.SqliteMainActivity"
                android:label="@string/title_activity_sqlite_main" >
                <intent-filter>
                    <action android:name="android.intent.action.MAIN" />
    
                    <category android:name="android.intent.category.LAUNCHER" />
                </intent-filter>
            </activity>
            <activity
                android:name="com.example.sqlitedb.AddInfoActivity"
                android:label="@string/title_activity_add_info" >
            </activity>
            <activity
                android:name="com.example.sqlitedb.ViewInfoActivity"
                android:label="@string/title_activity_view_info" >
            </activity>
        </application>
    </manifest>
    
    SqliteMainActivity:

    This is our launcher activity class. It has two buttons : Add button and View button. It performs following tasks:
    • Load layout file for this class
    • If Add button is clicked, a new intent is created. A new activity, named as AddInfoActivity, is started using this intent.
    • If View button is clicked, intent is created. A new activity, named as ViewInfoActivity, is started using this intent.
    Here is the detail of this class:

    SqliteMainActivity.java
    Code:
    package com.example.sqlitedb;
    
    import android.os.Bundle;
    import android.app.Activity;
    import android.content.Intent;
    import android.view.Menu;
    import android.view.View;
    import android.view.View.OnClickListener;
    import android.widget.Button;
    
    public class SqliteMainActivity extends Activity {
    
    	private Button btnAdd;
    	private Button btnView;
        @Override
        public void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_sqlite_main);
            
            btnAdd = (Button) findViewById(R.id.btnAdd);
            btnView = (Button) findViewById(R.id.btnView);
            
            btnAdd.setOnClickListener(new OnClickListener() {
    			
    			public void onClick(View v) {
    				// TODO Auto-generated method stub
    				Intent intent =  new Intent(SqliteMainActivity.this, AddInfoActivity.class);
    				startActivity(intent);
    			}
    		});
            
            btnView.setOnClickListener(new OnClickListener() {
    			
    			public void onClick(View v) {
    				// TODO Auto-generated method stub
    				
    				Intent intent =  new Intent(SqliteMainActivity.this, ViewInfoActivity.class);
    				startActivity(intent);
    			}
    		});
        }
    
        @Override
        public boolean onCreateOptionsMenu(Menu menu) {
            getMenuInflater().inflate(R.menu.activity_sqlite_main, menu);
            return true;
        }
    }
    
    The layout file for the above activity contains two buttons.
    • First Button is used to start the activity of adding information.
    • Second button is used to start the activity of displaying information.
    Details are as follows:

    activity_sqlite_main.xml
    Code:
    <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" >
    
        <Button
            android:id="@+id/btnAdd"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignParentTop="true"
            android:layout_centerHorizontal="true"
            android:layout_marginTop="106dp"
            android:text="Add" />
    
        <Button
            android:id="@+id/btnView"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignLeft="@+id/btnAdd"
            android:layout_centerVertical="true"
            android:text="View" />
    </RelativeLayout>
    
    AddInfoActivity

    This class is responsible for inserting employee information in sqlite database. It is used for performing following tasks:
    • Load layout file for this class.
    • If Add Info button is clicked, extract the employee id, employee name and employee designation information and store it in one integer and two string variables.
    • Call a function named as addInfo. Previously retrieved employee id, employee name and employee designation information are sent to addInfo function.
    • In addInfo function, create a Dbhandler instance. Using this instance, add the employee information to the employeeInfo table of the employeeDb database.
    Here is the detail of the AddInfoActivity class.

    AddInfoActivity.java
    Code:
    package com.example.sqlitedb;
    
    import android.os.Bundle;
    import android.app.Activity;
    import android.util.Log;
    import android.view.Menu;
    import android.view.View;
    import android.widget.Button;
    import android.widget.EditText;
    
    public class AddInfoActivity extends Activity {
    	
    	private EditText edEmpId;
    	private EditText edEmpName;
    	private EditText edEmpDesignation;
    	private Button btnAddInfo;
    	int empId;
    	String empName;
    	String empDesignation;
    	
        @Override
        public void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_add_info);
            
            btnAddInfo = (Button) findViewById(R.id.buttonAddInfo);
          
          btnAddInfo.setOnClickListener(new View.OnClickListener() {
    		
    		public void onClick(View arg0) {
    			// TODO Auto-generated method stub
    		      edEmpId = (EditText) findViewById(R.id.editTextId);
    		      edEmpName = (EditText) findViewById(R.id.editTextName);
    		      edEmpDesignation = (EditText) findViewById(R.id.editTextDesignation);
    		      
    		      empId = Integer.valueOf(edEmpId.getText().toString());
    		      empName = edEmpName.getText().toString();
    		      empDesignation = edEmpDesignation.getText().toString();
    			Log.d("test value", empId+" "+empName+" "+empDesignation);
    			addInfo(empId, empName, empDesignation);
    		}
    	});
        }
        
        public void addInfo(int id, String name, String designation)
        {
        	DbHandler db =  new DbHandler(this);
        	db.addEmployeeInfo(new EmployeeInfo(id, name, designation));	
        }
        
        @Override
        public boolean onCreateOptionsMenu(Menu menu) {
            getMenuInflater().inflate(R.menu.activity_add_info, menu);
            return true;
        }    
    }
    
    The layout file of above class contains:
    • 3(three) TextView controls
    • 3(three) EditText controls
    • 1(one) button control
    Why should we use these controls?
    • The TextView controls are used to display 3(three) static text: “Id”, “Name” and “Designation”.
    • The EditText controls are used to take input from user. 3 EditText will be used to take 3 information : employee id, name and designation respectively.
    • The Button control is to handle the submission of employee data.
    Here is the detail of the layout file.

    activity_add_info.xml

    Code:
    <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
        xmlns:tools="http://schemas.android.com/tools"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >
    
        <TableLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_alignParentLeft="true"
            android:layout_centerInParent="true" >
    
            <TableRow
                android:id="@+id/tableRow1"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content" >
    
                <TextView
                    android:id="@+id/textViewId"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:text="Id" />
    
                <EditText
                    android:id="@+id/editTextId"
                    android:layout_width="fill_parent"
                    android:layout_height="wrap_content"
                    android:inputType="textNoSuggestions" >
    
                    <requestFocus />
                </EditText>
            </TableRow>
    
            <TableRow
                android:id="@+id/tableRow2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content" >
    
                <TextView
                    android:id="@+id/textViewName"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:text="Name" />
    
                <EditText
                    android:id="@+id/editTextName"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:inputType="textNoSuggestions" />
            </TableRow>
    
            <TableRow
                android:id="@+id/tableRow3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content" >
    
                <TextView
                    android:id="@+id/textViewDesignation"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:text="Designation" />
    
                <EditText
                    android:id="@+id/editTextDesignation"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:inputType="textNoSuggestions" />
            </TableRow>
    
            <TableRow
                android:id="@+id/tableRow4"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content" >
    
                <Button
                    android:id="@+id/buttonAddInfo"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:gravity="center_vertical|center_horizontal|fill_horizontal"
                    android:text="Add Info" />
            </TableRow>
        </TableLayout>
    </RelativeLayout>
    
    ViewInfoActivity

    This class is responsible for displaying employee information from the employeeInfo table of employeeDb database. The following tasks are performed:

    • Load layout file for this class.
    • Create an instance of DbHandler class.
    • Create a List instance of EmployeeInfo class named as employeeInfo.
    • Create an arraylist named employeeList.
    • Using getEmployeeList function, retrieve all employee information and store in employeeInfo List.
    • In a for loop, create a HashMap<String, String> instance named as empInfo to store employee information in a key value pair. Put employee name and designation in this HashMap as we will display the name and designation of each employee in the ListView. But how will we get the employee name and designation? To retrieve this information, use getEmployeeName and getEmployeeDesignation function of EmployeeInfo class. Then add the HashMap empInfo in the List employeeList.
    • Create an instance of ListAdapter named as adapter using the sublclass SimpleAdapter. Prototype of our used SimpleAdapter is :

      android.widget.SimpleAdapter.SimpleAdapter(Context context, List<? extends Map<String, ?>> data, int resource, String[] from, int[] to)
    • Use setListAdapter function to provide the cursor for the listview.
    Here is the detail of the ViewInfoActivity class.

    ViewInfoActivity.java

    Code:
    package com.example.sqlitedb;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import android.app.ListActivity;
    import android.os.Bundle;
    import android.util.Log;
    import android.widget.ListAdapter;
    import android.widget.SimpleAdapter;
    
    public class ViewInfoActivity extends ListActivity {
    	
    	private static final String TAG_ID = "id";
    	private static final String TAG_NAME = "name";
    	private static final String TAG_DESIGNATION = "designation";
    	
    	ArrayList<HashMap<String, String>> employeeList;
    	
    	
        @Override
        public void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_view_info);
            
            Log.d("Employee: ", "Reading employee information.");
            DbHandler db =  new DbHandler(this);
            List<EmployeeInfo> employeeInfo = db.getEmployeeList();    
    		employeeList = new ArrayList<HashMap<String, String>>();
     
            for (EmployeeInfo info : employeeInfo) {
                String log = "Id: "+info.getEmployeeId()+" ,Name: " + info.getEmployeeName() + " ,Designation: " + info.getEmployeeDesignation();
                    
            Log.d("Employee Info: ", log);
            
    		HashMap<String, String> empInfo = new HashMap<String, String>();
    		
           empInfo.put(TAG_NAME, info.getEmployeeName());
           empInfo.put(TAG_DESIGNATION, info.getEmployeeDesignation());   
           employeeList.add(empInfo);
            
            }
            
        	ListAdapter adapter = new SimpleAdapter(
    				ViewInfoActivity.this, employeeList,
    				R.layout.list_item, new String[] { TAG_NAME,
    						TAG_DESIGNATION }, new int[] {
    						R.id.Name, R.id.Designation });
    		setListAdapter(adapter);   
        }
    }
    
    The laoyout file of this class contains a listview. It is used to display the employee information in a list manner.

    Here is the code block for the layout file:

    activity_view-info.xml
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
        android:layout_width="fill_parent"
        android:layout_height="fill_parent"
        android:orientation="vertical">
        <ListView
            android:id="@android:id/list"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"/>
    </LinearLayout>
    
    The list_item layout file contains two TextiView controls.

    Why to use these controls?



    Every single item of the listview consists of information of Employee name and employee designation. The layout file for every single employee contains two TextView controls to display employee name and employee designation. Here is the code block.

    list_item.xml
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical"
        android:padding="10dp"
        android:paddingLeft="10dp"
        android:paddingRight="10dp" >
    
        <TextView
            android:id="@+id/Name"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:paddingBottom="2dip"
            android:textColor="#000000"
            android:textStyle="bold" />
    
        <TextView
            android:id="@+id/Designation"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:gravity="left"
            android:textColor="#5d5d5d" />
    </LinearLayout>
    
    This is a practical example to perform sqlite operation from user interface. To improve the quality, some exception handling needs to be added for some conditions like:
    • If any problem is occurred in database operation
    • If add button is clicked without all field is filled up
    • If view button is clicked but no information is available to show.
    Except the above mentioned exceptions, this tutorial will perform accurately for insert and view operation of sqlite database. Hope it will be helpful for all.
     
    Last edited by a moderator: Jan 21, 2017
    PREETHAMND and (deleted member) like this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice