Tutorial: Locale game scores with a SQL-Database

2011-01-31T13:06:00.006+01:00

Create a standard Android-Project, or include the following into your game.
At first we need a class which creates a database if it doesn't exist and can access the database.

Make a new class and call it ScoreDatabase and extend it from SQLiteOpenHelper

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;
import at.bartinger.scoreexample.sql.model.ScoreModel;

public class ScoreDatabase extends SQLiteOpenHelper{

 public ScoreDatabase(Context context) {
  super(context, "score.db", null, 1);
}

 @Override
 public void onCreate(SQLiteDatabase db) {
  // TODO Create your tables here
  Log.i("ScoreExample", "Create database: " + ScoreModel.TABLE_NAME);
  
  /*Creates the table in the database.
   * id: to access a single entry; for example for deleting a row
   * value: is the score which we want to save
   * 
   */
  
  db.execSQL("create table " + ScoreModel.TABLE_NAME + " (" + 
          BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
          ScoreModel.KEY_VALUE + " INTEGER NOT NULL," +
          ScoreModel.KEY_TIME + " TEXT);");
  
 }

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

}

This creates a database and a table with the rows: id, value(which is the score) and the date when the score gets inserted.

The super method contains:

the application context to create and open the database
the name of the database file
used for creating cursor objects, or null for the default
number of the database (starting at 1); if the database is older, onUpgrade(SQLiteDatabase, int, int) will be used to upgrade the database

the onCreate gets called when the database doesn't exist and should be created
there we create the table with the db.execSQL(...)
Note: you need some SQL knowledge, Google-skills or post a question

Next we make a new class named ScoreModel, which contains the table and row name and some method to access the score table.

import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.provider.BaseColumns;
import android.util.Log;

public class ScoreModel extends BaseModel {

 public final static String TABLE_NAME = "Score";

 public static final String KEY_VALUE = "value";

 public static final String KEY_TIME = "time";

 public ScoreModel(final Context context) {
  super(context, TABLE_NAME);
 }

 public Cursor getAllOrdered() {
  return database.query(TABLE_NAME, null, null, null, null, null, KEY_VALUE + " DESC");
 }

 public String getAllFormated(){
  
  String result = "";

  Cursor cursor = getAllOrdered();

  for (int i = 0; i < cursor.getCount(); i++) {
   if(!cursor.moveToPosition(i)){
    break;
   }
   int value = cursor.getInt(cursor.getColumnIndex(ScoreModel.KEY_VALUE));
   String time = cursor.getString(cursor.getColumnIndex(ScoreModel.KEY_TIME));
   
   result+= (i+1) +".     " + value + "      " + time + "\n";
  }
  
  return result;
 }

 public void add(final int value) {
  final ContentValues values = new ContentValues();
  values.put(KEY_VALUE, value);
  Date tmpDate = new Date();
  String date = tmpDate.getDate() + "." + tmpDate.getMonth()+1;
  values.put(KEY_TIME, date);

  database.insert(TABLE_NAME, null, values);
 }

 @Override
 public void remove(final int id) {
  database.delete(TABLE_NAME, BaseColumns._ID + " = " + id, null);
 }

}

BaseModel is a class by TomTasche. You can just copy it and use it for every Model you'll ever need.

Download it here.

Note: In the BaseModel class you have to change this line if you named your database class different
database = new ScoreDatabase(context).getWritableDatabase();

At next we create some constants (table and row names).
getAllOrdered()

returns a Cursor which contains all scores ordered by score (DESC means highest first, ASC would be lowest first)

a cursor contains the results of a database query

getAllFormated()

calls the getAllOrdered() and saves the results in one String

add()

inserts a new score into the database with ContentValues
put(rowName, value)

remove()

is clear i think

Now here is how to use this whole random stuff we created :)

ScoreModel model = new ScoreModel(this);

 //Generate a random score ...
 int score = (int)(Math.random()*100);
 //... and insert it into the database
 model.add(score);

 text.setText(model.getAllFormated());

text is a TextView. Download the full source here. Looks like this >

DONE!!!

If you like it please share leave a comment :)

Regards Bartinger!

Welcome back :)

2011-01-29T21:20:00.002+01:00

Hey guys!
I was very lazy the last few months in terms of writing for my blog. But now all my test are over and have time again be more active here. Currently I'm working on a two main projects.

Altitude
A 3D puzzle game

Altitude will be a condition based settings app. So you determine some conditions, like location or time and based on this the app will change the settings (wifi, mobile data, brightness, screen timeout) of your phone. You might know Locale. I'm working on an app, which generally will hopefully be able to do the same. I decided to release it for FREE in the Android Market and also push the source code up to GitHub. ♥OpenSource♥

The second project is just int the early stages of development and will be an puzzle game where you try to move a block into a whole.

This is a (very early) screenshot =>

I think at first I will finish Altitude and the go on with my puzzle game.

I also decided to make some Android Code tutorials, because I want to help you and also i want to improve my English-skills and I thought that would be a good training. So stay tuned.

If you have any questions, suggestions or ideas for Altitude, tutorials or something else please comment or feel free to contact me.

If you like it please share and leave a comment:)

Regards Bartinger!

PS: Stay updated on Twitter!

Bartinger and Android

Tutorial: Locale game scores with a SQL-Database

Welcome back :)