mardi 4 août 2015

Retrieving value from SQLite DB in Android?

I need some help. I'm reading values from an excel sheet (Apache POI) and inputting them into a database. In my excel doc and in my excel sheet called "GENERAL" I have three columns titled "general", each having 60 rows each.

@Override
public void onCreate(SQLiteDatabase db) {
    // Called when database needs to be newly created
    // Only create the database if it doesn't already exist

    // Load the general facts initially from the Excel sheet
        try {
            is = assetManager.open("Facts.xls");
            // Add each column of data to main ArrayList holder
            listHolder.add(getColumnContent(1, "GENERAL"));
            listHolder.add(getColumnContent(2, "GENERAL"));
            listHolder.add(getColumnContent(3, "GENERAL"));
        } catch (IOException e) {
            e.printStackTrace();
        }

        // Store in database now

    storeInDatabase(db);
}

Where listHolder is an ArrayList containing ArrayLists and getColumnContent gets the content from the specific column and returns and ArrayList object.

I store these in a database upon initial load-up after loading it into the ArrayList. This is what I do.

private void storeInDatabase(SQLiteDatabase db) {
        try {
            db.beginTransaction();
            db.execSQL("CREATE TABLE general (_id INTEGER PRIMARY KEY AUTOINCREMENT, general1 TEXT, general2 TEXT, general3 TEXT);");
            ContentValues cv = new ContentValues();
            for(int i = 0; i < listHolder.size(); i++) {
                for(int j = 0; j < listHolder.get(i).size(); j++) {
                    cv.put("general" + Integer.toString(i + 1), listHolder.get(i).get(j));
                    db.insert("general", null, cv);
                }
            }

            db.setTransactionSuccessful();

        } finally {
            db.endTransaction();
        }
    }

Well, that is all and well but my problem comes when retrieving it.

I want to retrieve a specific value corresponding with an ID value. Here is a part of the switch statement I use to get my Cursor object.

private Cursor getCursor(int m, int column) {
        switch(column) {
            case 0:
                String[] columns1 = new String[]{"_id", "general1"};
                String where1 = "_id=?";
                String[] whereArgs1 = new String[]{m+""};

                Cursor c1 = db.query(Utils.GENERAL, columns1, where1 , whereArgs1, null, null, null);
                return c1;

So as far as my understanding goes, creating a table with _id will automatically add an ID number each time I add values, so ID 1 would be associated with my first row for each column, and so on.

I'm not really sure if I am doing it right, but I want to grab a specific value from a column at a specific id.

So if this was my table I would want to grab the value from column general1 which is at ID # 3, thirdrow:

_id        general1        general2       
1          firstrow         firstrow
2          secondrow        secondrow
3          thirdrow         thirdrow

After I get the Cursor, I try to do this

Cursor c = getCursor(3, 0);

     if(c.moveToFirst()) {
                    String fact = c.getString(c.getColumnIndexOrThrow("general1"));
                    return fact;
                }

though moveToFirst always returns false. Can someone help me troubleshoot why this is happening?

Aucun commentaire:

Enregistrer un commentaire