参考:https://www.jianshu.com/p/65923fa3e3dc
1 正常全部使用流程
1.1 定义全局变量
public static SQLiteHelper dbHelper;
public static String folder = "android.xxx.xxx"; // 数据库保存地址
public static String file = "database.db"; // 数据库名称
public static int DB_VERSION = 1; // 数据库的版本号
public static SQLiteDatabase db; // 数据库
1.2 初始化数据库(在MainActivity中或者Services中)
这样得到的数据库保存在公共文件夹,即使卸载APP,数据库也不会丢失。
static void init_db(Context context) {
String pathname = GetDir.getDir(Constant.folder);
File file = new File(pathname, Constant.file);
try {
if(!file.exists()){
file.createNewFile();
Uri uri = Uri.fromFile(file);
Intent intent = new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri);
context.sendBroadcast(intent);
}
} catch (IOException e) {
Log.v("main", "failed1");
e.printStackTrace();
}
try {
Constant.dbHelper = new SQLiteHelper(context, pathname+"/"+Constant.file, null, Constant.DB_VERSION);
Constant.db = Constant.dbHelper.getWritableDatabase(); // 调用SQLiteHelper.OnCreate()
Log.v("main", "new db");
} catch (IllegalArgumentException e) {
Log.v("main", "failed2");
e.printStackTrace();
Constant.dbHelper.onUpgrade(Constant.db, Constant.DB_VERSION - 1, Constant.DB_VERSION);
}
}
其中
GetDir.getDir
如下:
public class GetDir {
public static String getDir(String pathname) {
String sdcardPath = Environment.getExternalStorageDirectory().toString();
File dir = new File(sdcardPath + File.separator + pathname /*+ File.separator + "Files"*/);
if (dir.exists()) {
return dir.toString();
} else {
dir.mkdirs();
return dir.toString();
}
}
}
1.3 在SQLiteHelper中定义数据库初始化的步骤,数据库升级和降级的操作
public class SQLiteHelper extends SQLiteOpenHelper {
static final String TAG = "SQLiteHelper";
public SQLiteHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
/**
* 创建新表
*/
@Override
public void onCreate(SQLiteDatabase db) {
Log.v(TAG,"onCreate");
db.execSQL("CREATE TABLE IF NOT EXISTS mytable (name varchar(15), age varchar(5))");
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.v(TAG,"onDowngrade");
if(oldVersion == 3 && newVersion == 2){ // 从3降到2
Log.i(TAG, "onDowngrade: 从3降到2");
db.execSQL("create table tmp_mytable as select name, age from mytable"); // 根据旧表创建新表,相当于删除score列
db.execSQL("drop table mytable");
db.execSQL("alter table tmp_mytable rename to mytable");
}
}
/**
* 当检测与前一次创建的数据库版本不一样时,先删除表再创建新表
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.v(TAG,"onUpgrade");
switch (newVersion){
case 2: { // 给表添加score字段,并赋初值0
Log.i(TAG, "onUpgrade: newVersion = 2");
// 方式1
db.execSQL("alter table mytable add score varchar(5)");
ContentValues values = new ContentValues();
values.put("score", "0");
db.update("mytable ", values, "name!=?", new String[]{""});
// 方式2
// 先创建符合要求的临时表
db.execSQL("CREATE TABLE IF NOT EXISTS mytable (name varchar(15), age varchar(5), score varchar(5))");
// 将数据从旧表复制到临时表
ContentValues values = new ContentValues();
values.put("score", "0");
db.execSQL("INSERT INTO tmp_mytable (name, age) SELECT name, age from mytable");
db.update("tmp_mytable", values, "name!=?", new String[]{""}); // 只要name不为空,都修改score为0
// 删除旧表
db.execSQL("DROP TABLE IF EXISTS mytable");
// 将临时表重命名为旧表名
db.execSQL("ALTER TABLE tmp_mytable RENAME TO mytable");
break;
}
case 3: { // 删掉两个表中的region字段
Log.i(TAG, "onUpgrade: newVersion = 3");
db.execSQL("create table tmp_mytable as select name, age from mytable"); // 若写 where 1 = 2 则只会复制表结构,不复制内容
db.execSQL("drop table mytable");
db.execSQL("alter table tmp_mytable rename to mytable");
break;
}
default:
break;
}
}
}
2. sqlite版本升级
2.1 方法1 硬升级 不推荐
首先是一种硬升级的方法,删除所有的所有旧表,然后重新创建数据库中的所有表。这种方法不够优雅,删除数据存在无法恢复的风险,不推荐这种方式。
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// 硬升级
db.execSQL("DROP TABLE IF EXISTS numbers");
db.execSQL("DROP TABLE IF EXISTS recordInfo");
onCreate(db);
2.2 推荐方法2种如下
/**
* 当检测与前一次创建的数据库版本不一样时,先删除表再创建新表
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.v(TAG,"onUpgrade");
switch (newVersion){
case 2: { // 给表添加score字段,并赋初值0
Log.i(TAG, "onUpgrade: newVersion = 2");
// 方式1
// 先检查是否已经有score字段,如果没有,才给它升级
ContentValues values = new ContentValues();
values.put("score", "0");
if(!checkColumnExist(db, "mytable", "score")){
db.execSQL("alter table mytable add score varchar(5)");
db.update("mytable", values, "name!=?", new String[]{""});
}
// 方式2
// 先创建符合要求的临时表
db.execSQL("CREATE TABLE IF NOT EXISTS mytable (name varchar(15), age varchar(5), score varchar(5))");
// 将数据从旧表复制到临时表
ContentValues values = new ContentValues();
values.put("score", "0");
db.execSQL("INSERT INTO tmp_mytable (name, age) SELECT name, age from mytable");
db.update("tmp_mytable", values, "name!=?", new String[]{""}); // 只要name不为空,都修改score为0
// 删除旧表
db.execSQL("DROP TABLE IF EXISTS mytable");
// 将临时表重命名为旧表名
db.execSQL("ALTER TABLE tmp_mytable RENAME TO mytable");
break;
}
case 3: { // 删掉两个表中的region字段
Log.i(TAG, "onUpgrade: newVersion = 3");
db.execSQL("create table tmp_mytable as select name, age from mytable"); // 若写 where 1 = 2 则只会复制表结构,不复制内容
db.execSQL("drop table mytable");
db.execSQL("alter table tmp_mytable rename to mytable");
break;
}
default:
break;
}
}
}
检查某表列是否存在
/**
* 检查某表列是否存在
* @param db
* @param tableName 表名
* @param columnName 列名
* @return
*/
private boolean checkColumnExist(SQLiteDatabase db, String tableName, String columnName) {
boolean result = false ;
Cursor cursor = null ;
try{
//查询一行
cursor = db.rawQuery( "SELECT * FROM " + tableName + " LIMIT 0", null );
result = cursor != null && cursor.getColumnIndex(columnName) != -1 ;
}catch (Exception e){
Log.e(TAG,"checkColumnExists1..." + e.getMessage()) ;
}finally{
if(null != cursor && !cursor.isClosed()){
cursor.close() ;
}
}
return result ;
}
2. 降级
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(oldVersion == 3 && newVersion == 2){ // 从3降到2
Log.i(TAG, "onDowngrade: 从3降到2");
db.execSQL("create table tmp_mytable as select name, age from mytable");
db.execSQL("drop table mytable");
db.execSQL("alter table tmp_tb1 rename to mytable");
}
}