Pull Data From Specific Row in SQLiteDatabase

Question

My app has one RecyclerView list and it's populated with data in the SQLite table: UserDatabase. I do this by calling the method getAllUser from in the DbHelper class I've shown below. I also have a method in my Recylcerview's ListAdaptor which starts ItemEditActivty on a list item's click. ItemEditActivity allows the user to edit or delete the row's data that they just clicked/opened with the methods UpdateUserDetail and DeleteRowItem, as shown below.

This all works perfectly, however, I have no idea how to set the three EditTexts of ItemEditActivity to the row's Table values of NAME, QUANTITY and DESCRIPTION; as stored in the table. Currently, when the user opens ItemEditActivity the EditText's et_name, et_quantity and et_description are blank. How do I set these EditText's to their data saved in their row of the Table, during onCreate?

EDIT: I get the selected list position item by passing an intent of from the ListAdapter and setting the variable to itemPosition in ItemEditActivity.

DbHelper Class:

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.provider.SyncStateContract;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class DbHelper extends SQLiteOpenHelper {
    String stringo;

    private static final String TAG = "DbHelper";
    private static final String DATABASE_NAME = "UserDatabase";
    private static final int DATABASE_VERSION = 1;
    private static DbHelper mDbHelper;

    public static String TABLE_USERdETAIL = "userdetail";

    private static final String _ID = "_id";
    private static final String NAME = "name";
    private static final String QUANTITY = "quantity";
    private static final String DESCRIPTION = "description";

    public static synchronized DbHelper getInstance(Context context) {
        if (mDbHelper == null) {
            mDbHelper = new DbHelper(context.getApplicationContext());
        }
        return mDbHelper;
    }

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


    @Override
    public void onCreate(SQLiteDatabase db) {

        String CREATE_USERDETAIL_TABLE = "CREATE TABLE " + TABLE_USERdETAIL +
                "(" +
                _ID + " INTEGER PRIMARY KEY ," +
                NAME + " TEXT," +
                QUANTITY + " INTEGER," +
                DESCRIPTION + " TEXT" +
                ")";


        db.execSQL(CREATE_USERDETAIL_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion != newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERdETAIL);

            onCreate(db);
        }
    }

    /*
   Insert a  user detail into database
   */

    public void insertUserDetail(UserData userData) {

        SQLiteDatabase db = getWritableDatabase();

        db.beginTransaction();

        try {
            ContentValues values = new ContentValues();
            values.put(NAME, userData.name);
            values.put(QUANTITY, userData.quantity);
            values.put(DESCRIPTION, userData.description);


            db.insertOrThrow(TABLE_USERdETAIL, null, values);
            db.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
            Log.d(TAG, "Error while trying to add post to the database insertUserDetail");
        } finally {
            db.endTransaction();
        }


    }

    public void updateUserDetail(int id, String v1, String v2, String v3){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(NAME, v1);
        values.put(QUANTITY, v2);
        values.put(DESCRIPTION, v3);
        db.update(TABLE_USERdETAIL, values, "_id="+id, null);


    }

   /*
   fetch all data from UserTable
    */

    public List<UserData> getAllUser() {

        List<UserData> usersdetail = new ArrayList<>();

        String USER_DETAIL_SELECT_QUERY = "SELECT * FROM " + TABLE_USERdETAIL;

        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(USER_DETAIL_SELECT_QUERY, null);

        try {
            if (cursor.moveToFirst()) {
                do {
                    UserData userData = new UserData();
                    userData.id = cursor.getString(cursor.getColumnIndex(_ID));
                    userData.name = cursor.getString(cursor.getColumnIndex(NAME));
                    userData.quantity = cursor.getString(cursor.getColumnIndex(QUANTITY));
                    userData.description = cursor.getString(cursor.getColumnIndex(DESCRIPTION));


                    usersdetail.add(userData);

                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to get posts from database");
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return usersdetail;

    }

    /*
   Delete single row from UserTable
     */
    void deleteRow(String name) {
        SQLiteDatabase db = getWritableDatabase();
        try {
            db.beginTransaction();
            db.execSQL("delete from " + TABLE_USERdETAIL + " where name ='" + name + "'");
            db.setTransactionSuccessful();
            Log.d(TAG, "delete called");
        } catch (SQLException e) {
            Log.d(TAG, "Error while trying to delete  users detail");
        } finally {
            db.endTransaction();
        }
    }

    void deleteRowItem(String id) {
        SQLiteDatabase db = getWritableDatabase();
        try {
            db.beginTransaction();
            db.execSQL("delete from " + TABLE_USERdETAIL + " where _id ='" + id + "'");
            db.setTransactionSuccessful();
            Log.d(TAG, "delete called");
        } catch (SQLException e) {
            Log.d(TAG, "Error while trying to delete  users detail");
        } finally {
            db.endTransaction();
        }
    }

}

ItemEditActivity Class:

import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.EditText;
import android.widget.Toast;

public class ItemEditActivity extends AppCompatActivity {
    private static final String TAG = "ItemCreateActivity";
    DbHelper dbHelper;
    EditText et_name, et_quantity, et_description;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_item_creation);
        setTitle("Edit Item");


        Intent Intent = getIntent();
        Integer itemPostion = Intent.getIntExtra("ItemNumber", 1)


        dbHelper = DbHelper.getInstance(getApplicationContext());
        et_name = (EditText) findViewById(R.id.et_name);
        et_quantity = (EditText) findViewById(R.id.et_quantity);
        et_description = (EditText) findViewById(R.id.et_description);




    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.character_edit_menu, menu);
        return super.onCreateOptionsMenu(menu);

    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        Intent Intent = getIntent();
        final Integer itemPostion = Intent.getIntExtra("ItemNumber", 1);

        switch (item.getItemId()) {
            case R.id.save_character:
                Log.d(TAG, "Save character creation activity");
                UserData userData = new UserData();

                if (!et_name.getText().toString().isEmpty()) {
                    userData.name = et_name.getText().toString();
                    if (!et_quantity.getText().toString().isEmpty()) {
                        userData.quantity = "Quantity: " + et_quantity.getText().toString();
                        if (!et_description.getText().toString().isEmpty()) {
                            userData.description = et_description.getText().toString();
                            dbHelper.updateUserDetail(itemPostion, et_name.getText().toString(), "Quantity: " + et_quantity.getText().toString(), et_description.getText().toString());

                            Toast.makeText(this, "Saved", Toast.LENGTH_SHORT).show();
                            finish();
                        } else {
                            Toast.makeText(this, "Enter Description", Toast.LENGTH_SHORT).show();
                        }
                    } else {
                        Toast.makeText(this, "Enter Quantity", Toast.LENGTH_SHORT).show();
                    }
                } else {
                    Toast.makeText(this, "Enter Name", Toast.LENGTH_SHORT).show();
                }
                return true;

            case R.id.delete_character:
                new AlertDialog.Builder(this)
                        .setMessage("This item will be deleted")
                        .setPositiveButton(android.R.string.ok, new DialogInterface.OnClickListener() {
                            public void onClick(DialogInterface dialog, int which) {
                                // continue with delete
                                dbHelper.deleteRowItem(itemPostion + "");
                                finish();
                            }
                        })
                        .setNegativeButton(android.R.string.cancel, new DialogInterface.OnClickListener() {
                            public void onClick(DialogInterface dialog, int which) {
                                // do nothing
                            }
                        })
                        .show();
                return true;

            default:
                return super.onOptionsItemSelected(item);
        }
    }
}

