1

Hi I am struggling with these two: i) I created sqlite database for my android app. And added rows through Main Activity. Now, whenever I am running my program, everytime the same rows getting added in database. How to ensure only one time database gets created with all these rows. ii) I made id (int type) as primary key and made it auto-increment.I don't want primary ID to be auto-incremental and can be set manually for each row. How I can do it?

1. All database related functions have been in this code:
public class DataBaseHandlerActivity extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "shopsManager";

    // Shop table name
    private static final String TABLE_SHOPS = "shops";

    // Shop Table Columns names
    private static final String KEY_ID = "shopid";
    private static final String KEY_NAME = "shopname";
    private static final String KEY_ADDRESS = "shopaddress";
    private static final String KEY_CAT1="category1";
    private static final String KEY_CAT2="category2";
    private static final String KEY_CAT3="category3";
    private static final String KEY_CAT4="category4";
    private static final String KEY_IMAGE="shopimage";

    public DataBaseHandlerActivity(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }


    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        String CREATE_SHOPS_TABLE="CREATE TABLE "+TABLE_SHOPS+"("+KEY_ID+" INTEGER PRIMARY KEY,"+KEY_NAME+" TEXT,"+
                KEY_IMAGE+" TEXT,"+
                KEY_ADDRESS+" TEXT,"+KEY_CAT1+" TEXT,"+KEY_CAT2+" TEXT,"+KEY_CAT3+" TEXT,"+KEY_CAT4+" TEXT"+
                ")";
        db.execSQL(CREATE_SHOPS_TABLE);

    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS"+TABLE_SHOPS);

        //CREATE TABLE AGAIN
        onCreate(db);


    }


    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    //Add a new shop row
    void addShop(Shop shop){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_NAME,shop.getShopName());
        values.put(KEY_IMAGE,shop.getShopImage());
        values.put(KEY_ADDRESS,shop.getShopAddress());
        values.put(KEY_CAT1,shop.getShopCat1());
        values.put(KEY_CAT2,shop.getShopCat2());
        values.put(KEY_CAT3,shop.getShopCat3());
        values.put(KEY_CAT4,shop.getShopCat4());

        //Inserting row
        db.insert(TABLE_SHOPS,null,values);
        db.close(); //Close db connection
    }

    //Get a shop items corresponding to a primary key

    Shop getShop(int id){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.query(TABLE_SHOPS,new String[]{KEY_ID,KEY_NAME,KEY_IMAGE,KEY_ADDRESS,KEY_CAT1,KEY_CAT2,KEY_CAT3,KEY_CAT4},
                KEY_ID +"=?",new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();
       Shop shop = new Shop(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getString(2),cursor.getString(3),cursor.getString(4),cursor.getString(5),
       cursor.getString(6),cursor.getString(7));
        // return shop
        return shop;
    }

    // Getting All Shops
    public List<Shop> getAllShops() {
        List<Shop> shopList = new ArrayList<Shop>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_SHOPS;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Shop shop = new Shop();
                shop.setShopId(Integer.parseInt(cursor.getString(0)));
                shop.setShopName(cursor.getString(1));
                shop.setShopImage(cursor.getString(2));
                shop.setShopAddress(cursor.getString(3));
                shop.setShopCat1(cursor.getString(4));
                shop.setShopCat2(cursor.getString(5));
                shop.setShopCat3(cursor.getString(6));
                shop.setShopCat4(cursor.getString(7));
                // Adding contact to list
                shopList.add(shop);
            } while (cursor.moveToNext());
        }

        // return shop list
        return shopList;
    }

    // Updating single shop
    public int updateShop(Shop shop) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME,shop.getShopName());
        values.put(KEY_IMAGE,shop.getShopImage());
        values.put(KEY_ADDRESS,shop.getShopAddress());
        values.put(KEY_CAT1,shop.getShopCat1());
        values.put(KEY_CAT2,shop.getShopCat2());
        values.put(KEY_CAT3,shop.getShopCat3());
        values.put(KEY_CAT4,shop.getShopCat4());

        // updating row
        return db.update(TABLE_SHOPS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(shop.getShopId()) });
    }

    // Deleting single shop
    public void deleteShop(Shop shop) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_SHOPS, KEY_ID + " = ?",
                new String[] { String.valueOf(shop.getShopId()) });
        db.close();
    }


    // Getting shops Count
    public int getShopsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_SHOPS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int count=cursor.getCount();
        cursor.close();
        db.close() ;
        // return count
        return count;
    }


}

2. Main Activity where I pass data to create table:

package com.trillbit.databaseactivity;

import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;

import java.util.List;

public class MainActivity extends AppCompatActivity {


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

        DataBaseHandlerActivity db=new DataBaseHandlerActivity(this);
/**
 * CRUD Operations
 * */
        // Inserting Shops
        Log.d("Insert: ", "Inserting ..");
        db.addShop(new Shop("Shoppers Stop","shopperstop","1st Floor,Forum Mall","Fashion","Clothes","Home","Shoes"));
        db.addShop(new Shop("LifeStyle", "lifestyle", "2nd Floor, Forum Mall", "Fashion", "Clothes","Home", "Mobile"));
        db.addShop(new Shop("Ezone", "ezone","3rd Floor, Forum Mall","Mobile","Electronics","Apppliances","Home"));
        db.addShop(new Shop("Dominos", "dominos","1st Floor,Forum Mall","Food","Pizza","Cake","Pastery"));

