Android Sqlite: How to Retrieve Specific Data from Particular Column

Android sqlite: how to retrieve specific data from particular column?

try this:

String query = "SELECT * FROM todo WHERE category='" + vg;

Cursor  cursor = database.rawQuery(query,null);

        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;

Retrieve Specific Columns from SQLite Database

If you want to fetch only selected fields of table, then use the following query:

SELECT Coulmn1, Coulmn2, Coulmn3 FROM TABLENAME;

or u want all data

SELECT * FROM TABLENAME;

for more info https://www.tutorialspoint.com/sqlite/sqlite_select_query.htm

how to select particular column data in SQLite db in android studio?

The issue you have, assuming that the active user string is a valid exisitng user, is that you are trying to extract from the position that is effectively before the first row.

That is, you need to MOVE to a row in the Cursor.

Additionally the Cursor's getInt method expects the offset of the column from which to get the data, which would be 0. However, it is better (less prone to error and generally more flexible) to use the Cursor's getColumnIndex method to retrieve the column's offset.

As such change :-

    public int previousBal(String activeuser){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery("select balance from transactions where email=? order by t_id desc limit 1",new String[]{activeuser});
        int balance = cursor.getInt(Integer.parseInt("balance"));
        return balance;

    }

to :-

    public int previousBal(String activeuser){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery("select balance from transactions where email=? order by t_id desc limit 1",new String[]{activeuser});
        if (cursor.moveToFirst()) {
            int balance = cursor.getInt(cursor.getColumnIndex("balance"));
        } else {
            balance = 0;
        }
        return balance;

    }

• This will move to the first row, if there is one, and extract the vale in the balance column of the cursor.
• If there is no row that matches the query's selection criteria then the else clause will set the balance to 0.
  
  
  • If multiple rows are extracted the value from the first row will be returned.

Addtional

Frequently the use of the SQLiteDatabase rawQuery is frowned upon unless it is necessary. In you case the SQLitedatabase query method can be used. As such, the recommended previousBal method would be :-

    public int previousBal(String activeuser){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor.query(
            "transactions",               // name of the table to query
            new String[]{"balanace"},     // String array of columns to extract
            "email=?",                    // WHERE clause (? indicates an arg)
            new String[]{activeuser},     // The list of args to replace the ? (or ?'s on a sequential basis)
            null,                         // GROUP BY clause
            null,                         // HAVING clause
            "t_id DESC",                  // ORDER clause
            "1"                           // LIMIT value as a String
        );
        if (cursor.moveToFirst()) {
            int balance = cursor.getInt(cursor.getColumnIndex("balance"));
        } else {
            balance = 0;
        }
        return balance;   
    }

• The query convenience method builds the SQL escaping characters and offers improved protection against SQL injection.

Retrieve specific column from SQLite database

You can try the following command to get particular columns list.

public String[] getit(String tablename)
{
    try {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor x = db.rawQuery("SELECT * FROM "+tablename, null);
    int n=x.getCount();
    x.moveToFirst();
    String[] a=new String[n];int i=0;
   do
    {
        a[i]=x.getString(x.getColumnIndex("medname"));
        i++;
    } while(x.moveToNext());

    x.close();
    return a;
    }
    catch (Exception e)
    {
        return null;
    }
}

Or change your code as,

  NamesList.add(cursor.getString(x.getColumnIndex("medname")));

In your email intent, use putExtra()

StringBuilder sb = new StringBuilder(); 
for (String s : db.getit("table")) {
    sb.append(s);
    sb.append("\n");
}
intent.putExtra(Intent.EXTRA_TEXT, sb.toString());

How can I retrieve a particular coloumn from SQLite in android?

Retrieving data from SQLite databases in Android is done using Cursors. The Android SQLite query method returns a Cursor object containing the results of the query. To use Cursors android.database.Cursor must be imported.

To get all the column values

Try this

DatabaseHelper mDbHelper = new DatabaseHelper(getApplicationContext());

SQLiteDatabase mDb = mDbHelper.getWritableDatabase();

Cursor cursor = mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
            KEY_DESIGNATION}, null, null, null, null, null);

To get a particular column data

Try this,

Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
              KEY_NAME, KEY_DESIGNATION}, KEY_ROWID + "=" + yourPrimaryKey, null,
              null, null, null, null);
        if (mCursor != null) {
          mCursor.moveToFirst();
        }

