How to Retrieve Data from Cursor Class

How to retrieve data from cursor class

Once you have the Cursor object, you can do something like this:

if (cursor.moveToFirst()){
   do{
      String data = cursor.getString(cursor.getColumnIndex("data"));
      // do what ever you want here
   }while(cursor.moveToNext());
}
cursor.close();

How to retrieve data from cursor?

You need to write moveToFirst(); before fetching data from cursor, like below,

public String getSum(String code) 
{
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_OWNER, new String[] { KEY_SUM }, KEY_CODE + "=?", new String[] { code }, null, null, null, null);

    if ( cursor.getCount() > 0 )
    {
        cursor.moveToFirst();                                            // Add this line
        String data = cursor.getString(cursor.getColumnIndex("data"));
        return data;
    }
    return null;
}

How to retrieve data from a cursor in android?

You can use a while loop to iterate of all row the cursor returns.

while(c.moveToNext()){
    double lat = Double.parseDouble(c.getString(c.getColumnIndexOrThrow(ParkingSpotDBAdapter.KEY_LAT)));
    double lng = Double.parseDouble(c.getString(c.getColumnIndexOrThrow(ParkingSpotDBAdapter.KEY_LNG)));
    String name = c.getString(c.getColumnIndexOrThrow(ParkingSpotDBAdapter.KEY_ADDRESS));
}

Of course you have to do something with the lat, lng and name variable as they will get overridden on each iteration. I suggest you implement a DBLocation class which can store the name, longitude and latitude of a location. You can then store all the objects you create from the cursor in a array or list.

How do I retrieve data from database using cursor? (Android, SQLite)

Your problem happens when you populate the database (your first code segment).

You need to combine the INSERT statements:

db.execSQL("INSERT INTO MyFilms (Title, Director, Year) VALUES('" 
    + title + "','" + director + "'," + year + ");");

Otherwise, you wind up with rows that contain a null value for Title or Director.

(Note: Don't use the SQL notation above, because it may leave you vulnerable to SQL injection. I only wrote it this way for the sake of simplicity. In production code, you should use parameters or sanitize your values. Don't become a victim of Little Bobby Tables).

EDIT

Here are some threads regarding prepared statements:

• How do I use prepared statements in SQlite in Android?
• Queries with prepared statements in Android?

Using cursor to retrieve one row of data (all entries from one id)

You are fetching Id column in your query and you want entire one row for that Use this :

public Cursor getOneData(int id){
SQLiteDatabase db = this.getWritableDatabase();
Cursor res = db.rawQuery("select * from "+table_name+" WHERE id='" + id + "'",null);
if (res != null && res.moveToFirst()) { 
do { 
    //Call Your Function
   } 
while (res.moveToNext()); 
}
 return res;
 }

Get the field value with a Cursor

I think you can forget about checking for null.

Instead check if there is data and then access the columns using the cursor:

Cursor cursor = fetchOption(0);

if (cursor.moveToFirst()) // data?
   System.out.println(cursor.getString(cursor.getColumnIndex("title")); 

cursor.close(); // that's important too, otherwise you're gonna leak cursors

It might also make sense to read an Android tutorial. The notepad tutorial seems to fit the bill: http://developer.android.com/guide/tutorials/notepad/index.html

how to retrieve data from sqlite

As you have a User class then you can extract all the data and return a User.

You will obviously need to identify the user that is to be returned and later updated. Typically you would store the id column, as a long, in the User class although that may not be necessary, although as none of the other columns are defined with the UNIQUE constraint using the id column would be the correct way to avoid any ambiguities.

So if you haven't already defined a member of the class for the id then it would be suggested to do so e.g. the User class could be (the example also makes use of the additional constructors) :-

User.java

public class User {

    private long id;
    private String name;
    private String email;
    private String password;

    public User() {
    }

    public User(String name, String email, String password) {
        this(-1,name,email,password);
    }

    public User(long id, String name, String email, String password) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.password = password;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

Databasehelper.java (additional methods)

The DatabaseHelper class could have various methods to extract a User object such as :-

public User getUserByID(long id) {
    User rv = new User();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.query(TABLE_USER,null,COLUMN_USER_ID + "=?",new String[]{String.valueOf(id)},null,null,null);
    if (cursor.moveToFirst()) {
        rv.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_USER_ID))); //<<<<<<<<<<optional
        rv.setName(cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME)));
        rv.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
        rv.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
    }
    cursor.close();
    db.close();
    return rv;
}

public User getUserByName(String name) {
    User rv = new User();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.query(TABLE_USER,null,COLUMN_USER_NAME + "=?",new String[]{name},null,null,null);
    if (cursor.moveToFirst()) {
        rv.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_USER_ID))); //<<<<<<<<<<optional
        rv.setName(cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME)));
        rv.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
        rv.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
    }
    cursor.close();
    db.close();
    return rv;
}
public User getUserByEmail(String email) {
    User rv = new User();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.query(TABLE_USER,null,COLUMN_USER_EMAIL + "=?",new String[]{email},null,null,null);
    if (cursor.moveToFirst()) {
        rv.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_USER_ID))); //<<<<<<<<<<optional
        rv.setName(cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME)));
        rv.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
        rv.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
    }
    cursor.close();
    db.close();
    return rv;
}

• Note that getUserById being the preferable method as there can be no ambiguities.

