我已经在一个Android应用程序中设置了一个SQLite数据库,但为了专注于immeify问题,我只会引用Users表.在DatabaseContract中,Users表被定义为:

public final class DatabaseContract {
    private DatabaseContract() {}

    public static class Users implements BaseColumns {
        public static final String TABLE_NAME = "users";
        public static final String COLUMN_USER_ID = "_id";
        public static final String COLUMN_USER_NAME = "user_name";
        public static final String COLUMN_PASSWORD = "password";
        public static final String COLUMN_EMAIL = "email";
        public static final String COLUMN_DISPLAY_NAME = "display_name";
    }
    ...rest of tables in db
}

DatabaseHelper级,这是Users张表:

    private static final String SQL_CREATE_USERS_TABLE =
            "CREATE TABLE " + DatabaseContract.Users.TABLE_NAME + " (" +
                    DatabaseContract.Users.COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    DatabaseContract.Users.COLUMN_USER_NAME + " TEXT NOT NULL, " +
                    DatabaseContract.Users.COLUMN_PASSWORD + " TEXT NOT NULL, " +
                    DatabaseContract.Users.COLUMN_EMAIL + " TEXT, " +
                    DatabaseContract.Users.COLUMN_DISPLAY_NAME + " TEXT)";

在我的AddUserActivity类中,运行这个方法addUserToDatabase(String name, String email, String password, String displayName),它似乎可以工作,但是我希望从Users表中获得自动生成的‘id’,然后在这里检索‘id’:

        // Check if the insertion was successful
        if (newRowId != -1) {
            // User was added successfully
            Toast.makeText(this, "User added successfully!", Toast.LENGTH_SHORT).show();

            // Get the user_id of the newly added user
            int user_id = getUserId(db, name);
      ...

下面是方法getUserId(db, name):

   private int getUserId(SQLiteDatabase db, String name) {
        int user_id = -1; // Default value if no user is found

        // Define the columns you want to retrieve
        String[] projection = {
                DatabaseContract.Users.COLUMN_USER_ID
        };

        // Define the selection based on the user name
        String selection = DatabaseContract.Users.COLUMN_USER_NAME + " = ?";
        String[] selectionArgs = { name };

        // Define the sort order (if needed)
        String sortOrder = null;

        Cursor cursor = db.query(
                DatabaseContract.Users.TABLE_NAME,
                projection,
                selection,
                selectionArgs,
                null,
                null,
                sortOrder
        );

        if (cursor.moveToFirst()) {
            // Get the user_id from the cursor
            int usersIdIndex = cursor.getColumnIndexOrThrow(DatabaseContract.Users.COLUMN_USER_ID);
            user_id = cursor.getInt(usersIdIndex);
        }

        // Close the cursor (don't close the database here)
        cursor.close();

        return user_id;
    }

然后,我创建了一个意图,将此数据传递给WelcomeActivity页:

      ...
            // Create an Intent to navigate to WelcomeActivity
            Intent intent = new Intent(AddUserActivity.this, WelcomeActivity.class);

            int testNumber = 777;
            // Pass user data and user_id as extras in the Intent
            intent.putExtra("USER_ID", user_id);//testNumber
            intent.putExtra("USER_NAME", name);
            intent.putExtra("USER_EMAIL", email);
            intent.putExtra("USER_DISPLAY_NAME", displayName);

            // Start the WelcomeActivity
            startActivity(intent);

            // Close this activity
            finish();

此时,程序崩溃,并显示以下错误:

E/SQLiteLog: (1) no such column: _id in "SELECT _id FROM users WHERE user_name = ?"
D/AndroidRuntime: Shutting down VM
E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.enetapplications.songtracker, PID: 5937
    android.database.sqlite.SQLiteException: no such column: _id (code 1 SQLITE_ERROR[1]): , while compiling: SELECT _id FROM users WHERE user_name = ?
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1478)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:916)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:2088)
        at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1935)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1806)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1974)
        at com.enetapplications.songtracker.AddUserActivity.getUserId(AddUserActivity.java:124)
        at com.enetapplications.songtracker.AddUserActivity.addUserToDatabase(AddUserActivity.java:84)
        at com.enetapplications.songtracker.AddUserActivity.access$400(AddUserActivity.java:20)
        at com.enetapplications.songtracker.AddUserActivity$1.onClick(AddUserActivity.java:58)
        at android.view.View.performClick(View.java:7792)
        at android.widget.TextView.performClick(TextView.java:16112)
        at android.view.View.performClickInternal(View.java:7769)
        at android.view.View.access$3800(View.java:910)
        at android.view.View$PerformClick.run(View.java:30218)
        at android.os.Handler.handleCallback(Handler.java:938)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loopOnce(Looper.java:226)
        at android.os.Looper.loop(Looper.java:313)
        at android.app.ActivityThread.main(ActivityThread.java:8663)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:567)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1135)

