Database using SQLiteOpenHelper Class accessing in main activity

I need to Preload some data in a sqlite database.I tried using Insert query.But it is inserting data everytime I open app.My searches made me understand SQLiteOpenHelper is the best option.But I am a beginner and I have no idea about SQLiteOpenHelper.I dont know how to implement it.

DataBaseHelper.java

public class DataBaseHandler extends SQLiteOpenHelper{

   public static final String DATABASE_NAME = "products.db";
   public static final String CONTACTS_TABLE_NAME = "product";
   public static final String CONTACTS_COLUMN_ID = "pid";
   public static final String CONTACTS_COLUMN_NAME = "pname";
   public static final String CONTACTS_COLUMN_EMAIL = "pspec";
   public static final String CONTACTS_COLUMN_STREET = "pprice";
   public static final String CONTACTS_COLUMN_CITY = "pfeature";
   public static final String CONTACTS_COLUMN_PHONE = "pimage";

public DataBaseHandler(Context context, String name, CursorFactory factory,
        int version) {
    super(context, name, factory, version);
    // TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL("CREATE TABLE IF NOT EXISTS product(pimage BLOB,pid INTEGER PRIMARY KEY,pname TEXT,pprice NUMERIC,pspec TEXT,pfeature TEXT)");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('Candle stick 3',4000,'Solar garden / pathway light,Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA ,Material:Stainless steel ,WaterProof and safe ')");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('Candle stick 4',4500,'Solar garden / pathway light, Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA, Material:Stainless steel, WaterProof and safe IP44 ')");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('Candle stick 5',3500,'Solar garden / pathway light, Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA, Material:Stainless steel, WaterProof and safe IP44 ')");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('Candle stick 6',6000,'Solar garden / pathway light, Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA, Material:Stainless steel, WaterProof and safe IP44 ')");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('lawn Delight',8800,'Solar garden / pathway light, Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA, Material:Stainless steel, WaterProof and safe IP44 ')");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('SGLC10',4500,'Solar garden / pathway light, Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA, Material:Stainless steel, WaterProof and safe IP44 ')");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('Senson',4500,'Solar garden / pathway light, Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA, Material:Stainless steel, WaterProof and safe IP44 ')");
    db.execSQL("INSERT INTO product(pname,pprice,pspec) VALUES('Thejus',7500,'Solar garden / pathway light, Solar Panel:1pc crystal silicon solar cell, Battery:1pc 1.2V Ni-MH/Ni-CD AA battery 600MA, Material:Stainless steel, WaterProof and safe IP44 ')");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub

}

}

Product_display.java

public class product_display extends Activity {

ListView prd_list;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.product_disply);
    prd_list = (ListView) findViewById(R.id.list);

    DataBaseHandler dbh = new DataBaseHandler(this);

     Intent in=getIntent();
     Bundle bundle=in.getExtras();
     final String list=bundle.getString("key");
     SQLiteDatabase db = dbh.getWritableDatabase();

    Cursor cr = db.rawQuery("SELECT * FROM product", null);
    final String[] pname = new String[cr.getCount()];
    String[] price = new String[cr.getCount()];

    int i = 0;
    while(cr.moveToNext())
    {
        String name = cr.getString(cr.getColumnIndex("pname"));
        String prprice = cr.getString(cr.getColumnIndex("pprice"));

        pname[i] = name;
        price[i] = prprice;
        i++;
    }

    ListAdapter adapter = new com.example.login_signup.ListAdapter(this, pname, price);
    prd_list.setAdapter(adapter);

    prd_list.setOnItemClickListener(new OnItemClickListener() {

        public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
                long arg3) {
            // TODO Auto-generated method stub
            String nme = pname[arg2];   

            Bundle bun = new Bundle();
            bun.putString("key",list);
            Bundle bn = new Bundle();
            bn.putString("name",nme);
            Intent in = new Intent(product_display.this, Product_Details.class);
            in.putExtras(bun);
            in.putExtras(bn);
            startActivity(in);
            }

    });



}

}

