How to check if a user already exists in SQLite database in Android?

Question

I am trying to create an app which does the basic signup and login functions based on an SQLite database.

How can I check if a user already exists? What I am trying here is, if we are trying to add duplicate user it is supposed to toast a message "USER ALREADY EXITS"

My code:

final SQLiteDatabase database = this.openOrCreateDatabase("Users", MODE_PRIVATE, null);
database.execSQL("CREATE TABLE IF NOT EXISTS users (uname VARCHAR UNIQUE, pword VARCHAR UNIQUE)");
//database.execSQL("CREATE UNIQUE INDEX idx_something ON users (uname, pword)");
signUp.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View view) {

        String username1 = userName.getText().toString();
        String  password1 = passWord.getText().toString();

        if (username1 != null && !username1.trim().isEmpty() && password1 != null && !password1.trim().isEmpty()) {
        try {
            database.execSQL("INSERT OR REPLACE INTO users(uname, pword) VALUES('"+username1+"', '"+password1+"')");
        } catch (Exception e) {
            e.printStackTrace();
        }

        Toast.makeText(getApplicationContext(), passWord.getText().toString(), Toast.LENGTH_LONG).show();
        } else {
            Toast.makeText(getApplicationContext(), "Please enter details", Toast.LENGTH_LONG).show();
        }
    }
});

logIn.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View view) {
        Intent intent = new Intent(MainActivity.this, secondscreen.class);
        try {
            Cursor c = database.rawQuery("SELECT * FROM users", null);
            int unameIndex = c.getColumnIndex("uname");
            int pwordIndex = c.getColumnIndex("pword");
            c.moveToFirst();
            while (c != null) {
                 {
                    String savedUname = c.getString(unameIndex);
                String savedPword = c.getString(pwordIndex);
                     Log.i("Table Content:",savedPword.toString()+savedUname.toString());

              if (savedUname.equals(userName.getText().toString())) {

                    if (savedPword.equals(passWord.getText().toString())) {

                        intent.setAction(Intent.ACTION_VIEW);
                        startActivity(intent);
                    } else {
                        Toast.makeText(getApplicationContext(), " Incorrect username or password ", Toast.LENGTH_LONG).show();
                    }
                } 
            }
                c.moveToNext();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }
});

Possible duplicate of [how to set a column as unique indexer on Sqlite](http://stackoverflow.com/questions/8544231/how-to-set-a-column-as-unique-indexer-on-sqlite) — Vishal Thakkar, Sep 03 '16 at 04:07
see this link http://stackoverflow.com/questions/2701877/sqlite-table-constraint-unique-on-multiple-columns — Vishal Thakkar, Sep 03 '16 at 04:08
thanks for your response @VishalThakkar but it is not working in my case, can you please have a look at my code — Jahnavi Nandamuri, Sep 03 '16 at 04:39
can you replace varchar to text .varchar is not type of sqlite see this https://www.sqlite.org/datatype3.html — Vishal Thakkar, Sep 03 '16 at 04:43
hey i have posted some code please try it and give me update after try it. — Vishal Thakkar, Sep 03 '16 at 07:17

score 4 · Accepted Answer · answered Sep 03 '16 at 07:15

you can do like this.

        logIn.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
    String username1 = userName.getText().toString().trim();
            Intent intent = new Intent(MainActivity.this, secondscreen.class);
            try {
                Cursor c = database.rawQuery("SELECT * FROM users where uname= "+username1, null);
    if(c.getCount()>0)
    {
    Toast.makeText(getApplicationContext(), "USER ALREADY EXITS", Toast.LENGTH_LONG).show();
    }
}catch(Exception e)
{
   e.printStackTrace();
}
}
});

RamithDR · Answer 2 · 2016-09-03T06:38:54.907

Add an auto-increment ID to your table definition, like :

CREATE TABLE IF NOT EXISTS users (id INTEGER primary key AUTOINCREMENT, uname VARCHAR UNIQUE, pword VARCHAR)

The id field will increment it self automatically by 1 whenever a new record is being inserted to the table, and making the ID field a primary key ensure each row of data in your table will be unique.

Remember to make only unameas UNIQUE which would ensure all registered users will have unique usernames.

I would like to point out without retrieving all the users when checking a login like

SELECT * FROM users

Retrieve a user as below :

SELECT * FROM users WHERE uname = "+uname_login_edittext+"";

Get the username text from the username field from your login form.

Since all the usernames in the your table are unique it will only retrieve 1 record or nothing, you can use that to check if there's a registered user or not and proceed with the login.

EDIT :

Try the login code below, make the savedUname and savedPword Strings global variables like I've done.

String inputUname = userName.getText().toString(); //get username from login form
String savedUname = null;
String savedPword = null;

    Cursor c = database.rawQuery("SELECT * FROM users WHERE uname = "+inputUname+"", null);
                int unameIndex = c.getColumnIndex("uname");
                int pwordIndex = c.getColumnIndex("pword");
                c.moveToFirst();
                if(c != null) {

                    savedUname = c.getString(unameIndex);
                    savedPword = c.getString(pwordIndex);


                {

                  if (savedUname.equals(userName.getText().toString())) {

                        if (savedPword.equals(passWord.getText().toString())) {

                            intent.setAction(Intent.ACTION_VIEW);
                            startActivity(intent);

                        } else {
                            Toast.makeText(getApplicationContext(), " Incorrect username or password ", Toast.LENGTH_LONG).show();
                        }
                    }

Thanks for your response @RamithDR, but it is also not working, moreover I am getting this error in log: '(table users has no column named id (code 1): , while compiling: INSERT INTO users(id, uname, pword) VALUES('hhhh', '1234'))' — Jahnavi Nandamuri, Sep 03 '16 at 05:15
@JahnaviJaanu it's because you're using the old database, uninstall the application from testing device and test again. — RamithDR, Sep 03 '16 at 05:37
Thank you @RamithDR its working after device restart. but SELECT * users WHERE is not working, can write it clearly and how to implement that to check existing users — Jahnavi Nandamuri, Sep 03 '16 at 06:04

score 1 · Answer 3 · edited May 23 '17 at 11:51

1

Try this

database.execSQL("REPLACE INTO users(uname, pword) VALUES('"+username1+"', '"+password1+"')");

uncomment your index creation query , uninstall your app and try again

if this doesn't work mean then possibly the values must have some space or something

if this does not work then you can simply use nest queries like this

edited May 23 '17 at 11:51

Community

1
1

answered Sep 03 '16 at 05:18

Pavneet_Singh

36,884
5
53
68

Thank you @Pavneet – Jahnavi Nandamuri Sep 03 '16 at 06:13

How to check if a user already exists in SQLite database in Android?

3 Answers3