SQLite database for Android applications

The Android system is embedded with the SQLite database. The advantage is that the processing speed is fast and the memory usage is small.

In Android applications, database operations are mainly performed through sqlitedatabase. This class encapsulates the following methods:

Method	Purpose
Execsql (SQL)	Execute SQL scripts, which are usually used to create or manage database tables.
Rawquery (SQL, selectionargs)	Execute SELECT query (SQL statement)
Query (..)	Execute the SELECT query operation (API method)
Insert (table, nullcolumnhack, values)	Add a record to a specified table
Update (table, values, whereclause, whereargs)	Update a specified record in a specified table
Delete (table, whereclause, whereargs)	Deletes a specified record in a specified table.

You can use these methods to add, delete, modify, and query databases. sqlitedatabase also introduces transaction support.
Begintransaction (): starts a transaction.
Endtransaction (): Submit or roll back a transaction
Compared with Web applications, the essential difference between mobile clients is that system access is single-user without concurrent operations. Therefore, if the database does not have concurrent write operations, you can ignore transaction processing.

Sqlitedatabase is obtained through sqliteopenhelper. This class is an abstract class. This class must be inherited when developing database-based Android applications, construct your own sqliteopenhelper instance and overwrite the Abstract METHODS declared by its superclasses:
Oncreate (sqlitedatabase dB): used to create a database table
Onupgrade (sqlitedatabase dB, int oldversion, int newversion): used to update the database table structure
With sqliteopenhelper, you can use its getwritabledatabase () method to obtain the sqlitedatabase object to complete the database curd operation.

public class WordSQLiteHelper extends SQLiteOpenHelper{public static final String TABLE_WORDS = "words";public static final String COLUMN_ID = "_id";public static final String COLUMN_ENGLISH = "english";public static final String COLUMN_CHINESE = "chinese";public static final String COLUMN_PHRASE = "phrase";public static final String COLUMN_TRANSLATION = "translation";public static final String COLUMN_LEVEL = "level";private static final String DATABASE_NAME = "words.db";private static final int DATABASE_VERSION = 1;public WordSQLiteHelper(Context context){super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {StringBuilder sql=new StringBuilder("create table ").append(TABLE_WORDS).append("(").append(COLUMN_ID).append(" integer primary key autoincrement,").append(COLUMN_ENGLISH).append(" text not null,").append(COLUMN_CHINESE).append(" text not null,").append(COLUMN_PHRASE).append(" text not null,").append(COLUMN_LEVEL).append(" real not null,").append(COLUMN_TRANSLATION).append(" text not null);");db.execSQL(sql.toString());}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {Log.w(WordSQLiteHelper.class.getName(),"Upgrading database from version " + oldVersion + " to "            + newVersion + ", which will destroy all old data");db.execSQL("DROP TABLE IF EXISTS "+TABLE_WORDS);onCreate(db);}}

In Web applications, code layers are usually divided into three layers (entity, Dao, and service) for better clarity)
The creation of Dao makes service compilation easier. Android applications can build their own Dao instances based on sqlitedatabase objects.

public class WordDao implements IBaseDao<Word>{private SQLiteDatabase database;private WordSQLiteHelper dbHelper;private String[] allColumns={WordSQLiteHelper.COLUMN_ID,WordSQLiteHelper.COLUMN_ENGLISH,WordSQLiteHelper.COLUMN_CHINESE,WordSQLiteHelper.COLUMN_PHRASE,WordSQLiteHelper.COLUMN_TRANSLATION,WordSQLiteHelper.COLUMN_LEVEL};public WordDao(Context context){dbHelper=new WordSQLiteHelper(context);}public void open(){database=dbHelper.getWritableDatabase();}public void close(){dbHelper.close();}@Overridepublic void create(Word word){ContentValues values = new ContentValues();values.put(WordSQLiteHelper.COLUMN_ENGLISH,word.getEnglish());values.put(WordSQLiteHelper.COLUMN_CHINESE,word.getChinese());values.put(WordSQLiteHelper.COLUMN_PHRASE,word.getPhrase());values.put(WordSQLiteHelper.COLUMN_TRANSLATION,word.getTranslation());values.put(WordSQLiteHelper.COLUMN_LEVEL,word.getLevel());long insertId=database.insert(WordSQLiteHelper.TABLE_WORDS,null,values);word.setId(insertId);}@Overridepublic void update(Word word){ContentValues values = new ContentValues();values.put(WordSQLiteHelper.COLUMN_ENGLISH,word.getEnglish());values.put(WordSQLiteHelper.COLUMN_CHINESE,word.getChinese());values.put(WordSQLiteHelper.COLUMN_PHRASE,word.getPhrase());values.put(WordSQLiteHelper.COLUMN_TRANSLATION,word.getTranslation());values.put(WordSQLiteHelper.COLUMN_LEVEL,word.getLevel());database.update(WordSQLiteHelper.TABLE_WORDS,values,WordSQLiteHelper.COLUMN_ID+"="+word.getId(),null);}@Overridepublic void delete(Word word){long id=word.getId();database.delete(WordSQLiteHelper.TABLE_WORDS,WordSQLiteHelper.COLUMN_ID+"="+id,null);}@Overridepublic List<Word> getAll(){List<Word> words=new ArrayList<Word>();Cursor cursor = database.query(WordSQLiteHelper.TABLE_WORDS,        allColumns, null, null, null, null, null);//Cursor cursor=database.rawQuery("select * from "+WordSQLiteHelper.TABLE_WORDS,new String[]{});cursor.moveToFirst();while(!cursor.isAfterLast()){words.add(encapsulate(cursor));cursor.moveToNext();}cursor.close();return words;}@Overridepublic List<Word> findOnePage(int offset,int num){List<Word> words=new ArrayList<Word>();Cursor cursor = database.query(WordSQLiteHelper.TABLE_WORDS,        allColumns, null, null, null, null, null);cursor.moveToFirst();cursor.move(offset);int i=0;while(!cursor.isAfterLast()){if(i==num){break;}words.add(encapsulate(cursor));cursor.moveToNext();i++;}cursor.close();return words;}@Overridepublic int getCount(){Cursor cursor = database.query(WordSQLiteHelper.TABLE_WORDS,        allColumns, null, null, null, null, null);return cursor.getCount();}private Word encapsulate(Cursor cursor){Word word=new Word();word.setId(cursor.getLong(0));word.setEnglish(cursor.getString(1));word.setChinese(cursor.getString(2));word.setPhrase(cursor.getString(3));word.setTranslation(cursor.getString(4));word.setLevel(cursor.getFloat(5));return word;}}

Unlike the hibenrate processing method, Dao needs to manually enable and disable the session (by executing the Open and Close Methods), and each DAO instance encapsulates only one session object.

In Web applications, Dao instances are generally managed by spring containers. The introduction of containers ensures that only one Dao instance exists in the system. Because each DAO encapsulates a session, the DAO of a single instance also avoids the waste of session resources.
We can follow the spring management method to compile our Dao container and declare the clear method in the container. When the system app exits, execute this method to destroy the container, when the container is destroyed, close the session corresponding to Dao.

public class DaoContext {private Map<String,IBaseDao<?>> daoMap;private static DaoContext instance=new DaoContext();private DaoContext(){daoMap=new HashMap<String,IBaseDao<?>>();}public static DaoContext getInstance(){return instance;}public void addDao(IBaseDao<?> dao){String daoName=dao.getClass().getSimpleName();removeDao(daoName);daoMap.put(dao.getClass().getSimpleName(),dao);dao.open();}public void removeDao(String daoName){if(daoMap.containsKey(daoName)){daoMap.remove(daoName);IBaseDao<?> dao=getDao(daoName);dao.close();}}public IBaseDao<?> getDao(String daoName){return daoMap.get(daoName);}public void clear(){for(Entry<String, IBaseDao<?>> entry:daoMap.entrySet()){IBaseDao<?> dao=entry.getValue();dao.close();}daoMap.clear();}}