blocks|key|514095|text|您的代码看起来是正确的。我的建议是数据库已经认为它已经升级了。如果您在递增版本号之后执行项目，但在添加execSQL调用之前，测试设备/仿真器上的数据库可能已经认为它是版本2。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|514096|验证这一点的一种快速方法是将版本号更改为3+--如果在这之后升级，你就知道这只是因为你的设备认为它已经升级了。|514097|entityMap^0|1F|7|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

Your code looks correct. My suggestion is that the database already thinks it's upgraded. If you executed the project after incrementing the version number, but before adding the <code>execSQL</code> call, the database on your test device/emulator may already believe it's at version 2.

A quick way to verify this would be to change the version number to 3 -- if it upgrades after that, you know it was just because your device believed it was already upgraded.

blocks|key|514141|text|您可以使用SQLiteOpenHelper的onUpgrade方法。在onUpgrade方法中，您将获得oldVersion作为参数之一。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|514142|在onUpgrade中使用switch，在每个case中使用版本号来跟踪数据库的当前版本。|514143|最好是从oldVersion循环到newVersion，将version一次递增1，然后逐步升级数据库。当使用数据库版本1的人在很长一段时间后将应用程序升级到使用数据库版本7的版本，并且应用程序因为某些不兼容的更改而开始崩溃时，这是非常有用的。|514144|然后，数据库中的更新将逐步完成，涵盖所有可能的情况，即将为每个新版本所做的更改合并到数据库中，从而防止您的应用程序崩溃。|514145|例如：|514146|public+void+onUpgrade(SQLiteDatabase+db,+int+oldVersion,+int+newVersion)+{
++++switch+(oldVersion)+{
++++case+1:
++++++++String+sql+=+"ALTER+TABLE+"+%2B+TABLE_SECRET+%2B+"+ADD+COLUMN+"+%2B+"name_of_column_to_be_added"+%2B+"+INTEGER";
++++++++db.execSQL(sql);
++++++++break;

++++case+2:
++++++++String+sql+=+"SOME_QUERY";
++++++++db.execSQL(sql);
++++++++break;
++++}

}|code-block|syntax|javascript|514147|entityMap^0|M|9|0|1|9|D|6|N|4|0|4|A|H|A|T|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|1B|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|1C|8|@]|D|@]|E|$]]|$1|N|3|O|5|P|7|1D|8|@]|D|@]|E|$Q|R]]|$1|S|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|T|$]]

You can use SQLiteOpenHelper's <code>onUpgrade</code> method. In the onUpgrade method, you get the oldVersion as one of the parameters.

In the <code>onUpgrade</code> use a <code>switch</code> and in each of the <code>case</code>s use the version number to keep track of the current version of database.

It's best that you loop over from <code>oldVersion</code> to <code>newVersion</code>, incrementing <code>version</code> by 1 at a time and then upgrade the database step by step. This is very helpful when someone with database version 1 upgrades the app after a long time, to a version using database version 7 and the app starts crashing because of certain incompatible changes.

Then the updates in the database will be done step-wise, covering all possible cases, i.e. incorporating the changes in the database done for each new version and thereby preventing your application from crashing.

For example:

<pre><code>public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 switch (oldVersion) {
 case 1:
 String sql = "ALTER TABLE " + TABLE_SECRET + " ADD COLUMN " + "name_of_column_to_be_added" + " INTEGER";
 db.execSQL(sql);
 break;

 case 2:
 String sql = "SOME_QUERY";
 db.execSQL(sql);
 break;
 }

}
</code></pre>

