Home > database >  Data Registration to database and validation from database Sqlite won't work
Data Registration to database and validation from database Sqlite won't work

Time：10-30

I've got this Sqlite database named DatabaseHelper and i need to register the data (username, pass, email, and phone) i need to check if username already exists when button register pushed and check user and pass data at login activity. Here is my code :

At first i build the the onCreate and insert function on the database with only user and password, and i can insert that 2 values to the database and can do the validation when press the regis and login button, but after i add 2 more values (email and phone number), when i press the register button nothing will happened, and the app showing no errors.

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "login.db";


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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE user(ID INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT, email TEXT, phone INTEGER)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS user");
    }

    public boolean Insert(String username, String password, String email, Integer phone){
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("username", username);
        contentValues.put("password", password);
        contentValues.put("email", email);
        contentValues.put("phone", phone);
        long result = sqLiteDatabase.insert("user", null, contentValues);
        if(result == -1){
            return false;
        }else{
            return true;
        }
    }
    // check if username already exists
    public Boolean CheckUsername(String username){
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM user WHERE username=?", new String[]{username});
        if(cursor.getCount() > 0){
            return false;
        }else{
            return true;
        }
    }
    // check user and pass matching at login activity
    public Boolean CheckLogin(String username, String password){
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM user WHERE username=? AND password=?", new String[]{username, password});
        if(cursor.getCount() > 0){
            return true;
        }else{
            return false;
        }
    }
}

Register.java

register.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View view) {
            String User = user.getText().toString().trim();
            String Pass = pass.getText().toString().trim();
            String Email = email.getText().toString().trim();
            String Phone = phone.getText().toString().trim();
            Integer phoneNumber = Integer.parseInt(Phone);
            Boolean checkUsername = databaseHelper.CheckUsername(User);
            if(checkUsername){
                Boolean insert = databaseHelper.Insert(User, Pass, Email, phoneNumber);
                if(insert){
                    Intent registerIntent = new Intent(Register.this,MainActivity.class);
                    startActivity(registerIntent);
                }
            }else{
                Toast.makeText(getApplicationContext(), "Username already taken", Toast.LENGTH_SHORT).show();
            }
    }
});

Login.java

login.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View view) {
        String User = username.getText().toString();
        String Pass = password.getText().toString();
        Boolean checklogin = databaseHelper.CheckLogin(User, Pass);
        if(username.getText().toString().isEmpty()){
            Toast.makeText(getApplicationContext(), "Username must not be empty!", Toast.LENGTH_SHORT).show();
        }else if(password.getText().toString().isEmpty()) {
            Toast.makeText(getApplicationContext(), "Password must not be empty!", Toast.LENGTH_SHORT).show();
        }else if(checklogin){
            Intent homeintent = new Intent(getBaseContext(),Home.class);
            startActivity(homeintent);
        }else{
            Toast.makeText(getApplicationContext(), "Invalid username or password", Toast.LENGTH_SHORT).show();
        }

    }
});

CodePudding user response：

Potential Issue 1 (probable cause according to your symptoms)

Your checkUsername method is probably incorrect as it returns false if the username has been located, true if not. I suspect that you really want:-

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

However, you might want to consider using the more compact/consice :-

public boolean checkUserName(String userName) {
    return DatabaseUtils.longForQuery(this.getWritableDatabase(),"SELECT count(*) FROM user WHERE username=?", new String[]{userName}) >= 1;
}

Potential Issue 2 (may well cause future issues)

when i press the register button nothing will happened, and the app showing no errors.

You don't say what doesn't happen i.e. what you expect to happen.

A lot could depend upon the code that you haven't shown and you may encounters issues due to using startActivity to apparently return to the invoking/parent activity (without the complete code this may or may not be the case).

Using 'startActivity' will not return to the actual activity, instead it will end (destroy) the invoking activity and start a brand new one.

Instead, you should return to the invoking/parent activity by finishing the child activity using finish().

**Potential Issue 3 (may well result in issues) The phone number column is indicated as being a java integer. This would be insufficient to hold the full range of 10 figure phone numbers.

