java 在 Libgdx 中使用 SQLite 数据库

Using a SQLite database in Libgdx

提问by Cristiano Santos

I'm new in Libgdx and I'm getting trouble on using a database on my game.

我是 Libgdx 的新手，在我的游戏中使用数据库时遇到了问题。

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.

我搜索了一个关于如何使用 Libgdx 使 SQLite 在 Android 和桌面应用程序上工作的教程，但我没有找到一个简单的教程。

The last time I used a database in Android, I created a class that extends from SQLiteOpenHelper.

上次我在 Android 中使用数据库时，我创建了一个从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?

是否有使用 Libgdx 执行相同操作的简单方法？或者至少，有人能指点我一步一步的教程或类似的东西吗？

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.

我忘了说我正在寻找可以让我管理诸如SQLiteOpenHelper. 换句话说，当我在代码上更改我的数据库版本时，我想在安装 apk 时在 Android 中重新创建我的数据库。

SOLUTION

解决方案

Following @42n4answer, I managed how to connect to SQLite Database using SQLiteOpenHelperon Android Application and JDBCon Desktop Application.

以下@42n4回答，我管理了如何使用SQLiteOpenHelperAndroid 应用程序和JDBC桌面应用程序连接到 SQLite 数据库。

First, I created a "common class" for both Desktop and Android Applications:

首先，我为桌面和 Android 应用程序创建了一个“通用类”：

//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 DatabaseDesktopClass for Desktop Application:

然后，我DatabaseDesktop为桌面应用程序创建了一个类：

    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 DatabaseAndroidfor Android Application

和一个DatabaseAndroid适用于 Android 的应用程序

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);
        }

        [...]

    }

}

Finally, I changed the Main Classes of both Android and Desktop Applications:

最后，我更改了 Android 和桌面应用程序的主类：

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 SQLiteOpenHelperusing the PRAGMA user_version. This way, I just change the version of the DataBaseclass when I need to upgrade it.

我做了一个版本管理，就像SQLiteOpenHelper使用PRAGMA user_version. 这样，DataBase当我需要升级它时，我只需更改类的版本。

I didn't put all the methods that I made on Resultbut, I put the ones that I think that are more important.that are more important.

我没有把我做过的所有方法都放在上面，Result但是，我把那些我认为更重要的。那些更重要的。

采纳答案by 42n4

http://marakana.com/techtv/android_bootcamp_screencast_series.htmlClass 4, Part 1: Android Bootcamp - statusData, for libgdx: http://code.google.com/p/libgdx-users/wiki/SQLite

http://marakana.com/techtv/android_bootcamp_screencast_series.html第 4 类，第 1 部分：Android 训练营 - statusData，对于 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

编辑：我应该提到关于 Udacity 的两门关于 libgdx 游戏的新课程：https: //github.com/udacity/ud405

https://github.com/udacity/ud406

https://github.com/udacity/ud406

回答by Rafay

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

我编写了一个扩展程序（称为 gdx-sqlite），它可以完成您需要的大部分工作。这个扩展的最新版本可以从这里下载。源代码和自述位于：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.

此扩展目前支持 Android 和桌面平台。此外，不支持打开位于 Android 应用程序资产文件夹中的数据库。但是，这是一个待定的功能，将很快添加。

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");
    }
}