blocks|key|514192|text|@jkschneider的答案是正确的。然而，还有一种更好的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|514193|按照链接https://riggaroo.co.za/android-sqlite-database-use-onupgrade-correctly/中的说明，为每次更新在sql文件中写入所需的更改。|offset|length|514194|from_1_to_2.sql|514195|ALTER+TABLE+books+ADD+COLUMN+book_rating+INTEGER;|code-block|syntax|javascript|514196|from_2_to_3.sql|514197|ALTER+TABLE+books+RENAME+TO+book_information;|514198|from_3_to_4.sql|514199|ALTER+TABLE+book_information+ADD+COLUMN+calculated_pages_times_rating+INTEGER;
UPDATE+book_information+SET+calculated_pages_times_rating+=+(book_pages+*+book_rating)+;|514200|这些.sql文件将根据数据库的版本在onUpgrade()方法中执行。|514201|DatabaseHelper.java|514202|public+class+DatabaseHelper+extends+SQLiteOpenHelper+{

++++private+static+final+int+DATABASE_VERSION+=+4;

++++private+static+final+String+DATABASE_NAME+=+"database.db";
++++private+static+final+String+TAG+=+DatabaseHelper.class.getName();

++++private+static+DatabaseHelper+mInstance+=+null;
++++private+final+Context+context;

++++private+DatabaseHelper(Context+context)+{
++++++++super(context,+DATABASE_NAME,+null,+DATABASE_VERSION);
++++++++this.context+=+context;
++++}

++++public+static+synchronized+DatabaseHelper+getInstance(Context+ctx)+{
++++++++if+(mInstance+==+null)+{
++++++++++++mInstance+=+new+DatabaseHelper(ctx.getApplicationContext());
++++++++}
++++++++return+mInstance;
++++}

++++@Override
++++public+void+onCreate(SQLiteDatabase+db)+{
++++++++db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
++++++++//+The+rest+of+your+create+scripts+go+here.

++++}


++++@Override
++++public+void+onUpgrade(SQLiteDatabase+db,+int+oldVersion,+int+newVersion)+{
++++++++Log.e(TAG,+"Updating+table+from+"+%2B+oldVersion+%2B+"+to+"+%2B+newVersion);
++++++++//+You+will+not+need+to+modify+this+unless+you+need+to+do+some+android+specific+things.
++++++++//+When+upgrading+the+database,+all+you+need+to+do+is+add+a+file+to+the+assets+folder+and+name+it:
++++++++//+from_1_to_2.sql+with+the+version+that+you+are+upgrading+to+as+the+last+version.
++++++++try+{
++++++++++++for+(int+i+=+oldVersion;+i+<+newVersion;+%2B%2Bi)+{
++++++++++++++++String+migrationName+=+String.format("from_%25d_to_%25d.sql",+i,+(i+%2B+1));
++++++++++++++++Log.d(TAG,+"Looking+for+migration+file:+"+%2B+migrationName);
++++++++++++++++readAndExecuteSQLScript(db,+context,+migrationName);
++++++++++++}
++++++++}+catch+(Exception+exception)+{
++++++++++++Log.e(TAG,+"Exception+running+upgrade+script:",+exception);
++++++++}

++++}

++++@Override
++++public+void+onDowngrade(SQLiteDatabase+db,+int+oldVersion,+int+newVersion)+{

++++}

++++private+void+readAndExecuteSQLScript(SQLiteDatabase+db,+Context+ctx,+String+fileName)+{
++++++++if+(TextUtils.isEmpty(fileName))+{
++++++++++++Log.d(TAG,+"SQL+script+file+name+is+empty");
++++++++++++return;
++++++++}

++++++++Log.d(TAG,+"Script+found.+Executing...");
++++++++AssetManager+assetManager+=+ctx.getAssets();
++++++++BufferedReader+reader+=+null;

++++++++try+{
++++++++++++InputStream+is+=+assetManager.open(fileName);
++++++++++++InputStreamReader+isr+=+new+InputStreamReader(is);
++++++++++++reader+=+new+BufferedReader(isr);
++++++++++++executeSQLScript(db,+reader);
++++++++}+catch+(IOException+e)+{
++++++++++++Log.e(TAG,+"IOException:",+e);
++++++++}+finally+{
++++++++++++if+(reader+!=+null)+{
++++++++++++++++try+{
++++++++++++++++++++reader.close();
++++++++++++++++}+catch+(IOException+e)+{
++++++++++++++++++++Log.e(TAG,+"IOException:",+e);
++++++++++++++++}
++++++++++++}
++++++++}

++++}

++++private+void+executeSQLScript(SQLiteDatabase+db,+BufferedReader+reader)+throws+IOException+{
++++++++String+line;
++++++++StringBuilder+statement+=+new+StringBuilder();
++++++++while+((line+=+reader.readLine())+!=+null)+{
++++++++++++statement.append(line);
++++++++++++statement.append("\n");
++++++++++++if+(line.endsWith(";"))+{
++++++++++++++++db.execSQL(statement.toString());
++++++++++++++++statement+=+new+StringBuilder();
++++++++++++}
++++++++}
++++}
}|514203|在同一链接中还提供了一个示例项目：https://github.com/riggaroo/AndroidDatabaseUpgrades|514204|entityMap|0|LINK|mutability|MUTABLE|url|https://riggaroo.co.za/android-sqlite-database-use-onupgrade-correctly/|1|https://github.com/riggaroo/AndroidDatabaseUpgrades^0|0|4|1Z|0|0|0|0|0|0|0|0|0|0|0|H|1F|1|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1D|8|@]|9|@$D|1E|E|1F|1|1G]]|A|$]]|$1|F|3|G|5|6|7|1H|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|1I|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1J|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1K|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1L|8|@]|9|@]|A|$]]|$1|S|3|T|5|J|7|1M|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|1N|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1O|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|J|7|1P|8|@]|9|@]|A|$K|L]]|$1|10|3|11|5|6|7|1Q|8|@]|9|@$D|1R|E|1S|1|1T]]|A|$]]|$1|12|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]|1A|$5|15|16|17|A|$18|1B]]]]

@jkschneider's answer is right. However there is a better approach. 