我是SQLite的新手,因此习惯了SQLite-但非常熟悉标准的SQL/MySQL.我倾向于将Users表的键命名为user_id,而SQLite标准似乎将表的键保持为_id

提前感谢您的 comments 和 comments !

最好的, 布赖恩

推荐答案

这是一个非常奇怪的问题

不,它不是,它应该是这样的.与经常被称为onCreate的活动等不同,数据库是一个持久化实体,否则它将毫无用处(至少在其预期用途上是如此).无论设备或应用程序的状态如何,都需要保留存储在其中的数据(除非在用户 Select 不再想要数据时卸载).此外,在更新应用程序时,很可能必须保留数据(这可能涉及也可能不涉及数据库模式的更改).

简单地说,onCreate方法反映了只有在创建数据库时才调用它的事实.

如果需要改变,那就是onUpdate开始发挥作用(或很少使用的onDownGrade).它们都是由数据库版本驱动的,就像构造扩展SQLiteOpenHelper的类时传递的那样.

我一直收到错误,无法undo撤消原始的数据库配置.

根据这条 comments .在数据库的整个生命周期内,SQLiteOpenHelper的(and therefore a class that extends SQLiteOpenHelper) onCreate方法只被调用一次.因此,对代码的任何更改(通常是在最初创建表的位置)都不会反映出来,因为代码不会运行.

如果在应用程序发布之前开发它,并且数据库保存的数据可能会丢失,那么所有需要做的就是删除数据库.这可以通过卸载App来实现.由于没有数据库,因此将调用onCreate.

如果应用已经发布,或者需要保留数据,则可以使用数据库版本和onUpdate方法从较低版本迁移数据库.

解决方案是更改数据库名称--只需添加‘2’:私有静态最终字符串DATABASE_NAME="Song_tracker2.db";

这是因为您现在已经指定了另一个数据库作为要使用的数据库,因此已经调用了onCreate方法,并且模式与预期一致.然而,原始数据库仍将存在,并浪费一些存储空间.

Demo Migration

以下是如何使用onUpgrade方法引入更改的有效演示(即使只需卸载应用程序就足够了,因为您很乐意使用另一个数据库).

该演示所做的是展示在使用onUpgrade方法时如何保留数据.它显示了将几乎任何更改应用于表的基本方法.在本例中,将主键的列名从USERS_ID更改为_ID.

因此,对于DatabaseHelper类,请考虑以下内容(显然,您将拥有自己的类):-

class DatabaseHelper extends SQLiteOpenHelper {

    private static final String SQL_CREATE_USERS_TABLE =
            "CREATE TABLE " + DatabaseContract.Users.TABLE_NAME + " (" +
                    DatabaseContract.Users.COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    DatabaseContract.Users.COLUMN_USER_NAME + " TEXT NOT NULL, " +
                    DatabaseContract.Users.COLUMN_PASSWORD + " TEXT NOT NULL, " +
                    DatabaseContract.Users.COLUMN_EMAIL + " TEXT, " +
                    DatabaseContract.Users.COLUMN_DISPLAY_NAME + " TEXT)";

    private static volatile DatabaseHelper instance;

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

    public static DatabaseHelper getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseHelper(context);
        }
        return instance;
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_USERS_TABLE);
        // .... other tables
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion <= 2) {migrateDBFromVersion1ToVersion2(db);}
    }

    private void migrateDBFromVersion1ToVersion2(SQLiteDatabase db) {
        final String ORIGINAL_PREFIX = "original_";
        db.beginTransaction();
        try {
            db.execSQL("DROP TABLE IF EXISTS " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME);
            db.execSQL(
                    "ALTER TABLE " + DatabaseContract.Users.TABLE_NAME +
                            " RENAME TO " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME
            );
            db.execSQL(SQL_CREATE_USERS_TABLE);
            db.execSQL("INSERT INTO " + DatabaseContract.Users.TABLE_NAME +
                    " SELECT * FROM " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME);
            db.setTransactionSuccessful();
        } catch (SQLiteException e) {
            e.printStackTrace();
            //....
        }
        db.endTransaction();
    }

    public void logSchema() {
        Cursor csr = instance.getWritableDatabase().rawQuery("SELECT * FROM sqlite_master;", null);
        Log.i("DBVERSION","Database version is " + instance.getWritableDatabase().getVersion() +
                "; Path to database is " + instance.getWritableDatabase().getPath());
        DatabaseUtils.dumpCursor(csr);
        csr.close();
    }
}

