In my android application (playing cards game), users obtain a specific score (a double value, percentage 'right' during the game) and a time value (duration of the game in seconds, stored in an integer).
I'm learning SQL(ite) and wanted to try and include a SQLite Database in my android app so that scores can be stored and viewed in a 'highscore' section in the app. Note: the code below comes from a standalone test database android application. In the end, I want to implement working code into my card playing application.
My question: how can I store, sort and display the 10 (in descending order) best scores. Also, in the case of similar scores (double values), how can i sort these scores based on the least amount of seconds to complete the game? So, in the case of identical score percentages, a quicker time means a better score and higher place in a top-10 highscore list. Finally, how can I erase all entries in the database and start a fresh one with this specific structure?
Some code:
In MyDBHandler.java, after constants:
public MyDBHandler(Context context, String name,
SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_SCORES_TABLE = "CREATE TABLE " +
TABLE_SCORES + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_TIME
+ " INTEGER," + COLUMN_SCORE + " REAL" + ")";
db.execSQL(CREATE_SCORES_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion,
int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCORES);
onCreate(db);
}
public void addScore(Score score) {
ContentValues values = new ContentValues();
values.put(COLUMN_TIME, score.getTime());
values.put(COLUMN_SCORE, score.getScore());
SQLiteDatabase db = this.getWritableDatabase();
db.insert(TABLE_SCORES, null, values);
db.close();
}
public Score[] findAllScores() {
SQLiteDatabase db = this.getWritableDatabase();
String query = "Select * FROM " + TABLE_SCORES + " ORDER BY " + COLUMN_SCORE + " DESC LIMIT 10";
Cursor cursor = db.rawQuery(query, null);
Score[] score = new Score[cursor.getCount()];
int counter = 0;
while (cursor.moveToNext()) {
score[counter] = new Score(cursor.getInt(0), cursor.getInt(1), cursor.getDouble(2));
counter++;
}
cursor.close();
db.close();
return score;
}
In my MainActivity:
TextView idView;
EditText timeField;
EditText scoreField;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
idView = (TextView) findViewById(R.id.outputField);
timeField = (EditText) findViewById(R.id.time);
scoreField = (EditText) findViewById(R.id.score);
}
public void newScore (View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
try {
double sc =
Double.parseDouble(scoreField.getText().toString());
int time = Integer.parseInt(timeField.getText().toString());
Score score =
new Score(time, sc);
dbHandler.addScore(score);
timeField.setText("");
scoreField.setText("");
}
catch (IllegalArgumentException e){
Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
}
}
public void lookupScore (View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
try {
double sc =
Double.parseDouble(scoreField.getText().toString());
Score score =
dbHandler.findScores(sc);
if (score != null) {
idView.setText(String.valueOf(score.getID()));
scoreField.setText(String.valueOf(score.getScore()));
} else {
idView.setText("No Match Found");
}
}
catch (IllegalArgumentException e){
Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
}
}
public void lookupAllScores (View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
idView.setText(dbHandler.getDatabaseName()+ ": \n");
try {
Score[] score =
dbHandler.findAllScores();
for (int i = 1; i < score.length; i++) {
idView.append(score[i].getID() + "\t" + score[i].getScore() + "\t" + score[i].getTime() + "\n");
}
}
catch (SQLiteException e){
Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
}
}
public void removeScore (View view) {
try {
MyDBHandler dbHandler = new MyDBHandler(this, null,
null, 1);
String score = scoreField.getText().toString();
dbHandler.deleteScores(Double.parseDouble(score));
Toast.makeText(this, "Scores Deleted", Toast.LENGTH_LONG).show();
timeField.setText("");
scoreField.setText("");
}
catch (IllegalArgumentException e){
Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
}
}
Score.java:
public class Score {
private int _id;
private int _time;
private double _score;
public Score() {
}
public Score(int id, int time, double score) {
this._id = id;
this._time = time;
this._score = score;
}
public Score(int time, double score) {
this._time = time;
this._score = score;
}
public void setID(int id) {
this._id = id;
}
public int getID() {
return this._id;
}
public void setTime(int time) {
this._time = time;
}
public int getTime() {
return this._time;
}
public void setScore(double score) {
this._score = score;
}
public double getScore() {
return this._score;
}
}
Aucun commentaire:
Enregistrer un commentaire