Increment a counter in database SQLite

Question

I'm doing a project on estimote shoe sticker and I'm now doing database for the sticker. My issue is now how to set counter in database sqlite. Every once the sticker is picked up or clicked, in the database it will show an increment in count. I have post the codes for database and main activity below.

NearablesDemoActivity.java

 private void displayCurrentNearableInfo() {
    stickerdb = new Database_sticker(this);
    dbRow = stickerdb.getResult(currentNearable.identifier);
    dbRow.getId();
    dbRow.getIdentifier();
    count = stickerdb.getCount(currentNearable.identifier);
    dbRow.getCount();

    String desc = dbRow.getDesc().toString();
    dbRow.getCa().toString();
    dbRow.getSa().toString();
    String coo = dbRow.getCoo().toString();
    String sm = dbRow.getSm().toString();
    String price = dbRow.getPrice().toString();

    //Set the text to the TextView
    Desc.setText(desc);
    COO.setText(coo);
    SM.setText(sm);
    Price.setText("$" + price);
}

Database_sticker.java

public int getCount(String identifier) {
    String countQuery = "UPDATE" + TABLE_SRESULT + " SET " + KEY_COUNT + "=" + KEY_COUNT + "+1"
            + " WHERE " + KEY_IDENTIFIER + "='" + identifier + "'";
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    if(cursor != null && !cursor.isClosed()){
        cursor.close();
    }
    return cursor.getCount();
}


public Sresult getResult(String identifier) {
    String selectQuery = "SELECT * FROM " + TABLE_SRESULT + " WHERE " + KEY_IDENTIFIER + "='" + identifier + "'";
    SQLiteDatabase db = this.getWritableDatabase();   //open database
    Cursor cursor = db.rawQuery(selectQuery, null);
    //looping through all rows and adding to list
    Sresult sresult = new Sresult();
    if (cursor.moveToFirst()) {
        do {
            sresult.setId(Integer.parseInt(cursor.getString(0)));
            sresult.setIdentifier(cursor.getString(1));
            sresult.setDesc(cursor.getString(2));
            sresult.setSa(cursor.getString(3));
            sresult.setCa(cursor.getString(4));
            sresult.setCoo(cursor.getString(5));
            sresult.setSm(cursor.getString(6));
            sresult.setPrice(Float.parseFloat(cursor.getString(7)));
            sresult.setCount(Integer.parseInt(cursor.getString(8)));
        } while (cursor.moveToNext());
    }
    return sresult;
}

ListNearablesActivity.java

beaconManager.setNearableListener(new BeaconManager.NearableListener() {
            @Override
            public void onNearablesDiscovered(List<Nearable> nearables) {
                toolbar.setSubtitle("Found shoes: " + nearables.size());
                adapter.replaceWith(nearables);
                for (Nearable nearable : nearables) {
                    if (nearable.isMoving) {
                        try {
                            Class<?> clazz = Class.forName(getIntent().getStringExtra(EXTRAS_TARGET_ACTIVITY));
                            Intent intent = new Intent(ListNearablesActivity.this, clazz);
                            intent.putExtra(EXTRAS_NEARABLE, adapter.getItem(nearables.indexOf(nearable)));
                            startActivity(intent);
                        } //close for try
                        catch (ClassNotFoundException e) {
                            Log.e(TAG, "Finding class by name failed", e);
                        } //close for catch (ClassNotFoundException e)
                    }
                }
            } //for override
        });  //for beaconManager.setNearable



private AdapterView.OnItemClickListener createOnItemClickListener() {
        return new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, final int position, long id) {
                if (getIntent().getStringExtra(EXTRAS_TARGET_ACTIVITY) != null){
                    try {
                        Class<?> clazz = Class.forName(getIntent().getStringExtra(EXTRAS_TARGET_ACTIVITY));
                        Intent intent = new Intent(ListNearablesActivity.this, clazz);
                        intent.putExtra(EXTRAS_NEARABLE, adapter.getItem(position));
                        startActivity(intent);
                    } //close for try
                    catch (ClassNotFoundException e) {
                        Log.e(TAG, "Finding class by name failed", e);
                    } //close for catch (ClassNotFoundException e)
                } //close for getintent.getStringExtra()
            } //close for public void onitemclick
        };   //close for return new adapterview
    }  //close for private adapter

Solution

Database_sticker.java

 public int getStickerCount(String identifier) {
        String countQuery = "UPDATE " + TABLE_SRESULT + " SET " + KEY_SCOUNT + " = " + KEY_SCOUNT + "+1" + " WHERE " + KEY_IDENTIFIER + "= '" + identifier + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int scount = 0;
        if (cursor.moveToFirst())
        {
            do
            {
                scount = Integer.parseInt(cursor.getString(8));
            } while(cursor.moveToNext());
        }
        return scount; }

Hi @calvinfly, I have made edits to the code but the application still crash when I click on the button — keke
– keke, Commented Nov 2, 2015 at 2:45

KishuDroid · Accepted Answer · 2015-10-30 09:52:37Z

2

For incrementing value by one in counter , you need to fire UPDATE query to Table.

Like below :

UPDATE "Your tablename"
SET "counter column name"= "counter column name"+ 1
WHERE id= "pass your reference id";

Hope it will help you.

answered Oct 30, 2015 at 9:52

KishuDroid

5,4494 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

keke Over a year ago

Hi, I have add in the following codes, please take a look and see if there is anything that needs improvement or changes

KishuDroid Over a year ago

String countQuery = "UPDATE " + TABLE_SRESULT + " SET " + KEY_COUNT + " = " + KEY_COUNT + "+1" + " WHERE " + KEY_IDENTIFIER + "= " + identifier + "'"; Log.e("countQuery",countQuery)

keke Over a year ago

There's still error even though adding in those codes

KishuDroid Over a year ago

What is countQuery ?

keke Over a year ago

Hi, I've already solve the problem, I will update the solution

Collectives™ on Stack Overflow

Increment a counter in database SQLite

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related