d. Databse and Spinner Tutorial

In this Tutorial you will learn how to retrieve the Data from a Database through Android Application and how to use a spinner to select database data.(spinner is nothing but the modified form of drop down list used in html) The Database supported in Android is "SQLITE", which is a light open source Database which can be used for data insertion in Linux and Windows. SQL Database will run in all Android Devices without any pre installation or setup.

(TIP)source code is avialble at the bottom

There are two method by which you can use a Database in Android. One is by creating a Database through the application and insetion of data through the Java code itself. The other one is by attaching a Database with data to the application. Even though the first method seems to be simpler, if you want to insert say 50 data in to the database it is quite difficult and your code will look shabby. Editing the data and finding the data in code will also be difficult. In the second method we add data to the Database externally using a Database Manager and and attach it to the application in "assets" folder. The data insertion using this method is very simple and fast, even any external person can do this job.Here we are going to learn the second method

The first thing to do is downloading a suitable SQLITE database manger. I will suggest you to use Database Manger comes as a Firefox extension. It very low in size and easy to use with all facilities. Just search SQLite manger in Firefox addons (Tools>addons in Firefox). After getting the Database Manger we have to insert data in to it.

Click Tools>SQLite manger in Firefox and Click create database and give the name "Sample_database"
then create a new table named data as shown in the figure below.

Give the table name as "data". Now insert the following column names respectively, "name","age","sex"and _id"
click ok to create the table as shown below.

From the "browse and search" tab click "add" to add a new record and give the details as follows name:arise,age:31,sex:Male,_id:1 click ok to create the record.

Similarly add the following data to the table
name:Raman,age:40,sex:Male,_id:2 then,
name:Sita,age:35, sex:Female,_id:3

Inorder the database to be worked on Android we have to add a table named "Android Metadata". We can add this through script. "Click Execute SQL" tab and copy pase the following two lines one by one, as shown in figure below.


CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US'

INSERT INTO "android_metadata" VALUES ('en_US')

Now our database is ready to attach with the application. The database file will have sqlite extension it is not required so delete the extension.

Now create a new project named "Database" with package name "com.example.database"

Here we are creating a spinner for getting the data from user and a button to execute the Database operation. The result will be displayed in a Table Layout. A TextView will be used for showing the instruction to the user.
copy the following code in to the activity_main.xml file
Also copy the Database file just we have created named "Sample_database" in to assets folder.

<LinearLayout 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:orientation="vertical"
    tools:context=".MainActivity" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Find the Database data based on gender" />

    <Spinner
        android:id="@+id/spinner1"
        android:layout_width="170dp"
        android:layout_height="wrap_content"
        android:layout_marginTop="15dp"       
         />

    <Button
        android:id="@+id/button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"      
         android:onClick="RunDatabse"
        android:text="Button" />
   

    <TableLayout
        android:id="@+id/tableLayout1"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="13dp"      
        android:stretchColumns="*"
         >
                
    </TableLayout>

</LinearLayout>

Now in the MainActivity.java file copy the following code. the step by step information is given as comments so read the comments.

package com.example.database;

import java.io.IOException;
import android.app.Activity;
import android.content.res.Resources;
import android.database.SQLException;
import android.graphics.Typeface;
import android.os.Bundle;
import android.view.View;
import android.widget.Adapter;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.Toast;

public class MainActivity extends Activity {
    private String[] mystring;//creating a string array named mystring
    Spinner samplespinner; //Assigning a name for spinner
    String[] DataToDB;//defining a string array named DataToDB
    String[]result_array;//defining an array for saving the results obtained from DB
    String Selecteditem;//Defining a string for storing selected item from spinner

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        ///////////////////SPINNER/////////////////////////
       
        ArrayAdapter sampleadapter;//Assigning a name for ArrayAdapter
       
        Resources res = getResources();//Assigning a name for Resources
       
        mystring = res.getStringArray(R.array.Sex);//getting the array items to string named my string
        //mystring is an array which is defined on the top
       
        samplespinner= (Spinner) findViewById(R.id.spinner1); //samplespinner is defined in the top      
        //samplespinner is the name given to the spinner at the top
       
        sampleadapter = new ArrayAdapter<String>(this,
                                        android.R.layout.simple_spinner_item, mystring);
        samplespinner.setAdapter(sampleadapter);
      
