Store Integer in SQL Database & Retrieve it in Fragment

Question

I'm setting up an Android app and have just gone through the Android Developer documentation to set up an SQL Database http://developer.android.com/training/basics/data-storage/databases.html#WriteDbRow

I have a MainActivity that has the navigation drawer set up: http://pastebin.com/9nyejZdV, and a switch within that activity to replace fragments when items are clicked in the nav drawer.

The two fragments that are switched are:

StartingFragment, which is displayed when the app is first opened: http://pastebin.com/740LepNV

TeaCounterFragment, which is displayed when the second item in my navdrawer is clicked: http://pastebin.com/edfEe1Gd

In StartingFragment, in the onClick method, whenever a button called "Oolong" is pressed, it updates a TextView in my TeaCounterFragment, that displays how many times the "Oolong" button has been pressed (by using String.valueOf(an_integer), and a Bundle).

The problem is that whenever the app is completely closed, the TextView resets to default, which has no count displayed. So, I figured that I would use an SQL Database to store the integer, so that when the app is completely closed, the integer will still be stored. Then, when the app is opened back up, I can click on my NavDrawer and go to my TeaCounterFragment, and the number will still be there.

How can I achieve this?

Database Activity:

public final class Database {

    // To prevent someone from accidentally instantiating the database class,
    // give it an empty constructor.
    public Database() {
    }

    /* Inner class that defines the table contents */
    public static abstract class DBEntry implements BaseColumns {

        public static final String TABLE_NAME = "Number of Cups of Tea";
        public static final String COLUMN_NAME_ENTRY_ID = "entryid";
        public static final String COLUMN_NAME_TITLE = "Tea Counter Table";
        public static final String COLUMN_NAME_SUBTITLE_OOLONG = "Oolong";
    }

    private static final String TEXT_TYPE = " TEXT";
    private static final String COMMA_SEP = ",";
    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + DBEntry.TABLE_NAME + " (" +
                    DBEntry._ID + " INTEGER PRIMARY KEY," +
                    DBEntry.COLUMN_NAME_ENTRY_ID + TEXT_TYPE + COMMA_SEP +
                    DBEntry.COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP +
                    DBEntry.COLUMN_NAME_SUBTITLE_OOLONG + TEXT_TYPE + COMMA_SEP +
                    " )";

    private static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS" + DBEntry.TABLE_NAME;

    public class DatabaseHelper extends SQLiteOpenHelper {

        public static final String DATABASE_NAME = "TeaCounter.db";
        public static final int DATABASE_VERSION = 1;

        Context context;
        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            this.context = context;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(SQL_CREATE_ENTRIES); //creates Database
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // This database is only a cache for online data, so its upgrade policy is
            // to simply to discard the data and start over
            db.execSQL(SQL_DELETE_ENTRIES);
            onCreate(db);
        }

        public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            onUpgrade(db, oldVersion, newVersion);
        }

        public void storeCounts() {
            DatabaseHelper mDbHelper = new DatabaseHelper(context);
            SQLiteDatabase db = mDbHelper.getWritableDatabase();

            // Create a new map of values, where column names are the keys
            ContentValues values = new ContentValues();
            values.put(DBEntry.COLUMN_NAME_ENTRY_ID, 1);
            values.put(DBEntry.COLUMN_NAME_TITLE, "Tea Counter Table");
            values.put(DBEntry.COLUMN_NAME_SUBTITLE_OOLONG, "Oolong Tea");
        }
    }
}

I thought of using an SQL Database because I would like to be able to do this for multiple buttons. However, I'm also open to suggestions of any other way of achieving this.

@njzk2 I'm guessing it should be something like.."NUM_CUPS" ? — freddiev4, Jan 08 '15 at 21:22
possibly. There are a few constraints on table names, see http://stackoverflow.com/questions/3694276/what-are-valid-table-names-in-sqlite — njzk2, Jan 08 '15 at 21:35

score 1 · Answer 1 · answered Jan 08 '15 at 21:22

1

Since you want to store only an integer, you should use shared preferences instead of a database.

answered Jan 08 '15 at 21:22

Simar

600
4
17

