java - SQLite相关用户信息检索

Question

我正在开发一个使用 SQLite 数据库的登录系统。我已经有一个注册表单，它可以很好地创建所有用户，而且我还创建了一种方法来检查输入到 EditText 中的用户名是否存在。但现在我的问题是你将如何从该用户那里检索所有相关信息，例如我还需要检查该特定用户的密码是否匹配，我还需要从用户那里检索一些考试成绩也在数据库中，然后在主页上显示它们。这是用于登录用户的主要 Activity 中的代码:

public void loginUser() {
        EditText usernameInput = findViewById(R.id.login_username);
        EditText passwordInput = findViewById(R.id.login_password);
        String loginUsername = usernameInput.getText().toString();
        String loginPassword = passwordInput.getText().toString();

        if(loginUsername.matches("") || loginPassword.matches("")) {
            Toast.makeText(this, "Fields cannot be empty", Toast.LENGTH_SHORT).show();
        } else {
            if (dbHelper.ifUserExists(loginUsername)) {
                //password verification and login
                //how to retrieve relevant users password and compare to password input?
            } else {
                Toast.makeText(this, "Incorrect username or password", Toast.LENGTH_SHORT).show();
            }
        }
    }

这是检查用户是否存在的 databaseHelper 方法:

public boolean ifUserExists(String user) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_USERS,
                new String[]{KEY_ID, KEY_USERNAME, KEY_PASSWORD, KEY_SAT_READING, KEY_SAT_MATH, KEY_ACT_READING, KEY_ACT_MATH},
                KEY_USERNAME + "=?",
                new String[]{user},
                null, null, null);
        if(cursor != null && cursor.moveToFirst()&& cursor.getCount() > 0) {
            return true;
        } else {
            return false;
        }
    }

Answer 1

处理此问题的方法之一是拥有一个包含保存数据的变量的用户对象。这种与过程的结合而不是返回 boolean 值返回用户或 null。

也许考虑这个基于您的代码的演示应用程序，为简洁起见，它在数据库助手中包含一个用户类。

DatabaseHelper.java :-

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;
    public static final String TABLE_USERS = "users";
    public static final String KEY_ID = BaseColumns._ID;
    public static final String KEY_USERNAME = "username";
    public static final String KEY_PASSWORD = "password";
    public static final String KEY_SAT_READING = "reading";
    public static final String KEY_SAT_MATH = "satmath";
    public static final String KEY_ACT_READING = "actreading";
    public static final String KEY_ACT_MATH = "actmath";

    SQLiteDatabase db;

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String users_crt_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_USERS +
                "(" +
                KEY_ID + " INTEGER PRIMARY KEY," +
                KEY_USERNAME + " TEXT UNIQUE," +
                KEY_PASSWORD + " TEXT," +
                KEY_SAT_READING + " TEXT," +
                KEY_SAT_MATH + " TEXT," +
                KEY_ACT_READING + " TEXT," +
                KEY_ACT_MATH + " TEXT" +
                ")";
        db.execSQL(users_crt_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertNewUser(String name, String password) {
        ContentValues cv = new ContentValues();
        cv.put(KEY_USERNAME,name);
        cv.put(KEY_PASSWORD,password);
        return db.insert(TABLE_USERS,null,cv);
    }

    public User checkUser(String name, String password) {
        User rv = null;
        Cursor csr = db.query(
                TABLE_USERS,
                null,
                KEY_USERNAME + "=? AND " + KEY_PASSWORD + "=?",
                new String[]{name,password},
                null,null,null
        );
        if (csr.moveToFirst()) {
            rv = new User();
            rv.setUserId(csr.getLong(csr.getColumnIndex(KEY_ID)));
            rv.setUserName(name);
            rv.setUserPassword("");
            rv.setUserSatReading(csr.getString(csr.getColumnIndex(KEY_SAT_READING)));
            rv.setUserSatMath(csr.getString(csr.getColumnIndex(KEY_SAT_MATH)));
            rv.setUserActReading(csr.getString(csr.getColumnIndex(KEY_ACT_READING)));
            rv.setUserActMath(csr.getString(csr.getColumnIndex(KEY_ACT_MATH)));
        }
        csr.close();
        return rv;
    }

    public class User {
        long userId;
        String userName;
        String userPassword;
        String userSatReading;
        String userSatMath;
        String userActReading;
        String userActMath;

        public User() {
        }

        public User(String name, String password) {
            this(-1L, name, password, null, null, null, null);
        }

        public User(long id, String name, String password, String satReading, String satMath, String actReading, String actmath) {
            this.userId = id;
            this.userName = name;
            this.userPassword = password;
            this.userSatReading = satReading;
            this.userSatMath = satMath;
            this.userActReading = actReading;
            this.userActMath = actmath;
        }

        public long getUserId() {
            return userId;
        }

        public void setUserId(long userId) {
            this.userId = userId;
        }

        public String getUserName() {
            return userName;
        }

        public void setUserName(String userName) {
            this.userName = userName;
        }

        public String getUserPassword() {
            return userPassword;
        }

        public void setUserPassword(String userPassword) {
            this.userPassword = userPassword;
        }

        public String getUserSatReading() {
            return userSatReading;
        }

        public void setUserSatReading(String userSatReading) {
            this.userSatReading = userSatReading;
        }

        public String getUserSatMath() {
            return userSatMath;
        }

        public void setUserSatMath(String userSatMath) {
            this.userSatMath = userSatMath;
        }

        public String getUserActReading() {
            return userActReading;
        }

        public void setUserActReading(String userActReading) {
            this.userActReading = userActReading;
        }

        public String getUserActMath() {
            return userActMath;
        }

        public void setUserActMath(String userActMath) {
            this.userActMath = userActMath;
        }
    }
}

