Android SQLite development tutorial (3): Create a database

We will use TodoList as an example to introduce the basic usage of SQLite. We have several suggestions when designing the SQLite database table:

It is not recommended that some file resources (such as files or sounds) be directly stored in database tables. Currently, the file names corresponding to these resources are stored or the full URL name used by the content provider.
Although it is not mandatory, when designing database keywords, all tables contain a _ id field as the table keyword, which is an Automatically increasing integer field. If SQLite is used as a Content Provider, the unique _ id field is required.
The database structure of Todolist is as follows:

 

As mentioned above, the Android SDK provides SQLiteOpenHelper to help you create, open, and manage databases. A common design pattern is to create an Adpater for a database, provides an abstraction layer for applications to access the database, which can encapsulate direct interaction with the database:

 

In accordance with the above principles, we create two classes for the TodoList Database: ToDoDBAdapter and ToDoDBOpenHelper, where ToDoDBOpenHelper is used as the internal class definition of ToDoDBAdapter (or as a separate class ). Create an SQL statement for creating a database. The initial class definition is as follows:

[Java]
Public class ToDoDBAdapter {

Private static final String DATABASE_NAME = "todoList. db ";
Private static final String DATABASE_TABLE = "todoItems ";
Private static final int DATABASE_VERSION = 1;

Private SQLiteDatabase mDb;
Private final Context mContext;

Public static final String KEY_ID = "_ id ";
Public static final String KEY_TASK = "task ";
Public static final int TASK_COLUMN = 1;

Public static final String KEY_CREATION_DATE = "creation_date ";
Public static final int CREATION_DATE_COLUMN = 2;

Private ToDoDBOpenHelper dbOpenHelper;

Public ToDoDBAdapter (Context context ){
MContext = context;
DbOpenHelper = new ToDoDBOpenHelper (context, DATABASE_NAME, null,
DATABASE_VERSION );
}

Public void close (){
MDb. close ();
}

Public void open () throws SQLiteException {
Try {
MDb = dbOpenHelper. getWritableDatabase ();
} Catch (SQLiteException ex ){
MDb = dbOpenHelper. getReadableDatabase ();
}
}

Private static class ToDoDBOpenHelper extends SQLiteOpenHelper {

Public ToDoDBOpenHelper (Context context, String name,
CursorFactory factory, int version ){
Super (context, name, factory, version );
}

// SQL statement to create a new database
Private static final String DATABASE_CREATE = "create table"
+ DATABASE_TABLE + "(" + KEY_ID
+ "Integer primary key autoincrement," + KEY_TASK
+ "Text not null," + KEY_CREATION_DATE + "long );";

@ Override
Public void onCreate (SQLiteDatabase db ){
Db.exe cSQL (DATABASE_CREATE );

}

@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion,
Int newVersion ){
Log. w ("TaskDBAdapter", "Upgrading from version" + oldVersion
+ "To" + newVersion );

Db.exe cSQL ("drop table if exists" + DATABASE_TABLE );
OnCreate (db );

}

}

}
Public class ToDoDBAdapter {
 
Private static final String DATABASE_NAME = "todoList. db ";
Private static final String DATABASE_TABLE = "todoItems ";
Private static final int DATABASE_VERSION = 1;
 
Private SQLiteDatabase mDb;
Private final Context mContext;
 
Public static final String KEY_ID = "_ id ";
Public static final String KEY_TASK = "task ";
Public static final int TASK_COLUMN = 1;
 
Public static final String KEY_CREATION_DATE = "creation_date ";
Public static final int CREATION_DATE_COLUMN = 2;
 
Private ToDoDBOpenHelper dbOpenHelper;
 
Public ToDoDBAdapter (Context context ){
MContext = context;
DbOpenHelper = new ToDoDBOpenHelper (context, DATABASE_NAME, null,
DATABASE_VERSION );
}
 
Public void close (){
MDb. close ();
}
 
Public void open () throws SQLiteException {
Try {
MDb = dbOpenHelper. getWritableDatabase ();
} Catch (SQLiteException ex ){
MDb = dbOpenHelper. getReadableDatabase ();
}
}
 
Private static class ToDoDBOpenHelper extends SQLiteOpenHelper {
 
Public ToDoDBOpenHelper (Context context, String name,
CursorFactory factory, int version ){
Super (context, name, factory, version );
}
 
// SQL statement to create a new database
Private static final String DATABASE_CREATE = "create table"
+ DATABASE_TABLE + "(" + KEY_ID
+ "Integer primary key autoincrement," + KEY_TASK
+ "Text not null," + KEY_CREATION_DATE + "long );";
 
@ Override
Public void onCreate (SQLiteDatabase db ){
Db.exe cSQL (DATABASE_CREATE );
 
}
 
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion,
Int newVersion ){
Log. w ("TaskDBAdapter", "Upgrading from version" + oldVersion
+ "To" + newVersion );
 
Db.exe cSQL ("drop table if exists" + DATABASE_TABLE );
OnCreate (db );
 
}
 
}
 
}
ToDoDBOpenHelper is a subclass of SQLiteOpenHelper. Generally, You Need To overload onCreate (SQLiteDatabase) and onUpgrade (SQLiteDabase, int, int). You can also overload onOpen (SQLiteDatabase) if necessary ), this class can automatically create a database when the database is not created. If the database already exists, it can be opened.

It provides two methods, getReadableDatabase () and getWriteableDatabase (), to obtain the SQLiteDatabase object. At this time, SQLiteOpenHelper will create onCreate or upgrade the onUpdate database as needed. If the database already exists, open the corresponding database. Although there are two methods, the two methods usually return the same database object. getReadableDatabase () may return the read-only database object unless there is a problem (such as full disk space.

ToDoDBAdapter's open method first tries to obtain a database object that can be read and written. If it fails, it tries to obtain a read-only database.

 

 

Excerpted from the mobile app