vendredi 27 mars 2015

Sort SQLite database entries by descending score (double) and ascending time (int)

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