Write the needed changes in an sql file for each update as described in the link <a href="https://riggaroo.co.za/android-sqlite-database-use-onupgrade-correctly/" rel="nofollow noreferrer">https://riggaroo.co.za/android-sqlite-database-use-onupgrade-correctly/</a>

from_1_to_2.sql

<pre><code>ALTER TABLE books ADD COLUMN book_rating INTEGER;
</code></pre>

from_2_to_3.sql

<pre><code>ALTER TABLE books RENAME TO book_information;
</code></pre>

from_3_to_4.sql

<pre><code>ALTER TABLE book_information ADD COLUMN calculated_pages_times_rating INTEGER;
UPDATE book_information SET calculated_pages_times_rating = (book_pages * book_rating) ;
</code></pre>

These .sql files will be executed in onUpgrade() method according to the version of the database.

DatabaseHelper.java

<pre><code>public class DatabaseHelper extends SQLiteOpenHelper {

 private static final int DATABASE_VERSION = 4;

 private static final String DATABASE_NAME = "database.db";
 private static final String TAG = DatabaseHelper.class.getName();

 private static DatabaseHelper mInstance = null;
 private final Context context;

 private DatabaseHelper(Context context) {
 super(context, DATABASE_NAME, null, DATABASE_VERSION);
 this.context = context;
 }

 public static synchronized DatabaseHelper getInstance(Context ctx) {
 if (mInstance == null) {
 mInstance = new DatabaseHelper(ctx.getApplicationContext());
 }
 return mInstance;
 }

 @Override
 public void onCreate(SQLiteDatabase db) {
 db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
 // The rest of your create scripts go here.

 }


 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);
 // You will not need to modify this unless you need to do some android specific things.
 // When upgrading the database, all you need to do is add a file to the assets folder and name it:
 // from_1_to_2.sql with the version that you are upgrading to as the last version.
 try {
 for (int i = oldVersion; i &lt; newVersion; ++i) {
 String migrationName = String.format("from_%d_to_%d.sql", i, (i + 1));
 Log.d(TAG, "Looking for migration file: " + migrationName);
 readAndExecuteSQLScript(db, context, migrationName);
 }
 } catch (Exception exception) {
 Log.e(TAG, "Exception running upgrade script:", exception);
 }

 }

 @Override
 public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {

 }

 private void readAndExecuteSQLScript(SQLiteDatabase db, Context ctx, String fileName) {
 if (TextUtils.isEmpty(fileName)) {
 Log.d(TAG, "SQL script file name is empty");
 return;
 }

 Log.d(TAG, "Script found. Executing...");
 AssetManager assetManager = ctx.getAssets();
 BufferedReader reader = null;

 try {
 InputStream is = assetManager.open(fileName);
 InputStreamReader isr = new InputStreamReader(is);
 reader = new BufferedReader(isr);
 executeSQLScript(db, reader);
 } catch (IOException e) {
 Log.e(TAG, "IOException:", e);
 } finally {
 if (reader != null) {
 try {
 reader.close();
 } catch (IOException e) {
 Log.e(TAG, "IOException:", e);
 }
 }
 }

 }

 private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) throws IOException {
 String line;
 StringBuilder statement = new StringBuilder();
 while ((line = reader.readLine()) != null) {
 statement.append(line);
 statement.append("\n");
 if (line.endsWith(";")) {
 db.execSQL(statement.toString());
 statement = new StringBuilder();
 }
 }
 }
}
</code></pre>

An example project is provided in the same link also : <a href="https://github.com/riggaroo/AndroidDatabaseUpgrades" rel="nofollow noreferrer">https://github.com/riggaroo/AndroidDatabaseUpgrades</a>

I've already created sqlite tables for my app, but now I want to add a new table to the database.

I changed the DB version as below

<pre><code>private static final int DATABASE_VERSION = 2;
</code></pre>

and Added string to create table

<pre><code>private static final String DATABASE_CREATE_color = 
 "CREATE TABLE IF NOT EXISTS files(color text, incident_id text)";
</code></pre>

<code>onCreate</code> and <code>onUpgrade</code> as below:

<pre><code>@Override
 public void onCreate(SQLiteDatabase database) {
 database.execSQL(DATABASE_CREATE_incident);
 database.execSQL(DATABASE_CREATE_audio);
 database.execSQL(DATABASE_CREATE_video);
 database.execSQL(DATABASE_CREATE_image);

 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 //drop table and add new tables when version 2 released.
 db.execSQL(DATABASE_CREATE_color);

 }
</code></pre>

But for some reason the new table is not being created. What am I doing wrong?

Android: upgrading DB version and adding new table

数据库

我已经为我的应用程序创建了sqlite表，但现在我想向数据库中添加一个新表。我更改了DB版本，如下所示private static final int DATABASE_VERSION = 2;并添加了用于创建表的字符串private static final String DATABASE_CREATE_color ...

问Android:升级DB版本，添加新表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Android:升级DB版本，添加新表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Android:升级DB版本，添加新表
EN