        samplespinner.setOnItemSelectedListener(new OnItemSelectedListener()
            {
               public void onItemSelected(AdapterView<?> arg0, View arg1,
                                                        int arg2, long arg3)
                {
                //Toast.makeText(getBaseContext(), spVIA.getSelectedItem().toString(),
                //Toast.LENGTH_LONG).show();
               
                Selecteditem = samplespinner.getSelectedItem().toString();
                                        }
                        public void onNothingSelected(AdapterView<?> arg0)
                        {
                                        // TODO Auto-generated method stub                
                        }
            });}   
    //////////////////////////SPINNER ENDS///////////////////////////////////////////////////
       
    ////////////////////////////DATABASE////////////////////////////////////////////////////
        public void RunDatabse(View view) {
            DatabaseHelper myDbHelper = new DatabaseHelper(this);
            //Toast.makeText(this,Selecteditem,Toast.LENGTH_LONG).show();
           
            try {
           
            myDbHelper.createDataBase();
            DataToDB = myDbHelper.ReadFromDB(Selecteditem.trim());//sending the selected spinner item to database for query
            TableLayout tablelayout1= (TableLayout)findViewById(R.id.tableLayout1);
            tablelayout1.removeAllViews();
            //Adding raws to the Table dynamically
            for(int i=0;i < DataToDB.length;i++)//repeat adding raws to the table layout till the string array length ends
            {
              
                  
                    TableRow tR = new TableRow(this);
                    result_array = DataToDB[i].split(","); //splitting the comma separated string array
                       //result_array is the string array for storing result.it is defined in top          
                    tR.setPadding(5,5,5,5); //setting spacing between table raws            
                    TextView tV_txt1 = new TextView(this);//adding textViews to each Table cell
                    TextView tV_txt2 = new TextView(this);
                    TextView tV_txt3 = new TextView(this);
                            
                             
                    tV_txt1.setText(result_array[0]);//setting the first array item to text view
                   
                   
                    tV_txt2.setText(result_array[1]);
                   
                   
                    tV_txt3.setText(result_array[2]);
                  
                   
                              
                    tR.addView(tV_txt1);
                    tR.addView(tV_txt2);
                    tR.addView(tV_txt3);
                   

                    tablelayout1.addView(tR);//Adding all Table raws to the Table
               
            }
           
    }
     catch (IOException ioe) {
           
            throw new Error("Unable to create database");
           
            }
           
            try {
           
            try {
                myDbHelper.openDataBase();
            } catch (java.sql.SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
           
            }catch(SQLException sqle){
           
            throw sqle;
           
            }
        }
}


Here what we are doing is first loading the data stored in the Strings.xml file in to the spinner. The value of spinner items will be stored in to an string and it will be send to the database for query. The database will be read using code through a new java file which will be explained later.

Now copy the following code in to the strings.xml file in the values folder,

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="app_name">database</string>
    <string name="hello_world">Hello world!</string>
    <string name="menu_settings">Settings</string>
    <item type="string" name="databaseVersion" format="integer">1</item>
<string-array name="Sex">
    <item >Male</item>
    <item >Female</item>
   
</string-array>
</resources>

Here the string array name is "SEX" and the vlaues of array will be (Male, Female) which will be given as items. So What we are going to do is when the user select the Gender ie. Male or Female the data corresponding in the database will be shown to the user.

Now create a new CLass (Java file) called "DatabaseHelper" by right clicking on the package name "com.example.database" and select new>class give the above name for the java file.
Now cpy pase the following code in to the java file,

package com.example.database;


