我是 Android 开发的新手，我想确保我正在学习做事的体面做法。现在这是我的数据库类，它目前允许我创建单例的新实例，以及创建配置文件表，以及从配置文件表中添加/检索。

到目前为止，这是我的代码:

public class DatabaseHelper extends SQLiteOpenHelper {
    private static volatile SQLiteDatabase mDatabase;
    private static DatabaseHelper mInstance = null;
    private static Context mContext;

    private static final String DB_NAME = "database.db";
    private static final int DB_VERSION = 1; 

    public static final String PROFILES_TABLE = "PROFILES";
    public static final String PROFILES_COLUMN_ID = "_ID";
    public static final String PROFILES_COLUMN_NAME = "NAME";

    private static final String DB_CREATE_PROFILES_TABLE =  
            "CREATE TABLE " + PROFILES_TABLE + " ("
                    + PROFILES_COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + PROFILES_COLUMN_NAME + " TEXT UNIQUE NOT NULL)";


    public static synchronized DatabaseHelper getInstance(Context context) {

        if (mInstance == null) {
            mInstance = new DatabaseHelper(context.getApplicationContext());
            try {
                mInstance.open();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return mInstance;
    }

    private DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DB_CREATE_PROFILES_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 

    }

    @Override
    public void onConfigure(SQLiteDatabase db){
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

    public synchronized void open() throws SQLException {
        mDatabase = getWritableDatabase();
    }

    public synchronized void close() {
        mDatabase.close();
    }

    public synchronized long addNewProfile(String name) {
        ContentValues values = new ContentValues();
        values.put(DatabaseHelper.PROFILES_COLUMN_NAME, name);
        return mDatabase.insertWithOnConflict(DatabaseHelper.PROFILES_TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);
    }

    public synchronized Profile getProfileById(long profileId) {
        Cursor cursor = mDatabase.query(
                DatabaseHelper.PROFILES_TABLE, // table
                new String[]{DatabaseHelper.PROFILES_COLUMN_ID, DatabaseHelper.PROFILES_COLUMN_NAME}, // column names
                DatabaseHelper.PROFILES_COLUMN_ID + " = ?", // where clause
                new String[]{profileId + ""}, // where params
                null, // groupby
                null, // having
                null);  // orderby
        cursor.moveToFirst();
        Profile profile = null;
        if (!cursor.isAfterLast()) {
            String profileName = getStringFromColumnName(cursor, DatabaseHelper.PROFILES_COLUMN_NAME);
            profile = new Profile(profileId, profileName);
            cursor.moveToNext();
        }
        cursor.close();
        return profile;
    }

    public synchronized List<Profile> getAllProfiles() {
        List<Profile> profiles = new ArrayList<>();
        Cursor cursor = mDatabase.query(
                DatabaseHelper.PROFILES_TABLE, // table
                new String[]{DatabaseHelper.PROFILES_COLUMN_ID, DatabaseHelper.PROFILES_COLUMN_NAME}, // column names
                null, // where clause
                null, // where params
                null, // groupby
                null, // having
                DatabaseHelper.PROFILES_COLUMN_NAME); // orderby
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            long profileId = getLongFromColumnName(cursor, DatabaseHelper.PROFILES_COLUMN_ID);
            String profileName = getStringFromColumnName(cursor, DatabaseHelper.PROFILES_COLUMN_NAME);
            profiles.add(new Profile(profileId, profileName));
            cursor.moveToNext();
        }
        cursor.close();
        return profiles;
    }

    private synchronized long getLongFromColumnName(Cursor cursor, String columnName) {
        int columnIndex = cursor.getColumnIndex(columnName);
        return cursor.getLong(columnIndex);
    }

    private synchronized String getStringFromColumnName(Cursor cursor, String columnName) {
        int columnIndex = cursor.getColumnIndex(columnName);
        return cursor.getString(columnIndex);
    }

}

供引用(这可能是必要的，也可能不是必要的，但我发布它以防万一)，我的 Profile 类，这是我在应用程序的其他几个地方使用的东西:

public class Profile {
    private long mId;
    private String mName;

    public Profile(long id, String name) {
        mId = id;
        mName = name;
    }

    public long getId() {
        return mId;
    }

    public void setId(long id) {
        mId = id;
    }

    public String getName() {
        return mName;
    }

    public void setName(String name) {
        mName = name;
    }

}

我的问题:

1. 像这样将表的字段名称存储在数据库类中是否合适，或者我是否应该将其移动到它自己的单独类(例如 ProfileSql 类)某种包含所有名称的类型)。

2. 我是否应该以某种方式将 CRUD 查询逻辑与此类分离？我该怎么做呢？如果我有多个表、查询、线程方法等怎么办？这些也都在他们自己的单独类(class)中吗？如果我为多个表添加 CRUD 函数，这个类可能会很快变得非常大。

3. 我是否应该以某种方式将任何这些东西绑定(bind)到我的 Profile 类本身，我在我的应用程序的其他几个地方使用它？例如，我应该在 Profile 类中包括配置文件表 SQL 内容(创建表字符串和表/列名称)，还是将不应该的事情混在一起？

