Using a SQLite database in Libgdx
Solution 1
http://marakana.com/techtv/android_bootcamp_screencast_series.html Class 4, Part 1: Android Bootcamp - statusData, for libgdx: http://code.google.com/p/libgdx-users/wiki/SQLite
EDIT: I should mention about two new courses about libgdx games at Udacity: https://github.com/udacity/ud405
https://github.com/udacity/ud406
Solution 2
There is an extension (called gdx-sqlite) that I wrote which will do most of the work you require. Latest build of this extension can be downloaded from here. The source code and read me are located at: https://github.com/mrafayaleem/gdx-sqlite
This extension currently supports Android and Desktop platforms. Also, there is no support to open databases located in the assets folder of the Android app. However, this is a pending feature and will be added soon.
Follow the instructions in read me to setup your projects for database handling. Following is an example code:
package com.mrafayaleem.gdxsqlitetest;
import com.badlogic.gdx.Gdx;
import com.badlogic.gdx.sql.Database;
import com.badlogic.gdx.sql.DatabaseCursor;
import com.badlogic.gdx.sql.DatabaseFactory;
import com.badlogic.gdx.sql.SQLiteGdxException;
public class DatabaseTest {
Database dbHandler;
public static final String TABLE_COMMENTS = "comments";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_COMMENT = "comment";
private static final String DATABASE_NAME = "comments.db";
private static final int DATABASE_VERSION = 1;
// Database creation sql statement
private static final String DATABASE_CREATE = "create table if not exists "
+ TABLE_COMMENTS + "(" + COLUMN_ID
+ " integer primary key autoincrement, " + COLUMN_COMMENT
+ " text not null);";
public DatabaseTest() {
Gdx.app.log("DatabaseTest", "creation started");
dbHandler = DatabaseFactory.getNewDatabase(DATABASE_NAME,
DATABASE_VERSION, DATABASE_CREATE, null);
dbHandler.setupDatabase();
try {
dbHandler.openOrCreateDatabase();
dbHandler.execSQL(DATABASE_CREATE);
} catch (SQLiteGdxException e) {
e.printStackTrace();
}
Gdx.app.log("DatabaseTest", "created successfully");
try {
dbHandler
.execSQL("INSERT INTO comments ('comment') VALUES ('This is a test comment')");
} catch (SQLiteGdxException e) {
e.printStackTrace();
}
DatabaseCursor cursor = null;
try {
cursor = dbHandler.rawQuery("SELECT * FROM comments");
} catch (SQLiteGdxException e) {
e.printStackTrace();
}
while (cursor.next()) {
Gdx.app.log("FromDb", String.valueOf(cursor.getString(1)));
}
try {
dbHandler.closeDatabase();
} catch (SQLiteGdxException e) {
e.printStackTrace();
}
dbHandler = null;
Gdx.app.log("DatabaseTest", "dispose");
}
}
Related videos on Youtube
Cristiano Santos
Graduated in Computer Engineering Worked on AroundExtreme as Website Developer, for Appsalad on Mobile Development via oDesk and for Famous Gadget as a Game Developer Currently working for Logirix as a senior mobile developer in React Native
Updated on September 15, 2022Comments
-
Cristiano Santos over 1 year
I'm new in Libgdx and I'm getting trouble on using a database on my game.
I searched for a tutorial on how to make SQLite work on both Android and Desktop applications using Libgdx but I didn't found a easy one.
The last time I used a database in Android, I created a class that extends from
SQLiteOpenHelper
.Is there a simple way to do the same using Libgdx? Or at least, can anyone point me to a step-by-step tutorial or something similar?
EDIT
I forgot to say that I'm looking for something that let me manage versions like
SQLiteOpenHelper
. In other words, I want to recreate my database in Android on apk installation, when I change the version of my DB on code.SOLUTION
Following
@42n4
answer, I managed how to connect to SQLite Database usingSQLiteOpenHelper
on Android Application andJDBC
on Desktop Application.First, I created a "common class" for both Desktop and Android Applications:
//General class that needs to be implemented on Android and Desktop Applications public abstract class DataBase { protected static String database_name="recycling_separation"; protected static DataBase instance = null; protected static int version=1; //Runs a sql query like "create". public abstract void execute(String sql); //Identical to execute but returns the number of rows affected (useful for updates) public abstract int executeUpdate(String sql); //Runs a query and returns an Object with all the results of the query. [Result Interface is defined below] public abstract Result query(String sql); public void onCreate(){ //Example of Highscore table code (You should change this for your own DB code creation) execute("CREATE TABLE 'highscores' ('_id' INTEGER PRIMARY KEY NOT NULL , 'name' VARCHAR NOT NULL , 'score' INTEGER NOT NULL );"); execute("INSERT INTO 'highscores'(name,score) values ('Cris',1234)"); //Example of query to get DB data of Highscore table Result q=query("SELECT * FROM 'highscores'"); if (!q.isEmpty()){ q.moveToNext(); System.out.println("Highscore of "+q.getString(q.getColumnIndex("name"))+": "+q.getString(q.getColumnIndex("score"))); } } public void onUpgrade(){ //Example code (You should change this for your own DB code) execute("DROP TABLE IF EXISTS 'highscores';"); onCreate(); System.out.println("DB Upgrade maded because I changed DataBase.version on code"); } //Interface to be implemented on both Android and Desktop Applications public interface Result{ public boolean isEmpty(); public boolean moveToNext(); public int getColumnIndex(String name); public float getFloat(int columnIndex); [...] } }
Then, I created a
DatabaseDesktop
Class for Desktop Application:public class DatabaseDesktop extends DataBase{ protected Connection db_connection; protected Statement stmt; protected boolean nodatabase=false; public DatabaseDesktop() { loadDatabase(); if (isNewDatabase()){ onCreate(); upgradeVersion(); } else if (isVersionDifferent()){ onUpgrade(); upgradeVersion(); } } public void execute(String sql){ try { stmt.execute(sql); } catch (SQLException e) { e.printStackTrace(); } } public int executeUpdate(String sql){ try { return stmt.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } return 0; } public Result query(String sql) { try { return new ResultDesktop(stmt.executeQuery(sql)); } catch (SQLException e) { e.printStackTrace(); } return null; } private void loadDatabase(){ File file = new File (database_name+".db"); if(!file.exists()) nodatabase=true; try { Class.forName("org.sqlite.JDBC"); db_connection = DriverManager.getConnection("jdbc:sqlite:"+database_name+".db"); stmt = db_connection.createStatement(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } private void upgradeVersion() { execute("PRAGMA user_version="+version); } private boolean isNewDatabase() { return nodatabase; } private boolean isVersionDifferent(){ Result q=query("PRAGMA user_version"); if (!q.isEmpty()) return (q.getInt(1)!=version); else return true; } public class ResultDesktop implements Result{ ResultSet res; boolean called_is_empty=false; public ResultDesktop(ResultSet res) { this.res = res; } public boolean isEmpty() { try { if (res.getRow()==0){ called_is_empty=true; return !res.next(); } return res.getRow()==0; } catch (SQLException e) { e.printStackTrace(); } return false; } public boolean moveToNext() { try { if (called_is_empty){ called_is_empty=false; return true; } else return res.next(); } catch (SQLException e) { e.printStackTrace(); } return false; } public int getColumnIndex(String name) { try { return res.findColumn(name); } catch (SQLException e) { e.printStackTrace(); } return 0; } public float getFloat(int columnIndex) { try { return res.getFloat(columnIndex); } catch (SQLException e) { e.printStackTrace(); } return 0; } [...] } }
And a
DatabaseAndroid
for Android Applicationpublic class DatabaseAndroid extends DataBase{ protected SQLiteOpenHelper db_connection; protected SQLiteDatabase stmt; public DatabaseAndroid(Context context) { db_connection = new AndroidDB(context, database_name, null, version); stmt=db_connection.getWritableDatabase(); } public void execute(String sql){ stmt.execSQL(sql); } public int executeUpdate(String sql){ stmt.execSQL(sql); SQLiteStatement tmp = stmt.compileStatement("SELECT CHANGES()"); return (int) tmp.simpleQueryForLong(); } public Result query(String sql) { ResultAndroid result=new ResultAndroid(stmt.rawQuery(sql,null)); return result; } class AndroidDB extends SQLiteOpenHelper { public AndroidDB(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); } public void onCreate(SQLiteDatabase db) { stmt=db; DatabaseAndroid.this.onCreate(); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { stmt=db; DatabaseAndroid.this.onUpgrade(); } } public class ResultAndroid implements Result{ Cursor cursor; public ResultAndroid(Cursor cursor) { this.cursor=cursor; } public boolean isEmpty() { return cursor.getCount()==0; } public int getColumnIndex(String name) { return cursor.getColumnIndex(name); } public String[] getColumnNames() { return cursor.getColumnNames(); } public float getFloat(int columnIndex) { return cursor.getFloat(columnIndex); } [...] } }
Finally, I changed the Main Classes of both Android and Desktop Applications:
public class Main extends AndroidApplication { public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); initialize(new MyGame(new DatabaseAndroid(this.getBaseContext())), false); } } public class Main { public static void main(String[] args) { new LwjglApplication(new MyGame(new DatabaseDesktop()), "Example", MyGame.SCREEN_WIDTH, MyGame.SCREEN_HEIGHT,false); } }
Note that:
I made a version management like the one that happens in
SQLiteOpenHelper
using thePRAGMA user_version
. This way, I just change the version of theDataBase
class when I need to upgrade it.I didn't put all the methods that I made on
Result
but, I put the ones that I think that are more important.that are more important. -
Cristiano Santos about 11 yearsSo, for both Android and Desktop, I create a class named
StatusData
right? In Android, I can useSQLiteOpenHelper
like I used before. But what about the Desktop application? How could I, for example, make the Desktop Application recreate the DB when the DB version changes onStatusData
? -
Cristiano Santos about 11 yearsSo, just to be clear, I can use the
SQLiteOpenHelper
on Android and in Desktop I could make the same thing usingJDBC
and both of them are created on a class namedStatusData
or something similar. Right? -
42n4 about 11 yearsI think that that site has even one example, try to implement it.
-
Cristiano Santos about 11 yearsI already tried this second tutorial but I became stuck because I don't know how to recreate the database when I change something on it and I don't know How do I add SQLDroid to my current project. Anyway, thanks for the help =).