Android Studio - Saving value from cursor into a variable

Boda
July 16, 2021
143 views
0 votes
2 Answers

I have an android studio app that uses sqlite to save user data such as username, password, etc. In the login page after the user enters his login credentials, the user clicks on a button that calls the following function from a DatabaseHelper java class to check if the info is correct:

public boolean checkLogin(String username, String password){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM user WHERE username=? AND password=? ",
                new String[] {username, password});
        if (cursor.getCount() > 0){
            return true;
        }
        else {
            return false;
        }
    }

I want to save the row ID that matches this user so I can use it in the future and I was thinking of saving the ID into a variable that I will then send to different activities using an intent. The issue is that I can’t figure out how to save the ID from the query.

Answers

Basically you just need the

cursor.getInt([column position])

cursor.getString([column position])

to retrieve the data from the columns in the database. I made an example I hope this helps you. I’m not very familiar with programming language some cases so I can’t argue more.

public class User {
        private int id;
        private String name;
        private String password;
        private boolean  isLogged = false;

        public User() {
        }

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

        public int getId() {
            return id;
        }

        public boolean isLogged() {
            return isLogged;
        }

        public void setLogged(boolean x) {
            this.isLogged = x;
        }
    }

Method to retrieve the Id requested you could just create a String or something..

    public User checkLogin(String username, String password) {

        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery("SELECT * FROM user WHERE username=? AND password=? ", new String[] {username, password});

        if (cursor.moveToFirst() || cursor.getCount() > 0) {

            User user = new User();
            user.setId(cursor.getInt(0));//if you created a primary key should be the first column
            user.setLogged(true);
            
            cursor.close();// * Closes the Cursor, releasing all of its resources and making it completely invalid.
            db.close(); // * Releases a reference to the object, closing the object if the last reference* was released.
            return user;
        } else {
            return null;
        }
    }

I’d suggest returning a int rather than boolean the long being the id or -1 if the user/password combination doesn’t exist. So :-

public int checkLogin(String username, String password){
    int rv = -1;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM user WHERE username=? AND password=? ",
            new String[] {username, password});
    if (cursor.moveToFirst()) {
        rv = cursor.getInt(cursor.getColumnIndex("id"));
    }
    cursor.close();
    return rv;
}

Instead of using something like :-

if (checkLogin("the_user","the_password")) {
    logged in code ....
} else {
    not logged in code ....
}

You could use something like :-

private int current_userid = -1; // probably declared as a class variable
....

if ((current_userid = db.checkLogin("the_user","the_password")) > 0 ) {
    logged in OK code ....
} else {
    not logged in code ....
}

I want to save the row ID that matches this user so I can use it in the future and I was thinking of saving the ID into a variable that I will then send to different activities using an intent.

Here’s an example that does that and sends the id to another Activity (NextActivity) and then returns (finishes) from that activity after writing the username and password to the log.

First the Database Helper DBHelper :-

class DBHelper extends SQLiteOpenHelper {

    SQLiteDatabase db;

    public DBHelper(@Nullable Context context) {
        super(context, "mydb", null, 1);
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS user (" +
                    "id INTEGER PRIMARY KEY, " +
                "username TEXT UNIQUE, " +
                "password TEXT " +
                ")");
        ContentValues cv = new ContentValues();
        cv.put("username","fred");
        cv.put("password","password_for_fred");
        db.insert("user",null,cv);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) { }

    public int checkLogin(String username, String password){
        int rv = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM user WHERE username=? AND password=? ",
                new String[] {username, password});
        if (cursor.moveToFirst()) {
            rv = cursor.getInt(cursor.getColumnIndex("id"));
        }
        cursor.close();
        return rv;
    }

    public Cursor getUserById(int userId) {
        return db.query("user",null,"id=?",new String[]{String.valueOf(userId)},null,null,null);
    }
}

Note that this uses a single class variable for the SQLiteDatabase, so only needs the 1 getWriteableDatabase. It also forces the database to open when constructing by including db = this.getWriteableDatabase(); in the constructor.
Note the added method getUserById(ing userId) method which returns a Cursor according to the userId.
Note that a demo user is added to the table when it is created.

MainActivity (a little overly complex as it demonstrates both a failed login attempt (1st) as well as a successful login attempt (as part of handling the failed attempt)) :-

public class MainActivity extends AppCompatActivity {

    public static final String INTENT_EXTRA_CURRENT_USERID = "current_userid";

    DBHelper db;
    private int current_userid = -1;
    private Intent intent;

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

        Log.d("LOGIN1","Attempting to Login"); // Will purposefully fail login
        if ((current_userid = db.checkLogin("the_user","the_password")) > 0 ) {
            Log.d("LOGIN2","Successfully Logged in to user with ID = " + String.valueOf(current_userid));
            gotoNextActivity();
        } else {
            Toast.makeText(this,"Invalid Login, please try again",Toast.LENGTH_SHORT).show();
            Log.d("LOGIN1","First attempt to login failed");

            // Make 2nd attempt (will work as username and password are correct)
            Log.d("LOGIN2","Attemtping to Login (2nd) ");
            if((current_userid = db.checkLogin("fred","password_for_fred")) > 0 ) {
                Log.d("LOGIN2","Successfully Logged in to user with ID = " + String.valueOf(current_userid));
                gotoNextActivity();
            }
        }

    }
    private void gotoNextActivity() {
        intent = new Intent(this,NextActivity.class);
        intent.putExtra(INTENT_EXTRA_CURRENT_USERID,current_userid);
        startActivity(intent);
    }
}

Finally NextActivity :-

public class NextActivity extends AppCompatActivity {

    private int current_userid;
    private String current_username, current_password;
    private DBHelper db;
    Cursor csr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_next);
        db = new DBHelper(this);
        current_userid = this.getIntent().getIntExtra(MainActivity.INTENT_EXTRA_CURRENT_USERID,-1);
        csr = db.getUserById(current_userid);
        if (csr.moveToFirst()) {
            current_username = csr.getString(csr.getColumnIndex("username"));
            current_password = csr.getString(csr.getColumnIndex("password"));
        }
        if (current_userid > 0) {
            Log.d("NEXTACTIVTY","Valid user ID - Username = " + current_username + " password is " + current_password);
        } else {
            Log.d("NEXTACTIVITY","No Valid userid?");
        }
        // Finish the Activity and hence return to MainActivity
        // Hence it is unlikely that the NextActivity will even be noticed.
        finish();
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (!csr.isClosed()) {
            csr.close();
            Log.d("NEXTACTIVITY","Closing Cursor in onDestroy method");
        }
    }
}

Result

When run the log includes :-

2021-07-17 12:19:37.201 D/LOGIN1: Attempting to Login
2021-07-17 12:19:37.211 D/LOGIN1: First attempt to login failed
2021-07-17 12:19:37.211 D/LOGIN2: Attemtping to Login (2nd) 
2021-07-17 12:19:37.212 D/LOGIN2: Successfully Logged in to user with ID = 1
2021-07-17 12:19:37.392 D/NEXTACTIVTY: Valid user ID - Username = fred password is password_for_fred
2021-07-17 12:19:37.745 D/NEXTACTIVITY: Closing Cursor in onDestroy method

Please signup or login to give your own answer.

Click here to cancel reply.

Android Studio – Saving value from cursor into a variable

Answers