《第一行代码》阅读笔记（二十三）——数据库设计（补充）

废话不多说，先看下结构

然后直接上代码

public class StudyProgressDBHelper extends SQLiteOpenHelper {
    //数据库名
    private static final String DB_NAME = "progress.db";
    //数据库版本号
    private static final int DB_VERSION = 1;//9-16 09:38 2-->3  10-14 17:32 3-->4 11-8 14:14 4-->5

    private Context mContext;

    //构造函数
    public StudyProgressDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        mContext = context;
    }

    //创建学习进度数据库
    private static final String CREATE_STUDY_PROGRESS = "create table if not exists " + StudyProgressDAO.TABLE_NAME + " (" +
            StudyProgressDAO.VOA_ID + " Integer NOT NULL primary key," +
            StudyProgressDAO.LESSON + " varchar(20) ," +
            StudyProgressDAO.LISTEN_TIME + " int," +
            StudyProgressDAO.USER_ID + " int," +
            StudyProgressDAO.TOTAL_LISTEN_TIME + " int," +
            StudyProgressDAO.RIGHT_QUE_NUM + " int," +
            StudyProgressDAO.TOTAL_QUE_NUM + " int," +
            StudyProgressDAO.IS_EVALUATION_SENT_NUM + " int," +
            StudyProgressDAO.TOTAL_SENT_NUM + " int" + ")";

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_STUDY_PROGRESS);
        Timber.e("创建表成功"+CREATE_STUDY_PROGRESS);
    }

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

        }
    }
}

public interface StudyProgressDAO {

    String TABLE_NAME = "study_progress";

    String VOA_ID = "voa_id";
    String LESSON = "lesson";
    String USER_ID = "user_id";
    String LISTEN_TIME = "listen_time";
    String TOTAL_LISTEN_TIME = "total_listen_time";
    String RIGHT_QUE_NUM = "right_que_num";
    String TOTAL_QUE_NUM = "total_que_num";
    String IS_EVALUATION_SENT_NUM = "is_evaluation_sent_num";
    String TOTAL_SENT_NUM = "total_sent_num";

    public void setStudyProgress(int voaID, String databaseColumn, int value);

    public int getStudyProgress(String databaseColumn ,int voaID);

    public int getVoaId(int voaID);

}

public class StudyProgressDAOImpl implements StudyProgressDAO {

    private final SQLiteDatabase db;

    StudyProgressDAOImpl(SQLiteDatabase db) {
        this.db = db;
    }


    @Override
    public void setStudyProgress(int voaID, String databaseColumn, int value) {
        String sql;
        if (getVoaId(voaID) == 0) {
            sql = "Insert Into " + TABLE_NAME
                    + " ( " + VOA_ID + "," + databaseColumn + " )" +
                    " VALUES (" + voaID + "," + value + ")";
        } else {
            sql = " UPDATE " + TABLE_NAME
                    + " SET " + databaseColumn + " = " + value
                    + " WHERE " + VOA_ID + " = " + voaID;
        }
        db.execSQL(sql);
    }

    @Override
    public int getStudyProgress(String databaseColumn, int voaID) {
        String sql = " Select " + databaseColumn
                + " FROM " + TABLE_NAME
                + " WHERE " + VOA_ID + "= ?";
        String[] args = {String.valueOf(voaID)};
        Cursor cursor = db.rawQuery(sql, args);
        if (cursor != null && cursor.moveToFirst()) {
            return cursor.getInt(cursor.getColumnIndex(databaseColumn));
        } else {
            return 0;
        }

    }

    @Override
    public int getVoaId(int voaID) {
        String sql = " SELECT voa_id FROM study_progress  WHERE voa_id = ?" ;
        String[] args = {String.valueOf(voaID)};
        Cursor cursor = db.rawQuery(sql, args);
        db.execSQL(sql);
        if (cursor != null && cursor.moveToFirst()) {
            return cursor.getInt(cursor.getColumnIndex(VOA_ID));
        } else {
            return 0;
        }
    }
}

public class StudyProgressDBManager implements StudyProgressDAO {

    private static StudyProgressDBManager sInstance;

    private static StudyProgressDAOImpl studyProgressDAOImpl;

    public static void init(Context appContext) {
        if (sInstance == null) {
            sInstance = new StudyProgressDBManager(appContext);          
        }
    }

    public static StudyProgressDBManager getInstance() {
        if (null == sInstance) throw new NullPointerException("not init");
        return sInstance;
    }

    private StudyProgressDBManager(Context context) {
        StudyProgressDBHelper dbHelper = new StudyProgressDBHelper(context);//onCreate
        SQLiteDatabase db = dbHelper.getWritableDatabase();
     
        studyProgressDAOImpl = new StudyProgressDAOImpl(db);
    }

    @Override
    public void setStudyProgress(int voaID, String databaseColumn, int value) {
        studyProgressDAOImpl.setStudyProgress(voaID,databaseColumn,value);
    }

