安卓数据库为SQLite轻量级数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。它的设计目标是嵌入式的,占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。
在安卓SDK中,会有SQLiteOpenHelper类提供SQLite的基本操作。
在开发中,我们会重新定义SQLiteOpenHelper,并重写onCreate和onUpgrade方法来处理数据库表的创建和更新操作。
对于数据库的更新,我们还是要在onUpgrade方法中去自己处理。而对于数据库表添加新的字段或者修改字段类型,SQLite并没有提供方法。那么我们就只能在数据库需要升级的时候,创建新表、复制旧数据到新表中来进行升级。
简单介绍一下升级步骤:
1.将现有的所有表重命名,创建临时表;
2.使用新的创建表SQL创建新表;
3.讲临时表的数据写入新表,并删除临时表。
一、表对象继承BaseTable.java
/**
* Created by WangFeng on 2017/4/12 0012 11:38.
*
* @desc 数据表的基类,所有表都需要继承此抽象类
*/
public abstract class BaseTable {
/**
* 获取表名
* @return
*/
public abstract String getTableName();
/**
* 获取创建表语句
* @return
*/
public abstract String getCreateSQL();
/**
* 获取删除表语句
* @return
*/
public abstract String getDropSQL();
}
/**
* Created by WangFeng on 2017/3/29 0029.
* 图片数据表
*/
public class ImageTable extends BaseTable{
static final String TABLE_NAME = "images";
static final String _ID = "id";
static final String IMAGE_URL = "image_url";
static final String IMAGE_NAME = "image_name";
static final String IMAGE_DESC = "image_desc";
static final String CREATE_TIME = "create_time";
static final String UPDATE_TIME = "update_time";
static final String REMOVED = "removed";
static final String SQL_CREATE = new StringBuilder()
.append("CREATE TABLE ").append(TABLE_NAME)
.append("(")
.append(_ID).append(" VARCHAR(36) PRIMARY KEY NOT NULL,")
.append(IMAGE_URL).append(" VARCHAR(100),")
.append(IMAGE_NAME).append(" VARCHAR(16),")
.append(IMAGE_DESC).append(" TEXT,")
.append(CREATE_TIME).append(" CHAR(20),")
.append(UPDATE_TIME).append(" CHAR(20),")
.append(REMOVED).append(" BOOLEAN DEFAULT FALSE")
.append(")")
.toString();
static final String SQL_DROP = new StringBuilder()
.append("DROP TABLE ").append(TABLE_NAME)
.toString();
@Override
public String getTableName() {
return TABLE_NAME;
}
@Override
public String getCreateSQL() {
return SQL_CREATE;
}
@Override
public String getDropSQL() {
return SQL_DROP;
}
}
二、导入DBUpgradeHelper.java
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.support.annotation.NonNull;
import android.text.TextUtils;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* Created by WangFeng on 2017/4/12 0012 11:38.
*
* @desc 数据库表的升级
*/
public class DBUpgradeHelper {
private volatile static DBUpgradeHelper mInstance;
private DBUpgradeHelper() {
}
public static DBUpgradeHelper getInstance() {
if (mInstance == null) {
synchronized (DBUpgradeHelper.class) {
if (mInstance == null) {
mInstance = new DBUpgradeHelper();
}
}
}
return mInstance;
}
/**
* 升级数据库表
*
* @param db 数据库
* @param tables 要升级的表
*/
public void upgrade(SQLiteDatabase db, BaseTable... tables) {
createTempTables(db, tables); // 创建临时表
dropTables(db, tables); // 删除旧表
createNewTables(db, tables); // 创建新表
restoreData(db, tables); // 临时表数据写入新表,删除临时表
}
/**
* 创建临时表,存储旧的表数据
*
* @param db
* @param tables
*/
private void createTempTables(SQLiteDatabase db, BaseTable... tables) {
for (int i = 0; i < tables.length; i++) {
String tableName = tables[i].getTableName();
if (!checkTable(db, tableName))
continue;
String tempTableName = tableName.concat("_TEMP");
String SQL = new StringBuilder()
.append("ALTER TABLE ")
.append(tableName)
.append(" RENAME TO ")
.append(tempTableName)
.toString();
db.execSQL(SQL);
}
}
/**
* 检查表是否存在
*
* @param db
* @param tableName
* @return
*/
private Boolean checkTable(SQLiteDatabase db, String tableName) {
String SQL = new StringBuilder()
.append("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='")
.append(tableName)
.append("'")
.toString();
Cursor c = db.rawQuery(SQL, null);
if (c.moveToNext()) {
int count = c.getInt(0);
if (count > 0) {
return true;
}
return false;
}
return false;
}
/**
* 删除旧表
*
* @param db
* @param tables
*/
private void dropTables(SQLiteDatabase db, @NonNull BaseTable... tables) {
for (int i = 0; i < tables.length; i++) {
if(!checkTable(db, tables[i].getTableName()))
continue;
try {
db.execSQL(tables[i].getDropSQL());
} catch (SQLiteException e) {
e.printStackTrace();
}
}
}
/**
* 创建新的表结构
*
* @param db
* @param tables
*/
private void createNewTables(SQLiteDatabase db, @NonNull BaseTable... tables) {
for (int i = 0; i < tables.length; i++) {
if(checkTable(db, tables[i].getTableName()))
continue;
try {
db.execSQL(tables[i].getCreateSQL());
} catch (SQLiteException e) {
e.printStackTrace();
}
}
}
/**
* 将临时表的数据写入新表
*
* @param db
* @param tables
*/
private void restoreData(SQLiteDatabase db, BaseTable... tables) {
for (int i = 0; i < tables.length; i++) {
String tableName = tables[i].getTableName();
String tempTableName = tableName.concat("_TEMP");
if (!checkTable(db, tempTableName))
continue;
List<String> columns_temp = getColumns(db, tempTableName);
List<String> columns_new = getColumns(db, tempTableName);
ArrayList<String> properties = new ArrayList<>(columns_temp.size());
for (int j = 0; j < columns_new.size(); j++) {
String columnName = columns_new.get(j);
if (columns_temp.contains(columnName)) {
properties.add(columnName);
}
}
// 从临时表复制表数据到相同的字段
if (properties.size() > 0) {
String columnSQL = TextUtils.join(",", properties);
String SQL = new StringBuilder()
.append("INSERT INTO ").append(tableName)
.append(" (").append(columnSQL).append(") SELECT ").append(columnSQL)
.append(" FROM ").append(tempTableName)
.toString();
try {
db.execSQL(SQL);
} catch (SQLiteException e) {
e.printStackTrace();
}
}
// 删除临时表
String SQL_DROP = new StringBuilder()
.append("DROP TABLE ").append(tempTableName)
.toString();
try {
db.execSQL(SQL_DROP);
} catch (SQLiteException e) {
e.printStackTrace();
}
}
}
/**
* 获取表字段列表
*
* @param db
* @param tableName
* @return
*/
private List<String> getColumns(SQLiteDatabase db, String tableName) {
List<String> columns = null;
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 0", null);
if (null != cursor && cursor.getColumnCount() > 0) {
columns = Arrays.asList(cursor.getColumnNames());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
if (null == columns)
columns = new ArrayList<>();
}
return columns;
}
}
三、构建SQLiteOpenHelper,进行创建和升级数据库
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by WangFeng on 2017/3/27 0027.
* <p>
* 本地数据库
*/
public class OfflineDB {
public static final String DB_NAME = "myDB";
public static final int DB_VER = 4; // 数据库版本号,每次更改表结构或添加新表时,需要将版本号++
private SQLiteDatabase mDB;
private MyDBHelper mHelper;
public OfflineDB(Context context) {
mHelper = new MyDBHelper(context);
mDB = mHelper.getReadableDatabase();
mDB = mHelper.getWritableDatabase();
}
public SQLiteDatabase getDB() {
return mDB;
}
class MyDBHelper extends SQLiteOpenHelper {
public MyDBHelper(Context context) {
super(context, DB_NAME, null, DB_VER);
}
@Override
public void onCreate(SQLiteDatabase db) {
// 创建新数据库时调用,进行数据表创建
db.execSQL(ImageTable.SQL_CREATE);
db.execSQL(VideoTable.SQL_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// 数据库版本改变时调用,执行数据库表升级,将需要升级的表传入upgrade方法
DBUpgradeHelper.getInstance().upgrade(db, new ImageTable(), new VideoTable());
}
}
}
简单的数据库升级方法就介绍完了。
本人经验尚浅,若有不妥之处,还望大神多多指教,谢谢!