以上的重要因素包括:

  • 如果从低于2的版本升级到版本2,则onUpgrade方法中的编码将调用migrateDBFromVersion1ToVersion2

  • 公元migrateDBFromVersion1ToVersion2年遵循的基本程序是:

    • 删除原始表将重命名为的表(以防万一)
    • 重命名原始表
    • 使用新的/更改的SQL创建替换表(这也应该始终反映在onCreate方法中,以便新安装的应用程序具有相同的模式)
    • 用表的原始/先前版本(即重命名的表)中的数据填充表
    • 删除重命名的原始表(在运行演示时将其注释掉)
    • 以上所有操作都在单个事务中进行,只有在没有SQLite异常的情况下才提交该事务.如果存在SQLite异常,则事务将回滚,因此不会应用任何更改.
  • 与您的getTableMetaData方法类似的logSchema方法.

然后,DatabaseContract类作为驱动程序开始发挥作用,它被更改为包括数据库名称和版本,如下所示:

public final class DatabaseContract {
    private DatabaseContract() {}

    public static final String DATABASE_NAME = "song_tracker.db";
    public static final int DATABASE_VERSION = 1;

    public static class Users implements BaseColumns {
        public static final String TABLE_NAME = "users";
        //public static final String COLUMN_USER_ID = "_id"; /* Version 2 */
        public static final String COLUMN_USER_ID = "users_id"; /* Version 1 */
        public static final String COLUMN_USER_NAME = "user_name";
        public static final String COLUMN_PASSWORD = "password";
        public static final String COLUMN_EMAIL = "email";
        public static final String COLUMN_DISPLAY_NAME = "显示名称";
    }
    // ...rest of tables in db
}

显然,在活动中需要做一些事情,所以:

public class MainActivity extends AppCompatActivity {

    DatabaseHelper dbHelper;


    @SuppressLint("Range")
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = DatabaseHelper.getInstance(this);
        if (DatabaseContract.DATABASE_VERSION == 1) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            ContentValues cv = new ContentValues();
            cv.put(DatabaseContract.Users.COLUMN_DISPLAY_NAME,"Fred");
            cv.put(DatabaseContract.Users.COLUMN_EMAIL,"fred.bloggs.nomail.moc");
            cv.put(DatabaseContract.Users.COLUMN_PASSWORD,"0987654321");
            cv.put(DatabaseContract.Users.COLUMN_USER_NAME,"USER00001");
            db.insert(DatabaseContract.Users.TABLE_NAME,null,cv);
        }
        dbHelper.logSchema();
        Cursor csr = dbHelper.getWritableDatabase().query(
                DatabaseContract.Users.TABLE_NAME,
                null,null,null,null,null,null
        );
        while (csr.moveToNext()) {
            Log.i(
                    "USERDATA",
                    "User Name is " + csr.getString(csr.getColumnIndex(DatabaseContract.Users.COLUMN_USER_NAME))
                    + " Email is " + csr.getString(csr.getColumnIndex(DatabaseContract.Users.COLUMN_EMAIL))
                    // ....
            );
        }
        csr.close();
    }
}

因此,这将是:

  • 获取DatabaseHelper的实例
  • 如果根据代码,数据库版本为1,则插入一行
    • 注意:在此阶段,实际数据库版本可能与编码版本不同.数据库的实际版本存储在数据库文件的头中.SQLiteOpenHelper在打开数据库时确定版本.如果代码和实际版本之间存在差异,则将调用onUpgrade或onDowngrad.
  • LogSchema函数将模式输出到日志(log).
  • 从USERS表中提取数据并将其写入日志(log).

Result/Test 1 - Fresh Install of the App at Version 1

日志(log)包括:-