Arti · Accepted Answer · 2017-01-21 09:12:40Z

2

You have to create method in database to fetch single row. Create method as below in DbHelper class

 public UserData getSingleUserDetial(String userId) {
    SQLiteDatabase db = this.getWritableDatabase();
    UserData userData = null;

    Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_USERdETAIL + " WHERE " + _ID + "= ?", new String[]{userId});

    try {
        while (cursor.moveToNext()) 
        {
            userData = new UserData();
            userData.name = cursor.getString(cursor.getColumnIndex(NAME));
            userData.quantity = cursor.getString(cursor.getColumnIndex(QUANTITY));
            userData.description = cursor.getString(cursor.getColumnIndex(DESCRIPTION));

        }
        cursor.close();
    } catch (Exception e) {
        Log.d(TAG, "Error while trying to get data from database");
    } finally {
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
    }

    return userData;
}

And Fetch in ItemEditActivity like

UserData userData = dbHelper.getSingleUserDetial(userId);

edited Jan 21, 2017 at 9:12

answered Jan 21, 2017 at 5:56

Arti

9801 gold badge9 silver badges21 bronze badges

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

1 Comment

Tom James Over a year ago

Perfect, I'm very grateful. However, at the end of your 'while' there is a semicolon that I don't think should be there.

Collectives™ on Stack Overflow

Pull Data From Specific Row in SQLiteDatabase

DbHelper Class:

ItemEditActivity Class:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

DbHelper Class:

ItemEditActivity Class:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related