Database query is not working in SQLite room

Question

I am new to android. I am trying to learn Android and faced a problem. While working with SQLite, my insert operation is being executed. My loadAll User is also being executed. But while using parameters for retrieve user, the operation is not being executed. I am sharing the codes in the following.Thanks in advance for helping me out.

AppHome.java

package com.impresscapital.iclapp;

import android.content.Intent;
import android.graphics.Color;
import android.graphics.drawable.ColorDrawable;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;

import com.impresscapital.iclapp.dbobj.User;
import com.impresscapital.iclapp.util.ToastMessage;
import com.impresscapital.iclapp.util.UserSigninAuthentication;

public class AppHome extends AppCompatActivity {

    private TextView mUsernameTextView;
    private TextView mPasswordTextView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_app_home);

        initiateUI();
    }

    private void initiateUI(){
        mUsernameTextView=findViewById(R.id.editTextUsername);
        mPasswordTextView=findViewById(R.id.editTextPassword);
    }

    public void signUpPageLoader(View view){
        Intent registrationPageIntent=new Intent(AppHome.this,RegistrationPage.class);
        startActivity(registrationPageIntent);

    }

    @Override
    protected void onResume() {
        super.onResume();
    }

    public void signInUser(View view){
        try {
            String username=mUsernameTextView.getText().toString();
            String password=mPasswordTextView.getText().toString();
            UserSigninAuthentication userSigninAuthentication=new UserSigninAuthentication();
            if (userSigninAuthentication.authenticate(username, password, getApplicationContext())) {
                Intent userHomePageIntent = new Intent(AppHome.this, UserHome.class);
                startActivity(userHomePageIntent);
            } else {
                mPasswordTextView.setBackground(new ColorDrawable(Color.RED));
                mUsernameTextView.setBackground(new ColorDrawable(Color.RED));
                ToastMessage.toastMessage(this, "Username or password do not match");
                onResume();
            }
        }catch (NullPointerException e){
            mPasswordTextView.setBackground(new ColorDrawable(Color.RED));
            mUsernameTextView.setBackground(new ColorDrawable(Color.RED));
            ToastMessage.toastMessage(this, "Username or password do not match");

        }
    }
}

UserSigninAuthentication.java

package com.impresscapital.iclapp.util;

import android.content.Context;
import android.util.Log;

import com.impresscapital.iclapp.database.AppDatabase;
import com.impresscapital.iclapp.database.AppExecutors;
import com.impresscapital.iclapp.dbobj.User;

public class UserSigninAuthentication {

    private AppDatabase mDb;
    private Boolean isAuthenticate=false;

    public boolean authenticate(final String username, final String password,Context context){
        dataBaseOperation(username,password,context);
        return isAuthenticate;
    }

    public void dataBaseOperation(final String username, final String password, final Context context){
        AppExecutors.getsInstance().getDiskIO().execute(new Runnable() {
            @Override
            public void run() {
                try {
                    Log.d("DB","Inside db opt");
                    User user = mDb.userDao().loadUser(username, password);
                    if (user != null){
                        Log.d("DB","user found");
                        //ToastMessage.toastMessage(context,user.getUserID()+user.getEmail());
                        isAuthenticate = true;
                    }
                }catch (NullPointerException n){
                    isAuthenticate=false;
                }
            }
        });
    }
}

UserDao.java

package com.impresscapital.iclapp.dao;

import android.arch.persistence.room.Dao;
import android.arch.persistence.room.Delete;
import android.arch.persistence.room.Insert;
import android.arch.persistence.room.Query;
import android.arch.persistence.room.Update;

import com.impresscapital.iclapp.dbobj.User;

import java.util.List;

@Dao
public interface UserDao {


    @Query("Select * from user_table where username=:username and password=:password")
    User loadUser(String username,String password);

    @Query("Select * from user_table")
    List<User> retrieveAllUser();
    @Insert
    void registerUser(User user);

