Tuesday, 2 April 2013

Android SQLite Tutorial


 => Just like iOS android have in build sqlite database. In this tutorial, you learn how SQLite databases are designed and manipulated.

 => SQLite is a lightweight relational database engine. Instead of the heavyweight server-based databases, each SQLite database is stored within a single file on disk.

  => Android applications can choose to store private application data in a SQLite database.

  => In SQLite data are stored in table format. A table has columns with different data types. Each row in a table represents a data record.  
        
  => Lets take one example, Let’s say we have a product database with a table called Products.


  =>The Products  table might have 3 typed columns:

                          1) ProductID (number)
                          2) ProductName (string)
                          3) Pproductprice (string)

    => We could then add a record to the data base for an Product named computer.
    => We can do following operation on Products table :

                          1) ADD (INSERT query)
                          2) MODIFIED (UPDATE query)
                          3) DELETE (DELETE query)      

     => Now we perform create, modify and delete operation with Products table.
      

       (1) Create table

                                             
                    CREATE TABLE Products (id INTEGER PRIMARY KEY AUTOINCREMENT,                        
 Productname TEXT NOT NULL, Productprice TEXT NOT NULL );



       (2) INSERT into table       

                                
                     INSERT into Products(Productname, Productprice)
                       VALUES('Computer', '50000'); 

       (3) UPDATE table


           SQLiteDatabase db;
           db.update("Products", values, whereClause, whereArgs);



  Now we explain with example. We have to achieve following.












- create new android project AndroidAdvanceSqlite.


  - create class AndroidAdvanceSqliteActivity.java, this class describe two options add record and view record from sqlite database. For this we have to create main.xml like following.


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

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="15dp"
        android:layout_marginTop="10dp"
        android:gravity="center"
        android:text="Android Advance SQLite Tutorial"
        android:textSize="18dp"
        android:text />

    <Button
        android:id="@+id/btn_add"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:text="Add Products"
        android:text />

    <Button
        android:id="@+id/btn_view"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:text="View Products"
        android:text />

 <Button
        android:id="@+id/btn_contact"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:onClick="contactMe"
        android:text="Contact me for query"
        android:textStyle="bold" />

</LinearLayout>




  - We have to create two buttons for database operation.

AndroidAdvanceSqliteActivity.java


public class AndroidAdvanceSqliteActivity extends Activity implements
        OnClickListener {

    private Button btn_add, btn_view, btn_search;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        btn_add = (Button) findViewById(R.id.btn_add);
        btn_view = (Button) findViewById(R.id.btn_view);
         btn_search = (Button) findViewById(R.id.btn_search);
        btn_search.setOnClickListener(this);
        btn_add.setOnClickListener(this);
        btn_view.setOnClickListener(this);

    }

    @Override
    public void onClick(View v) {
        // TODO Auto-generated method stub
        switch (v.getId()) {
        case R.id.btn_add:
            Intent addintent = new Intent(AndroidAdvanceSqliteActivity.this,
                    AddRecord.class);
            startActivity(addintent);
            break;

        case R.id.btn_view:
            Intent viewintent = new Intent(AndroidAdvanceSqliteActivity.this,
                    ViewRecord.class);
            startActivity(viewintent);
            break;

            case R.id.btn_search:
            Intent searchintent = new Intent(AndroidAdvanceSqliteActivity.this,
                    SearchProduct.class);
            startActivity(searchintent);
            break;
        default:
            break;
        }

    }

       public void contactMe(View v) {

        final Intent emailIntent = new Intent(
                android.content.Intent.ACTION_SEND);
        emailIntent.setType("plain/text");
        emailIntent.putExtra(android.content.Intent.EXTRA_EMAIL,
                new String[] { "androiddevelopmentworld@gmail.com" });
        emailIntent.putExtra(android.content.Intent.EXTRA_SUBJECT,
                "Android sqlite tutorial");
        emailIntent.putExtra(android.content.Intent.EXTRA_TEXT,
                "Android sqlite tutorial contact Me");
        startActivity(Intent.createChooser(emailIntent, "Send mail..."));

    }
}