That is the highest value a Java int can be is 2147483647.

Potential Issue 4 (unlikely, according to your sysmptoms, to be an issue)

At first i build the the onCreate and insert function on the database with only user and password, and i can insert that 2 values to the database and can do the validation when press the regis and login button, but after i add 2 more values (email and phone number).

This could also be an issue, if you used the App, then altered the onCreate code to add the new columns and just reran the App. The onCreate method is only run once for the lifetime of the database, which unless you specifically delete the database or uninstall the App would then result in the new columns not existing. However, you would encounter column not found errors which would contradict you saying that there are no errors.

Demo

Perhaps consider the following that was used to debug Issue 1.

The used a modified DatabaseHelper with the following modifications:-

// check if username already exists
public Boolean CheckUsernameOriginal(String username){
    SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
    Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM user WHERE username=?", new String[]{username});
    if(cursor.getCount() > 0){
        return false;
    }else{
        return true;
    }
}
// check if username already exists
public Boolean CheckUsername(String username){
    SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
    Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM user WHERE username=?", new String[]{username});
    if(cursor.getCount() > 0){
        //return false;
        return true; /*<<<<<<<<<< CHANGED from previous commented out line */
    }else{
        //return true;
        return false; /*<<<<<<<<<< CHANGED from previous commented out line */
    }
}

public boolean checkUserName(String userName) {
    return DatabaseUtils.longForQuery(this.getWritableDatabase(),"SELECT count(*) FROM user WHERE username=?", new String[]{userName}) >= 1;
}

i.e.:-

  • the original CheckUsername method renamed to CheckUsernameOriginal
  • a new CheckUserName method changed accordingly to return true if the username was found.
  • a new checkUserName method that uses the suggested more compact/concise code and the longForQuery method.

The DBHelper was then used in MainActivity to check the code and additionally the resultant data in the database using :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper dbHelper;
    //boolean logged_in = false;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper = new DatabaseHelper(this);
        testUser("User001","password001", "[email protected]",1234567890);
        //testUser("User002","password002", "[email protected]",9999999999); /* WARNING 9999999999 too big for int */
        testUser("User002","password002", "[email protected]",1333333333);

        DatabaseUtils.dumpCursor(
                dbHelper.getWritableDatabase().query("user",null,null,null,null,null,null)
        );

    }

    void testUser(String testUserName, String testUserPassword, String testUserEmail, int testUserPhone) {
        Log.d("DBTEST001","User "   testUserName   " login = "   dbHelper.CheckLogin(testUserName,testUserPassword));
        Log.d("DBTEST002A","User"   testUserName   " checkuser = "   dbHelper.CheckUsername(testUserName));
        Log.d("DBTEST002B","User"   testUserName   " checkuser = "   dbHelper.checkUserName(testUserName));
        Log.d("DBTEST002C","User"   testUserName   " checkuser = "   dbHelper.CheckUsernameOriginal(testUserName));
        Log.d("DBTEST003","User "   testUserName   " added = "   dbHelper.Insert(testUserName,testUserPassword,testUserEmail,testUserPhone));
        Log.d("DBTEST004","User "   testUserName   " login = "   dbHelper.CheckLogin(testUserName,testUserPassword));
        Log.d("DBTEST005A","User"   testUserName   " checkuser = "   dbHelper.CheckUsername(testUserName));
        Log.d("DBTEST005B","User"   testUserName   " checkuser = "   dbHelper.checkUserName(testUserName));
        Log.d("DBTEST005C","User"   testUserName   " checkuser = "   dbHelper.CheckUsernameOriginal(testUserName));
        Log.d("DBTEST006","User "   testUserName   " added = "   dbHelper.Insert(testUserName,testUserPassword,testUserEmail,testUserPhone));
    }
}

The result written to the log:-

