title: Using a SQLite database in Libgdx
body: <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>