sqlite是一种我们日常工作中经常使用的数据存储的方式,适用于一些需要长期储存的,或者是一些大量数据的存储,今天我们来介绍一些基本使用。
1、基本使用
首先需要继承SQLiteOpenHelper类,我们可以看到SQLiteOpenHelper这个类的说明:
/**
* A helper class to manage database creation and version management.
即一个用来负责database的创建和版本控制的类。继承之后需要重写构造函数,构造函数由四个参数构成:
public SQLiteOpenHelper(@Nullable Context context, @Nullable String name,
@Nullable CursorFactory factory, int version) {
this(context, name, factory, version, null);
}
name是数据库名字,factory是CursorFactory类型,查看源码得知CursorFactory是一个接口,代码如下:
/**
* Used to allow returning sub-classes of {@link Cursor} when calling query.
*/
public interface CursorFactory {
/**
* See {@link SQLiteCursor#SQLiteCursor(SQLiteCursorDriver, String, SQLiteQuery)}.
*/
public Cursor newCursor(SQLiteDatabase db,
SQLiteCursorDriver masterQuery, String editTable,
SQLiteQuery query);
}
从注释可以看出这个方法是在我们执行数据库的查询操作的时候返回一个query的cursor对象,我们也可以在SQLiteDirectCursorDriver类的query方法中看到这个接口的使用:
public Cursor query(CursorFactory factory, String[] selectionArgs) {
final SQLiteQuery query = new SQLiteQuery(mDatabase, mSql, mCancellationSignal);
final Cursor cursor;
try {
query.bindAllArgsAsStrings(selectionArgs);
if (factory == null) {
cursor = new SQLiteCursor(this, mEditTable, query);
} else {
cursor = factory.newCursor(mDatabase, this, mEditTable, query);
}
} catch (RuntimeException ex) {
query.close();
throw ex;
}
mQuery = query;
return cursor;
}
这里我们不需要这个参数,所以置为空即可,version是数据库版本,传入1即可,如果需要后续数据库升级,要做好版本控制。然后就是重写onCreate和onUpgrade方法,分别是创建数据库,以及数据库的升级操作,由于我这里是开发系统应用,数据用来做本地存储,所以onUpGrade方法这里没有内容,一般这个是用来应用发布之后如果数据库的字段有修改来相应升级,避免应用crash;onCreate方法则就是数据库的创建:
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_VIDEO = "CREATE TABLE " + TB_VIDEO + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "duration text," + " name text)";
String CREATE_PHOTO = "CREATE TABLE " + TB_PICTURE + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "size text," + " name text)";
String CREATE_MUSIC = "CREATE TABLE " + TB_MUSIC + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "duration text," + "music_name text," + " name text)";
String CREATE_FILE = "CREATE TABLE " + TB_FILE + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "size text," + " name text)";
db.execSQL(CREATE_MUSIC);
db.execSQL(CREATE_VIDEO);
db.execSQL(CREATE_PHOTO);
db.execSQL(CREATE_FILE);
}
核心方法是db.execSQL(),即数据库执行SQL语句,这里的数据库语句CREATE_VIDEO的意思是,创建一张名为TB_VIDEO的表,id作为主键,随着数据插入而增加,并伴随有四个属性:String类型的path,int类型的type,String类型的duration以及String类型的name。完整文件如下:
public class DBHelper extends SQLiteOpenHelper {
private String TAG = DBHelper.class.getSimpleName();
public static final String DB_NAME = "media.db";
public static final String TB_VIDEO = "video";
public static final String TB_PICTURE = "picture";
public static final String TB_MUSIC = "music";
public static final String TB_FILE = "file";
public static final String PATH_KEY = "path";
public static final String TYPE_KEY = "type";
public static final String NAME_KEY = "name";
public static final String DURATION_KEY = "duration";
public static final String SIZE_KEY = "size";
public static final String MUSIC_NAME_KEY = "music_name";
private volatile static DBHelper instance = null;
private DBHelper(Context context) {
super(context, DB_NAME, null, 1);
}
public static DBHelper getInstance(Context context) {
if (instance == null) {
synchronized (DBHelper.class) {
if (instance == null) {
instance = new DBHelper(context);
}
}
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_VIDEO = "CREATE TABLE " + TB_VIDEO + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "duration text," + " name text)";
String CREATE_PHOTO = "CREATE TABLE " + TB_PICTURE + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "size text," + " name text)";
String CREATE_MUSIC = "CREATE TABLE " + TB_MUSIC + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "duration text," + "music_name text," + " name text)";
String CREATE_FILE = "CREATE TABLE " + TB_FILE + "(_id INTEGER PRIMARY key autoincrement,"
+ "path text," + "type Integer," + "size text," + " name text)";
db.execSQL(CREATE_MUSIC);
db.execSQL(CREATE_VIDEO);
db.execSQL(CREATE_PHOTO);
db.execSQL(CREATE_FILE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
DBHelper负责数据库的创建与管理,新建DatabaseUtil类来执行数据的CURD操作,首先获取DBHelper的实例,然后通过DBHelper获取SQLiteDatabase对象,最终通过对SQLiteDatabase对象进行数据的CURD等操作,看到这里我们发现从始至终也没有传入数据库的路径,那么数据库是存储在哪里呢,我们查看一下dbHelper.getWritableDatabase()的代码:
public SQLiteDatabase getWritableDatabase() {
synchronized (this) {
return getDatabaseLocked(true);
}
}
private SQLiteDatabase getDatabaseLocked(boolean writable) {
//省略部分代码
try {
mIsInitializing = true;
if (db != null) {
if (writable && db.isReadOnly()) {
db.reopenReadWrite();
}
} else if (mName == null) {
db = SQLiteDatabase.createInMemory(mOpenParamsBuilder.build());
} else {
final File filePath = mContext.getDatabasePath(mName);
SQLiteDatabase.OpenParams params = mOpenParamsBuilder.build();
try {
db = SQLiteDatabase.openDatabase(filePath, params);
// Keep pre-O-MR1 behavior by resetting file permissions to 660
setFilePermissionsForDb(filePath.getPath());
} catch (SQLException ex) {
if (writable) {
throw ex;
}
Log.e(TAG, "Couldn't open " + mName
+ " for writing (will try read-only):", ex);
params = params.toBuilder().addOpenFlags(SQLiteDatabase.OPEN_READONLY).build();
db = SQLiteDatabase.openDatabase(filePath, params);
}
}
onConfigure(db);
//省略部分代码
onOpen(db);
if (db.isReadOnly()) {
Log.w(TAG, "Opened " + mName + " in read-only mode");
}
mDatabase = db;
return db;
} finally {
mIsInitializing = false;
if (db != null && db != mDatabase) {
db.close();
}
}
}
我们看到在数据库为空的时候,执行了SQLiteDatabase.openDatabase(filePath, params)方法,
final File filePath = mContext.getDatabasePath(mName),执行了Context的获取database路径的方法,而最终也可以在ContextImpl类中找到filepath为当前应用的数据路径。
@Override
public File getDatabasePath(String name) {
File dir;
File f;
if (name.charAt(0) == File.separatorChar) {
String dirPath = name.substring(0, name.lastIndexOf(File.separatorChar));
dir = new File(dirPath);
name = name.substring(name.lastIndexOf(File.separatorChar));
f = new File(dir, name);
if (!dir.isDirectory() && dir.mkdir()) {
FileUtils.setPermissions(dir.getPath(),
FileUtils.S_IRWXU|FileUtils.S_IRWXG|FileUtils.S_IXOTH,
-1, -1);
}
} else {
dir = getDatabasesDir();
f = makeFilename(dir, name);
}
return f;
}
private File getDatabasesDir() {
synchronized (mSync) {
if (mDatabasesDir == null) {
if ("android".equals(getPackageName())) {
mDatabasesDir = new File("/data/system");
} else {
mDatabasesDir = new File(getDataDir(), "databases");
}
}
return ensurePrivateDirExists(mDatabasesDir);
}
}
接下来就是数据的插入、删除、查询等:
插入:
public void insertVideoData(VideoData videoData) {
ContentValues contentValues = new ContentValues();
contentValues.put(DBHelper.NAME_KEY, videoData.getName());
contentValues.put(DBHelper.PATH_KEY, videoData.getPath());
contentValues.put(DBHelper.TYPE_KEY, videoData.getType());
contentValues.put(DBHelper.DURATION_KEY, videoData.getDurationString());
sqLiteDatabase.insert(DBHelper.TB_VIDEO, null, contentValues);
}
查询:
public List<VideoData> getAllVideoData() {
Cursor cursor = sqLiteDatabase.query(DBHelper.TB_VIDEO, null, null,
null, null, null, null);
ArrayList<VideoData> mainDatas = new ArrayList<>();
while (cursor.moveToNext()) {
VideoData videoData = new VideoData();
videoData.setName(cursor.getString(cursor.getColumnIndex(DBHelper.NAME_KEY)));
videoData.setPath(cursor.getString(cursor.getColumnIndex(DBHelper.PATH_KEY)));
videoData.setType(cursor.getInt(cursor.getColumnIndex(DBHelper.TYPE_KEY)));
videoData.setDurationString(cursor.getString(cursor.getColumnIndex(DBHelper.DURATION_KEY)));
mainDatas.add(videoData);
}
cursor.close();
return mainDatas;
}
这里都是使用ContentValues来实现。
2、数据的模糊操作
有时候我们只需要删除特定条件的数据,这时候就需要模糊删除了。
public void deletePathData(String path) {
sqLiteDatabase.delete(DBHelper.TB_VIDEO, DBHelper.PATH_KEY + " like ? ",
new String[]{path + "%"});
sqLiteDatabase.delete(DBHelper.TB_MUSIC, DBHelper.PATH_KEY + " like ? ",
new String[]{path + "%"});
sqLiteDatabase.delete(DBHelper.TB_PICTURE, DBHelper.PATH_KEY + " like ? ",
new String[]{path + "%"});
sqLiteDatabase.delete(DBHelper.TB_FILE, DBHelper.PATH_KEY + " like ? ",
new String[]{path + "%"});
}
在delete语句的参数中传入DBHelper.PATH_KEY + " like ? ",new String[]{path + “%”}即可,这里的意思是,对于PATH_KEY的数据中心包含指定path的数据都删除。
数据库的操作核心其实还是SQL语句,所有的查询删除等语句,都可以通过SQL语句实现,只不过android为了我们使用的便利封装了这些方法,我们日常工作中用这些方法就可以了。