2022-10-30 11:19:01.465 D/DBTEST001: User User001 login = false
2022-10-30 11:19:01.466 D/DBTEST002A: UserUser001 checkuser = false
2022-10-30 11:19:01.466 D/DBTEST002B: UserUser001 checkuser = false
2022-10-30 11:19:01.467 D/DBTEST002C: UserUser001 checkuser = true
2022-10-30 11:19:01.477 D/DBTEST003: User User001 added = true
2022-10-30 11:19:01.478 D/DBTEST004: User User001 login = true
2022-10-30 11:19:01.479 D/DBTEST005A: UserUser001 checkuser = true
2022-10-30 11:19:01.479 D/DBTEST005B: UserUser001 checkuser = true
2022-10-30 11:19:01.480 D/DBTEST005C: UserUser001 checkuser = false
2022-10-30 11:19:01.488 D/DBTEST006: User User001 added = true


2022-10-30 11:19:01.489 D/DBTEST001: User User002 login = false
2022-10-30 11:19:01.490 D/DBTEST002A: UserUser002 checkuser = false
2022-10-30 11:19:01.490 D/DBTEST002B: UserUser002 checkuser = false
2022-10-30 11:19:01.491 D/DBTEST002C: UserUser002 checkuser = true
2022-10-30 11:19:01.500 D/DBTEST003: User User002 added = true
2022-10-30 11:19:01.500 D/DBTEST004: User User002 login = true
2022-10-30 11:19:01.501 D/DBTEST005A: UserUser002 checkuser = true
2022-10-30 11:19:01.537 D/DBTEST005B: UserUser002 checkuser = true
2022-10-30 11:19:01.537 D/DBTEST005C: UserUser002 checkuser = false
2022-10-30 11:19:01.555 D/DBTEST006: User User002 added = true


2022-10-30 11:19:01.555 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@cf2ef01
2022-10-30 11:19:01.556 I/System.out: 0 {
2022-10-30 11:19:01.556 I/System.out:    ID=1
2022-10-30 11:19:01.556 I/System.out:    username=User001
2022-10-30 11:19:01.556 I/System.out:    password=password001
2022-10-30 11:19:01.556 I/System.out:    [email protected]
2022-10-30 11:19:01.556 I/System.out:    phone=1234567890
2022-10-30 11:19:01.556 I/System.out: }
2022-10-30 11:19:01.557 I/System.out: 1 {
2022-10-30 11:19:01.557 I/System.out:    ID=2
2022-10-30 11:19:01.557 I/System.out:    username=User001
2022-10-30 11:19:01.557 I/System.out:    password=password001
2022-10-30 11:19:01.557 I/System.out:    [email protected]
2022-10-30 11:19:01.557 I/System.out:    phone=1234567890
2022-10-30 11:19:01.557 I/System.out: }
2022-10-30 11:19:01.557 I/System.out: 2 {
2022-10-30 11:19:01.557 I/System.out:    ID=3
2022-10-30 11:19:01.557 I/System.out:    username=User002
2022-10-30 11:19:01.557 I/System.out:    password=password002
2022-10-30 11:19:01.557 I/System.out:    [email protected]
2022-10-30 11:19:01.557 I/System.out:    phone=1333333333
2022-10-30 11:19:01.557 I/System.out: }
2022-10-30 11:19:01.558 I/System.out: 3 {
2022-10-30 11:19:01.558 I/System.out:    ID=4
2022-10-30 11:19:01.558 I/System.out:    username=User002
2022-10-30 11:19:01.558 I/System.out:    password=password002
2022-10-30 11:19:01.558 I/System.out:    [email protected]
2022-10-30 11:19:01.558 I/System.out:    phone=1333333333
2022-10-30 11:19:01.558 I/System.out: }
2022-10-30 11:19:01.558 I/System.out: <<<<<

As can be seen:-

  • the corrected CheckUsername method (A) returns the appropriate result (false if the user does not exist and true if the user does exist)
  • the alternative checkUserName method (B) returns the same as (A)
  • the original CheckUsername now CheckUsernameOriginal (C) returns the incorrect results .
  • the users have been added accordingly (note that no logic has been incorporated to stop the duplication of a username and hence the 4 rows)
    • if you want to restrict usernames, then rather than using logic you could make the username column UNIQUE.