- We are passing intents to AddRecord.java and ViewRecord.java classes for operation. Now first we have to create AddRecord.java class and for that we have to  create xml file as following.


  -create new addrecord.xml file


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

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="15dp"
        android:layout_marginTop="10dp"
        android:gravity="center"
        android:text="ADD Record"
        android:textSize="18dp"
        android:text />

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="5dp"
        android:text="Product ID" />

    <EditText
        android:id="@+id/txtpid"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:hint="product ID" />

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="5dp"
        android:text="Enter Product Name" />

    <EditText
        android:id="@+id/txtpname"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:hint="product name" />

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="5dp"
        android:text="Enter Product Price" />

    <EditText
        android:id="@+id/txtpprice"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:hint="product price" />

    <Button
        android:id="@+id/btn_addrecord"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:text="Save" />

</LinearLayout>


  - Now we have to create AddRecord.java  like following. When user click on save button we need to add that record in our database for that we have to fire query which add record in our database.


public class AddRecord extends Activity implements OnClickListener {

    private Button btn_addrecord;
    private EditText txtpname, txtpprice, txtpid;
    DatabaseHelper db;
    ProductModel pm;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.addrecord);

        txtpname = (EditText) findViewById(R.id.txtpname);
        txtpprice = (EditText) findViewById(R.id.txtpprice);
        btn_addrecord = (Button) findViewById(R.id.btn_addrecord);

        txtpid = (EditText) findViewById(R.id.txtpid);
        btn_addrecord.setOnClickListener(this);

    }

    @Override
    public void onClick(View v) {
        // TODO Auto-generated method stub
        switch (v.getId()) {
        case R.id.btn_addrecord:

            if (txtpname.getText().toString().equals("")
                    || txtpprice.getText().toString().equals("")) {
                Toast.makeText(AddRecord.this, "Please add values..",
                        Toast.LENGTH_LONG).show();
            } else {

                db = new DatabaseHelper(getApplicationContext());
                db.getWritableDatabase();
                pm = new ProductModel();
                pm.idno = (txtpid.getText().toString());
                pm.productname = txtpname.getText().toString();
                pm.productprice = txtpprice.getText().toString();

                Log.i("idno,productname,productprice", "" + pm.idno + ""
                        + pm.productname + "" + pm.productprice);
                   db.addProduct(pm);
                Toast.makeText(AddRecord.this, "Record Added successfully.",
                        Toast.LENGTH_LONG).show();
                finish();
            }
            break;

        default:
            break;
        }

    }
}


- Here we add record using db.addProduct() method. Which we have to create in our DatabaseHelper.java class.

 - our DatabaseHelper.java looks like..


public class DatabaseHelper extends SQLiteOpenHelper {

    public static String DATABASENAME = "androidadvancesqlite";  // our database Name

    public static String PRODUCTTABLE = "products";                    //table name

    private ArrayList<ProductModel> cartList = new ArrayList<ProductModel>();
    Context c;


  //constructor
    public DatabaseHelper(Context context) {
        super(context, DATABASENAME, null, 33);
        c = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE if not exists producttable(id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "productidno"
                + " TEXT ,"
                + "productname"
                + " TEXT,"
                + "productprice" + " TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS" + PRODUCTTABLE);
        onCreate(db);
    }