• 请注意，checkUser 方法等同于您的 idUserExists 方法，但如果未找到用户，则返回的 User 对象将为空。
  • 您应该在使用完 Cursor 后随时关闭它。
  • 检查一个游标是否为 null，如果它是由 SQliteDatabase 方法返回的，则它是无用的，因为如果该方法返回，将始终返回一个有效的 Cursor。
  • Lilewise 检查 moveTofirst 和游标的计数是一种浪费，如果计数为 0，moveToFirst 将返回 false，只需要进行其中一项检查。

以上内容用于以下内容的 Activity 中，它反射(reflect)了您现有的代码和您想要实现的目标。

应用程序最初启动时会添加一个名为Admin 且密码为adminpassword 的用户(用于演示)。 UI 由一个显示 未登录!!!! 的 TextView (显示顶部)、两个编辑文本(根据您的代码)和一个用于登录的按钮组成。

MainActivity.java

public class MainActivity extends AppCompatActivity {

    DatabaseHelper dbHelper;
    DatabaseHelper.User current_user = null;
    EditText loginUsername, loginPassword;
    TextView status;
    Button login;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        status = this.findViewById(R.id.status);
        loginUsername = this.findViewById(R.id.login_username);
        loginPassword = this.findViewById(R.id.login_password);
        login = this.findViewById(R.id.login);
        login.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                loginUser();
            }
        });

        dbHelper = new DatabaseHelper(this);
        dbHelper.insertNewUser("Admin","adminpassword");

    }

    private void loginUser() {
        if (
                loginUsername.getText().toString() == null
                        || loginUsername.getText().toString().length() < 1
                        ||loginPassword.getText().toString() == null
                        || loginPassword.getText().toString().length() < 1) {
            Toast.makeText(this, "Fields cannot be empty", Toast.LENGTH_SHORT).show();
            return;
        }
        if ((current_user = dbHelper.checkUser(loginUsername.getText().toString(),loginPassword.getText().toString())) != null) {
            loginUsername.setVisibility(View.GONE);
            loginPassword.setVisibility(View.GONE);
            login.setText("DO SOMETHNG");
            login.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    Toast.makeText(v.getContext(),"Hello " +
                            current_user.getUserName() +
                            " you are logged in so you can do something!",Toast.LENGTH_SHORT).show();
                }
            });
            status.setText("Successfully Logged-In as " + current_user.getUserName());
        } else {
            Toast.makeText(this, "Incorrect username or password", Toast.LENGTH_SHORT).show();
        }
    }
}

结果

初始运行

• 如果在任一编辑文本中均未输入任何内容并且点击了登录按钮，则*Fields cannot be Empty消息被烘烤。
• 如果输入了无效的用户名和密码组合并点击了登录按钮，则会显示用户名或密码不正确消息。
• 然后输入有效的用户名和密码:-
  • topd 的文本更改为Successfully login in as ????!! (其中 ???? 是用户名).
  • 删除用户名和密码的编辑文本。
  • Login 按钮变为DO SOMETHING as per :-