A Basic Android Database Example

In this post we shall develop a basic Android Database Example.

Every Android phone contains an inbuilt SQLite database. Therefore, no separate setup or configuration is required.

In this post we shall create a SQLite table, insert some data into it, and also perform a select query.The table contains two fields bookname of type text, and price of type int.
This application contains three buttons Select,Insert and Create for selecting, inserting and creating the table respectively. It also contains two EditBoxes for supplying the Book Name and Price values. There is a TextView for providing status messages.Here is a picture of the User Interface:-



The main classes involved are:-
android.database.sqlite.SQLiteOpenHelper
This class needs to be subclassed to provide for creation of the database.
It has two methods
    @Override
    public void onCreate(SQLiteDatabase database) {
//Called when the database is created.       
   }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldversion, int newversion) {
//Called when the database is upgraded.
       
    }

 android.database.sqlite.SQLiteDatabase
Encapsulates a connection to a SQLite database and provides methods for sending queries to the Database and recovering the result.


android.database.Cursor
This class provides methods for accessing the results of a select query.It can be compared to the java.sql.ResultSet.
Here is the code for the DatabaseHelper

DatabaseHelper.java

package project.hypatia.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    public DatabaseHelper(Context context) {
        super(context, "hypatiadata", null, 1);
//hypatiadata is the name of the database
// 1 is the version no.
//The null parameter can be used to pass a CursorFactory. We are not using it, so it is null.
        // TODO Auto-generated constructor stub
    }

        @Override
    public void onCreate(SQLiteDatabase database) {
        // TODO Auto-generated method stub
       

    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldversion, int newversion) {
        // TODO Auto-generated method stub
       
    }

}

This is the code for the ProjectActivity

It contains a subclass DataHandler which contains the click events for the three buttons.

The select button

//*********************************************************************************

  if(b.equals(bttnselect))
                {
                    try
                    {
                        String bookname="" + txtBookName.getText();
                        bookname=bookname.replaceAll("'", "''").trim();
                       
                   
                        String[] columns={"Price"};
                        String selection="BookName='"+ bookname + "'";
                       
                    cursor=    database.query("HypatiaBooks", columns, selection, null, null, null, null);

//The Parameters mean

//1) The Name of the Table

//2) A string array containing the fields to be queried.

//3) A string containing the selection criteria

//4) A string array of selection arguments

//5) groupby statement

//6) having

//7) orderby

//The ones not being used are null
                    if(cursor==null)
                    {
                        txtResult.setText("No Data Found");
                        return;
                    }
                    if(cursor.moveToFirst())
                    {
                    int price=cursor.getInt(0);
                    txtPrice.setText("" + price);
                        txtResult.setText("Data Selected");
                    }
                    else
                        txtResult.setText("No Data Found");
                        cursor.close();
                    }
                    catch (Exception ex) {
                        // TODO: handle exception
                        txtResult.setText(ex.getMessage());
                    }
                }

//*********************************************************************************

the cursor provides methods for accessing the data fields, unlike jdbc the indexing starts at 0.

Here is the complete code

HypatiaAndroidProjectActivity.java

package project.hypatia.database;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.database.*;
import android.database.sqlite.*;
public class HypatiaAndroidProjectActivity extends Activity {
    /** Called when the activity is first created. */
    Button bttncreate,bttninsert,bttnselect;
    EditText txtBookName,txtPrice;
    TextView txtResult;
   
    private Cursor cursor;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        bttncreate=(Button)findViewById(R.id.button1);
        bttninsert=(Button)findViewById(R.id.button2);
        bttnselect=(Button)findViewById(R.id.button3);
        txtBookName=(EditText)findViewById(R.id.txtBookName);
        txtPrice=(EditText)findViewById(R.id.txtPrice);
        txtResult=(TextView)findViewById(R.id.txtOutput);
        DataHandler dh=new  DataHandler();
        DatabaseHelper dhh=new DatabaseHelper(this);
       database= dhh.getWritableDatabase();
       bttncreate.setOnClickListener(dh);
       bttninsert.setOnClickListener(dh);
       bttnselect.setOnClickListener(dh);
    }
    //****************************************************************
   SQLiteDatabase database;
    class DataHandler implements OnClickListener
    {

        @Override
        public void onClick(View view) {
            // TODO Auto-generated method stub
       
           
                Button b=(Button)view;
                if(b.equals(bttncreate))
                {
                    try
                    {
                        database.execSQL( "create table HypatiaBooks(bookname text primary key,price integer not null )");
                        txtResult.setText("Table Created");
                    }
                    catch (Exception ex) {
                        // TODO: handle exception
                        txtResult.setText(ex.getMessage());
                    }
                }
                if(b.equals(bttninsert))
                {
                    try
                    {
                        String bookname="" + txtBookName.getText();
                        bookname=bookname.replaceAll("'", "''").trim();
                        String price="" + txtPrice.getText();
                        price=price.replaceAll("'","''").trim();
                        database.execSQL("insert into HypatiaBooks values('" + bookname + "'," + price + ")");
                        txtResult.setText("Data Inserted");
                    }
                    catch (Exception ex) {
                        // TODO: handle exception
                        txtResult.setText(ex.getMessage());
                    }
                }
               
                if(b.equals(bttnselect))
                {
                    try
                    {
                        String bookname="" + txtBookName.getText();
                        bookname=bookname.replaceAll("'", "''").trim();
                       
                   
                        String[] columns={"Price"};
                        String selection="BookName='"+ bookname + "'";
                       
                    cursor=    database.query("HypatiaBooks", columns, selection, null, null, null, null);
                    if(cursor==null)
                    {
                        txtResult.setText("No Data Found");
                        return;
                    }
                    if(cursor.moveToFirst())
                    {
                    int price=cursor.getInt(0);
                    txtPrice.setText("" + price);
                        txtResult.setText("Data Selected");
                    }
                    else
                        txtResult.setText("No Data Found");
                        cursor.close();
                    }
                    catch (Exception ex) {
                        // TODO: handle exception
                        txtResult.setText(ex.getMessage());
                    }
                }
            }
           
           
           
        }
      
      
          
      
    }
   
    //****************************************************************

 Here is the main.xml

<?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" >


    <EditText
        android:id="@+id/txtBookName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <requestFocus />
    </EditText>


    <EditText
        android:id="@+id/txtPrice"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:inputType="number"
         />


    <TextView
        android:id="@+id/txtOutput"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />



    <Button
        android:id="@+id/button1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/bttncreate" />



    <Button
        android:id="@+id/button2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/bttninsert" />



    <Button
        android:id="@+id/button3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/bttnselect" />

  

</LinearLayout>

Here is the design view:-

The application in Eclipse

strings.xml

 

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

    <string name="hello">Hypatia Basic Android Database </string>
    <string name="app_name">Hypatia Basic Android Database </string>
    <string name="b1">Insert</string>
    <string name="b2">Select</string>
    <string name="bttnselect">select</string>
    <string name="bttninsert">Insert</string>
    <string name="bttncreate">create</string>

</resources>

The APK file can be downloaded at http://hypatiasoftwaresolutions.com/HypatiaAndroidProject.zip

Next, we shall focus on using the Camera in an Android Device.