  //Add record
    public void addProduct(ProductModel productitem) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("productidno", productitem.idno);
        contentValues.put("productname", productitem.productname);
        contentValues.put("productprice", productitem.productprice);
        db.insert("producttable", null, contentValues);
        db.close();
    }

 //update record
    public void updateProduct(ProductModel productList) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("productname", productList.productname);

        contentValues.put("productprice", productList.productprice);
        db.update("producttable", contentValues, "productidno="
                + productList.idno, null);
        db.close();
    }

  // delete entire data from table
    public void emptyProduct() {
        try {
            SQLiteDatabase db = this.getWritableDatabase();
            db.execSQL("delete from producttable");
            db.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

  // remove specific record from table
    public void removeProduct(String productid, String pname, String pprice) {
        try {
            String[] args = { productid };
            getWritableDatabase().delete("producttable", "productidno=?", args);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

  // get all products from database
    public ArrayList<ProductModel> getProudcts() {

        cartList.clear();

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from producttable", null);
        if (cursor.getCount() != 0) {
            if (cursor.moveToFirst()) {
                do {
                    ProductModel item = new ProductModel();

                    item.idno = cursor.getString(cursor
                            .getColumnIndex("productidno"));

                    item.productname = cursor.getString(cursor
                            .getColumnIndex("productname"));

                    item.productprice = cursor.getString(cursor
                            .getColumnIndex("productprice"));

                    cartList.add(item);

                } while (cursor.moveToNext());
            }
        }
        cursor.close();
        db.close();
        return cartList;
    }


   public ArrayList<ProductModel> getProudcts(String record) {

        cartList.clear();

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(true, "producttable", new String[] {
                "productidno", "productname", "productprice" }, "productname"
                + "=?", new String[] { record }, null, null, null, null);

        if (cursor.getCount() != 0) {
            if (cursor.moveToFirst()) {
                do {
                    ProductModel item = new ProductModel();

                    item.idno = cursor.getString(cursor
                            .getColumnIndex("productidno"));

                    item.productname = cursor.getString(cursor
                            .getColumnIndex("productname"));

                    item.productprice = cursor.getString(cursor
                            .getColumnIndex("productprice"));

                    cartList.add(item);

                } while (cursor.moveToNext());
            }
        }
        cursor.close();
        db.close();
        return cartList;
    }
}



- Here we have to specify ProductModel class which is our POJO(Plain Old Java Object) which will help us to populate all values.




 - Create new class ProductModel.java which looks like following. It have three variables idno,productname,productprice and also we have to declare   setter and getter methods for that.

 ProductModel.java

public class ProductModel {

   public String idno="", productname="", productprice="";

    public String getProductname() {
        return productname;
    }

    public void setProductname(String productname) {
        this.productname = productname;
    }

    public String getProductprice() {
        return productprice;
    }

    public void setProductprice(String productprice) {
        this.productprice = productprice;
    }

   

    public String getIdno() {
        return idno;
    }

    public void setIdno(String idno) {
        this.idno = idno;
    }

}


- Now for delete,update  and view records from products table we have to create new class ViewRecord.java. Here we desplay all records from database table and delete or update specific record from list.


 - create new viewrecord.xml file. We display all records in a listview.


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

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="15dp"
        android:layout_marginTop="10dp"
        android:gravity="center"
        android:text="View Record"
        android:textSize="18dp"
        android:text />

    <TextView
        android:id="@+id/totalrecords"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="15dp"
        android:layout_marginTop="10dp"
        android:gravity="center"
        android:textSize="18dp"
        android:text />

    <ListView
        android:id="@+id/listview"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >
    </ListView>

</LinearLayout>


 - Now for handling events and display data we have to create ViewRecord.java like following.


ViewRecord.java

public class ViewRecord extends Activity {

    private ListView listview;
    TextView totalrecords;
    DatabaseHelper db;
    public ArrayList<ProductModel> _productlist = new ArrayList<ProductModel>();

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.viewrecord);
        totalrecords = (TextView) findViewById(R.id.totalrecords);
        listview = (ListView) findViewById(R.id.listview);
    }

    @Override
    protected void onResume() {
        // TODO Auto-generated method stub
        super.onResume();

        _productlist.clear();

        db = new DatabaseHelper(getApplicationContext());
        db.getWritableDatabase();
        ArrayList<ProductModel> product_list = db.getProudcts();

        for (int i = 0; i < product_list.size(); i++) {

            String tidno = product_list.get(i).getIdno();

            String tname = product_list.get(i).getProductname();
            String tprice = product_list.get(i).getProductprice();

            ProductModel _ProductModel = new ProductModel();

            _ProductModel.setIdno(tidno);
            _ProductModel.setProductname(tname);
            _ProductModel.setProductprice(tprice);

            _productlist.add(_ProductModel);
        }
        totalrecords.setText("Total Records :-" + _productlist.size());
        listview.setAdapter(new ListAdapter(this));
        db.close();

    }

    private class ListAdapter extends BaseAdapter {
        LayoutInflater inflater;
        ViewHolder viewHolder;

        public ListAdapter(Context context) {
            // TODO Auto-generated constructor stub
            inflater = LayoutInflater.from(context);
        }

        @Override
        public int getCount() {
            // TODO Auto-generated method stub
            return _productlist.size();
        }

        @Override
        public Object getItem(int position) {
            // TODO Auto-generated method stub
            return position;
        }

        @Override
        public long getItemId(int position) {
            // TODO Auto-generated method stub
            return position;
        }

        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
            // TODO Auto-generated method stub
            if (convertView == null) {

                convertView = inflater.inflate(R.layout.listview_row, null);
                viewHolder = new ViewHolder();

                viewHolder.txt_pname = (TextView) convertView
                        .findViewById(R.id.txtdisplaypname);
                viewHolder.txt_pprice = (TextView) convertView
                        .findViewById(R.id.txtdisplaypprice);

                viewHolder.txtidno = (TextView) convertView
                        .findViewById(R.id.txtdisplaypid);
                convertView.setTag(viewHolder);

            } else {
                viewHolder = (ViewHolder) convertView.getTag();
            }

            viewHolder.txt_pname.setText(_productlist.get(position)
                    .getProductname().trim());
            viewHolder.txt_pprice.setText(_productlist.get(position)
                    .getProductprice().trim());

            viewHolder.txtidno.setText(_productlist.get(position).getIdno()
                    .trim());

            final int temp = position;
            (convertView.findViewById(R.id.btn_update))
                    .setOnClickListener(new OnClickListener() {

                        public void onClick(View arg0) {

                            String _productid = String.valueOf(_productlist
                                    .get(temp).getIdno());
                            String _productname = _productlist.get(temp)
                                    .getProductname();
                            String _productprice = _productlist.get(temp)
                                    .getProductprice();

                            Intent intent = new Intent(ViewRecord.this,
                                    AddUpdateValues.class);

                            Bundle bundle = new Bundle();
                            bundle.putString("id", _productid);
                            bundle.putString("name", _productname);
                            bundle.putString("price", _productprice);
                            intent.putExtras(bundle);
                            startActivity(intent);

                        }
                    });



  - for delete record we no need to create class we just call removeProduct() method of DatabaseHelper like following.



            (convertView.findViewById(R.id.btn_delete))
                    .setOnClickListener(new OnClickListener() {

                        public void onClick(View arg0) {

                            AlertDialog.Builder alertbox = new AlertDialog.Builder(
                                    ViewRecord.this);
                            alertbox.setCancelable(true);
                            alertbox.setMessage("Are you sure you want to delete ?");
                            alertbox.setPositiveButton("Yes",
                                    new DialogInterface.OnClickListener() {

                                        public void onClick(
                                                DialogInterface arg0, int arg1) {

                                            db.removeProduct(
                                                    _productlist.get(temp)
                                                            .getIdno().trim(),
                                                    "", "");

                                            ViewRecord.this.onResume();

                                            Toast.makeText(
                                                    getApplicationContext(),
                                                    "Record Deleted...",
                                                    Toast.LENGTH_SHORT).show();

                                        }

                                    });
                            alertbox.setNegativeButton("No",
                                    new DialogInterface.OnClickListener() {
                                        public void onClick(
                                                DialogInterface arg0, int arg1) {

                                        }
                                    });
                            alertbox.show();
                        }
                    });
            return convertView;

        }
    }

    private class ViewHolder {
        TextView txt_pname;
        TextView txt_pprice;
        TextView txtidno;

    }

}


 - Here we have to give two buttons with each record in a list that is update and delete. When user click on Update button we have to pass intent to new activity that is AddUpdateValues.java and we have to also pass id,product name and product price of perticular record using Bundle.



 - For update record we create AddUpdateValues.java class. Which will update your record.


