《第一行代码》阅读笔记（二十三）——数据库设计（补充）
废话不多说，先看下结构
然后直接上代码
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回调，即可。
