Populating SQLite Database Via File or Adapter

Question

I am trying to populate a table in my SQLite database but I am running into some trouble. I am trying to add the data manually via inserts in android studio rather then in the app. I have created a database adapter and from here I call the insertRow command.

My app is going to scan barcodes and then query the database with the number received for the product name. I am trying to populate the barcode database with number and names. Is there a better way of adding the numbers and names then below? as every time I open this fragment the data gets added again

Fragment

package app.rory.pocket_chef.Fragments;

import android.app.AlertDialog;
import android.app.Fragment;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;

import java.sql.SQLException;

import javax.xml.transform.Result;

import app.rory.pocket_chef.Adapters.Barcodes_Adapter;
import app.rory.pocket_chef.Adapters.DBAdapter;
import app.rory.slidemenu.R;
import me.dm7.barcodescanner.zxing.ZXingScannerView;

/**
 * Created by Rory on 10/22/2014.
 */


public class scan_Foods_Fragment extends Fragment implements ZXingScannerView.ResultHandler{

    private Barcodes_Adapter bar;
    private DBAdapter db;

    private ZXingScannerView scannerView;

    View rootview;
    @Nullable
    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        rootview = inflater.inflate(R.layout.scan_foods_layout, container, false);

        scannerView = (ZXingScannerView) rootview.findViewById(R.id.scanner_view);
        scannerView.startCamera();
        scannerView.setResultHandler(this);

        return rootview;
    }

    @Override
    public void handleResult(com.google.zxing.Result result) {
        AlertDialog.Builder alert = new AlertDialog.Builder(getActivity());

        alert.setTitle(result.getBarcodeFormat().toString());
        String product = productReturn(result);

        alert.setMessage("You have " + product);
        alert.show();
    }

    //Queries the barcode database for product ID
    public String productReturn(com.google.zxing.Result barcode) {

        //open DB
        try {
            db.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        bar.createBarcode(“butter”, 011069001254);
        bar.createBarcode(“milk”, 011056020428);
        bar.createBarcode(“cheese”, 390413010228);
        bar.createBarcode(“ham”, 011069162580);
        bar.createBarcode(“chicken”, 099874147596);
        bar.createBarcode(“thai spice”, 50020768);
        bar.createBarcode(“rice”, 010034514003);
        bar.createBarcode(“soy sauce”, 000111044240);

        //Query DB
        String result = bar.getProduct(barcode);

        //Close DB
        db.close();

        return result;
    }

}

Adapter

package app.rory.pocket_chef.Adapters;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.google.zxing.Result;

import java.sql.SQLException;

/**
 * Created by Rory on 06/10/15.
 */
public class Barcodes_Adapter {

    public static final String ROW_ID = "_id";
    public static final String NAME = "name";
    public static final String NUMBER = "number";

    private static final String DATABASE_TABLE = "barcodes";

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DBAdapter.DATABASE_NAME, null, DBAdapter.DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     *
     * @param ctx
     *            the Context within which to work
     */
    public Barcodes_Adapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the public database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     *
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException
     *             if the database could be neither opened or created
     */
    public Barcodes_Adapter open() throws SQLException {
        this.mDbHelper = new DatabaseHelper(this.mCtx);
        this.mDb = this.mDbHelper.getWritableDatabase();
        return this;
    }

    /**
     * close return type: void
     */
    public void close() {
        this.mDbHelper.close();
    }

    /**
     * Create a new barcode. If the barcode is successfully created return the new
     * rowId for that barcode, otherwise return a -1 to indicate failure.
     *
     * @param name
     * @param number
     * @return rowId or -1 if failed
     */
    public long createBarcode(String name, int number){
        ContentValues initialValues = new ContentValues();
        initialValues.put(NAME, name);
        initialValues.put(NUMBER, number);
        return this.mDb.insert(DATABASE_TABLE, null, initialValues);
    }



    /**
     * Delete the barcode with the given rowId
     *
     * @param rowId
     * @return true if deleted, false otherwise
     */
    public boolean deleteBarcode(long rowId) {

        return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$
    }

    /**
     * Return a Cursor over the list of all barcodes in the database
     *
     * @return Cursor over all barcodes
     */
    public Cursor getAllBarcodes() {

        return this.mDb.query(DATABASE_TABLE, new String[] { ROW_ID,
                NAME, NUMBER }, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the barcode that matches the given rowId
     * @param rowId
     * @return Cursor positioned to matching barcode, if found
     * @throws SQLException if barcode could not be found/retrieved
     */
    public Cursor getBarcode(long rowId) throws SQLException {

        Cursor mCursor =

                this.mDb.query(true, DATABASE_TABLE, new String[] { ROW_ID, NAME,
                        NUMBER}, ROW_ID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    /**
     * Returns Product name for Barcode number
     * @return String of product
     * @param barcode
     */
    public String getProduct(Result barcode) {

        Cursor result = this.mDb.query(true, DATABASE_TABLE, new String[]{NAME}, NUMBER + "=" + barcode, null, null, null, null, null);

        result.moveToFirst();
        String product = result.getString(result.getColumnIndex("name"));

        return product;

    }

    /**
     * Update the barcode.
     *
     * @param rowId
     * @param name
     * @param number
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateBarcode(long rowId, String name, String number,
                             String year){
        ContentValues args = new ContentValues();
        args.put(NAME, name);
        args.put(NUMBER, number);

        return this.mDb.update(DATABASE_TABLE, args, ROW_ID + "=" + rowId, null) >0;
    }
}

please add some code and few more details related to your database like DatabaseHelper class — PunitD, Oct 27 '15 at 12:26
How do I create a prefilled database? @Droidwala updated the question with code — Hayes121, Oct 27 '15 at 12:31
Put the db in the assets folder. At run time che if the file is present in `/data/data/....`. If not, copy it. Done. — Phantômaxx, Oct 27 '15 at 12:34
So just create a new java class in the assets foder containing the table create and all the inserts? then as you said check that its there at runtime? — Hayes121, Oct 27 '15 at 12:42
what are the lines like `bar.createBarcode(“butter”, 011069001254);` and similar lines doing inside your **productReturn** method? Because a call to createBarcode is basically inserting entries into your db — PunitD, Oct 27 '15 at 12:57
That was a mistake, but I tried moving them to the adapter itsefl but I cant call the createBarcode function from there for some reason — Hayes121, Oct 27 '15 at 13:09
No. In `assets`, you put the prefilled .db file. See this post: http://stackoverflow.com/questions/513084/how-to-ship-an-android-application-with-a-database — Phantômaxx, Oct 27 '15 at 13:14
@Hayes121 I will write detailed answer where changes need to be made according to your requirement in few hours once i reach my development machine at home.. Meantime can you tell where do you create your table, i don't see a create statement to create table in your code? — PunitD, Oct 27 '15 at 13:39
Also,the approach provided by @FrankN.Stein is a valid approach in case your product inventory doesn't contain large amount of items..otherwise shipping db contain large amount of rows along with your apk is not advisable.. it increases the size of your apk unnecessarily.. — PunitD, Oct 27 '15 at 13:42
@Droidwala, I am using more then one table so I have a adpater for each table and then on main DB adapeter where I call all the table creates, thanks for doing it — Hayes121, Oct 27 '15 at 15:52
Np..One more question..Will you be inserting product barcode values only once(first time app is opened) or do you intend to add products later on using `createBarcode()` method? — PunitD, Oct 27 '15 at 16:03
Just once, perhaps in a future update down the line I may add more though — Hayes121, Oct 27 '15 at 16:19

score 0 · Accepted Answer · answered Oct 27 '15 at 16:35

First:
Comment out all the createBarcode() lines used inside productReturn() method.

public String productReturn(com.google.zxing.Result barcode) {

    //open DB
    try {
        db.open();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    /* comment out this lines
    bar.createBarcode(“butter”, 011069001254);
    bar.createBarcode(“milk”, 011056020428);
    bar.createBarcode(“cheese”, 390413010228);
    bar.createBarcode(“ham”, 011069162580);
    bar.createBarcode(“chicken”, 099874147596);
    bar.createBarcode(“thai spice”, 50020768);
    bar.createBarcode(“rice”, 010034514003);
    bar.createBarcode(“soy sauce”, 000111044240);
                                                  */
    //Query DB
    String result = bar.getProduct(barcode);

    //Close DB
    db.close();

    return result;
}

Second:
Change your createBarcode() method code like below:

public long createBarcode(){
    ContentValues initialValues = new ContentValues();
    initialValues.put(“butter”, 011069001254);
    initialValues.put(“milk”, 011056020428);
    initialValues.put(“cheese”, 390413010228);
    initialValues.put(“ham”, 011069162580);
    initialValues.put(“chicken”, 099874147596);
    initialValues.put(“thai spice”, 50020768);
    initialValues.put(“rice”, 010034514003);
    initialValues.put(“soy sauce”, 000111044240);
    //Similarly you could add new products to the table in future version..
    return this.mDb.insert(DATABASE_TABLE, null, initialValues);
}

Third:
If possible , Call createBarCode() immediately after creating your barcodes table

And If not, then you could use your getAllBarcodes() inside productReturn() method like below and accordingly decide whether to call changed createBarcode() method or not.

public String productReturn(com.google.zxing.Result barcode) {

    //open DB
    try {
        db.open();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    Cursor c = bar.getAllBarCodes();
    if(c.getCount() == 0)
       bar.createBarcode();// we call createBarcode only if value don't exist inside db.

    /* comment out this lines
    bar.createBarcode(“butter”, 011069001254);
    bar.createBarcode(“milk”, 011056020428);
    bar.createBarcode(“cheese”, 390413010228);
    bar.createBarcode(“ham”, 011069162580);
    bar.createBarcode(“chicken”, 099874147596);
    bar.createBarcode(“thai spice”, 50020768);
    bar.createBarcode(“rice”, 010034514003);
    bar.createBarcode(“soy sauce”, 000111044240);
                                                  */
    //Query DB
    String result = bar.getProduct(barcode);

    //Close DB
    db.close();

    return result;
}

Hope it helps!

@Hayes121 Let me know if the above changes work well in your case!! — PunitD, Oct 28 '15 at 08:19

Populating SQLite Database Via File or Adapter

1 Answers1