SQLite相关的用户信息检索

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

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);
if(cursor != null && cursor.moveToFirst()&& cursor.getcount() > 0) {
return true;
} else {
return false;
}
}


可以使用的多种方法之一是使User对象包含包含保存数据的变量。与进程的这种结合，而不是返回布尔值，将返回User或null。

也许可以根据您的代码来考虑这个演示应用程序，为简便起见，它在数据库帮助器中包括一个User类。

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,cv);
}
public User checkUser(String name,String password) {
User rv = null;
Cursor csr = db.query(
TABLE_USERS,KEY_USERNAME + "=? AND " + KEY_PASSWORD + "=?",new String[]{name,password},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);
}
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对象将为null。
  
  
  
  • 完成游标后，您应该保证关闭它。
  
  
  • 如果游标由SQliteDatabase返回，则检查游标是否为null，因为该方法返回时将始终返回有效的游标。
  
  
  • 逐个检查moveTofirst，如果计数为0，则浪费游标的计数，moveToFirst将返回false，仅需要执行一项检查。
  
  
  
  

上面的内容在以下活动中使用，它反映了您现有的代码和想要实现的目标。

最初启动时，应用程序会添加一个用户（用于演示），其名称为 Admin ，其密码为 adminpassword 。用户界面包括一个显示未登录！!!! （显示顶部）的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();
}
}
}

结果

初始运行

• 如果任一编辑文本中没有出现任何疑问，并且单击了登录按钮，则会显示 *字段不能为空消息。


• 如果输入无效的用户名和密码，并单击了登录按钮，则会显示 用户名或密码错误 消息。
  


• 输入有效的用户名和密码，然后：-
  
  
  
  • 顶部的文本更改为 成功登录为???? !!! （其中????是用户名）。
  
  
  • 用户名和密码的编辑文本被删除。
  
  
  • 登录按钮根据以下内容更改为执行某些操作：-