java - 如何让数据库停止覆盖以前的记录？
问题描述
所以在图片中，每次用户完成测验时，整个分数列都会被覆盖。我不太确定我应该使用哪种方法来单独保存分数。我也尝试更新而不是插入，但没有运气。我正在使用共享首选项来保存最高分并继续显示。但我也想将其实现到数据库中。
MyDataBaseHelper.java
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Debug;
import android.util.Log;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
// class to provide operations with database
public class MyDataBaseHelper extends SQLiteOpenHelper {
// Database name
public static String DATABASE_QUESTION = "QUIZ.db";
// Current version of database
private static final int DATABASE_VERSION = 1;
// Database table name
private static final String TABLE_QUESTION = "QuestionBank";
private static final String TABLE_USER = "Users";
// All fields used in question table
private static final String KEY_ID_QUESTION = "id";
private static final String QUESTION = "question";
private static final String CHOICE1 = "choice1";
private static final String CHOICE2 = "choice2";
private static final String CHOICE3 = "choice3";
private static final String CHOICE4 = "choice4";
private static final String ANSWER = "answer";
// All fields used in user table
private static final String KEY_ID_USERS = "id";
private static final String USER_NAME = "name";
private static final String USER_SCORE = "score";
// Question Table Create Query in this string
private static final String CREATE_TABLE_QUESTION = "CREATE TABLE "
+ TABLE_QUESTION + "(" + KEY_ID_QUESTION
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + QUESTION + " TEXT,"
+ CHOICE1 + " TEXT, " + CHOICE2 + " TEXT, " + CHOICE3 + " TEXT, "
+ CHOICE4 + " TEXT, " + ANSWER + " TEXT);";
// User Table Query
private static final String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + "(" + KEY_ID_USERS
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + USER_NAME + " TEXT, " + USER_SCORE + " INTEGER);";
public MyDataBaseHelper(Context context) {
super(context, DATABASE_QUESTION, null, DATABASE_VERSION);
}
/**
* This method is called by system if the database is accessed but not yet
* created.
*/
@Override
public void onCreate(SQLiteDatabase db) {
Log.d("DBHelper", "Created db");
Log.d("DBHelper", CREATE_TABLE_QUESTION);
db.execSQL(CREATE_TABLE_QUESTION); // create question table
db.execSQL(CREATE_TABLE_USER);
}
/**
* This method is called when any modifications in database are done like
* version is updated or database schema is changed
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldvers, int newvers) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUESTION);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
onCreate(db);
}
/**
* This method is used to add question detail in question Table
*/
public long addInitialQuestion (Question question) {
SQLiteDatabase db = this.getWritableDatabase();
// Creating content values
ContentValues values = new ContentValues();
values.put(QUESTION, question.getQuestion());
values.put(CHOICE1, question.getChoice(0));
values.put(CHOICE2, question.getChoice(1));
values.put(CHOICE3, question.getChoice(2));
values.put(CHOICE4, question.getChoice(3));
values.put(ANSWER, question.getAnswer());
// insert row in question table
long insert = db.insert(TABLE_QUESTION, null, values);
return insert;
}
/**
* To extract data from database and save it Arraylist of data type
* Question
*/
public List<Question> getAllQuestionsList() {
List<Question> questionArrayList = new ArrayList<>();
String selectQuery = "SELECT * FROM " + TABLE_QUESTION;
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all records and adding to the list
if (c.moveToFirst()) {
do {
Question question = new Question();
String questText= c.getString(c.getColumnIndex(QUESTION));
question.setQuestion(questText);
String choice1Text= c.getString(c.getColumnIndex(CHOICE1));
question.setChoice(0,choice1Text);
String choice2Text= c.getString(c.getColumnIndex(CHOICE2));
question.setChoice(1,choice2Text);
String choice3Text= c.getString(c.getColumnIndex(CHOICE3));
question.setChoice(2,choice3Text);
String choice4Text= c.getString(c.getColumnIndex(CHOICE4));
question.setChoice(3,choice4Text);
String answerText= c.getString(c.getColumnIndex(ANSWER));
question.setAnswer(answerText);
// adding to Questions list
questionArrayList.add(question);
} while (c.moveToNext());
Collections.shuffle(questionArrayList);
}
return questionArrayList;
}
public List<User> getAllUser(){
String sortOrder = "SELECT * FROM " + TABLE_USER + " ORDER BY " + USER_SCORE + " DESC";
List<User> userList = new ArrayList<User>();
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(sortOrder, null);
if(c.moveToFirst()){
User user = new User();
}
return userList;
}
public void addUser(User user) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(USER_NAME, user.getName());
// Inserting Row
db.insert(TABLE_USER, USER_NAME, values);
db.close();
}
public void updateUser(User user) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(USER_NAME, user.getName());
// updating row
db.update(TABLE_USER, values, USER_NAME + " = ?",
new String[]{String.valueOf(user.getId())});
db.close();
}
public void deleteUser(User user) {
SQLiteDatabase db = this.getWritableDatabase();
// delete user record by id
db.delete(TABLE_USER, USER_NAME + " = ?",
new String[]{String.valueOf(user.getId())});
db.delete(TABLE_USER, USER_SCORE + " = ?",
new String[]{String.valueOf(user.getId())});
db.close();
}
public boolean hasUser(String user){
String searchQuery = "SELECT * FROM " + TABLE_USER + " WHERE " + USER_NAME + " = '" + user + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(searchQuery, null);
c.moveToFirst();
if (c.getCount() > 0) {
c.close();
db.close();
return true;
} else {
c.close();
db.close();
return false;
}
}
public void addScore(int point){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(USER_SCORE, point);
// Inserting Row
db.update(TABLE_USER, values,null , null);
db.close();
}
}
测验活动.java
import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;
import java.util.List;
public class QuizActivity extends AppCompatActivity {
private QuestionBank mQuestionLibrary = new QuestionBank();
private TextView mScoreView; // view for current total score
private TextView mQuestionView; //current question to answer
private Button mButtonChoice1; // multiple choice 1 for mQuestionView
private Button mButtonChoice2; // multiple choice 2 for mQuestionView
private Button mButtonChoice3; // multiple choice 3 for mQuestionView
private Button mButtonChoice4; // multiple choice 4 for mQuestionView
private String mAnswer; // correct answer for question in mQuestionView
private int mScore = 0; // current total score
private int mQuestionNumber = 0; // current question number
private String username = "";
MyDataBaseHelper myDataBaseHelper;
private static final String KEY_QUESTION_NUMBER = "questionNumber";
private static final String KEY_SCORE = "currentScore";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_quiz);
// setup screen for the first question with four alternative to answer
mScoreView = (TextView)findViewById(R.id.score);
mQuestionView = (TextView)findViewById(R.id.question);
mButtonChoice1 = (Button)findViewById(R.id.choice1);
mButtonChoice2 = (Button)findViewById(R.id.choice2);
mButtonChoice3 = (Button)findViewById(R.id.choice3);
mButtonChoice4 = (Button)findViewById(R.id.choice4);
Intent intent = getIntent();
username = intent.getStringExtra("username");
myDataBaseHelper = new MyDataBaseHelper(this);
mQuestionLibrary.initQuestions(getApplicationContext());
if(savedInstanceState != null){
mScore = savedInstanceState.getInt(KEY_SCORE,0);
mQuestionNumber = savedInstanceState.getInt(KEY_QUESTION_NUMBER, 0) - 1;
}
updateQuestion();
// show current total score for the user
updateScore(mScore);
}
private void updateQuestion(){
// check if we are not outside array bounds for questions
if(mQuestionNumber<mQuestionLibrary.getLength() ){
// set the text for new question,
// and new 4 alternative to answer on four buttons
mQuestionView.setText(mQuestionLibrary.getQuestion(mQuestionNumber));
mButtonChoice1.setText(mQuestionLibrary.getChoice(mQuestionNumber, 1));
mButtonChoice2.setText(mQuestionLibrary.getChoice(mQuestionNumber, 2));
mButtonChoice3.setText(mQuestionLibrary.getChoice(mQuestionNumber, 3));
mButtonChoice4.setText(mQuestionLibrary.getChoice(mQuestionNumber,4));
mAnswer = mQuestionLibrary.getCorrectAnswer(mQuestionNumber);
mQuestionNumber++;
}
else {
Toast.makeText(QuizActivity.this, "It was the last question!", Toast.LENGTH_SHORT).show();
Intent intent = new Intent(QuizActivity.this, HighScoreActivity.class);
intent.putExtra("score", mScore); // pass the current score to the second screen
intent.putExtra("username", username);
myDataBaseHelper.addScore(mScore);
startActivity(intent);
}
}
// show current total score for the user
private void updateScore(int point) {
mScoreView.setText(""+mScore+"/"+mQuestionLibrary.getLength());
}
public void onClick(View view) {
//all logic for all answers buttons in one method
Button answer = (Button) view;
// if the answer is correct, increase the score
if (answer.getText().equals(mAnswer)){
mScore = mScore + 1;
Toast.makeText(QuizActivity.this, "Correct!", Toast.LENGTH_SHORT).show();
}else
Toast.makeText(QuizActivity.this, "Wrong!", Toast.LENGTH_SHORT).show();
// show current total score for the user
updateScore(mScore);
// once user answer the question, we move on to the next one, if any
updateQuestion();
}
@Override
protected void onResume() {
super.onResume();
/*
MyDataBaseHelper dataBaseHelper = new MyDataBaseHelper((this));
List<Question> questions = dataBaseHelper.getAllQuestionsList();
if(questions.isEmpty())
{
Log.d("QuizActivity", "List is empty");
} dataBaseHelper.addInitialQuestion(new Question("1. When did Google acquire Android ?",
new String[]{"2001", "2003", "2004", "2005"}, "2005"));
*/
}
@Override
protected void onSaveInstanceState(Bundle savedInstanceState) {
super.onSaveInstanceState(savedInstanceState);
savedInstanceState.putInt(KEY_SCORE, mScore);
savedInstanceState.putInt(KEY_QUESTION_NUMBER, mQuestionNumber);
}
@Override
protected void onRestoreInstanceState(Bundle savedInstanceState) {
super.onRestoreInstanceState(savedInstanceState);
mScore = savedInstanceState.getInt(KEY_SCORE,0);
mQuestionNumber = savedInstanceState.getInt(KEY_QUESTION_NUMBER, 0);
}
}
HighScoreActivity.java
import android.content.Intent;
import android.content.SharedPreferences;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
public class HighScoreActivity extends AppCompatActivity {
private String username;
private TextView txtScore;
private TextView txtHighScore;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_highest_score);
txtScore = (TextView) findViewById(R.id.textScore);
txtHighScore = (TextView) findViewById(R.id.textHighScore);
Intent intent = getIntent();
int score = intent.getIntExtra("score",0);
username = intent.getStringExtra("username");
txtScore.setText(username + "'s Score: " + score);
//
// SharedPreferences mypref = getPreferences(MODE_PRIVATE);
// int highscore = mypref.getInt("highscore", 0);
// if (highscore >= score){
// txtHighScore.setText("High Score: " + highscore);
// }
// else {
// txtHighScore.setText("New High Score: " + score);
// SharedPreferences.Editor editor = mypref.edit();
// editor.putInt("highscore", score);
// editor.commit();
// }
}
public void onClick(View view) {
Intent intent = new Intent(HighScoreActivity.this, QuizActivity.class);
intent.putExtra("username", username);
startActivity(intent);
}
public void logOutOnClick(View view) {
Intent intent = new Intent(HighScoreActivity.this, LoginActivity.class);
startActivity(intent);
finish();
}
}
解决方案
我相信以下内容符合您的需求。
更改后的签名，以便您可以传递要更新的用户的 ID、where 子句（不带 WHERE 关键字）和要在 where 子句中使用的参数。
也就是说，如果没有 where 子句，更新方法将更新所有行。（我相信是你的核心问题）
因此，您在数据库助手（SQLiteOpenHelper 的子类）中的代码可能是：-
public void addScore(String username, int point){ //<<<<<<<<<< Changed signature
SQLiteDatabase db = this.getWritableDatabase();
String whereclause = USER_NAME + "=?"; //<<<<<<<<<< Added
String[] whereargs = new String[]{username}; //<<<<<<<<<< Added
ContentValues values = new ContentValues();
values.put(USER_SCORE, point);
// Inserting Row
db.update(TABLE_USER, values, whereclause, whereargs);
db.close();
}
然后你需要调用 addScore 使用myDataBaseHelper.addScore(username,mScore);
- 请注意，这不会添加分数，而是将其更改为传递的值。
- 上面的代码是in-principal code，它没有经过测试，所以可能有一些错误。
更新分数
（即添加到现有分数（或如果提供负值则减去））
public void addScore(String username, int points_to_add) {
SQLiteDatabase db = this.getWritableDatabase();
String sql = "UPDATE " + TABLE_USER + " SET " + USER_SCORE + " = " + USER_SCORE + "+? WHERE " + USER_NAME + "=?";
String[] args = new String[]{String.valueOf(points_to_add), username};
db.execSQL(sql,args);
db.close();
}
- execSQL 已被使用，因为您无法传递项目名称（例如，通过内容值的列名称（插入方法的限制））。
例子
考虑到上面的addScore方法和定制的（为了便于测试）addUser方法（只需传递一个字符串作为用户名并将分数设置为 0）：-
mDBHlpr = new MyDataBaseHelper(this);
// Add some users (Score will be 0)
mDBHlpr.addUser("Fred");
mDBHlpr.addUser("Bert");
mDBHlpr.addUser("Mary");
mDBHlpr.addUser("Sue");
mDBHlpr.addScore("Fred",1200);
mDBHlpr.addScore("Sarah",50000); // non-existant user
mDBHlpr.addScore("Mary",6000);
mDBHlpr.addScore("Mary", -2000);
mDBHlpr.addScore("Sue", -1000);
mDBHlpr.addScore("Bert", -1700);
SQLiteDatabase db = mDBHlpr.getWritableDatabase();
Cursor csr = db.query("Users",null,null,null,null,null,null);
DatabaseUtils.dumpCursor(csr);
csr.close();
db.close();
那么期望是：-
- 弗雷德的分数是 1200
- 伯特的得分为 - 1700
- 玛丽的分数为 6000，然后减少 2000 到 4000
- Sue 的得分为 -1000
- 莎拉，因为用户不存在导致不采取任何行动或失败
dumpCursor方法将转储游标（所有用户行），结果是（如预期的那样）：-
I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534a4524
I/System.out: 0 {
I/System.out: id=1
I/System.out: name=Fred
I/System.out: score=1200
I/System.out: }
I/System.out: 1 {
I/System.out: id=2
I/System.out: name=Bert
I/System.out: score=-1700
I/System.out: }
I/System.out: 2 {
I/System.out: id=3
I/System.out: name=Mary
I/System.out: score=4000
I/System.out: }
I/System.out: 3 {
I/System.out: id=4
I/System.out: name=Sue
I/System.out: score=-1000
I/System.out: }
I/System.out: <<<<<
推荐阅读
- php - 如何在 laravel 5.8 中隐藏滚动条？
- windows - 向循环添加函数
- elasticsearch - 如何仅根据词频获取文档？
- android - Android Fragment：进入和返回的不同动画
- java - 如何修复错误：未找到 ID 为“com.google.gms:google-services:4.2.0”的插件
- r - 提取以特定预定义字符串正则表达式开头的行？
- python - 我不能在 WSL Ubuntu 上使用 pip
- c# - Mongo DB C# 驱动程序 - 如何定义没有属性的复合 ID？
- npm - npm 组存储库在 nexus 3 中的意外包解析行为
- django - DJANGO 如何进行可以在两个值之间进行过滤的 Q 查询？