StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing a SQLite database in Libgdx
text
Body
copied!<p>I'm new in Libgdx and I'm getting trouble on using a database on my game.</p> <p>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.</p> <p>The last time I used a database in Android, I created a class that extends from <code>SQLiteOpenHelper</code>.</p> <p>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?</p> <p><strong>EDIT</strong></p> <p>I forgot to say that I'm looking for something that let me manage versions like <code>SQLiteOpenHelper</code>. In other words, I want to recreate my database in Android on apk installation, when I change the version of my DB on code.</p> <p><strong>SOLUTION</strong></p> <p>Following <code>@42n4</code> answer, I managed how to connect to SQLite Database using <code>SQLiteOpenHelper</code> on Android Application and <code>JDBC</code> on Desktop Application.</p> <p>First, I created a "common class" for both Desktop and Android Applications:</p> <pre><code>//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); [...] } } </code></pre> <p>Then, I created a <code>DatabaseDesktop</code> Class for Desktop Application:</p> <pre><code> 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; } [...] } } </code></pre> <p>And a <code>DatabaseAndroid</code> for Android Application</p> <pre><code>public 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); } [...] } } </code></pre> <p>Finally, I changed the Main Classes of both Android and Desktop Applications:</p> <pre><code>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); } } </code></pre> <p>Note that:</p> <p>I made a version management like the one that happens in <code>SQLiteOpenHelper</code> using the <code>PRAGMA user_version</code>. This way, I just change the version of the <code>DataBase</code> class when I need to upgrade it.</p> <p>I didn't put all the methods that I made on <code>Result</code> but, I put the ones that I think that are more important.that are more important.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload