如何检查 Android SQLite 数据库中是否存在表？

How does one check if a table exists in an Android SQLite database?

提问by camperdave

I have an android app that needs to check if there's already a record in the database, and if not, process some things and eventually insert it, and simply read the data from the database if the data does exist. I'm using a subclass of SQLiteOpenHelper to create and get a rewritable instance of SQLiteDatabase, which I thought automatically took care of creating the table if it didn't already exist (since the code to do that is in the onCreate(...) method).

我有一个android应用程序需要检查数据库中是否已经有记录，如果没有，处理一些事情并最终插入它，如果数据确实存在，只需从数据库中读取数据。我正在使用 SQLiteOpenHelper 的子类来创建和获取 SQLiteDatabase 的可重写实例，我认为如果表不存在，它会自动处理创建表（因为执行此操作的代码在 onCreate(... ） 方法）。

However, when the table does NOT yet exist, and the first method ran upon the SQLiteDatabase object I have is a call to query(...), my logcat shows an error of "I/Database(26434): sqlite returned: error code = 1, msg = no such table: appdata", and sure enough, the appdata table isn't being created.

但是，当该表尚不存在，并且在我拥有的 SQLiteDatabase 对象上运行的第一个方法是对 query(...) 的调用时，我的 logcat 显示错误“I/Database(26434)：sqlite 返回：错误code = 1, msg = no such table: appdata"，果然没有创建 appdata 表。

Any ideas on why?

关于为什么的任何想法？

I'm looking for either a method to test if the table exists (because if it doesn't, the data's certainly not in it, and I don't need to read it until I write to it, which seems to create the table properly), or a way to make sure that it gets created, and is just empty, in time for that first call to query(...)

我正在寻找一种方法来测试表是否存在（因为如果不存在，则数据肯定不在其中，并且在写入它之前我不需要读取它，这似乎创建了表正确），或者确保它被创建并且是空的，及时第一次调用 query(...)

EDIT
This was posted after the two answers below:
I think I may have found the problem. I for some reason decided that a different SQLiteOpenHelper was supposed to be created for each table, even though both access the same database file. I think refactoring that code to only use one OpenHelper, and creating both tables inside it's onCreate may work better...

编辑
这是在以下两个答案之后发布的：
我想我可能已经发现了问题。我出于某种原因决定应该为每个表创建一个不同的 SQLiteOpenHelper，即使它们都访问相同的数据库文件。我认为重构该代码以仅使用一个 OpenHelper，并在其 onCreate 内创建两个表可能会更好...

采纳答案by camperdave

Yep, turns out the theory in my edit was right: the problem that was causing the onCreate method not to run, was the fact that SQLiteOpenHelperobjects should refer to databases, and not have a separate one for each table. Packing both tables into one SQLiteOpenHelpersolved the problem.

是的，事实证明我编辑中的理论是正确的：导致 onCreate 方法无法运行的问题是SQLiteOpenHelper对象应该引用数据库，而不是每个表都有一个单独的对象。将两张桌子SQLiteOpenHelper合二为一解决了这个问题。

回答by Nikolay DS

Try this one:

试试这个：

public boolean isTableExists(String tableName, boolean openDb) {
    if(openDb) {
        if(mDatabase == null || !mDatabase.isOpen()) {
            mDatabase = getReadableDatabase();
        }

        if(!mDatabase.isReadOnly()) {
            mDatabase.close();
            mDatabase = getReadableDatabase();
        }
    }

    String query = "select DISTINCT tbl_name from sqlite_master where tbl_name = '"+tableName+"'";
    try (Cursor cursor = mDatabase.rawQuery(query, null)) {
        if(cursor!=null) {
            if(cursor.getCount()>0) {
                return true;
            }
        }
        return false;
    }
}

回答by chrisbtoo

I know nothing about the Android SQLite API, but if you're able to talk to it in SQL directly, you can do this:

我对 Android SQLite API 一无所知，但是如果您能够直接在 SQL 中与之对话，则可以执行以下操作：

create table if not exists mytable (col1 type, col2 type);

Which will ensure that the table is always created and not throw any errors if it already existed.

这将确保该表始终被创建并且如果它已经存在则不会抛出任何错误。

回答by robguinness

Although there are already a lot of good answers to this question, I came up with another solution that I think is more simple. Surround your query with a try block and the following catch:

虽然这个问题已经有很多很好的答案了，但我想出了另一个我认为更简单的解决方案。用 try 块和以下捕获包围您的查询：