- create new addupdatevalues.xml file.


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

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="5dp"
        android:text="Enter Product Name" />

    <EditText
        android:id="@+id/txt_udatepname"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:hint="product name" />

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="5dp"
        android:text="Enter Product Price" />

    <EditText
        android:id="@+id/txt_udatepprice"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_margin="5dp"
        android:hint="product price" />

    <Button
        android:id="@+id/btn_updaterecord"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:text="Save" />

</LinearLayout>

 - Here user enter new values for product name and product price so we declare two edittext.

 - Now create new class AddUpdateValues.java for update records.


public class AddUpdateValues extends Activity implements OnClickListener {
    private Button btn_updaterecord;
    private EditText txtpname, txtpprice;
    DatabaseHelper db;
    ProductModel pm;
    Intent i;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.addupdatevalues);
        i = getIntent();
        txtpname = (EditText) findViewById(R.id.txt_udatepname);
        txtpprice = (EditText) findViewById(R.id.txt_udatepprice);

  - Here we get string from intent and sets it to perticular field.

        txtpname.setText(i.getExtras().getString("name"));
        txtpprice.setText(i.getExtras().getString("price"));
        btn_updaterecord = (Button) findViewById(R.id.btn_updaterecord);
        btn_updaterecord.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        // TODO Auto-generated method stub
        switch (v.getId()) {
        case R.id.btn_updaterecord:
            if (txtpname.getText().toString().equals("")
                    || txtpprice.getText().toString().equals("")) {
                Toast.makeText(AddUpdateValues.this, "Please add values..",
                        Toast.LENGTH_LONG).show();
            } else {

                db = new DatabaseHelper(getApplicationContext());
                db.getWritableDatabase();
                pm = new ProductModel();
                pm.productname = txtpname.getText().toString();
                pm.productprice = txtpprice.getText().toString();
                pm.idno = i.getExtras().getString("id");
                db.updateProduct(pm);
                Toast.makeText(AddUpdateValues.this,
                        "Record Update successfully.", Toast.LENGTH_LONG)
                        .show();
                db.close();
                super.onResume();
                finish();
            }
            break;
        }
    }
}


