[TUTORIAL] How to use SQLlite database in your game

  ... tutorials on how to use AndEngine.

Re: guide: How to use SQLlite for high-scores

Postby RealMayo » Tue Jan 31, 2012 2:29 am

Here is how I use SQLlite.
The following is a class that you should copy in its entirety and paste into a new class in your project. This class defines the database, and will be called by your main activity as needed to retrieve or insert information into the database. This works in both GLES1 and GLES2. Be sure to rename the package on the first line to match the package name of your game. You can easily modify this so that you are calling to the field fLevelScore for the purpose of manipulating the high score.
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. package com.myapp;
  2.  
  3. import android.content.ContentValues;
  4. import android.content.Context;
  5. import android.database.Cursor;
  6. import android.database.sqlite.SQLiteDatabase;
  7. import android.database.sqlite.SQLiteOpenHelper;
  8.  
  9. public class myDatabase extends SQLiteOpenHelper {
  10.  
  11.         static final String dbName = "myDB";
  12.         static final String tLevels = "Levels";
  13.         static final String fLevelID = "levelNum";
  14.         static final String fLevelUnLocked = "levelLocked";
  15.         static final String fLevelBeat = "levelBeat";
  16.         static final String fLevelScore = "levelScore";
  17.        
  18.         public myDatabase(Context context) {
  19. // THE VALUE OF 1 ON THE NEXT LINE REPRESENTS THE VERSION NUMBER OF THE DATABASE
  20. // IN THE FUTURE IF YOU MAKE CHANGES TO THE DATABASE, YOU NEED TO INCREMENT THIS NUMBER
  21. // DOING SO WILL CAUSE THE METHOD onUpgrade() TO AUTOMATICALLY GET TRIGGERED
  22.                 super(context, dbName, null, 1);
  23.         }
  24.  
  25.         @Override
  26.         public void onCreate(SQLiteDatabase db) {
  27. // ESTABLISH NEW DATABASE TABLES IF THEY DON'T ALREADY EXIST IN THE DATABASE
  28.                 db.execSQL("CREATE TABLE IF NOT EXISTS "+tLevels+" (" +
  29.                                         fLevelID + " INTEGER PRIMARY KEY , " +
  30.                                         fLevelUnLocked + " TEXT, " +
  31.                                         fLevelBeat + " TEXT, " +
  32.                                         fLevelScore + " TEXT" +
  33.                                         ")");
  34.        
  35. // OPTIONALLY PREPOPULATE THE TABLE WITH SOME VALUES   
  36.                  ContentValues cv = new ContentValues();
  37.                         cv.put(fLevelID, 1);
  38.                         cv.put(fLevelUnLocked, "true");
  39.                         cv.put(fLevelBeat, "false");
  40.                         cv.put(fLevelScore, "0");
  41.                                 db.insert(tLevels, null, cv);
  42.                         cv.put(fLevelID, 2);
  43.                         cv.put(fLevelUnLocked, "false");
  44.                         cv.put(fLevelBeat, "false");
  45.                         cv.put(fLevelScore, "0");
  46.                                 db.insert(tLevels, null, cv);
  47.                         cv.put(fLevelID, 3);
  48.                         cv.put(fLevelUnLocked, "false");
  49.                         cv.put(fLevelBeat, "false");
  50.                         cv.put(fLevelScore, "0");
  51.                                 db.insert(tLevels, null, cv);
  52.                                        
  53. /*             
  54.  * MORE ADVANCED EXAMPLES OF USAGE
  55.  *
  56.                 db.execSQL("CREATE TRIGGER fk_empdept_deptid " +
  57.                                 " BEFORE INSERT "+
  58.                                 " ON "+employeeTable+                          
  59.                                 " FOR EACH ROW BEGIN"+
  60.                                 " SELECT CASE WHEN ((SELECT "+colDeptID+" FROM "+deptTable+" WHERE "+colDeptID+"=new."+colDept+" ) IS NULL)"+
  61.                                 " THEN RAISE (ABORT,'Foreign Key Violation') END;"+
  62.                                 "  END;");
  63.                
  64.                 db.execSQL("CREATE VIEW "+viewEmps+
  65.                                 " AS SELECT "+employeeTable+"."+colID+" AS _id,"+
  66.                                 " "+employeeTable+"."+colName+","+
  67.                                 " "+employeeTable+"."+colAge+","+
  68.                                 " "+deptTable+"."+colDeptName+""+
  69.                                 " FROM "+employeeTable+" JOIN "+deptTable+
  70.                                 " ON "+employeeTable+"."+colDept+" ="+deptTable+"."+colDeptID
  71.                                 );
  72. */                             
  73.         }
  74.  
  75.         @Override
  76.         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  77. // THIS METHOD DELETES THE EXISTING TABLE AND THEN CALLS THE METHOD onCreate() AGAIN TO RECREATE A NEW TABLE
  78. // THIS SERVES TO ESSENTIALLY RESET THE DATABASE
  79. // INSTEAD YOU COULD MODIFY THE EXISTING TABLES BY ADDING/REMOVING COLUMNS/ROWS/VALUES THEN NO EXISTING DATA WOULD BE LOST
  80.                 db.execSQL("DROP TABLE IF EXISTS "+tLevels);
  81.                 onCreate(db);
  82.         }
  83.        
  84.          public String isLevelUnLocked(int ID) {
  85. // THIS METHOD IS CALLED BY YOUR MAIN ACTIVITY TO READ A VALUE FROM THE DATABASE                 
  86.                  SQLiteDatabase myDB = this.getReadableDatabase();
  87.                  String[] mySearch = new String[]{String.valueOf(ID)};
  88.                  Cursor myCursor = myDB.rawQuery("SELECT "+ fLevelUnLocked +" FROM "+ tLevels +" WHERE "+ fLevelID +"=?",mySearch);
  89.                  myCursor.moveToFirst();
  90.                  int index = myCursor.getColumnIndex(fLevelUnLocked);
  91.                  String myAnswer = myCursor.getString(index);
  92.                  myCursor.close();
  93.                  return myAnswer;
  94.          }
  95.          
  96.          public int unLockLevel(int ID, String isUnLocked)
  97.          {
  98. // THIS METHOD IS CALLED BY YOUR MAIN ACTIVITY TO WRITE A VALUE TO THE DATABASE          
  99.                  SQLiteDatabase myDB = this.getWritableDatabase();
  100.                  ContentValues cv = new ContentValues();
  101.                  cv.put(fLevelUnLocked, isUnLocked);
  102.                  int numRowsAffected = myDB.update(tLevels, cv, fLevelID+"=?", new String []{String.valueOf(ID)});
  103.                  return numRowsAffected;
  104.          }
  105.                
  106.                
  107. /*       
  108.  * MORE ADVANCED EXAMPLES OF USAGE
  109.  *
  110.          void AddEmployee(String _name, int _age, int _dept) {
  111.                 SQLiteDatabase db= this.getWritableDatabase();
  112.                 ContentValues cv=new ContentValues();
  113.                         cv.put(colName, _name);
  114.                         cv.put(colAge, _age);
  115.                         cv.put(colDept, _dept);
  116.                         //cv.put(colDept,2);
  117.                 db.insert(employeeTable, colName, cv);
  118.                 db.close();
  119.         }
  120.        
  121.          int getEmployeeCount()
  122.          {
  123.                 SQLiteDatabase db=this.getWritableDatabase();
  124.                 Cursor cur= db.rawQuery("Select * from "+employeeTable, null);
  125.                 int x= cur.getCount();
  126.                 cur.close();
  127.                 return x;
  128.          }
  129.          
  130.          Cursor getAllEmployees()
  131.          {
  132.                  SQLiteDatabase db=this.getWritableDatabase();
  133.                  //Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+", "+colAge+" from "+employeeTable, new String [] {});
  134.                  Cursor cur= db.rawQuery("SELECT * FROM "+viewEmps,null);
  135.                  return cur;
  136.          }
  137.          
  138.          public int GetDeptID(String Dept)
  139.          {
  140.                  SQLiteDatabase db=this.getReadableDatabase();
  141.                  Cursor c=db.query(deptTable, new String[]{colDeptID+" as _id",colDeptName},colDeptName+"=?", new String[]{Dept}, null, null, null);
  142.                  //Cursor c=db.rawQuery("SELECT "+colDeptID+" as _id FROM "+deptTable+" WHERE "+colDeptName+"=?", new String []{Dept});
  143.                  c.moveToFirst();
  144.                  return c.getInt(c.getColumnIndex("_id"));
  145.          }
  146.          
  147.          public String GetDept(int ID)
  148.          {
  149.                  SQLiteDatabase db=this.getReadableDatabase();
  150.                  String[] params=new String[]{String.valueOf(ID)};
  151.                  Cursor c=db.rawQuery("SELECT "+colDeptName+" FROM"+ deptTable+" WHERE "+colDeptID+"=?",params);
  152.                  c.moveToFirst();
  153.                  int index= c.getColumnIndex(colDeptName);
  154.                  return c.getString(index);
  155.          }
  156.          
  157.          public Cursor getEmpByDept(String Dept)
  158.          {
  159.                  SQLiteDatabase db=this.getReadableDatabase();
  160.                  String [] columns=new String[]{"_id",colName,colAge,colDeptName};
  161.                  Cursor c=db.query(viewEmps, columns, colDeptName+"=?", new String[]{Dept}, null, null, null);
  162.                  return c;
  163.          }
  164.          
  165.          public int UpdateEmp(String _name, int _age, int _dept, int _eid)
  166.          {
  167.                  SQLiteDatabase db=this.getWritableDatabase();
  168.                  ContentValues cv=new ContentValues();
  169.                  cv.put(colName, _name);
  170.                  cv.put(colAge, _age);
  171.                  cv.put(colDept, _dept);
  172.                  return db.update(employeeTable, cv, colID+"=?", new String []{String.valueOf(_eid)});
  173.          }
  174.          
  175.          public void DeleteEmp(String _name, int _age, int _dept, int _eid)
  176.          {
  177.                  SQLiteDatabase db=this.getWritableDatabase();
  178.                  db.delete(employeeTable,colID+"=?", new String [] {String.valueOf(_eid)});
  179.                  db.close();           
  180.          }
  181. */       
  182.  
  183. }
  184.  