    @Override
    public int getStudyProgress(String databaseColumn, int voaID) {
        return studyProgressDAOImpl.getStudyProgress(databaseColumn,voaID);
    }

    @Override
    public int getVoaId(int voaID) {
        return studyProgressDAOImpl.getVoaId(voaID);
    }
}

大家看懂了没有？这是公司里面一个大神的数据库设计方式，第一次看到也被这种清晰的结构所折服。接下来就让笔者班门弄斧一下，给大家提供一些解释吧。

StudyProgressDBHelper

public class StudyProgressDBHelper extends SQLiteOpenHelper {
    //数据库名
    private static final String DB_NAME = "progress.db";
    //数据库版本号
    private static final int DB_VERSION = 1;//9-16 09:38 2-->3  10-14 17:32 3-->4 11-8 14:14 4-->5

    private Context mContext;

    //构造函数
    public StudyProgressDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        mContext = context;
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
     
    }

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

        }
    }
}

首先我们应该编写的就是Helper类，这个类需要继承SQLiteOpenHelper。其实大家在创建郭神的《第一行代码》中应该早就熟悉了这个类的使用。

在这个类中，我们需要确定包名和数据库版本，均使用 public static final修饰，并提供一个环境的成员变量。
之后添加一个构造函数，在构造函数中直接调用父类的构造函数，传入环境，数据库名，null（工厂），数据库版本，同时将环境赋值给成员变量。

然后重写SQLiteOpenHelper的两个方法，创建数据库和更新数据库，并把创建数据库的SQL抽取出来，让结构更加清晰。

StudyProgressDBManager

这个管理类就是整个数据库设计的核心，在这里采用非常常用的单例设计模式，这样就可以有效的防止数据库操作的冲突。

private static StudyProgressDBManager sInstance;

下面这三个函数就是Manager的核心

public static void init(Context appContext) {
        if (sInstance == null) {
            sInstance = new StudyProgressDBManager(appContext);
            Timber.e("sInstance 创建");
        }
    }

    public static StudyProgressDBManager getInstance() {
        if (null == sInstance) throw new NullPointerException("not init");
        return sInstance;
    }

    private StudyProgressDBManager(Context context) {
        StudyProgressDBHelper dbHelper = new StudyProgressDBHelper(context);//onCreate
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Timber.e("数据库创建成功");
    }

init()函数接收的是一个Application的Context，然后判断sInstance是不是null，如果不为空就调用StudyProgressDBManager的构造函数。常常这个函数会被放在自定义的Application中，这样就可以在项目一被创建就实例化成功。

StudyProgressDBManager的构造函数中，首先实例化StudyProgressDBHelper，然后创建一个数据库。

最后就是一个getInstance()的方法，如果sInstance成员变量不为空，就返回实例化的sInstance（在init()函数中被实例化）。

在后续的编程中如果需要实例Manager，实现以下代码即可。

private StudyProgressDBManager 
studyProgressDBManager = StudyProgressDBManager.getInstance();
studyProgressDBManager.xxx

StudyProgressDAO

StudyProgressDAO是一个接口，相当于数据库中的表，里面的信息有表名和字段名。还有一些需要使用的方法，例如添加，查找等。

编写完成后，可以在helper类中，创建数据库表。

    //创建学习进度数据库
    private static final String CREATE_STUDY_PROGRESS = "create table if not exists " + StudyProgressDAO.TABLE_NAME + " (" +
            StudyProgressDAO.VOA_ID + " Integer NOT NULL primary key," +
            StudyProgressDAO.LESSON + " varchar(20) ," +
            StudyProgressDAO.LISTEN_TIME + " int," +
            StudyProgressDAO.USER_ID + " int," +
            StudyProgressDAO.TOTAL_LISTEN_TIME + " int," +
            StudyProgressDAO.RIGHT_QUE_NUM + " int," +
            StudyProgressDAO.TOTAL_QUE_NUM + " int," +
            StudyProgressDAO.IS_EVALUATION_SENT_NUM + " int," +
            StudyProgressDAO.TOTAL_SENT_NUM + " int" + ")";

在create函数中添加。

   db.execSQL(CREATE_STUDY_PROGRESS);

如果已经更新过一个版本，就需要增加DB_VERSION的值，并且使用update()更新数据库。

StudyProgressDAOImpl

StudyProgressDAOImpl是StudyProgressDAO的实现类，实现StudyProgressDAO并继承方法，其房费就是真正的数据库操作。

其中还有一个构造函数，接收一个数据库文件，并传递给成员变量。

编写完这个类之后，需要让StudyProgressDBManager继承StudyProgressDAO，然后重写方法。并在StudyProgressDBManager构造函数中studyProgressDAOImpl = new StudyProgressDAOImpl(db);实例化DAO的实现了。在重新DAO的方式的时候，直接使用Impl回调，即可。