As per many user's request, I have create search functionality in this example.

Create searchproduct.xml file.


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/searchlinear"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:gravity="center_horizontal"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" >

        <TextView
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_marginBottom="15dp"
            android:layout_marginTop="10dp"
            android:layout_weight="1"
            android:gravity="center"
            android:text="Search Record"
            android:textSize="18dp"
            android:textStyle="bold" />

        <ProgressBar
            android:id="@+id/showprogress"
            style="?android:attr/progressBarStyleSmall"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginTop="10dp"
            android:paddingRight="10dp"
            android:visibility="gone" />
    </LinearLayout>

    <EditText
        android:id="@+id/txtsearchproduct"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:hint="search"
        android:lines="1"
        android:paddingLeft="25dp"
        android:visibility="visible" >
    </EditText>

    <AutoCompleteTextView
        android:id="@+id/myautocomplete"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:completionThreshold="1"
        android:hint="search"
        android:lines="1"
        android:visibility="gone" />

    <ListView
        android:id="@+id/searchlistview"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >
    </ListView>

</LinearLayout>


Now for display searched items, we need to create xml file for display single searched item.

Create product_list.xml for display single searched item.


<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/category_list_id"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:background="@android:color/white"
    android:paddingBottom="1dp" >

    <LinearLayout
        android:id="@+id/ll_place_name_use"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_centerVertical="true"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="10dp"
        android:orientation="vertical"
        android:paddingRight="8dp" >

        <TextView
            android:id="@+id/txt_title_text"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:maxLines="3"
            android:padding="2dp"
            android:text="aaa"
            android:textColor="@android:color/black"
            android:textSize="16sp"
            android:textStyle="bold" >
        </TextView>

        <TextView
            android:id="@+id/txt_price"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:padding="2dp"
            android:text="1000"
            android:textColor="@android:color/black"
            android:textSize="14sp"
            android:visibility="visible" >
        </TextView>
    </LinearLayout>

</RelativeLayout>


Now last but not least create java file for searching.

Create SearchProduct.java


package com.androidadvance.screen;

import java.util.ArrayList;
import java.util.List;

import com.androidadvance.db.DatabaseHelper;

import android.app.Activity;
import android.content.Context;
import android.graphics.Color;
import android.os.AsyncTask;
import android.os.Bundle;
import android.os.Handler;
import android.text.Editable;
import android.text.TextWatcher;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.AutoCompleteTextView;
import android.widget.BaseAdapter;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.ProgressBar;
import android.widget.RelativeLayout;
import android.widget.TextView;
import android.widget.Toast;