Output if using UNIQUE constraint on username column :-

2022-10-30 11:46:23.992 D/DBTEST001: User User001 login = false
2022-10-30 11:46:23.993 D/DBTEST002A: UserUser001 checkuser = false
2022-10-30 11:46:23.994 D/DBTEST002B: UserUser001 checkuser = false
2022-10-30 11:46:23.995 D/DBTEST002C: UserUser001 checkuser = true
2022-10-30 11:46:24.006 D/DBTEST003: User User001 added = true
2022-10-30 11:46:24.006 D/DBTEST004: User User001 login = true
2022-10-30 11:46:24.006 D/DBTEST005A: UserUser001 checkuser = true
2022-10-30 11:46:24.007 D/DBTEST005B: UserUser001 checkuser = true
2022-10-30 11:46:24.007 D/DBTEST005C: UserUser001 checkuser = false
2022-10-30 11:46:24.011 E/SQLiteDatabase: Error inserting username=User001 [email protected] phone=1234567890 password=password001
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: user.username (code 2067 SQLITE_CONSTRAINT_UNIQUE)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:938)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1701)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1570)
        at a.a.so74247039javasqliteinsertnotshowing.DatabaseHelper.Insert(DatabaseHelper.java:36)
        at a.a.so74247039javasqliteinsertnotshowing.MainActivity.testUser(MainActivity.java:39)
        at a.a.so74247039javasqliteinsertnotshowing.MainActivity.onCreate(MainActivity.java:19)
        at android.app.Activity.performCreate(Activity.java:7994)
        at android.app.Activity.performCreate(Activity.java:7978)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1309)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3422)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3601)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2066)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:223)
        at android.app.ActivityThread.main(ActivityThread.java:7656)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)
2022-10-30 11:46:24.011 D/DBTEST006: User User001 added = false
2022-10-30 11:46:24.011 D/DBTEST001: User User002 login = false
2022-10-30 11:46:24.012 D/DBTEST002A: UserUser002 checkuser = false
2022-10-30 11:46:24.012 D/DBTEST002B: UserUser002 checkuser = false
2022-10-30 11:46:24.013 D/DBTEST002C: UserUser002 checkuser = true
2022-10-30 11:46:24.026 D/DBTEST003: User User002 added = true
2022-10-30 11:46:24.026 D/DBTEST004: User User002 login = true
2022-10-30 11:46:24.027 D/DBTEST005A: UserUser002 checkuser = true
2022-10-30 11:46:24.055 D/DBTEST005B: UserUser002 checkuser = true
2022-10-30 11:46:24.055 D/DBTEST005C: UserUser002 checkuser = false
2022-10-30 11:46:24.057 E/SQLiteDatabase: Error inserting username=User002 [email protected] phone=1333333333 password=password002
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: user.username (code 2067 SQLITE_CONSTRAINT_UNIQUE)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:938)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1701)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1570)
        at a.a.so74247039javasqliteinsertnotshowing.DatabaseHelper.Insert(DatabaseHelper.java:36)
        at a.a.so74247039javasqliteinsertnotshowing.MainActivity.testUser(MainActivity.java:39)
        at a.a.so74247039javasqliteinsertnotshowing.MainActivity.onCreate(MainActivity.java:21)
        at android.app.Activity.performCreate(Activity.java:7994)
        at android.app.Activity.performCreate(Activity.java:7978)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1309)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3422)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3601)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2066)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:223)
        at android.app.ActivityThread.main(ActivityThread.java:7656)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)
2022-10-30 11:46:24.057 D/DBTEST006: User User002 added = false
  • Note that the UNIQUE constraint conflict is trapped by the insert method but the trapped exception is still written to the log.
  • as can be seen the duplicated user names are not added and the DBTEST006 messages indicate that the row was not added.
  • the additional rows are now not present in the extract from the database.

Page link：https//www.codepudding.com/database/593221.html

Prev:How do I create an activity that will change the contents of the views after user clicks on a button
Next:Excel filter formula for multiple criteria and value in array
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding