我在Logcat中收到一个错误提示，说我的SQLiteOpenHelper子类中某个列不存在。我以为可以通过更改DATABASE_CREATE字符串来升级数据库，但这似乎行不通，那么我应该如何（逐步）将我的SQLite数据库从版本1升级到版本2？
非常抱歉如果问题看起来有点新手，因为我还在学习Android。
@Pentium10 这是我在onUpgrade中所做的：
private static final int DATABASE_VERSION = 1;
....
switch (upgradeVersion) {
case 1:
db.execSQL("ALTER TABLE task ADD body TEXT");
upgradeVersion = 2;
break;
}
...
在你遇到更大的问题之前，你应该知道SQLite在ALTER TABLE命令上是受限制的，它只允许添加和重命名，而不允许删除/丢弃，这需要重新创建表来完成。
你应该始终准备好新的表创建查询，并使用它来升级和转移任何现有的数据。注意：onUpgrade方法仅运行一次于你的SQLite帮助程序对象，并且您需要处理其中的所有表。
那么在onUpgrade中推荐什么：
if not exists的表创建（我们正在进行升级，所以该表可能尚不存在，它将无法进行修改和删除）List<String> columns = DBUtils.GetColumns(db, TableName);ALTER table " + TableName + " RENAME TO 'temp_" + TableName）columns.retainAll(DBUtils.GetColumns(db, TableName));）String cols = StringUtils.join(columns, ",");
db.execSQL(String.format(
"INSERT INTO %s (%s) SELECT %s from temp_%s",
TableName, cols, cols, TableName));
）DROP table 'temp_" + TableName）.
public static List<String> GetColumns(SQLiteDatabase db, String tableName) {
List<String> ar = null;
Cursor c = null;
try {
c = db.rawQuery("select * from " + tableName + " limit 1", null);
if (c != null) {
ar = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
}
} catch (Exception e) {
Log.v(tableName, e.getMessage(), e);
e.printStackTrace();
} finally {
if (c != null)
c.close();
}
return ar;
}
public static String join(List<String> list, String delim) {
StringBuilder buf = new StringBuilder();
int num = list.size();
for (int i = 0; i < num; i++) {
if (i != 0)
buf.append(delim);
buf.append((String) list.get(i));
}
return buf.toString();
}
private static final String DATABASE_ALTER_TEAM_1 = "ALTER TABLE "
+ TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " string;";
private static final String DATABASE_ALTER_TEAM_2 = "ALTER TABLE "
+ TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " string;";
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion < 2) {
db.execSQL(DATABASE_ALTER_TEAM_1);
}
if (oldVersion < 3) {
db.execSQL(DATABASE_ALTER_TEAM_2);
}
}
如果您想了解更多相关信息，请查看此博客。
gameType 列。在新版本中，它存在了。 void upgradeDatabase() throws IOException {
try {
String column = DatabaseConstants.GAME_TYPE_COLUMN_NAME; // gameType
String table = DatabaseConstants.RECORDS_TABLE;
String query = String.format("SELECT %s FROM %s LIMIT 1", column, table);
database.rawQuery(query, null);
return;
}
catch (Exception e) {
// Column doesn't exist. User had old version of app installed, so upgrade database.
}
// Save all old data
String query = "SELECT * FROM " + DatabaseConstants.USERS_TABLE;
Cursor c = database.rawQuery(query, null);
List<List<Object>> values1 = new ArrayList<List<Object>>();
if (c.moveToFirst()) {
while (!c.isAfterLast()) {
List<Object> record = new ArrayList<Object>();
record.add(c.getInt(0));
record.add(c.getString(1));
values1.add(record);
c.moveToNext();
}
}
c.close();
query = "SELECT * FROM " + DatabaseConstants.RECORDS_TABLE;
c = database.rawQuery(query, null);
List<List<Object>> values2 = new ArrayList<List<Object>>();
if (c.moveToFirst()) {
while (!c.isAfterLast()) {
List<Object> record = new ArrayList<Object>();
record.add(c.getInt(0));
record.add(c.getInt(1));
record.add(c.getInt(2));
record.add(c.getInt(3));
values2.add(record);
c.moveToNext();
}
}
c.close();
// Copy empty database with new schema
copyDatabase();
// Restore all old data
for (List<Object> record : values1) {
ContentValues cv = new ContentValues();
cv.put(DatabaseConstants.ID_COLUMN_NAME, (Integer) record.get(0));
cv.put(DatabaseConstants.USERNAME_COLUMN_NAME, record.get(1).toString());
database.insert(DatabaseConstants.USERS_TABLE, null, cv);
}
for (List<Object> record : values2) {
ContentValues cv = new ContentValues();
cv.put(DatabaseConstants.USER_ID_COLUMN_NAME, (Integer) record.get(0));
cv.put(DatabaseConstants.GAME_TYPE_COLUMN_NAME, GameType.CLASSIC.name());
cv.put(DatabaseConstants.WINS_COLUMN_NAME, (Integer) record.get(1));
cv.put(DatabaseConstants.LOSSES_COLUMN_NAME, (Integer) record.get(2));
cv.put(DatabaseConstants.TIES_COLUMN_NAME, (Integer) record.get(3));
database.insert(DatabaseConstants.RECORDS_TABLE, null, cv);
}
}
这是复制数据库文件的代码。数据库最初是空的，并且我在应用程序外创建了它。（我使用了一个名为Navicat for SQLite的程序。）
public DatabaseHelper(Context context) {
super(context, DatabaseConstants.DATABASE_NAME, null, 1);
this.context = context;
databasePath = context.getDatabasePath(DatabaseConstants.DATABASE_NAME).getPath();
}
void copyDatabase() throws IOException {
InputStream is = context.getAssets().open(DatabaseConstants.DATABASE_NAME); // data.db
OutputStream os = new FileOutputStream(databasePath);
byte[] buffer = new byte[1024];
int length;
while ((length = is.read(buffer)) > 0) {
os.write(buffer, 0, length);
}
// Close the streams.
os.flush();
os.close();
is.close();
}