Wednesday, March 20, 2013

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



35 comments:

  1. Hai,
    I am a fresher.i want one program.just having two edit text and one button.In first edit text we entered the date and the second edit text just enter one name after that click the ok button it will be stored in database.
    Then another one edittext and one button in the same layout.In this edittext enter the date and click the search button which fetch the match result from that date and display the result below the same activity in list view manner.please send my mail id deesh003@gmail.com
    Regards
    Satheesh.R

    ReplyDelete
  2. Nice post.Give it up. Thanks for share this article. For more visit:Web App Development

    ReplyDelete
  3. I run the project in emulator.
    it shows the screen with the Spinner control & allows me to choose values.
    after choosing a value ,

    Unfortunately Database has stopped and application closes.

    Please help to move further with this error.
    Thanks in Advance.

    ReplyDelete
    Replies
    1. Uninsall the application from emulator by clicking Menu>settings>app>uninstall and then run the application again.

      Delete
    2. I tried Uninstalling the application and run it again via Emulator but still getting the same error

      Delete
    3. Then try renaming the database file in the asset folder and give another name also change the name in the code (private static String DB_NAME = "Sample_databse";). If that does not work uncomment all the toast which are given in the code and see anything is displayed in the toast or not

      Delete
    4. This comment has been removed by the author.

      Delete
    5. This comment has been removed by the author.

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. The source you provided worked out fine for me peter. Now i started up with my Android Application Development. Thanks a lot.

    One more genral issue i'm facing is, In my eclipse ADT Environment, i can't able to open my Android SDK manager by either clicking on the icon in the toolbar or by clicking Window---> Android SDK manager(In these 2 cases it Shows : Starting Android SDK Manager, Initializing... SDK manager will show up shortly, but nothing happens) or by clicking SDKmanager.exe directly inside the root folder(a command window flashes one second and disappers)

    Can u help me with this issue..?

    Thanks in Advance!

    Regards
    Mohan M
    mmohanmsc@live.com




    ReplyDelete
  6. Hi Peter , its me again.,

    Still a newbie to this Andro-World, now my requirement is now i wanna create an application as that of yours, but i need more is i wanna Select,Insert,Update & Delete from the SQL-Lite database (With 4 buttons & also 4textboxes to update data). Also i need to know whether any designed templates available for table layout's so that we can make use of them in our coding?

    Give me some idea to start-up with this & also about the design in table layouts..!

    Thanks in Advance.!
    Regards
    Mohan M
    mmohanmsc@live.com

    ReplyDelete
  7. Please Tell me How to Custom Listview in this project same db but in mainactivity i want to just show records in custom list view and with edit ,delete and new record insert facility please ge me sample like this i am very thankful to you .Thanks Alot once again.please mail me at muhammamwaqas2@yahoo.com

    ReplyDelete
    Replies
    1. There are many such type database tutorials are available in websites. The following is a tutorial which I think will fit for your need. Source code is also available for this.Only slight modification will be needed. There are so many other tutorials also of the same type just search "android database tutorial" in Google.
      http://javapapers.com/android/android-sqlite-database

      Delete
  8. Hi,
    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

    getting array can't be resolved and can't find it defined anywhere.

    Thanks for the work

    ReplyDelete
  9. The R.java file gets completely corrupted when I clean it so something is missing, probably in the resources.
    It only gets corrupted after I add the string file and I clean it otherwise it's the array problem above to begin with.

    ReplyDelete
  10. Ok fixed it, had to take the main.xml file out of the menu folder and put your activity_main also in there in that was in your download folder, normally I have that in the layout folder.

    ReplyDelete
  11. Works like a charm!

    ReplyDelete
  12. It's been a great work from the writer to write this post. it is very helpful for me as I am also engaged with Android applications. This tutorial of database and spinner is truly nice. It is very helpful to the android application developers to deliver best service and support.

    ReplyDelete
  13. Thanks for your great work.

    However, there are some minor mistakes on your post here. I didn't download your source codes from the link but I did it by copying what you put on your page here. The mistakes are as follow:
    1) Missing parentheses at the end of the code: CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US'
    2) Missing code in the strings.xml: <string name="action_settings">Settings</string>
    3) Misspell database name in the DatabaseHelper.java: private static String DB_NAME = "Sample_Databae";

    After correcting those minor mistakes, the application runs fine.

    Thanks again.

    ReplyDelete
  14. Thanks for the good tutorial.
    Able to compile especially after Safuan Abdul Latif fixed.
    However, i encounter runtime errors, database has stopped.
    Kindly seek your advice.

    ReplyDelete
    Replies
    1. Hi Glen,
      I think there are some errors in the code I have pasted. Just see the comments. Please download the source code given at the end. I think it should work.
      Thank You

      Delete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Hi... project works fluent. The Downloaded workspace works just perfectly !! Yup there are some errors in the code over here, but the downloaded Workspace is just great !!

    ReplyDelete
  17. Also how to insert a record by taking input from user from edittext and add the record to the Database

    ReplyDelete
  18. after trying so many days, i finally can run on my bluestack now, but when i select male or female it doesnt show data, buton doesnt work or database didnt link?? any1 have this problem, how to solve this

    ReplyDelete
    Replies
    1. Just downlo0ad the source code it should work!
      http://rapidshare.com/files/2602646334/Database.zip

      Delete
    2. ty for quick response, i download as u said when i import it there are 4 erors in mainactivity.java

      The project was not built since its build path is incomplete. Cannot find the class file for java.lang.

      The type java.lang.Object cannot be resolved. It is indirectly referenced from required .class files

      Unable to resolve target 'android-17'

      Delete
    3. Are you using eclipse with latest version of Android tools? whih version of Android you are using as target when creating a new Application?

      Delete
    4. i think it's my adt, its old 22,3.0, i will update and post result, i hope i can do it now :)

      Delete
  19. 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" <--- i'm confuse here too, should i make folder in database->data>data>com.example.database>databases and paste sample_database?

    ReplyDelete
    Replies
    1. No the Application will load it in to this location when running. You dont have to do anything

      Delete
  20. Everything is working fine now, thank you so much Mr. Arise,
    how to add scroll on result if it is more than one page, and i want to hide sex on result, can u help me again :P

    ReplyDelete

Please give your valuable comments so as to know whether this blog is useful to you.You can also write helpful Android Development Syntax or Shortcuts or Tips.