public class SearchProduct extends Activity implements TextWatcher {
    EditText _searchbox;
    private ProgressBar showprogress;
    searchtask dotask;
    private ArrayList<ProductModel> _productList;
    ListView _listview;
    DatabaseHelper db;
    public AutoCompleteTextView myAutoComplete;
    private ArrayList<ProductModel> _productList_Temp;
    String query = "";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.searchproduct);
        _searchbox = (EditText) findViewById(R.id.txtsearchproduct);
        showprogress = (ProgressBar) findViewById(R.id.showprogress);
        _listview = (ListView) findViewById(R.id.searchlistview);
        _searchbox.addTextChangedListener(textwatcher);

    }

    Runnable runn = new Runnable() {

        @Override
        public void run() {
            // TODO Auto-generated method stub
            handlersearch.sendEmptyMessage(0);
        }
    };
    TextWatcher textwatcher = new TextWatcher() {

        public void onTextChanged(CharSequence s, int start, int before,
                int count) {
            Log.i("---onTextChanged ----", "---------onTextChanged ----");

            if (_searchbox.getText().toString().length() > 2) {
                query = _searchbox.getText().toString().replace(" ", "%20");

                handlersearch.removeCallbacks(runn);
                handlersearch.post(runn);

            } else {
                showprogress.setVisibility(View.GONE);

                if (dotask != null) {
                    if (dotask.getStatus().equals(AsyncTask.Status.RUNNING)) {
                        dotask.cancel(true);
                    }
                }

                handlersearch.removeCallbacks(runn);

                _productList = new ArrayList<ProductModel>();
                _productList.clear();
                _listview.setAdapter(new CustomBaseAdapter(SearchProduct.this,
                        _productList));
            }

        }

        public void beforeTextChanged(CharSequence s, int start, int count,
                int after) {
            // TODO Auto-generated method stub

        }

        public void afterTextChanged(Editable s) {
            // TODO Auto-generated method stub

        }
    };

    Handler handlersearch = new Handler() {

        public void handleMessage(android.os.Message msg) {
            dotask = new searchtask();
            dotask.execute();
        };
    };

    private class searchtask extends AsyncTask<Void, Void, Void> {

        protected void onPreExecute() {

            showprogress.setVisibility(View.VISIBLE);
        };

        protected void onPostExecute(Void param) {
            // animation.dismiss();
            showprogress.setVisibility(View.GONE);
            if (_productList == null)
                return;

            ArrayList<String> item = new ArrayList<String>();

            for (int i = 0; i < _productList.size(); i++) {
                item.add(_productList.get(i).productname);
            }

            myAutoComplete = (AutoCompleteTextView) findViewById(R.id.myautocomplete);

            myAutoComplete.addTextChangedListener(SearchProduct.this);

            myAutoComplete.setAdapter(new ArrayAdapter<String>(
                    SearchProduct.this,
                    android.R.layout.simple_dropdown_item_1line, item));

            _productList_Temp = new ArrayList<ProductModel>();

            for (int i = 0; i < _productList.size(); i++) {

                _productList_Temp.add(_productList.get(i));

            }

            _listview.setAdapter(new CustomBaseAdapter(SearchProduct.this,
                    _productList_Temp));

        }

        @Override
        protected Void doInBackground(Void... params) {

            db = new DatabaseHelper(getApplicationContext());
            db.getWritableDatabase();
            ArrayList<ProductModel> product_list = db.getProudcts(query);

            for (int i = 0; i < product_list.size(); i++) {

                String tidno = product_list.get(i).getIdno();

                System.out.println("tidno>>>>>" + tidno);
                String tname = product_list.get(i).getProductname();
                String tprice = product_list.get(i).getProductprice();

                ProductModel _ProductModel = new ProductModel();

                _ProductModel.setIdno(tidno);
                _ProductModel.setProductname(tname);
                _ProductModel.setProductprice(tprice);

                _productList.add(_ProductModel);
            }
            // _productList = _parser.getProductList();

            return null;
        }

    }

    private class CustomBaseAdapter extends BaseAdapter {
        LayoutInflater _inflater;

        List<ProductModel> productList;

        public CustomBaseAdapter(Context context, List<ProductModel> productList) {
            _inflater = LayoutInflater.from(context);
            this.productList = productList;

        }

        public int getCount() {
            // TODO Auto-generated method stub
            return productList.size();
        }

        public Object getItem(int position) {
            // TODO Auto-generated method stub
            return position;
        }

        public long getItemId(int position) {
            // TODO Auto-generated method stub
            return position;
        }

        public View getView(int position, View convertView, ViewGroup parent) {

            ViewHolder _holder;
            if (convertView == null) {

                convertView = _inflater.inflate(R.layout.product_list, null);
                _holder = new ViewHolder();

                _holder.title = (TextView) convertView
                        .findViewById(R.id.txt_title_text);
                _holder.price = (TextView) convertView
                        .findViewById(R.id.txt_price);

                convertView.setTag(_holder);
            } else {
                _holder = (ViewHolder) convertView.getTag();
            }

            _holder.title.setText(productList.get(position).productname.trim());

            _holder.price.setText(productList.get(position).productprice);

            return convertView;
        }

        private class ViewHolder {
            TextView title;
            TextView price;
        }
    }

    @Override
    public void afterTextChanged(Editable arg0) {
        // TODO Auto-generated method stub

    }

    @Override
    public void beforeTextChanged(CharSequence s, int start, int count,
            int after) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onTextChanged(CharSequence s, int start, int before, int count) {
        // TODO Auto-generated method stub

    }

    //
}