        // Reading all shops
        Log.d("Reading: ", "Reading all shops..");
        List<Shop> shops = db.getAllShops();
        for (Shop shop : shops) {
            String log = "Id: " + shop.getShopId() + " ,Name: " + shop.getShopName() + " ,Image: " + shop.getShopImage()
                    + ",Address: " + shop.getShopAddress() + ",Categort1: " + shop.getShopCat1() + ", Category2: " + shop.getShopCat2()
                    + ",Category3: " + shop.getShopCat3() + ",Catgeory4: " + shop.getShopCat4();
            // Writing Shops to log
            Log.d("Name: ", log);
        }


    }
}

3.Output in logcat:

D/Name:: Id: 5 ,Name: Shoppers Stop ,Image: shopperstop,Address: 1st Floor,Forunm Mall,Categort1: Fashion, Category2: Clothes,Category3: Home,Catgeory4: Shoes
01-14 16:07:49.687 7993-7993/com.trillbit.databaseactivity D/Name:: Id: 6 ,Name: LifeStyle ,Image: lifestyle,Address: 2nd Floor, Forum Mall,Categort1: Fashion, Category2: Clothes,Category3: Home,Catgeory4: Mobile
01-14 16:07:49.687 7993-7993/com.trillbit.databaseactivity D/Name:: Id: 7 ,Name: Ezone ,Image: ezone,Address: 3rd Floor, Forum Mall,Categort1: Mobile, Category2: Electronics,Category3: Apppliances,Catgeory4: Home
01-14 16:07:49.687 7993-7993/com.trillbit.databaseactivity D/Name:: Id: 8 ,Name: Dominos ,Image: dominos,Address: 1st Floor,Forum Mall,Categort1: Food, Category2: Pizza,Category3: Cake,Catgeory4: Pastery
01-14 16:07:49.687 7993-7993/com.trillbit.databaseactivity D/Name:: Id: 9 ,Name: Shoppers Stop ,Image: shopperstop,Address: 1st Floor,Forum Mall,Categort1: Fashion, Category2: Clothes,Category3: Home,Catgeory4: Shoes
01-14 16:07:49.687 7993-7993/com.trillbit.databaseactivity D/Name:: Id: 10 ,Name: LifeStyle ,Image: lifestyle,Address: 2nd Floor, Forum Mall,Categort1: Fashion, Category2: Clothes,Category3: Home,Catgeory4: Mobile
01-14 16:07:49.687 7993-7993/com.trillbit.databaseactivity D/Name:: Id: 11 ,Name: Ezone ,Image: ezone,Address: 3rd Floor, Forum Mall,Categort1: Mobile, Category2: Electronics,Category3: Apppliances,Catgeory4: Home
01-14 16:07:49.687 7993-7993/com.trillbit.databaseactivity D/Name:: Id: 12 ,Name: Dominos ,Image: dominos,Address: 1st Floor,Forum Mall,Categort1: Food, Category2: Pizza,Category3: Cake,Catgeory4: Pastery
Bill Bell
  • 21,021
  • 5
  • 43
  • 58
shashikant burnwal
  • 61
  • 7
  • Possible duplicate of [How can I execute something just once per application start?](http://stackoverflow.com/questions/7360846/how-can-i-execute-something-just-once-per-application-start) – OneCricketeer Jan 14 '17 at 15:54

3 Answers3

0

Create a UNIQUE CONSTRAINT in the database table definition.

It will automatically prevent duplicate entries.

OR

Instead of

INSERT ...

do

INSERT OR IGNORE ...

This means that instead of returning an error code if you violate a constraint, SQLite just returns SQLITE_OK as if the command succeeded.

Moien.Dev
  • 1,030
  • 2
  • 10
  • 18
0

You add the those rows every single time your Activity is created.

Use SharedPreferences to mark a boolean that says your app started once

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
0

For others benefit, I am writing how I solved using shared preferences as suggested by cricket-007. At start of main activity, created database when application gets installed and run first time.

SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(this);
        if(!prefs.getBoolean("firstTime", false)) {
            // run your one time code
            // Inserting Shops

            Log.d("Insert: ", "Inserting ..");
            db.addShop(new Shop("01","Shoppers Stop","shopperstop","1st Floor,Forum Mall","Fashion","Clothes","Home","Shoes"));
            db.addShop(new Shop("02","LifeStyle", "lifestyle", "2nd Floor, Forum Mall", "Fashion", "Clothes","Home", "Mobile"));
            db.addShop(new Shop("03","Ezone", "ezone","3rd Floor, Forum Mall","Mobile","Electronics","Apppliances","Home"));
            db.addShop(new Shop("04","Dominos", "dominos","1st Floor,Forum Mall","Food","Pizza","Cake","Pastery"));
            SharedPreferences.Editor editor = prefs.edit();
            editor.putBoolean("firstTime", true);
            editor.commit();
        }
shashikant burnwal
  • 61
  • 7