SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。详细介绍参见https://www.runoob.com/sqlite/sqlite-intro.html
本系列的文章介绍如何在Android app开发中使用其自带的数据库SQLite，包括定义并建立数据库，在本项目中对数据进行增删改操作。
本文前提：
a、了解SQL数据库基础知识。
b、新建Android app项目TimeTable，开发环境：Android studio。
一、新建文件
在代码目录下新建一个文件夹data，用于存放数据库操作相关的代码。data目录中新建三个文件：
TimeTablecontract.java —— 用于定义数据库和表。
TimeTableDbHelper.java—— 继承 SQLiteOpenHelper，用于创建和升级数据库和表。
TimeTableProvider.java—— 继承 ContentProvider，用于提供对数据库和表的Uri形式的访问方式。
二、上述三个新增文件的内容及介绍：
1、TimeTablecontract.java ：
public class TimeTableContract {
// The "Content authority" is a name for the entire content provider, similar to the
// relationship between a domain name and its website. A convenient string to use for the
// content authority is the package name for the app, which is guaranteed to be unique on the
// device.
public static final String CONTENT_AUTHORITY = "com.xuzhi.jinshu.timetable";
// Use CONTENT_AUTHORITY to create the base of all URI's which apps will use to contact
// the content provider.
public static final Uri BASE_CONTENT_URI = Uri.parse("content://" + CONTENT_AUTHORITY);
public static final String PATH_COURSE_INFO = CourseInfo.TABLE_NAME;
public static final String PATH_EXECUTING_SCHEDULE = ExecutingSchedule.TABLE_NAME;public static final class CourseInfo implements BaseColumns {
static final public Uri CONTENT_URI =
BASE_CONTENT_URI.buildUpon().appendPath(PATH_COURSE_INFO).build();
public static final String CONTENT_TYPE =
ContentResolver.CURSOR_DIR_BASE_TYPE + "/" + CONTENT_AUTHORITY + "/" + PATH_COURSE_INFO;
public static final String CONTENT_ITEM_TYPE =
ContentResolver.CURSOR_ITEM_BASE_TYPE + "/" + CONTENT_AUTHORITY + "/" + PATH_COURSE_INFO;
public static final String TABLE_NAME = "CourseInfo";
public static final String COLUMN_COURSE_NAME = "courseName";
public static final String COLUMN_TEACHER_NAME = "teacherName";
public static final String COLUMN_STUDENT_NAME = "studentName";
public static final String COLUMN_NUMBER_OF_CLASSES = "numberOfClasses";
public static final String COLUMN_START_DATE = "startDate";
public static final String COLUMN_COURSE_SCHEDULE = "courseSchedule";
public static final String COLUMN_PRICE = "price";
public static final String COLUMN_NOTE = "note";
public static final String COLUMN_IS_TODAY_START = "isTodayStart";
public static Uri buildCourseInfoUri(long id) {
return ContentUris.withAppendedId(CONTENT_URI, id);
}
public static Uri buildCourseInfoUriById(int id) {
Log.e("BY ID","ID = " + id);
return CONTENT_URI.buildUpon().appendPath(_ID).appendPath(Integer.toString(id)).build();
}
public static String getTheSecondPara(Uri uri) {
return uri.getPathSegments().get(2);
}
}public static final class ExecutingSchedule implements BaseColumns {
private final String LOG_TAG = this.getClass().getSimpleName();
static final public Uri CONTENT_URI =
BASE_CONTENT_URI.buildUpon().appendPath(PATH_EXECUTING_SCHEDULE).build();
public static final String CONTENT_TYPE =
ContentResolver.CURSOR_DIR_BASE_TYPE + "/" + CONTENT_AUTHORITY + "/" + PATH_EXECUTING_SCHEDULE;
public static final String CONTENT_ITEM_TYPE =
ContentResolver.CURSOR_ITEM_BASE_TYPE + "/" + CONTENT_AUTHORITY + "/" + PATH_EXECUTING_SCHEDULE;
public static final String TABLE_NAME = "ExecutingSchedule";
public static final String COLUMN_COURSE_ID = "courseId";/*COLUMN_ID in courseInfo: ExtendKey*/
public static final String COLUMN_SEQUENCE_ID = "sequenceId";
public static final String COLUMN_DATE = "date";
public static final String COLUMN_WEEKDAY = "weekday";
public static final String COLUMN_START_TIME = "start_time";//format hour:minute
public static final String COLUMN_END_TIME = "end_time";//format hour:minute
public static final String COLUMN_LEARN_STATUS = "learn_status";//"unLearned" or "unLearned"or "cancel" or "suspend"
public static final String COLUMN_LEAVE_STATUS = "leave_status";//"unLearned" or "unLearned"or "cancel" or "suspend"
public static final String COLUMN_CHANGE_STATUS = "change_status";//"unLearned" or "unLearned"or "cancel" or "suspend"
public static final String COLUMN_NOTE = "note";//leave_by_student leave_by_teacher change_by_legal_holiday
/*调课时保存调课前的数据*/
public static final String COLUMN_DATE_BEFORE_CHANGE = "date_before_change";
public static final String COLUMN_WEEKDAY_BEFORE_CHANGE = "weekday_before_change";
public static final String COLUMN_START_TIME_BEFORE_CHANGE = "start_time_before_change";//format hour:minute
public static final String COLUMN_END_TIME_BEFORE_CHANGE = "end_time_before_change";//format hour:minute
public static final String PATH_COURSE_ID = COLUMN_COURSE_ID;
public static final String PATH_ID = _ID;
public static final String PATH_SEQUENCE_ID = COLUMN_SEQUENCE_ID;
public static Uri buildExecutingScheduleUri(long id) {
return ContentUris.withAppendedId(CONTENT_URI, id);
}
public static Uri buildExecutingScheduleUriWithCourseId(long courseId) {
return CONTENT_URI.buildUpon().appendPath(PATH_COURSE_ID).appendPath(String.valueOf(courseId)).build();
}
public static Uri buildExecutingScheduleUriWithId(long id) {
return CONTENT_URI.buildUpon().appendPath(PATH_ID).appendPath(String.valueOf(id)).build();
}
public static String getTheSecondPara(Uri uri) {
return uri.getPathSegments().get(2);
}
public static String getTheThirdPara(Uri uri) {
return uri.getPathSegments().get(3);
}
}}
各属性定义说明见下表：
属性 | 说明 |
CONTENT_AUTHORITY | 本数据库的唯一访问标识 |
BASE_CONTENT_URI | 数据库的Uri访问地址 |
PATH_COURSE_INFO | 数据库中表的相对访问路径 |
CourseInfo | 数据库中表的定义 |
CourseInfo.CONTENT_URI | 表的Uri访问地址 |
CourseInfo.TABLE_NAME | 表名称 |
CourseInfo.COLUMN_xxx | 表中各列名称定义 |
2、TimeTableDbHelper.java
public class TimeTableDbHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
static final public String DATABASE_NAME = "TimeTable.db";
private static TimeTableDbHelper instance;
/**
* 单例模式
* @param context 传入上下文
* @return 返回TimeTableDbHelper 对象
*/
public static TimeTableDbHelper getInstance(Context context) {
if (null == instance) {
synchronized (TimeTableDbHelper.class) {
if (null == instance) {
instance = new TimeTableDbHelper(context);
}
}
}
return instance;
}
public TimeTableDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
// Create a table to hold CourseTable's info.
Log.v(LOG_TAG, "TimeTableDbHelper onCreate");
final String SQL_CREATE_COURSE_INFO_TABLE = "CREATE TABLE " + CourseInfo.TABLE_NAME + " (" +
CourseInfo._ID + " INTEGER PRIMARY KEY NOT NULL, " +
CourseInfo.COLUMN_COURSE_NAME + " TEXT NOT NULL, " +
CourseInfo.COLUMN_TEACHER_NAME + " TEXT NOT NULL, " +
CourseInfo.COLUMN_STUDENT_NAME + " TEXT NOT NULL, " +
CourseInfo.COLUMN_START_DATE + " TEXT NOT NULL, " +
CourseInfo.COLUMN_PRICE + " INTEGER NOT NULL, " +
CourseInfo.COLUMN_COURSE_SCHEDULE + " TEXT NOT NULL, " +
CourseInfo.COLUMN_NOTE + " TEXT NOT NULL, " +
CourseInfo.COLUMN_IS_TODAY_START + " TEXT NOT NULL, " +
CourseInfo.COLUMN_NUMBER_OF_CLASSES + " INTEGER NOT NULL);";final String SQL_CREATE_EXECUTING_SCHEDULE_TABLE = "CREATE TABLE " + ExecutingSchedule.TABLE_NAME + " (" +
ExecutingSchedule._ID + " INTEGER PRIMARY KEY NOT NULL, " +
ExecutingSchedule.COLUMN_COURSE_ID + " INTEGER NOT NULL, " +
ExecutingSchedule.COLUMN_SEQUENCE_ID + " INTEGER NOT NULL, " +
ExecutingSchedule.COLUMN_LEARN_STATUS + " TEXT default('unLearned'), " +
ExecutingSchedule.COLUMN_LEAVE_STATUS + " TEXT default('no_leave'), " +
ExecutingSchedule.COLUMN_CHANGE_STATUS + " TEXT default('no_change'), " +
ExecutingSchedule.COLUMN_NOTE + " TEXT default(' '), " +
ExecutingSchedule.COLUMN_DATE + " TEXT NOT NULL, " +
ExecutingSchedule.COLUMN_WEEKDAY + " TEXT NOT NULL, " +
ExecutingSchedule.COLUMN_START_TIME + " TEXT NOT NULL, " +
ExecutingSchedule.COLUMN_END_TIME + " TEXT NOT NULL, " +
ExecutingSchedule.COLUMN_DATE_BEFORE_CHANGE + " TEXT default('null'), " +
ExecutingSchedule.COLUMN_WEEKDAY_BEFORE_CHANGE + " TEXT default('null'), " +
ExecutingSchedule.COLUMN_START_TIME_BEFORE_CHANGE + " TEXT default('null'), " +
ExecutingSchedule.COLUMN_END_TIME_BEFORE_CHANGE + " TEXT default('null'));";sqLiteDatabase.execSQL(SQL_CREATE_COURSE_INFO_TABLE);
sqLiteDatabase.execSQL(SQL_CREATE_EXECUTING_SCHEDULE_TABLE);}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
// This database is only a cache for online data, so its upgrade policy is
// to simply to discard the data and start over
// Note that this only fires if you change the version number for your database.
// It does NOT depend on the version number for your application.
// If you want to update the schema without wiping data, commenting out the next 2 lines
// should be your top priority before modifying this method.
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + CourseInfo.TABLE_NAME);
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + ExecutingSchedule.TABLE_NAME);Log.e(LOG_TAG,"onUpgrade");
onCreate(sqLiteDatabase);
}
}SQLiteOpenHelper 是Android提供的一个类，用于SQLite数据库的创建和版本管理。TimeTableDbHelper 作为SQLiteOpenHelper 的继承类，需要关注以下几项。
内容 | 说明 |
| 创建或获取TimeTableDbHelper类。 |
| 当数据库被创建时调用，在这里实现表的创建和初始化。 |
| 当数据库升级时调用，在这里实现表的新增、删除等操作。 |
| 数据库版本，当数据库需要升级时，需要更新此版本号。 |
| 数据库名称。 |
3、TimeTableProvider.java
public class TimeTableProvider extends ContentProvider {
private final String LOG_TAG = this.getClass().getSimpleName();
// The URI Matcher used by this content provider.
private static final UriMatcher sUriMatcher = buildUriMatcher();
private com.xuzhi.jinshu.timetable.data.TimeTableDbHelper mOpenHelper;
static final int TIME_TABLE_COURSE_INFO = 100;
static final int TIME_TABLE_COURSE_INFO_WITH_ID = 101;
static{
sTimeTableQueryBuilder = new SQLiteQueryBuilder();
}
static UriMatcher buildUriMatcher() {
// I know what you're thinking. Why create a UriMatcher when you can use regular
// expressions instead? Because you're not crazy, that's why.
// All paths added to the UriMatcher have a corresponding code to return when a match is
// found. The code passed into the constructor represents the code to return for the root
// URI. It's common to use NO_MATCH as the code for this case.
final UriMatcher matcher = new UriMatcher(UriMatcher.NO_MATCH);
final String authority = CONTENT_AUTHORITY;
// For each type of URI you want to add, create a corresponding code.
matcher.addURI(authority, PATH_COURSE_INFO, TIME_TABLE_COURSE_INFO);
matcher.addURI(authority, PATH_COURSE_INFO + "/" + TimeTableContract.CourseInfo._ID +"/*", TIME_TABLE_COURSE_INFO_WITH_ID);
return matcher;
}
@Override
public boolean onCreate() {
mOpenHelper = new TimeTableDbHelper(getContext());
return true;
}
@Override
public String getType(Uri uri) {
// Use the Uri Matcher to determine what kind of URI this is.
final int match = sUriMatcher.match(uri);
switch (match) {
case TIME_TABLE_COURSE_INFO:
return TimeTableContract.CourseInfo.CONTENT_TYPE;
case TIME_TABLE_COURSE_INFO_WITH_ID:
return TimeTableContract.CourseInfo.CONTENT_ITEM_TYPE;
default:
throw new UnsupportedOperationException("Unknown uri: " + uri);
}
}
/***************************begin CourseInfo*******************************/
private Cursor getAllCourseInfo(
Uri uri, String[] projection, String sortOrder) {
Log.e(LOG_TAG, TimeTableContract.CourseInfo.TABLE_NAME);
sTimeTableQueryBuilder.setTables(TimeTableContract.CourseInfo.TABLE_NAME);
return sTimeTableQueryBuilder.query(mOpenHelper.getReadableDatabase(),
projection,
null,
null,
null,
null,
sortOrder);
}
/*********************end CourseInfo***********************************/
/***************************begin ExecutingSchedule*******************************/
//ExecutingSchedule.courseId = ?
private static final String sExecutingScheduleByCourseId =
TimeTableContract.ExecutingSchedule.TABLE_NAME +
"." + TimeTableContract.ExecutingSchedule.COLUMN_COURSE_ID + " = ? ";
private Cursor getExecutingScheduleWithCourseId(Uri uri, String[] projection, String sortOrder)
{
String courseId = com.xuzhi.jinshu.timetable.data.TimeTableContract.ExecutingSchedule.getTheSecondPara(uri);
Log.e(LOG_TAG, TimeTableContract.ExecutingSchedule.TABLE_NAME);
sTimeTableQueryBuilder.setTables(TimeTableContract.ExecutingSchedule.TABLE_NAME);
return sTimeTableQueryBuilder.query(mOpenHelper.getReadableDatabase(),
projection,
sExecutingScheduleByCourseId,
new String[]{courseId},
null,
null,
sortOrder);
}
//ExecutingSchedule._id = ? private static final String sExecutingScheduleByIdSelection = TimeTableContract.ExecutingSchedule.TABLE_NAME + "." + TimeTableContract.ExecutingSchedule._ID + " = ? ";
private int DeleteExecutingScheduleById(Uri uri)
{
String id = TimeTableContract.ExecutingSchedule.getTheSecondPara(uri);
final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
return db.delete( TimeTableContract.ExecutingSchedule.TABLE_NAME, sExecutingScheduleByIdSelection, new String[]{id});
}
private int UpdateExecutingScheduleById(Uri uri, ContentValues values) {
String id = TimeTableContract.ExecutingSchedule.getTheSecondPara(uri);
final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
return db.update(TimeTableContract.ExecutingSchedule.TABLE_NAME, values, sExecutingScheduleByIdSelection,
new String[]{id});
}
/*********************end ExecutingSchedule***********************************/
@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,
String sortOrder) {
// Here's the switch statement that, given a URI, will determine what kind of request it is,
// and query the database accordingly.
Cursor retCursor;
Log.e(LOG_TAG, "query uri = " + uri.toString());
switch (sUriMatcher.match(uri)) {
case TIME_TABLE_COURSE_INFO: {
retCursor = getAllCourseInfo(uri, projection, sortOrder);
break;
}
case TIME_TABLE_EXECUTING_SCHEDULE_WITH_COURSE_ID: {
retCursor = getExecutingScheduleWithCourseId(uri, projection, sortOrder);
break;
}
default:
throw new UnsupportedOperationException("Unknown uri: " + uri);
}
retCursor.setNotificationUri(getContext().getContentResolver(), uri);
return retCursor;
}
@Override
public Uri insert(Uri uri, ContentValues values) {
final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
final int match = sUriMatcher.match(uri);
Uri returnUri;
Log.e(LOG_TAG, "insert uri = " + uri.toString());
switch (match) {
case TIME_TABLE_COURSE_INFO: {
long _id = db.insert(TimeTableContract.CourseInfo.TABLE_NAME, null, values);
if ( _id > 0 )
returnUri = TimeTableContract.CourseInfo.buildCourseInfoUri(_id);
else
throw new android.database.SQLException("Failed to insert row into " + uri);
break;
}
case TIME_TABLE_EXECUTING_SCHEDULE: {
long _id = db.insert(TimeTableContract.ExecutingSchedule.TABLE_NAME, null, values);
if ( _id > 0 )
returnUri = TimeTableContract.ExecutingSchedule.buildExecutingScheduleUri(_id);
else
throw new android.database.SQLException("Failed to insert row into " + uri);
break;
}
default:
throw new UnsupportedOperationException("Unknown uri: " + uri);
}
getContext().getContentResolver().notifyChange(uri, null);
return returnUri;
}
@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
final int match = sUriMatcher.match(uri);
int rowsDeleted;
// this makes delete all rows return the number of rows deleted
if ( null == selection ) selection = "1";
switch (match) {
case TIME_TABLE_EXECUTING_SCHEDULE_WITH_ID:
rowsDeleted = DeleteExecutingScheduleById(uri);
break;
default:
throw new UnsupportedOperationException("Unknown uri: " + uri);
}
// Because a null deletes all rows
if (rowsDeleted != 0) {
getContext().getContentResolver().notifyChange(uri, null);
}
return rowsDeleted;
}
@Override
public int update(
Uri uri, ContentValues values, String selection, String[] selectionArgs) {
final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
final int match = sUriMatcher.match(uri);
int rowsUpdated;
switch (match) {
case TIME_TABLE_EXECUTING_SCHEDULE_WITH_ID:
rowsUpdated = UpdateExecutingScheduleById(uri, values);
break;
default:
throw new UnsupportedOperationException("Unknown uri: " + uri);
}
if (rowsUpdated != 0) {
getContext().getContentResolver().notifyChange(uri, null);
}
return rowsUpdated;
}
@Override
public int bulkInsert(Uri uri, ContentValues[] values) {
final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
final int match = sUriMatcher.match(uri);
switch (match) {
case TIME_TABLE_COURSE_INFO:{
db.beginTransaction();
int returnCount = 0;
try {
for (ContentValues value : values) {
long _id = db.insert(TimeTableContract.CourseInfo.TABLE_NAME, null, value);
if (_id != -1) {
returnCount++;
}
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
getContext().getContentResolver().notifyChange(uri, null);
return returnCount;
}
case TIME_TABLE_EXECUTING_SCHEDULE:{
db.beginTransaction();
int returnCount = 0;
try {
for (ContentValues value : values) {
long _id = db.insert(TimeTableContract.ExecutingSchedule.TABLE_NAME, null, value);
if (_id != -1) {
returnCount++;
}
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
getContext().getContentResolver().notifyChange(uri, null);
return returnCount;
}
default:
return super.bulkInsert(uri, values);
}
}
// You do not need to call this method. This is a method specifically to assist the testing
// framework in running smoothly. You can read more at:
// http://developer.android.com/reference/android/content/ContentProvider.html#shutdown()
@Override
@TargetApi(11)
public void shutdown() {
mOpenHelper.close();
super.shutdown();
}
}
ContentProvider是Android的四大组件之一，它的作用是为不同的模块或应用之间数据共享提供统一的接口。ContentProvider通过Uri来标识被访问的数据，通过ContentResolver的增、删、改、查方法实现对共享数据的操作。
TimeTableProvider作为ContentProvider的继承类，它将TimeTable数据库封装起来，提供Uri形式的数据增、删、改操作。本文的数据库访问仅限于本应用内。
TimeTableProvider需了解的内容如下：
内容 | 说明 |
| 将外部应用和模块访问数据时所使用的Uri映射到ContentProvider内部。 |
| 帮助实现SQLite数据库查询操作的类 |
| 获取数据库查询结果的类型。类型有两种：0或多个结果、1个结果。 |
insert(),delete(),update(),query() | 数据库操作：增删改查 |
bulkinsert() | 数据库操作：批量插入 |
shutdown() | 关闭数据库 |
三、配置ContentProvider
为了使用ContentProvider，还需要在App的AndroidManifest.xml中配置它。如下：
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<provider
android:name=".data.TimeTableProvider"
android:authorities="com.aaa.bbb.timetable" />
<activity></activity></application>
————————————————————————
Android SQLite数据库操作的核心文件到此就创建完毕了。
下一篇文我们将介绍在App使用这些文件中的方法来操作数据库。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
