Detailed Explanation of Encapsulation and Use of SQLiteOpenHelper in android

In android, there are three basic types of commonly used data storage, sqlite, SharedPreferences and file storage, among which sqlite is used more for object storage, because it can be added, deleted and checked. This article mainly explains the encapsulation and use of SQLiteOpenHelper, and the code is quoted from https://github.com/iMeiji/Toutiao

Specific use

The main methods include creating a database and upgrading the database.

Constructor: Contains 3 parameters, context, name, factory, version

onCreate: Three main forms are created

getDatabase: In fact, two databases can be obtained here, namely getWritableDatabase and getReadableDatabase. The difference between them is not particularly big, and they all have read and write permissions to the database.

The instance obtained by getWritableDatabase is to open the database by reading and writing. If the open database disk is full, it can only be read but not written at this time. If the instance of getWritableDatabase is called at this time, an error (exception) will occur

The instance obtained by getReadableDatabase is to first call getWritableDatabase to open the database in a read-write way. If the disk of the database is full, it will return to open failure at this time, and then use the instance of getReadableDatabase to open the database in a read-only way

onUpgrade: Mainly used to upgrade the database

public class DatabaseHelper extends SQLiteOpenHelper {

  private static final String DB_NAME = "Toutiao";
  private static final int DB_VERSION = 5;
  private static final String CLEAR_TABLE_DATA = "delete from ";
  private static final String DROP_TABLE = "drop table if exists ";
  private static DatabaseHelper instance = null;
  private static SQLiteDatabase db = null;

  private DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
  }

  private static synchronized DatabaseHelper getInstance() {
    if (instance == null) {
      instance = new DatabaseHelper(InitApp.AppContext, DB_NAME, null, DB_VERSION);
    }
    return instance;
  }

  public static synchronized SQLiteDatabase getDatabase() {
    if (db == null) {
      db = getInstance().getWritableDatabase();
    }
    return db;
  }

  public static synchronized void closeDatabase() {
    if (db != null) {
      db.close();
    }
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(NewsChannelTable.CREATE_TABLE);
    db.execSQL(MediaChannelTable.CREATE_TABLE);
    db.execSQL(SearchHistoryTable.CREATE_TABLE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
      case 1:
        db.execSQL(MediaChannelTable.CREATE_TABLE);
        break;
      case 2:
        db.execSQL(CLEAR_TABLE_DATA + NewsChannelTable.TABLENAME);// Delete data from a table 
        break;
      case 3:
        ContentValues values = new ContentValues();
        values.put(NewsChannelTable.ID, "");
        values.put(NewsChannelTable.NAME, " Recommend ");
        values.put(NewsChannelTable.IS_ENABLE, 0);
        values.put(NewsChannelTable.POSITION, 46);
        db.insert(NewsChannelTable.TABLENAME, null, values);// New table 
        break;
      case 4:
        db.execSQL(SearchHistoryTable.CREATE_TABLE);
        break;
    }
  }
}

Encapsulation of table operations

addInitData Add initialization data

add Insert into Table

query Query for Specific Data

public class NewsChannelDao {

  private SQLiteDatabase db;

  public NewsChannelDao() {
    this.db = DatabaseHelper.getDatabase();
  }

  public void addInitData() {
    String categoryId[] = InitApp.AppContext.getResources().getStringArray(R.array.mobile_news_id);
    String categoryName[] = InitApp.AppContext.getResources().getStringArray(R.array.mobile_news_name);
    for (int i = 0; i < 8; i++) {
      add(categoryId[i], categoryName[i], Constant.NEWS_CHANNEL_ENABLE, i);
    }
    for (int i = 8; i < categoryId.length; i++) {
      add(categoryId[i], categoryName[i], Constant.NEWS_CHANNEL_DISABLE, i);
    }
  }

  public boolean add(String channelId, String channelName, int isEnable, int position) {
    ContentValues values = new ContentValues();
    values.put(NewsChannelTable.ID, channelId);
    values.put(NewsChannelTable.NAME, channelName);
    values.put(NewsChannelTable.IS_ENABLE, isEnable);
    values.put(NewsChannelTable.POSITION, position);
    long result = db.insert(NewsChannelTable.TABLENAME, null, values);
    return result != -1;
  }

  public List<NewsChannelBean> query(int isEnable) {
    Cursor cursor = db.query(NewsChannelTable.TABLENAME, null, NewsChannelTable.IS_ENABLE + "=?",
        new String[]{isEnable + ""}, null, null, null);
    List<NewsChannelBean> list = new ArrayList<>();
    while (cursor.moveToNext()) {
      NewsChannelBean bean = new NewsChannelBean();
      bean.setChannelId(cursor.getString(NewsChannelTable.ID_ID));
      bean.setChannelName(cursor.getString(NewsChannelTable.ID_NAME));
      bean.setIsEnable(cursor.getInt(NewsChannelTable.ID_ISENABLE));
      bean.setPosition(cursor.getInt(NewsChannelTable.ID_POSITION));
      list.add(bean);
    }
    cursor.close();
    return list;
  }

  public List<NewsChannelBean> queryAll() {
    Cursor cursor = db.query(NewsChannelTable.TABLENAME, null, null, null, null, null, null);
    List<NewsChannelBean> list = new ArrayList<>();
    while (cursor.moveToNext()) {
      NewsChannelBean bean = new NewsChannelBean();
      bean.setChannelId(cursor.getString(NewsChannelTable.ID_ID));
      bean.setChannelName(cursor.getString(NewsChannelTable.ID_NAME));
      bean.setIsEnable(cursor.getInt(NewsChannelTable.ID_ISENABLE));
      bean.setPosition(cursor.getInt(NewsChannelTable.ID_POSITION));
      list.add(bean);
    }
    cursor.close();
    return list;
  }

  public void updateAll(List<NewsChannelBean> list) {
  }

  public boolean removeAll() {
    int result = db.delete(NewsChannelTable.TABLENAME, null, null);
    return result != -1;
  }
}