import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class DatabaseHelper extends SQLiteOpenHelper{
   
//The Android's default system path of your application database.
private static String DB_PATH = "/data/data/com.example.database/databases/";


private static String DB_NAME = "Sample_databse";//name of your Database

private SQLiteDatabase myDataBase;

private final Context myContext;

/**
  * Constructor
  * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
  * @param context
  */
public DatabaseHelper(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;
}

/**
  * Creates a empty database on the system and rewrites it with your own database.
  * */
public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();




if(dbExist)
   {
    //do nothing - database already exist
   
    //Toast.makeText(this.myContext,"Db Exists",Toast.LENGTH_LONG).show();
   
   
    }
else{

        //By calling this method and empty database will be created into the default system path
        //of your application so we are gonna be able to overwrite that database with our database.
        this.getReadableDatabase();
       
        //Toast.makeText(this.myContext,"Create new DB",Toast.LENGTH_LONG).show();
       
        try {
       
        copyDataBase();
       
        //Toast.makeText(this.myContext,"Copy DB",Toast.LENGTH_LONG).show();
       
        } catch (IOException e) {
       
        throw new Error("Error copying database");
       
        }
    }


}


    public String[] ReadFromDB(String Selecteditem) {
       // Retrieve a string array of all our Data
    //Toast.makeText(this.myContext,"Read From DB",Toast.LENGTH_LONG).show();
   
    ArrayList temp_array = new ArrayList();
    String[] notes_array = new String[0];
    //The SQL Query
 
  
    String sqlQuery = "SELECT * FROM data where sex = '"+ Selecteditem +"'"  ;      
   
    //Here we are querying the databse with the selected item from the spinner. Only the data with the selected item will be retrieved from the database          
   
    //Define database and cursor
   
    //Toast.makeText(this.myContext,sqlQuery,Toast.LENGTH_LONG).show();
   
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery(sqlQuery, null);

    //Loop through the results and add it to the temp_array
        //You shoud give the column names in the Database exactly here below        
             
              if (c.moveToFirst()){
                  do{
                        temp_array.add(  c.getString(c.getColumnIndex("name")) +
                                       "," + c.getString(c.getColumnIndex("age")) +
                                       "," + c.getString(c.getColumnIndex("sex"))                                     
                                     );
                       
              //Toast.makeText(this.myContext,c.getString(c.getColumnIndex("TIME")),Toast.LENGTH_LONG).show();
             
             
              
             
        }while(c.moveToNext());
     }
    //Close the cursor
    c.close();
    //Transfer from the ArrayList to string array
    notes_array = (String[]) temp_array.toArray(notes_array);
    //Return the string array
    return notes_array;
  }
   

    
/**
  * Check if the database already exist to avoid re-copying the file each time you open the application.
  * @return true if it exists, false if it doesn't
  */
private boolean checkDataBase(){

        SQLiteDatabase checkDB = null;
       
        //Toast.makeText(this.myContext,"check db",Toast.LENGTH_LONG).show();
       
        try{
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
       
        //Toast.makeText(this.myContext,myPath,Toast.LENGTH_LONG).show();
           
           
        }catch(SQLiteException e){
       
            //Toast.makeText(this.myContext,"DB Does not exists",Toast.LENGTH_LONG).show();
           
        //database does't exist yet.
       
        }
       
        if(checkDB != null){
       
            checkDB.close();
       
        }
       
        return checkDB != null ? true : false;
}

/**
  * Copies your database from your local assets-folder to the just created empty database in the
  * system folder, from where it can be accessed and handled.
  * This is done by transfering bytestream.
  * */
private void copyDataBase() throws IOException{

        //Open your local db as the input stream
        InputStream myInput = myContext.getAssets().open(DB_NAME);
       
        // Path to the just created empty db
        String outFileName = DB_PATH + DB_NAME;
       
        //Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);
       
        //transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer))>0){
        myOutput.write(buffer, 0, length);
        }
       
        //Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();

}

public void openDataBase() throws SQLException{

        //Open the database
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
       



}

@Override
public synchronized void close() {

    if(myDataBase != null)
    myDataBase.close();
   
    super.close();

}

@Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

    // Add your public helper methods to access and get content from the database.
    // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
    // to you to create adapters for your views.

}

Android cannot execute the database directly from the asset folder so it first load it in to the system folder and run it from there. Your database will be stored in the following location "/data/data/com.example.database/databases/" with the name "Sample_databse"


the data is retrived from the database using cursor the following code
"SELECT * FROM data where sex = '"+ Selecteditem +"'"  

The criteria for selecting the data is passing through the string named  "Selecteditem" which was used to store the selected item from the spinner. So if the criteria ie the sex column matches the selected item in spinner for example "Male" all the data having the gender as "male" will be retrived and shown to the user. In the cursor you should give the exact column name given in the Database (See he comment in the code) Then we store it in a string array and pass it back to the MainActivity to show the result in the Table Layout. Here we don't know how many data will be retrieved so we use a dynamic (automatically adding raws to the Table layout) method to display the data. Please note that in Table layout we have to add TextViews to each cell in order to display the text.

When you run the application you will get the result as follows,

Now we have learn how to use a spinner,SQLite database and Table Layout.
You may download the code by clicking the following link

DOWNLOAD SOURCE CODE