    @Update
    void updateUser(User user);

    @Delete
    void deleteUser(User user);
}

I have retrieved all the users in another class. The log code is

List<User> users=mDb.userDao().retrieveAllUser();
                for(User user1:users){
                    Log.d("Db working",user1.getUsername()+" "+user1.getPassword());
                }
                finish();

And the log message:

2018-10-29 13:11:47.210 11488-13813/com.impresscapital.iclapp D/Db working: y y
2018-10-29 13:11:47.210 11488-13813/com.impresscapital.iclapp D/Db working: g 1

The AppDatabase Class is uploaded for your convenience:

AppDatabase.claa

package com.impresscapital.iclapp.database;


import android.arch.persistence.room.Database;
import android.arch.persistence.room.Room;
import android.arch.persistence.room.RoomDatabase;
import android.arch.persistence.room.TypeConverter;
import android.arch.persistence.room.TypeConverters;
import android.content.Context;
import android.util.Log;

import com.impresscapital.iclapp.dao.UserDao;
import com.impresscapital.iclapp.dbobj.User;
import com.impresscapital.iclapp.util.DateConverter;

@Database(entities = {User.class},version = 1, exportSchema = false)
@TypeConverters({DateConverter.class})
public abstract class AppDatabase extends RoomDatabase {

    private static final String LOG_CAT=AppDatabase.class.getSimpleName();
    private static final Object LOCK=new Object();
    private static final String DATABASE_NAME="db_icl_app";
    private static AppDatabase sInstance;

    public static AppDatabase getInstance(Context context){
        if(sInstance==null){
            synchronized (LOCK){
                Log.d(LOG_CAT,"Creating new Database");
                sInstance=Room.databaseBuilder(context.getApplicationContext(),
                        AppDatabase.class,AppDatabase.DATABASE_NAME).build();
            }

        }
        Log.d(LOG_CAT,"Getting the database instance");
        return sInstance;
    }

    public abstract UserDao userDao();
}

UserSigninAuthentication.class

package com.impresscapital.iclapp.util;

import android.content.Context;
import android.util.Log;

import com.impresscapital.iclapp.database.AppDatabase;
import com.impresscapital.iclapp.database.AppExecutors;
import com.impresscapital.iclapp.dbobj.User;

import java.util.List;

public class UserSigninAuthentication {

    private AppDatabase mDb;
    private Boolean isAuthenticate=false;

    public boolean authenticate(final String username, final String password,Context context){
        dataBaseOperation(username.trim(),password.trim(),context);
        return isAuthenticate;
    }

    public void dataBaseOperation(final String username, final String password, final Context context){

        Log.d("Username","username: "+username);
        Log.d("Username","username length: "+username.length());
        Log.d("Password","password:"+password);
        Log.d("Password","password length:"+password.length());
        AppExecutors.getsInstance().getDiskIO().execute(new Runnable() {
            @Override
            public void run() {
                try {
                    List<User> users=mDb.userDao().retrieveAllUser();
                    for(User user1:users){
                        Log.d("Db working","Username:"+user1.getUsername()+" Password:"+user1.getPassword());
                    }
                    Log.d("DB","Inside db opt");
                    List<User> user1 = mDb.userDao().loadUser(username, password);
                    Log.d("Total Users after query","Tot"+ user1.size());
                    //ToastMessage.toastMessage(context,user.getUserID()+user.getEmail());
                    isAuthenticate = true;
                }catch (NullPointerException n){
                    isAuthenticate=false;
                }
                Log.d("isAuthenticate Variable", isAuthenticate.toString());
            }
        });
    }
}

Answer 1

try following query with your code.

SELECT * FROM user_table WHERE username like :username and password like :password

as mentioned in comments initializing mDb before querying db worked.

Answer 2

Please try with ' ' instead.

@Query("Select * from user_table where username='username' and password= 'password'")