Download Source code

62 comments:

  1. Hi,

    Fantastic tutorial, is there any chance you can send me the project (source code)?

    It would be very much appreciated.

    Thanks
    Stefan

    ReplyDelete
  2. Hello Stefan,

    You want project source code for this demo ?

    ReplyDelete
  3. I uploaded link for source code. You can download it.

    Thanks

    ReplyDelete
  4. code sensational.
    lacked layout listview_row.xml
    I took the source.

    ReplyDelete
    Replies
    1. Thank you for comment Ricardo.

      Delete
    2. I hope to learn more from you. Thanks for posting source as I went through the tutorial it was helpful. Really Helpful and Useful. Good spot Ricardo as your comment helped also at the very end. Thank you.

      Delete
    3. YAA ME 2 FACED THE SAME PRBLM...

      Delete
    4. Thank you so much for your support brother.

      KP Ranjith what is your issue ? Let me know what issue are you facing in example ?

      Delete
  5. I followed your whole tutorial :) but its giving the error on getWritableDatabase(), says "The method getWritableDatabase() is undefined for the type DatabaseHelper"..

    ReplyDelete
    Replies
    1. Hello I had also add source code link. You can download code from there. You forgot "extends SQLiteOpenHelper" thats why you facing this error.

      Delete
  6. Nice,Sample but try to improve code formatting and presentation.Demo is awesome :)

    ReplyDelete
    Replies
    1. improve presentation and code formatting?! Com'n men...it's just a tutorial...!! Give us a break!
      (No, just kiddin'... code formatting and presentation is a good practice to improve development skills.)

      Delete
    2. He was referring to the way the code is displayed in this blog, not the way he actually code it.

      A good way of doing it, is with the tag " < pre > ... < / pre >"

      Delete
  7. in this tutorial www.developerfeed.com/android/tutorial/building-todo-list-app-android-using-sqlite
    The call to getWritableDatabase crash the app.but this code works.
    why ?
    also, how can i connect to this data base from adb ?

    thank you

    ReplyDelete
  8. What a tutorials venky keep it up !!

    ReplyDelete
  9. I agree with most comments...it's a great tutorial!! It's just what I was looking for so thank you so much ;)

    ReplyDelete
  10. hi, thanks for the tutorial...
    Somebody would have the source code of the project ?

    ReplyDelete
  11. Thanks that write for tutorial android

    ReplyDelete
  12. how can i connect this database from adb ?
    i still confuse how to get connect with database...

    ReplyDelete
  13. i download the source code but Updating the record is not working for me however it display the toast sms

    ReplyDelete
    Replies
    1. Hello BANGASH,

      Update operation working perfectly. I had checked. Let me know what issue you are facing.

      Delete
    2. Updating the record is not working and plz one thing more plz send me method which search string in that database and if string is found it return the complete row I am begineer plz help and thanks in advance

      Delete
    3. Hello MYK BANGASH,

      I just upload new code with search functionality. Download now and enjoy. I also update entire tutorial demo with searching. Let me know any issue now ?


      Thanks,
      Hardik

      Delete
  14. I agree with MYK BANGASH. The Update function doesnt seem to work. I cant seem to find a work around for it. Please help. Thanks!

    ReplyDelete
    Replies
    1. Hello JesCarlo,

      I had checked again, It works. Let me know what is issue regarding update operation ?

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

    ReplyDelete
  16. Hey Dude..
    your tutorial is so far awesome. Now i get to know clearly about sqllite and its functioning..
    Thanks a lot man..
    n agree with above comments that there is a some problems with Update thing..

    See, when we update price like from 25000 to 2500 or anything else its still 25000 after updation, something is missing dude.
    I think the problem is with this ",null"
    ___________________________
    db.update("producttable", contentValues, "productidno="+ productList.idno, null);
    ___________________________
    Here you have to return a new String instead of null..

    I request please just check it out.. although your tutorial is far better then others.. :)
    Best of luck.. :D

    ReplyDelete
    Replies
    1. Hello Parth,

      Thank you for your good comments. But I just check it out. update operation works perfectly. I cant find why it not works with most of the users.

      Delete
  17. Hey Hardik,
    Sorry for interrupting you again and again..
    after detailed study of your Code I found this mistake..
    and cleared the problem with update thing.. :)
    This should be the code in

    public void updateProduct(ProductModel productList) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("productname", productList.productname);

    contentValues.put("productprice",productList.productprice);
    db.update("producttable", contentValues, "productidno=?" , new String[]{String.valueOf(cartList.idno)});
    db.close();
    }

    istead of ",null" that you are using.. ;)

    ReplyDelete
    Replies
    1. Thanks but "cartList.idno" cause a problem. What should i put there?

      Delete
  18. Hi. Thanks for your tutorial. I want to add another col in the sqlite using your the template from your tutorial, but col table is not found. Will you please help me?

    ReplyDelete
    Replies
    1. Hello Wenying,

      Did you added column or not ?

      Delete
    2. hi did you get to add more columns to the database?
      please reply

      Delete
  19. Im having a problem, its not saving at all, at Logcat it says,

    "insert | Error inserting table = PRODUCTTABLE
    android.database.sqlite.SQLiteException: table PRODUCTTABLE has no
    column named productprice:, while compiling: INSERT INTO PRODUCTTABLE..."

    I downloaded the file, also has the same problem.
    Please help thanks.

    ReplyDelete
    Replies
    1. Hello Raphaelle,

      Did you solved your issue ? Let me know any help needed or not.

      Delete
  20. Hardik Joshi......i need a method which search a string in your database and return all the row where it found

    ReplyDelete
    Replies
    1. Hello MYK BANGASH,

      what have you tried ?

      Delete
    2. I have try most of the method but every time I got error.....

      Delete
    3. Hello MYK BANGASH,

      I just upload new code with search functionality. Download now and enjoy. I also update entire tutorial demo with searching. Let me know any issue now ?


      Thanks,
      Hardik

      Delete
  21. ultimate tutorial...
    Now i want to search particular product from the product list, how to implement searchview please help me out.

    ReplyDelete
    Replies
    1. I will publish new example with search functionality today.

      Delete
    2. Hello Nilesh,

      I just upload new code with search functionality. Download now and enjoy. I also update entire tutorial demo with searching. Let me know any issue now ?


      Thanks,
      Hardik

      Delete
  22. Hi, everything run well, except the updating part i faced some errors. May u help me to see whether how to solve this kind of errors?! thanks a lot.

    Error updating dbtimeout=113 dbport=8000 dbwanip=1.1.1.1 dbpw=1111 dblocalip=192.168.1.234 using UPDATE accounttable SET dbtimeout=?, dbport=?, dbwanip=?, dbpw=?, dblocalip=? WHERE dbname=aaa

    android.database.sqlite.SQLiteException: no such column: aaa: , while compiling: UPDATE accounttable SET dbtimeout=?, dbport=?, dbwanip=?, dbpw=?, dblocalip=? WHERE dbname=aaa

    From Jaz.

    ReplyDelete
    Replies
    1. Hello Jasmine,

      Error is in your query syntax. Let me see your code for query.

      Delete
    2. Hello Jasmine,

      Did you solve your issue or not ?

      Delete
  23. Plz someone help I need a methos which search string in that database and return all entry if string is found

    ReplyDelete
    Replies
    1. Hello MYK BANGASH,

      I will upload demo with search functionality.

      Delete
    2. Hello MYK BANGASH,

      I just upload new code with search functionality. Download now and enjoy. I also update entire tutorial demo with searching. Let me know any issue now ?


      Thanks,
      Hardik

      Delete
  24. Great job Brother.. works perfectly
    I have a question, can you show me how can i add a Spinner.. for example in place of product name there is 5 products you get to choose between them.

    ReplyDelete
  25. If product id contains letters, update function gives error

    ReplyDelete
  26. How to add other columns to the database?
    should i change database name if i want to add other columns to it?
    please reply..
    thanks...

    ReplyDelete
    Replies
    1. This has been a while, but just incase for anyone else...
      Whenever you change the database it doesn't re-create it, because as far as it's concerned it has already been created.
      When you add a new column to the database, you must uninstall the application from your phone, or put in code to check the database version number. Either way will fix this :)

      Delete
  27. Hi,
    This has been amazing! Great, great help with my dissertation. My only complaint is there is no comments. Its hard when first starting to know exactly what's happening without it being explained. Infact, I'm still having an issue with the search function.
    If you could explain it, it'd be greatly appreciated.
    And if not, I'd stilll like to say a big THANK YOU. This has really been invaluable for me

    ReplyDelete