Parsed in 0.017 seconds, using GeSHi 1.0.8.4


Then to use the database. In your main activity you can create some methods that call to the database class like this...
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.     private String isLevelUnLocked(int levelNum){
  2.         myDatabase myDB = new myDatabase(this);
  3.         String myReturn = myDB.isLevelUnLocked(levelNum);
  4.         myDB.close();
  5.         return myReturn;
  6.     }
  7.        
  8.     private int unLockLevel(int levelNum, String isUnLocked){
  9.         myDatabase myDB = new myDatabase(this);
  10.         int myReturn = myDB.unLockLevel(levelNum, isUnLocked);
  11.         myDB.close();
  12.         return myReturn;
  13.     }
  14.  
Parsed in 0.011 seconds, using GeSHi 1.0.8.4
User avatar
RealMayo
 
Posts: 1694
Joined: Sat Sep 03, 2011 9:25 pm
Location: Chicago, IL

Re: guide: How to use SQLlite for high-scores

Postby dinodeuces » Tue Jul 17, 2012 9:18 pm

im still confused :(

how do i incorporate this in my game

do i like call it when i hit a high score button?

and should i have an empty screen and this will print all of them to the screen?

im having big trouble any further explanation would be great. i've never used sql
dinodeuces
 
Posts: 22
Joined: Sun Jul 08, 2012 5:31 pm

Re: guide: How to use SQLlite for high-scores

Postby RealMayo » Tue Jul 17, 2012 10:34 pm

dinodeuces wrote:im still confused :(
how do i incorporate this in my game
do i like call it when i hit a high score button?
and should i have an empty screen and this will print all of them to the screen?
im having big trouble any further explanation would be great. i've never used sql


Ok well pretend that your SQL database is actually an Excel file. In this example, the dbName would be the name of your Excel file. tLevels would be the particular sheet/tab in your Excel file. Then the columns in your Excel sheet would be fLevelID, fLevelUnLocked, fLevelBeat, fLevelScore. Once you populate your Excel sheet with data, it contains many rows of data. For example, some of the rows could be:
fLevelID=1 fLevelUnlocked=true fLevelBeat=true fLevelScore=136
fLevelID=2 fLevelUnlocked=true fLevelBeat=false fLevelScore=0

That is the same way that a simple SQL database works. Its just that in SQL, the dbName is the "database name", tLevels is the "table name", fLevelID is a "field name". So regarding the code that I supplied in my earlier post, you would go to your project in Eclipse, righ-click on your project and choose New Class. Name the class "myDatabase". Then open that class and replace the contents with my code. Then just rename the package name on line #1 to match the package name of your project. After you launch your game, then in the main activity of your game, the first time you call the following line of code, the SQL database will be initialized and be prepopulated with the values that are defined in my code...
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. myDatabase myDB = new myDatabase(this);
Parsed in 0.010 seconds, using GeSHi 1.0.8.4

Now go to the main activity in your game and try to query the database to verify the data that exists within it. As an example, copy/paste the methods I supplied which are called isLevelUnlocked and unLockLevel (later you can create similar methods related to highscore). Once those methods exist in your activity, you can make use of them in that same activity. Ok now try calling the isLevelUnlocked method from that same activity. Do something like this to check if Level #1 is unlocked...
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. final String checkUnLocked = isLevelUnLocked(1);
  2. if(checkUnLocked.compareTo("true") == 0){
  3. System.out.println("level one is unlocked!");
  4. }else{
  5. System.out.println("level one is not unlocked!");
  6. }
  7.  
Parsed in 0.010 seconds, using GeSHi 1.0.8.4

Ok, that was a "read" operation from the database. Now lets do a "write" operation to the database. Let's set Level #1 to be locked...
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. final int numRowsModified = unLockLevel(1,"false");
  2. System.out.println("we successfully modified " + numRowsModified + " rows in the database");
  3.  
Parsed in 0.010 seconds, using GeSHi 1.0.8.4

As for WHEN to incorporate this in the game... Well the calls to the methods like we've done above can be triggered by the user pressing a button on the screen. Or like in my game, once I detect collision between the gold nugget and the inside of the truck, then in my collision detection code I call some of those methods to store the new highscore and to unlock the next level.

Note: if you want to display multiple rows of data to the user, then you will need to use a Cursor. Let me know if you want to learn more about Cursors ;)
User avatar
RealMayo
 
Posts: 1694
Joined: Sat Sep 03, 2011 9:25 pm
Location: Chicago, IL

Re: guide: How to use SQLlite for high-scores

Postby salami » Fri Jul 27, 2012 2:15 pm

You could also use Singleton pattern like so:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public static Database getInstance(final Context pContext){
  2.                 if(mInstance == null)
  3.                         mInstance = new Database(pContext);
  4.                 return mInstance;
  5.         }
  6.  
Parsed in 0.011 seconds, using GeSHi 1.0.8.4


and in your Activity:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. final Database database = Database.getInstance(this);
  2. final Cursor info = database.getLevelInfo(mLevelPath);
  3. /*
  4.  *getting data from info...
  5. */
  6. info.close();
  7.  
Parsed in 0.011 seconds, using GeSHi 1.0.8.4
salami
 
Posts: 57
Joined: Wed May 11, 2011 3:22 pm

Re: guide: How to use SQLlite for high-scores

Postby UmzGames » Fri Nov 23, 2012 8:39 pm

GREAT :D RealMayo you f***ing legend :star: !
I really struggled along with SQLite, and actually had an implementation with preferences.. :? Which may have been better since it's only 6 records.. Hmm..

But this is a great class and will be reused across multiple projects. Much appreciated :lightning:
Umz Games -:- Enter Into a New Reality -:-

Umz Mini Games on Google Play - Download Now
User avatar
UmzGames
 
Posts: 46
Joined: Thu Sep 20, 2012 12:55 am
Location: UK

Re: guide: How to use SQLlite for high-scores

Postby riefdeok » Sat Jan 12, 2013 6:30 am

Radzell wrote:This is the code i used for creating a DB class in poly blast. I also changed it to make the Level Editor for my to be named peggle game.
You just implement it with
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. HighScoreDb db = new HighScoreSb(this);
Parsed in 0.012 seconds, using GeSHi 1.0.8.4

The you can add a row with createRow(info here...);
when you query the results with GetAllRows you get a cursor with all the information with score desending.
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public class HighScoreDb {
  2.     private static class HighScoreDbHelper extends SQLiteOpenHelper {
  3.  
  4.         public HighScoreDbHelper(Context context) {
  5.             super(context, DATABASE_NAME, null, DATABASE_VERSION);
  6.  
  7.         }
  8.  
  9.         @Override
  10.         public void onCreate(SQLiteDatabase db) {
  11.  
  12.             try {
  13.                 db.execSQL(SCORE_TABLE_CREATE);
  14.             } catch (SQLException e) {
  15.                 Log.i("Error", "Error making database");
  16.                 e.printStackTrace();
  17.             }
  18.         }
  19.  
  20.         @Override
  21.         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  22.             db.execSQL("DROP TABLE IF EXISTS " + SCORE_TABLE_NAME);
  23.             onCreate(db);
  24.         }
  25.  
  26.     }
  27.  
  28.     private static final int DATABASE_VERSION = 2;
  29.     private static final String SCORE_TABLE_NAME = "highscore";
  30.     private static final String SCORE_TABLE_CREATE = "CREATE TABLE "
  31.                                                      + SCORE_TABLE_NAME
  32.                                                      + " (_id INTEGER PRIMARY KEY autoincrement, "
  33.                                                      + "name TEXT NOT NULL, score INTEGER  NOT NULL)";
  34.     private static final String DATABASE_NAME = "polyblaster.db";
  35.     // The index (key) column name for use in where clauses.
  36.     public static final String KEY_ID = "_id";
  37.  
  38.     // The name and column index of each column in your database.
  39.     public static final String KEY_NAME = "name";
  40.     public static final String KEY_SCORE = "score";
  41.     public static final int NAME_COLUMN = 1;
  42.     public static final int NUMBER_COLUMN = 2;
  43.     public static final int SCORE_COLUMN = 3;
  44.  
  45.     SQLiteDatabase db;
  46.     private final Context ctx;
  47.     private final HighScoreDbHelper dbHelper;
  48.  
  49.     public HighScoreDb(Context context) {
  50.         this.ctx = context;
  51.         ctx.deleteDatabase(SCORE_TABLE_NAME);
  52.         dbHelper = new HighScoreDbHelper(context);
  53.         db = dbHelper.getWritableDatabase();
  54.     }
  55.  
  56.     public void close() {
  57.         if (db != null) {
  58.             db.close();
  59.         }
  60.  
  61.     }
  62.  
  63.     public void createRow(String name, int score) {
  64.         ContentValues intialValue = new ContentValues();
  65.         intialValue.put("name", name);
  66.         intialValue.put("score", score);
  67.         db.insertOrThrow(SCORE_TABLE_NAME, null, intialValue);
  68.  
  69.     }
  70.  
  71.     public void deleteRow(long rowId) {
  72.         db.delete(SCORE_TABLE_NAME, "_id=" + rowId, null);
  73.     }
  74.  
  75.     public Cursor GetAllRows() {
  76.         try {
  77.             return db.query(SCORE_TABLE_NAME, new String[] { "_id", "name", "score" }, null,
  78.                             null, null, null, "score DESC");
  79.         } catch (SQLException e) {
  80.             Log.i("Error on query", e.toString());
  81.             return null;
  82.         }
  83.  
  84.     }
  85.  
  86.     public void updateRow(long _id, String name, String score) {
  87.         ContentValues args = new ContentValues();
  88.         args.put("name", name);
  89.         args.put("number", score);
  90.         db.update(SCORE_TABLE_NAME, args, "_id=" + _id, null);
  91.     }
  92.  
  93. }
  94.  
  95.  
Parsed in 0.018 seconds, using GeSHi 1.0.8.4


hello, I'm a newbie in Sqlite.
I used code from Radzell' code for my games.
I try to fetch data with method GetAllRows(), but how to show all value from this table ?
is there any method for show the result?
thanks
riefdeok
 
Posts: 1
Joined: Thu Nov 29, 2012 7:38 am

Re: guide: How to use SQLlite for high-scores

Postby RealMayo » Sat Jan 12, 2013 10:42 pm

Hi
GetAllRows() returns a Cursor. You need to manipulate the Cursor in order to see the values.
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. HighScoreDb db = new HighScoreDb(this);
  2. Cursor myCursor = db.GetAllRows();
  3. myCursor.moveToPosition(0);
  4.     String Row1Value1 = myCursor.getString(0);
  5.     String Row1Value2 = myCursor.getString(1);
  6.     String Row1Value3 = myCursor.getString(2);
  7. myCursor.moveToPosition(1);
  8.     String Row2Value1 = myCursor.getString(0);
  9.     String Row2Value2 = myCursor.getString(1);
  10.     String Row2Value3 = myCursor.getString(2);
  11. myCursor.close();
  12. db.close();      
  13.  
Parsed in 0.013 seconds, using GeSHi 1.0.8.4
User avatar
RealMayo
 
Posts: 1694
Joined: Sat Sep 03, 2011 9:25 pm
Location: Chicago, IL

Re: guide: How to use SQLlite for high-scores

Postby wickedbelial » Fri Mar 08, 2013 7:48 am

I know this is sort of an old post but I'd like to ask a question.
Are Texture Atlas / Regions needed to be stored in SQLlite?
Im currently trying to solve a problem about relaunching my game after the screen is locked or a phone call comes up. Android calls onDestroy() which terminates my app and when I relaunch it I get a NullBitmapException so I have to launch it again. Im hoping that retrieving everything from database at onStart() will solve this. Thanks in advance.
wickedbelial
 
Posts: 1
Joined: Sat Mar 02, 2013 11:57 pm


Re: guide: How to use SQLlite for high-scores

Postby tolgamyth » Wed Apr 24, 2013 9:45 am

this is a nice piece of work, but what if i want to create my database under the assets folder? is there a way for this?
tolgamyth
 
Posts: 7
Joined: Thu Feb 14, 2013 1:33 pm

PreviousNext

Return to Tutorials

Who is online

Users browsing this forum: No registered users and 12 guests