After getting the Cursor, you can just iterate for the values like

cur.moveToFirst(); // move your cursor to first row
// Loop through the cursor
        while (cur.isAfterLast() == false) {
             cur.getString(colIndex); // will fetch you the data
            cur.moveToNext();
        }

    cur.close();

Hope this solves your problem.

how to retrieve specific values from SQLite database (android) and display in an editable textview

If i understood well, your problem is "how can i pass to the activity the old data so the user can read them and update?".
You can send the old data with the putExtra().
With your intent.putExtra(SELECTED_DEVICE, selecteddevice); you send only the type of device, if you put other information you can take them back from the UpdateDevice and you can SetText() of your EditText with the old data!
Like that you don't need the Database for take back information.
Have you try this?

Try to Start activity with StartActivityForResult, and take back new information with:

protected void onActivityResult(int requestCode, int resultCode, Intent data) {
    if(requestCode == 1) {
        if (resultCode == RESULT_OK) {


            String result1 = data.getStringExtra("result1"); 
            String result2 = data.getStringExtra("result2");
            String result3 = data.getStringExtra("result3");

            //Update the database with new data
        }

        if (resultCode == RESULT_CANCELED) {
            //No change
        }
    }
}//onActivityResult

If you need the DataBase for take back information follow this link that is very helpfull Guide for SqliteHelper

So, you need a class that manage the devices with the getter and setter method.
In the same time you need to save data in your DataBase for the "future maintenace".
As you can see in the link with the method

getContact()
// Getting single contact
public Contact getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
        KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
        new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
    cursor.moveToFirst();

Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
        cursor.getString(1), cursor.getString(2));
// return contact
return contact;
}

You can take back all information for a single Contact (for you device) searching it by id.
Or the second method that can help you is

getAllContacts()
// Getting All Contacts
public List<Contact> getAllContacts() {
List<Contact> contactList = new ArrayList<Contact>();
// Select All Query
String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

// looping through all rows and adding to list
if (cursor.moveToFirst()) {
    do {
        Contact contact = new Contact();
        contact.setID(Integer.parseInt(cursor.getString(0)));
        contact.setName(cursor.getString(1));
        contact.setPhoneNumber(cursor.getString(2));
        // Adding contact to list
        contactList.add(contact);
    } while (cursor.moveToNext());
}

// return contact list
return contactList;
}

That return a list of all items inside the table, so you can check something like that (put that code where you need to read all data from database):

DatabaseHandler db = new DatabaseHandler(this);

    // Reading all device
    List<Device> devices = db.getAllContacts();       

    for (Device dev : devices) {

          if(dev.getNameDevice() == DeviceToUpadeName){
            //do the get of all information
            device_number = dev.getDeviceNumber();//This getmethod is from you Device Class
            device_password = dev.getDevicePassword();
          }
}

All the information that i post here are from the link that i post.
I write some explanation for more help.
I hope it's all that you need!
PS:Remeber the Device class fot the method get and set!

Android SQLite how to get particular column:row value

You need to position the Cursor to the first row before you can get data from it. To do this you can call: mCursor.moveToFirst(). Also, this method call returns a boolean, which will be false if there are no results; so you can also use it to guard against that case.

If you need to iterate through multiple results, then after calling mCursor.moveToFirst(), you can use the mCursor.moveToNext() method go through the result rows one by one. Once again, this returns false when it reaches the end of the data set.

Hope that makes sense.

How to retrieve data of specific column and set it to edit text in android studio

In your 1st try, with this:

a = cursor.getString(cursor.getColumnIndex("balance"));

you try to get the value of the column "balance" instead of "alarm".

Your 2nd try looks ok, although you don't need the do loop since you return after you get the first value.

In your 3d try the error is:

cursor.getString(1);

The index 1 is wrong, it should be 0 because the column indexes are 0 based.

But the problem is also that you do not save the value anywhere and finally you return " "!!! Why?

You should do something like:

public String getAlarm(String al){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("select alarm from keywords where contact_number=? ",new String[]{al});
    String result = "";
    if (cursor.moveToFirst()) {
        result = cursor.getString(0);
    }
    cursor.close();
    return result;
}

---

Related Topics