As the id is definitely not ambiguous you may wish to re-consider the checkUser so that it not only checks the user BUT also returns the id of the checked user. Hence this method, has also been added :-

public long checkUserGettingId(String email, String password) {
    long rv = -1;
    String[] columns = {
            COLUMN_USER_ID
    };
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.query(TABLE_USER,columns,COLUMN_USER_EMAIL + "=? AND " + COLUMN_USER_PASSWORD + "=?",new String[]{email,password},null,null, null);
    if (cursor.moveToFirst()) {
        rv = cursor.getLong(cursor.getColumnIndex(COLUMN_USER_ID));
    }
    cursor.close();
    db.close();
    return rv;
}

Lastly you could have the following methods for updating, these both based upon updating the User object :-

public int updateUserByID(User user, long id) {
    int rv = 0;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(COLUMN_USER_NAME,user.getName());
    cv.put(COLUMN_USER_EMAIL,user.getEmail());
    cv.put(COLUMN_USER_PASSWORD,user.getPassword());
    rv = db.update(TABLE_USER,cv,COLUMN_USER_ID + "=?",new String[]{String.valueOf(id)});
    db.close();
    return rv;
}
public int updateUser(User user) {
    return updateUserByID(user, user.getId());
}

- the subtle difference being that the first, as it uses an independent **id** could be a little more flexible.

Example usage

The following is code (in an Activity in this case) that utilises many of the methods above.

It

1. initially adds 2 users.
2. then effectively logs in using the checkUserGettingID method.
3. Extracts the user data via the getUserById method to get the data for the logged in user as a User object (mCurrentUser).
4. Outputs the user data to the log.
5. Updates the user data and again retrieves the data logging the changed data.
6. Extracts the user data (updated) via the getUserByEMail method and outputs the data to the log.
7. Extracts the user data (updated) via the getUserByName method and outputs the data to the log.
8. Extracts the 2nd user data via the getUserByEmail method and outputs the data to the log.
9. Extracts a non-existent user's data via the getUserByEmail** method and outputs the data (all values are nulls, except id which is 0 (not an auto generated id as the first will be 1)) to the log. Note you would typically check the user exists by checking for a valid id and or null values.

The code used being :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper mDBHlpr;
    User mCurrentUser;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBHlpr = new DatabaseHelper(this);

        //Add 2 Users
        mDBHlpr.addUser(new User("Fred","Fred@fredmail.com","password"));
        mDBHlpr.addUser(new User("Alice","Alice@alicemail.com","1234567890"));

        long userid = mDBHlpr.checkUserGettingId("Fred@fredmail.com","password");
        if (userid > 0) {
            mCurrentUser = mDBHlpr.getUserByID(userid); //<<<<<<<<<<< Get all the user info
            logUserInfo(mCurrentUser);

            //Update the user
            mCurrentUser.setName("Bert");
            mCurrentUser.setEmail("Bert@Bertmail.com");
            mCurrentUser.setPassword("anewpassword");
            mDBHlpr.updateUser(mCurrentUser);
            mCurrentUser = mDBHlpr.getUserByID(mCurrentUser.getId());
            logUserInfo(mCurrentUser);

            // Update the user again
            mCurrentUser.setName("Mary");
            mCurrentUser.setEmail("Mary@marymail.com");
            mCurrentUser.setPassword("mypasswordismary");
            mDBHlpr.updateUserByID(mCurrentUser,userid);
            // get by email
            mCurrentUser = mDBHlpr.getUserByEmail("Mary@marymail.com");
            logUserInfo(mCurrentUser);
            // get by Name
            mCurrentUser = mDBHlpr.getUserByName("Mary");
            logUserInfo(mCurrentUser);

            // get 2nd user
            mCurrentUser = mDBHlpr.getUserByEmail("Alice@alicemail.com");
            logUserInfo(mCurrentUser);

            //Oooops
            mCurrentUser = mDBHlpr.getUserByEmail("nobody@nobody@email.com");
            logUserInfo(mCurrentUser);
         }
    }

    private void logUserInfo(User user) {
        Log.d("USERINFO",
                "User ID is " + String.valueOf(user.getId()) +
                        " UserName = " + user.getName() +
                        " UserEmail = " + user.getEmail() +
                        " UserPassword = " + user.getPassword());
    }
}

Result

The above when run for the first time (the code is designed as a one-off run) outputs :-

06-26 10:02:26.795 12054-12054/? D/USERINFO: User ID is 1 UserName = Fred UserEmail = Fred@fredmail.com UserPassword = password
06-26 10:02:26.801 12054-12054/? D/USERINFO: User ID is 1 UserName = Bert UserEmail = Bert@Bertmail.com UserPassword = anewpassword
06-26 10:02:26.807 12054-12054/? D/USERINFO: User ID is 1 UserName = Mary UserEmail = Mary@marymail.com UserPassword = mypasswordismary
06-26 10:02:26.808 12054-12054/? D/USERINFO: User ID is 1 UserName = Mary UserEmail = Mary@marymail.com UserPassword = mypasswordismary
06-26 10:02:26.811 12054-12054/? D/USERINFO: User ID is 2 UserName = Alice UserEmail = Alice@alicemail.com UserPassword = 1234567890
06-26 10:02:26.812 12054-12054/? D/USERINFO: User ID is 0 UserName = null UserEmail = null UserPassword = null

---

Related Topics