Even if I want to store integers for multiple button click counts? Because each button is going to have different integers depending on how many times each of them have been clicked. – freddiev4 Jan 08 '15 at 21:24
Yes. The shared preferences are just a collection of name vale pairs. Button1 5, Button2 8 and so on. – Simar Jan 08 '15 at 21:25
OK. Before I scrap the Database though...what if I wanted to later display these integers in a spreadsheet/table, on another fragment? And split button counts into clicks per day, per week, per month, per year? Could I still do that if I'm using SharedPreferences? Or would a Database then be more useful? – freddiev4 Jan 08 '15 at 21:28
OK. So then SharedPreferences is the best way to go about doing this. One more quick question for you then: When _should_ I actually use a database? As in...what cases would it be more appropriate to use a database over sharedpreferences? – freddiev4 Jan 08 '15 at 21:33
When you have a large data set. Or different attributes for a same thing. In this case for a button you just have the number of times it was clicked. But in case of lets say a car you have model, color, so on.. – Simar Jan 08 '15 at 21:34
When your project might get more complex you will end up using a sqlite db. That's what the guys here mentioned before. I can recommend a quite nice tutorial for sqlite when you're interested --> http://www.vogella.com/tutorials/AndroidSQLite/article.html – Kody Jan 08 '15 at 21:54

gyosida · Accepted Answer · 2015-01-08T22:38:32.643

1

If you just want to store a Integer, maybe you could take a look to the DefaultSharedPreferences class. See this link : http://developer.android.com/reference/android/content/SharedPreferences.html

if you are doing this from your fragment:

SharedPreferences pref = getActivity().getSharedPreferences("keyname", Context.MODE_PRIVATE);
SharedPreferences.Editor editor = pref.edit();
editor.putInt("counter", integer);
editor.commit();

Then when you want to retrieve it, assuming you already have the SharedPreferences instance

Integer counter = pref.getInt("counter", 0);

Hope it helps you.

edited Jan 08 '15 at 22:38

answered Jan 08 '15 at 21:29

gyosida

450
4
17

In the first block of code, which Fragment would I put that code in? StartingFragment, or TeaCounterFragment? And then I'm assuming that I should retrieve it in TeaCounterFragment? – freddiev4 Jan 08 '15 at 21:37
Yeah. The editing code in the Starting Fragment and the retrieval code in the TeaCounterFragment – Simar Jan 08 '15 at 21:42
@Simar OK. There is another problem now though. I get this error: **java.lang.NumberFormatException: Invalid int: "null"** from writing **pref.getInt("counter", null)**. Actually, it won't even allow me to write "null". I have to wrap it with Integer.parseInt() and when I do that, I get the error – freddiev4 Jan 08 '15 at 21:57
The second argument is to be the default value in case theitem is not present in the shared prefs. Since its an integer you should write 0 or -1 or whatever you want. – Simar Jan 08 '15 at 22:02
@Simar Got it. There's a bug though. If I click on the "Oolong" button, 3 times, it will show the number 3 for the counter. If the app closes completely, and then it is reopened, it's still there. Good. That's what is supposed to happen. The problem is that if I then click on the button again, it does not _add on to_ the already existing number. Instead, it will start back at 1. – freddiev4 Jan 08 '15 at 22:07
do you retrieve the stored value, add +1 and then store it again? – gyosida Jan 08 '15 at 22:09
At the start of you application you need to initialise the counter from the preferences. Counter = getActivity().getSharedPreferences ().getInt ("counter", 0); so if the app is started for the first time there would be no value for the tag counter. So u ll get 0. But in subsequent runs of your app the counter would be initialised with the value that you dtored upon exit of the app the last time. – Simar Jan 08 '15 at 22:11
@Simar I've updated the code for StartingFragment & TeaCounterFragment in my original post (pastebin links) if you don't mind taking a look – freddiev4 Jan 08 '15 at 22:22
In the onCreateView of your first fragment, initialise the COUNT . COUNT = getActivity ().getSharedPreferences ("keyname", Context_MODE_PRIVATE).getInt ("counter", 0); – Simar Jan 08 '15 at 22:29
@Simar Got it! Thank you both for your tremendous help! – freddiev4 Jan 08 '15 at 22:37

Store Integer in SQL Database & Retrieve it in Fragment

2 Answers2