product_dtls.java

public class Product_Details extends Activity{

TextView name,price,specification,feature;
String nme;
String pname;
String prprice;
String pspec;
String pfeature;
Button add2cart,by_nw;
ImageView image;
ImageButton imgbtn; 

Integer [] pmge ={R.drawable.candle1,R.drawable.candl3,
        R.drawable.candl4,R.drawable.candl5,R.drawable.candl6,
        R.drawable.lawn,R.drawable.sglc10,R.drawable.senson,R.drawable.thejus6669};
@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.product_dtls);
    image=(ImageView)findViewById(R.id.pr_img);
    name = (TextView) findViewById(R.id.txtPr_name);
    price = (TextView) findViewById(R.id.txtprice);
    specification=(TextView)findViewById(R.id.txtPr_spec);
    feature=(TextView)findViewById(R.id.txtPr_feature);
    imgbtn=(ImageButton)findViewById(R.id.cartimg);
    add2cart=(Button)findViewById(R.id.add2cart);
    by_nw=(Button)findViewById(R.id.buy_nw);
    DataBaseHandler dbh = new DataBaseHandler(this);
    SQLiteDatabase db = dbh.getWritableDatabase();
    Intent in = getIntent();
    Bundle bn = in.getExtras();
    Bundle bun=in.getExtras();
    final String dtl=bun.getString("key");
    nme = bn.getString("name");
    Cursor cr = db.rawQuery("SELECT * FROM product WHERE pname = '"+nme+"'", null);

    while(cr.moveToNext())
    {
        String name = cr.getString(cr.getColumnIndex("pname"));
        String pr1price = cr.getString(cr.getColumnIndex("pprice"));
        String prspc=cr.getString(cr.getColumnIndex("pspec"));
        String prfeature=cr.getString(cr.getColumnIndex("pfeature"));
        pname = name;
        prprice = pr1price;
        pspec=prspc;
        pfeature=prfeature;
    }
    name.setText(pname);
    price.setText("Rs " +prprice + "/-");
    specification.setText(pspec);
    feature.setText(pfeature);


    add2cart.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            boolean incart=false;
            String nm=name.getText().toString();
            db=Product_Details.this.openOrCreateDatabase("addcart", MODE_PRIVATE, null);
            Cursor cur=db.rawQuery("select * from add2cart where pnme='"+nm+"'",null);

            if (cur.moveToFirst()){
                String prdname=cur.getString(cur.getColumnIndex("pnme"));

                if (nm.equals(prdname)){
                    add2cart.setText("Already in Cart");
                    incart=true;
                }
            }

            if(incart==false){
                db.execSQL("INSERT INTO add2cart (pnme,prate)VALUES('"+nm+"','"+prprice+"')");
                Toast.makeText(getApplicationContext(),"added to cart",Toast.LENGTH_SHORT).show();


            }

        }

    });

}

}

Answers


  1. Consider embedding the database name and version in the code itself. Change

    public DataBaseHandler(Context context, String name, CursorFactory factory,
        int version) {
        super(context, name, factory, version);
    

    to something like

    public DataBaseHandler(Context context) {
        super(context, DATABASE_NAME, null, 1);
    
  2. Instantiate the helper in your activity:

    DataBaseHandler dbh = new DataBaseHandler(this);
    
  3. Call e.g. getWritableDatabase() on the helper:

    SQLiteDatabase db = dbh.getWritableDatabase();
    
  4. Use db for your database ops instead of the one you're obtaining via openOrCreateDatabase() now.


Need Your Help

Interact between winforms and console application and the observer design pattern

c# winforms visual-studio-2010 console observer-pattern

i just finished a basic observer pattern in C# console application. Its a basic server client application in which i have used the observer pattern.Now i want to integrate it with a winform applica...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.