如您所见，我主要是在询问东西应该放在哪里。现在我只是将它们全部集中到一个数据库类中。

我的主要目的是:

我希望这个示例足够短，以便有人可以向我展示重组所有这些内容的正确方法，以便我可以掌握这些技能并在事情变得越来越复杂时应用它们。

最佳答案

您应该将不同的表分成所谓的模型类。每个模型都有自己的一组数据库函数。

DatabaseHelper(或 DatabaseController)的存在只是为了提供一个“句柄”以供使用。

我没有时间写一个非常详细的答案，但我确实给你写了一些示例代码(可能无法直接编译)。

DatabaseController.java

此 Controller 还处理多个数据库用户(线程)并启用外键约束(您可能需要这些)。

public final class DatabaseController extends SQLiteOpenHelper {

    public static abstract class LocalDatabaseModel {

        public LocalDatabaseModel(){

        }

        public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion){

        }
        public abstract void onCreate(SQLiteDatabase database);
    }

    private SQLiteDatabase database;
    private int openConnections = 0;

    private static final String DATABASE = "database-name.db";
    private static final int VERSION = 1;
    private static DatabaseController instance = null;

    // Add you LocalDatabaseModels here.
    private final LocalDatabaseModel[] models = new LocalDatabaseModel[]{new Book.Model(), new Writer.Model()};


    public synchronized static DatabaseController getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseController(context.getApplicationContext());
        }
        return instance;
    }

    private DatabaseController(Context context) {
        super(context, DATABASE, null, VERSION);
    }

    /**
     * Must be called from the same thread as the original openDatabase call.
     */
    @Override
    public synchronized void close() {
        if(database == null || openConnections == 0){
            throw new IllegalStateException("Database already closed or has never been opened.");
        }
        openConnections--;
        if(openConnections != 0){
            return;
        }
        database = null;
        super.close();
    }

    /**
     * Do not manually call this method! Use openDatabase(), database() and close()!
     *
     * Opens the SQLiteDatabase if not already opened.
     * This implementation does the exact same thing as getWritableDatabase and thus will return a writable database.
     *
     * @return the newly opened database or the existing database.
     */
    @Override
    public synchronized SQLiteDatabase getReadableDatabase() {
        return getWritableDatabase();
    }

    /**
     *
     * Do not manually call this method! Use openDatabase(), database() and close()!
     *
     * Opens the SQLiteDatabase if not already opened.
     *
     * @return the newly opened database or the existing database.
     */
    @Override
    public synchronized SQLiteDatabase getWritableDatabase() {
        if(database == null){
            database = super.getWritableDatabase();
        }
        openConnections++;
        return database;
    }

    /**
     * Open the database. Always pair this call with close() and use database() to get the opened database!
     */
    public synchronized void openDatabase(){
        getWritableDatabase();
    }

    /**
     * Returns the opened database. Throws an exception if the database has not been opened yet!
     * @return the database.
     */
    public synchronized SQLiteDatabase database(){
        if(database == null){
            throw new IllegalStateException("Database has not been opened yet!");
        }
        return database;
    }

    @Override
    public synchronized void onCreate(SQLiteDatabase db) {
        setForeignKeyConstraintsEnabled(db);
        for(LocalDatabaseModel model: models){
            model.onCreate(db);
        }
    }

    @Override
    public synchronized void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        setForeignKeyConstraintsEnabled(db);
        for(LocalDatabaseModel model: models){
            model.onUpgrade(db, oldVersion, newVersion);
        }
    }

    @Override
    public synchronized void onOpen(SQLiteDatabase db) {
        setForeignKeyConstraintsEnabled(db);
    }

    @TargetApi(Build.VERSION_CODES.JELLY_BEAN)
    @Override
    public synchronized void onConfigure(SQLiteDatabase db) {
        db.setForeignKeyConstraintsEnabled(true);
    }

    private void setForeignKeyConstraintsEnabled(SQLiteDatabase db){
        //Skip for Android 4.1 and newer as this is already handled in onConfigure
        if(Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN && !db.isReadOnly()) {
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }

    /* I often have some utility methods in this class too. */
    public long getCount(String table){
        return DatabaseUtils.queryNumEntries(database(), table);
    }
}

Book.java

public final class Book {

    private long id = -1;
    private String title;

    public Book(String title){
        this.title = title;
    }

    private Book(long id, String title){
        this.title = title;
        this.id = id;
    }


    public void save(DatabaseController db){
        //save or update the book, throw an exception on failure.
    }

    //More non static methods (getters, setters, database methods) here

    public static Book getById(DatabaseController db, long id){
        //Do select query and get an existing book from the database.
    }

    //More static methods here

    public static class Model extends LocalDatabaseModel {

        public Model(){
        }

        @Override
        public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion){
             //Implement update logic for this model/table
         }
         @Override
         public void onCreate(SQLiteDatabase database){
             //Implement create logic for this model/table
          }
    }
}

用法

DatabaseController db = DatabaseController.getInstance(context);

db.openDatabase();


Book book = new Book("Alice in Wonderland");

book.save(db);

db.close();

关于java - 如何在 Android 中更好地安排我的 SQLite 数据库类，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37712349/