catch (SQLiteException e){
    if (e.getMessage().contains("no such table")){
            Log.e(TAG, "Creating table " + TABLE_NAME + "because it doesn't exist!" );
            // create table
            // re-run query, etc.
    }
}

It worked for me!

它对我有用！

回答by AndroidDebaser

This is what I did:

这就是我所做的：

/* open database, if doesn't exist, create it */
SQLiteDatabase mDatabase = openOrCreateDatabase("exampleDb.db", SQLiteDatabase.CREATE_IF_NECESSARY,null);

Cursor c = null;
boolean tableExists = false;
/* get cursor on it */
try
{
    c = mDatabase.query("tbl_example", null,
        null, null, null, null, null);
        tableExists = true;
}
catch (Exception e) {
    /* fail */
    Log.d(TAG, tblNameIn+" doesn't exist :(((");
}

return tableExists;

回答by Rich Schuler

You mentioned that you've created an class that extends SQLiteOpenHelperand implemented the onCreatemethod. Are you making sure that you're performing all your database acquire calls with that class? You should only be getting SQLiteDatabaseobjects via the SQLiteOpenHelper#getWritableDatabaseand getReadableDatabaseotherwise the onCreatemethod will not be called when necessary. If you are doing that already check and see if th SQLiteOpenHelper#onUpgrademethod is being called instead. If so, then the database version number was changed at some point in time but the table was never created properly when that happened.

您提到您已经创建了一个扩展SQLiteOpenHelper并实现了该onCreate方法的类。您是否确保使用该类执行所有数据库获取调用？您应该只通过 获取SQLiteDatabase对象SQLiteOpenHelper#getWritableDatabase，getReadableDatabase否则onCreate在必要时不会调用该方法。如果您已经这样做了，请检查并查看是否SQLiteOpenHelper#onUpgrade正在调用th方法。如果是这样，则数据库版本号在某个时间点发生了更改，但在发生这种情况时从未正确创建表。

As an aside, you can force the recreation of the database by making sure all connections to it are closed and calling Context#deleteDatabaseand then using the SQLiteOpenHelperto give you a new db object.

顺便说一句，您可以通过确保关闭所有连接并调用Context#deleteDatabase然后使用SQLiteOpenHelper为您提供新的 db 对象来强制重新创建数据库。

回答by Akshansh singh

 // @param db, readable database from SQLiteOpenHelper

 public boolean doesTableExist(SQLiteDatabase db, String tableName) {
        Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '" + tableName + "'", null);

    if (cursor != null) {
        if (cursor.getCount() > 0) {
            cursor.close();
            return true;
        }
        cursor.close();
    }
    return false;
}

• sqlite maintains sqlite_master table containing information of all tables and indexes in database.
• So here we are simply running SELECT command on it, we'll get cursor having count 1 if table exists.

• sqlite 维护 sqlite_master 表，其中包含数据库中所有表和索引的信息。
• 所以在这里我们只是在其上运行 SELECT 命令，如果表存在，我们将获得计数为 1 的游标。

回答by Yogesh Rathi

 public boolean isTableExists(String tableName) {
    boolean isExist = false;
    Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '" + tableName + "'", null);
    if (cursor != null) {
        if (cursor.getCount() > 0) {
            isExist = true;
        }
        cursor.close();
    }
    return isExist;
}

回答by Pankaj Talaviya

Importantcondition is IF NOT EXISTSto check table is already exist or not in database

重要的条件是IF NOT EXISTS检查表在数据库中是否已经存在

like...

喜欢...

String query = "CREATE TABLE IF NOT EXISTS " + TABLE_PLAYER_PHOTO + "("
            + KEY_PLAYER_ID + " TEXT,"
            + KEY_PLAYER_IMAGE + " TEXT)";
db.execSQL(query);

回答by Aly Abdelaal

i faced that and deal with it by try catch as simple as that i do what i want in table if it not exist will cause error so catch it by exceptions and create it :)

我面对这个问题并通过 try catch 处理它，就像我在表中做我想做的一样简单，如果它不存在会导致错误，所以通过异常捕获它并创建它:)

SQLiteDatabase db=this.getWritableDatabase();
        try{
            db.execSQL("INSERT INTO o_vacations SELECT * FROM vacations");
            db.execSQL("DELETE FROM vacations");
        }catch (SQLiteException e){
            db.execSQL("create table o_vacations (id integer primary key ,name text ,vacation text,date text,MONTH text)");
            db.execSQL("INSERT INTO o_vacations SELECT * FROM vacations");
            db.execSQL("DELETE FROM vacations");
        }