2023-09-14 07:53:24.666 I/DBVERSION: Database version is 1; Path to database is /data/user/0/a.a.so77093284javasqlitemigration/databases/song_tracker.db
2023-09-14 07:53:24.666 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@98fd0f5
2023-09-14 07:53:24.667 I/System.out: 0 {
2023-09-14 07:53:24.667 I/System.out:    type=table
2023-09-14 07:53:24.667 I/System.out:    name=android_metadata
2023-09-14 07:53:24.667 I/System.out:    tbl_name=android_metadata
2023-09-14 07:53:24.667 I/System.out:    rootpage=3
2023-09-14 07:53:24.667 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2023-09-14 07:53:24.667 I/System.out: }
2023-09-14 07:53:24.667 I/System.out: 1 {
2023-09-14 07:53:24.667 I/System.out:    type=table
2023-09-14 07:53:24.667 I/System.out:    name=users
2023-09-14 07:53:24.668 I/System.out:    tbl_name=users
2023-09-14 07:53:24.668 I/System.out:    rootpage=4
2023-09-14 07:53:24.668 I/System.out:    sql=CREATE TABLE users (users_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, 显示名称 TEXT)
2023-09-14 07:53:24.668 I/System.out: }
2023-09-14 07:53:24.668 I/System.out: 2 {
2023-09-14 07:53:24.668 I/System.out:    type=table
2023-09-14 07:53:24.668 I/System.out:    name=sqlite_sequence
2023-09-14 07:53:24.668 I/System.out:    tbl_name=sqlite_sequence
2023-09-14 07:53:24.668 I/System.out:    rootpage=5
2023-09-14 07:53:24.668 I/System.out:    sql=CREATE TABLE sqlite_sequence(name,seq)
2023-09-14 07:53:24.668 I/System.out: }
2023-09-14 07:53:24.668 I/System.out: <<<<<
2023-09-14 07:53:24.669 I/USERDATA: User Name is USER00001 Email is fred.bloggs.nomail.moc

因此,USERS表具有

  • users_id、用户名、密码、邮箱和显示名称.按照最初的版本.
  • USER00001的1行数据

Result/Test2 - App just rerun with changed code

在此测试中,更改了3行代码:

  • public static final int DATABASE_VERSION = 2;(曾为1)
  • public static final String COLUMN_USER_ID = "_id"; /* Version 2 */(已被注释掉)
  • //public static final String COLUMN_USER_ID = "users_id"; /* Version 1 */(未被注释掉)

日志(log)现包括:-

2023-09-14 08:03:06.896 I/DBVERSION: Database version is 2; Path to database is /data/user/0/a.a.so77093284javasqlitemigration/databases/song_tracker.db
2023-09-14 08:03:06.896 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@98fd0f5
2023-09-14 08:03:06.897 I/System.out: 0 {
2023-09-14 08:03:06.897 I/System.out:    type=table
2023-09-14 08:03:06.897 I/System.out:    name=android_metadata
2023-09-14 08:03:06.897 I/System.out:    tbl_name=android_metadata
2023-09-14 08:03:06.897 I/System.out:    rootpage=3
2023-09-14 08:03:06.897 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2023-09-14 08:03:06.897 I/System.out: }
2023-09-14 08:03:06.897 I/System.out: 1 {
2023-09-14 08:03:06.897 I/System.out:    type=table
2023-09-14 08:03:06.897 I/System.out:    name=original_users
2023-09-14 08:03:06.897 I/System.out:    tbl_name=original_users
2023-09-14 08:03:06.897 I/System.out:    rootpage=4
2023-09-14 08:03:06.898 I/System.out:    sql=CREATE TABLE "original_users" (users_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, 显示名称 TEXT)
2023-09-14 08:03:06.898 I/System.out: }
2023-09-14 08:03:06.898 I/System.out: 2 {
2023-09-14 08:03:06.898 I/System.out:    type=table
2023-09-14 08:03:06.898 I/System.out:    name=sqlite_sequence
2023-09-14 08:03:06.898 I/System.out:    tbl_name=sqlite_sequence
2023-09-14 08:03:06.898 I/System.out:    rootpage=5
2023-09-14 08:03:06.898 I/System.out:    sql=CREATE TABLE sqlite_sequence(name,seq)
2023-09-14 08:03:06.898 I/System.out: }
2023-09-14 08:03:06.898 I/System.out: 3 {
2023-09-14 08:03:06.898 I/System.out:    type=table
2023-09-14 08:03:06.898 I/System.out:    name=users
2023-09-14 08:03:06.899 I/System.out:    tbl_name=users
2023-09-14 08:03:06.899 I/System.out:    rootpage=6
2023-09-14 08:03:06.899 I/System.out:    sql=CREATE TABLE users (_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, 显示名称 TEXT)
2023-09-14 08:03:06.899 I/System.out: }
2023-09-14 08:03:06.899 I/System.out: <<<<<
2023-09-14 08:03:06.900 I/USERDATA: User Name is USER00001 Email is fred.bloggs.nomail.moc

