我是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;
    }

}

我的问题:
  • 将表的字段名称存储在这样的数据库类中是否合适,还是应该将其移至其自己的单独类(例如,某种拥有所有名称的ProfileSql类)。
  • 是否应该以某种方式将CRUD查询逻辑与此类分离?我该怎么做呢?如果我有几个表,查询,线程方法等怎么办?所有这些也都放在各自的单独的类中吗?如果我为多个表添加CRUD函数,则此类可能很快变得非常大。
  • 我是否应该以某种方式将这些东西绑定(bind)到我的Profile类本身中,以便在我的应用程序中的其他几个地方使用?例如,我应该在Profile类中包含配置文件表SQL内容(创建表字符串和表/列名称),还是应该将这些东西混在一起?

  • 如您所见,我主要是问东西应该去哪里。现在,我只是将它们全部合并到一个数据库类中。

    我的主要意图是:

    我希望这个示例足够简短,可以使有人向我展示重新构造所有这些结构的正确方法,以便我可以掌握这些技能,并在事情变得更加复杂时应用它们。

    最佳答案

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

    DatabaseHelper(或DatabaseController)仅用于提供要使用的“句柄”。

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

    DatabaseController.java

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

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

    09-05 16:50
    查看更多