how to retrieve data from sqlite

Question

i want to load the data from when the user created the account, im not sure of how to do it

in the class where i want to load the info from the account, the plan is to show and maybe also update any of the info given by the user

can someone lend me a hand?

databasehelper

package com.example.tulio.appkolev.sql;


public class DatabaseHelper  extends SQLiteOpenHelper{

    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_NAME = "UserManager.db";

    private static final String TABLE_USER = "user";

    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_USER_NAME = "user_name";
    private static final String COLUMN_USER_EMAIL = "user_email";
    private static final String COLUMN_USER_PASSWORD = "user_password";

    private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "("
            + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_USER_NAME + " TEXT,"
            + COLUMN_USER_EMAIL + " TEXT," + COLUMN_USER_PASSWORD + " TEXT" + ")";

    private String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER;

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

    @Override
    public void onCreate(SQLiteDatabase db){
        db.execSQL(CREATE_USER_TABLE);
    }

    @Override
    public  void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
        db.execSQL(DROP_USER_TABLE);
        onCreate(db);
    }

    public void addUser(User user){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_NAME, user.getName());
        values.put(COLUMN_USER_EMAIL, user.getEmail());
        values.put(COLUMN_USER_PASSWORD, user.getPassword());

        db.insert(TABLE_USER, null, values);
        db.close();
    }

    public boolean checkUser(String email){
        String[] columns = {
                COLUMN_USER_ID
        };
        SQLiteDatabase db = this.getWritableDatabase();
        String selection = COLUMN_USER_EMAIL + " = ?";
        String[] selectionArgs = { email };

        Cursor cursor = db.query(TABLE_USER,
                columns,
                selection,
                selectionArgs,
                null,
                null,
                null);
        int cursorCount = cursor.getCount();
        cursor.close();
        db.close();

        if (cursorCount > 0){
            return true;
        }
        return false;
    }

    public boolean checkUser(String email, String password){
        String[] columns = {
                COLUMN_USER_ID
        };
        SQLiteDatabase db = this.getWritableDatabase();
        String selection = COLUMN_USER_EMAIL + " = ?" + " AND " + COLUMN_USER_PASSWORD + " =?";
        String[] selectionArgs = { email, password };

        Cursor cursor = db.query(TABLE_USER,
                columns,
                selection,
                selectionArgs,
                null,
                null,
                null);
        int cursorCount = cursor.getCount();
        cursor.close();
        db.close();

        if (cursorCount > 0){
            return true;
        }
        return false;
    }
}

i appreciate the help, also i would be thankful if someone can maybe explain me how can i change the database from being local to being online

Answer 1

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