i.e:-

  • 数据库版本(从数据库中提取的实际版本)已增加到2.

  • USERS表的列现在是"-

    • _id美元,根据找零的金额
    • 用户名,
    • 口令,口令,
    • 邮箱,以及
    • 显示名称
  • 当最后DROP TABLE original_users的执行被注释掉时,现在不需要的ORIRENT_USERS表仍然存在,并且它具有users_id列,而不是新的_id列.

Result/Test3 Fresh Install - Version 2 code

应用程序被卸载,然后运行日志(log)包括:-

2023-09-14 08:12:01.230 I/DBVERSION: Database version is 2; Path to database is /data/user/0/a.a.so77093284javasqlitemigration/databases/song_tracker.db
2023-09-14 08:12:01.230 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@98fd0f5
2023-09-14 08:12:01.230 I/System.out: 0 {
2023-09-14 08:12:01.230 I/System.out:    type=table
2023-09-14 08:12:01.230 I/System.out:    name=android_metadata
2023-09-14 08:12:01.230 I/System.out:    tbl_name=android_metadata
2023-09-14 08:12:01.230 I/System.out:    rootpage=3
2023-09-14 08:12:01.231 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2023-09-14 08:12:01.231 I/System.out: }
2023-09-14 08:12:01.231 I/System.out: 1 {
2023-09-14 08:12:01.231 I/System.out:    type=table
2023-09-14 08:12:01.231 I/System.out:    name=users
2023-09-14 08:12:01.231 I/System.out:    tbl_name=users
2023-09-14 08:12:01.231 I/System.out:    rootpage=4
2023-09-14 08:12:01.231 I/System.out:    sql=CREATE TABLE users (_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, 显示名称 TEXT)
2023-09-14 08:12:01.231 I/System.out: }
2023-09-14 08:12:01.231 I/System.out: 2 {
2023-09-14 08:12:01.231 I/System.out:    type=table
2023-09-14 08:12:01.231 I/System.out:    name=sqlite_sequence
2023-09-14 08:12:01.231 I/System.out:    tbl_name=sqlite_sequence
2023-09-14 08:12:01.231 I/System.out:    rootpage=5
2023-09-14 08:12:01.231 I/System.out:    sql=CREATE TABLE sqlite_sequence(name,seq)
2023-09-14 08:12:01.231 I/System.out: }
2023-09-14 08:12:01.231 I/System.out: <<<<<
  • 栏目是_id
  • 没有Hibernate 的Origin_USERS表,并且
  • 没有数据(因为App只添加编码版本为1的数据)

Android相关问答推荐

为什么R8不混淆某些类?

在Android Studio Iguana上运行示例代码时,Gradle Build错误

如何在安卓系统上使用Float16霓虹灯?

Jetpack Compose-如何使用值动画直接控制其他动画

如何更新Kotlin中的显示?

在Android Studio中,如何在BuildSrc Dependenices Kotlin文件中指定时标记与旧版本的依赖关系

在Android 14/SDK 34中使用RegisterReceiver的正确方式是什么?

如何在Jetpack Compose android中使用导航

如何在 compose android中将图像覆盖在另一个图像上

减少Compose中可滚动选项卡之间的间距

修复报错 RecyclerView: No adapter attached;跳过布局

如何在 Jetpack Compose 中将文本绘制在另一个对象的中心?

在 Jetpack Compose 中对齐行项目

AirDroid Business 如何能够从屏幕截图中排除覆盖?

从活动共享视图模型以使用 hilt 组合函数

如何在组件之间导航

我的观点在jetpack compose中相互重叠

如何为具有不同屏幕尺寸但相同密度的手机创建响应式布局?

如何使用 Kotlin Coroutines 正确地拥有待处理的操作队列?

Jetpack 使用 Canvas 组成半圆