Java 如何检查我的 sqlite 表中是否有数据？

How can i check to see if my sqlite table has data in it?

提问by Holly

EDIT, Changed the code slightly based on answers below, but still haven't got it working. I also added a log message to tell me if getCount was returning > 0, and it was, so i supect somthing might be wrong with my query? or my use of the cursor..

编辑，根据下面的答案稍微更改了代码，但仍然无法正常工作。我还添加了一条日志消息来告诉我 getCount 是否返回 > 0，而且确实如此，所以我怀疑我的查询可能有问题？或者我对光标的使用..

I've created a table, and i want to check if its empty or not, if it's empty i want to run some insert statements (that are stored in an array).

我创建了一个表，我想检查它是否为空，如果它为空我想运行一些插入语句（存储在数组中）。

Below is my code, while i have no errors, when i pull the .db file out i can see that it doesn't work. How would you approach this problem?

下面是我的代码，虽然我没有错误，但当我拉出 .db 文件时，我可以看到它不起作用。你会如何处理这个问题？

public void onCreate(SQLiteDatabase db) {
        Log.i("DB onCreate", "Creating the database...");//log message
        db.execSQL(createCATBUDTAB);
        db.execSQL(createTWOWEETAB);
        try{
            Cursor cur = db.rawQuery("SELECT COUNT(*) FROM CAT_BUD_TAB", null);
        if (cur.getCount() > 0){
            Log.i("DB getCount", " getcount greater than 0");//log message
            //do nothing everything's as it should be
        }
        else{//put in these insert statements contained in the array
            Log.i("DB getCount", " getcount less than 0, should read array");//log message
            for(int i=0; i<13; i++){
                db.execSQL(catInsertArray[i]);
            }
        }
        }catch(SQLiteException e){System.err.println("Exception @ rawQuery: " + e.getMessage());}
    }

Sorry if this is a pretty stupid question or approach, i'm new to all this. Any answers much appreciated!

对不起，如果这是一个非常愚蠢的问题或方法，我是这一切的新手。任何答案都非常感谢！

采纳答案by paxdiablo

The query SELECT COUNT(*)on an existing table should neverreturn null. If there are no rows in the table, it should return one row containing the value zero.

查询SELECT COUNT(*)现有表应该永远不会返回null。如果表中没有行，它应该返回包含值零的一行。

Conversely, a row with a non-zero value indicates that it's notempty.

相反，具有非零值的行表示它不为空。

In both cases, one row should be returned, meaning that it will always go through the

在这两种情况下，都应该返回一行，这意味着它将始终通过

//do nothing everything's as it should be

section.

部分。

To fix it, leave your query as-is (you don't want to do select column_namesimply because that would be unnecessary and possibly a little inefficient). Leave it as select count(*), which will always return one row, and use the following code (tested only in my head so be careful):

要修复它，请保留您的查询原样（您不想这样做select column_name只是因为那是不必要的并且可能有点低效）。将其保留为select count(*)，它将始终返回一行，并使用以下代码（仅在我的脑海中测试过，所以要小心）：

Cursor cur = db.rawQuery("SELECT COUNT(*) FROM CAT_BUD_TAB", null);
if (cur != null) {
    cur.moveToFirst();                       // Always one row returned.
    if (cur.getInt (0) == 0) {               // Zero count means empty table.
        for (int i = 0; i < 13; i++) {
            db.execSQL (catInsertArray[i]);
        }
    }
}

回答by Varun

As paxdiablo said, the cursor will not be null. What you can do is try like this:

正如paxdiablo所说，光标不会为空。你可以做的是像这样尝试：

if (cur != null && cur.getCount() > 0){
     // do nothing, everything's as it should be
}

EDIT

编辑

Actually i had used the db.query() and it worked for me. I did this.

实际上我使用了 db.query() 并且它对我有用。我这样做了。

cursor = db.query(TABLE_NAME, new String[] { KEY_TYPE }, null, null, null, null, null);
if (cursor != null && cursor.getCount() > 0)
{
   retVal = true;
}

TABLE_NAME is my table and KEY_TYPE was my columnname

TABLE_NAME 是我的表，KEY_TYPE 是我的列名

回答by ccheneson

The rawQueryreturns a Cursorobject which is positioned before the first entry (See more info here) SELECT COUNT(*)will always return a result (considering the table exists)

在rawQuery返回Cursor其位于第一项之前的对象（见更多信息在这里） SELECT COUNT(*)总是会返回一个结果（考虑到表存在）

So I would do:

所以我会这样做：

if (cur != null){
    cur.moveToFirst();
    if (cur.getInt(0) == 0) {
      // Empty 
    }

}

回答by flowoverstack

you mean if it has record right ? i've tried this and for me it's work

你的意思是如果它有记录吗？我试过这个，对我来说它的工作

    dbCur = db.rawQuery("select * from player", null);
    Log.e("a", ""+dbCur.getCount());

it will show the sum of the table, if the table doesn't have record, then it will show 0.

它将显示表格的总和，如果表格没有记录，则显示0。

回答by Jimmy

My way of checking was something like this:

我的检查方式是这样的：

Cursor cursor = db.query(DbHelper.DB_NAME,DbHelper.DB_C_ID_ONLY,null,null,null,null,null);
if(cursor.isAfterLast())

I get the _identries which in my case are auto-increment. If the DB is empty, isAfterLastfor sure returns true.

我得到_id在我的情况下是自动递增的条目。如果数据库为空，isAfterLast肯定会返回true。

回答by The Holy Coder

You can check it manually :

您可以手动检查：

1. Go to DDMS in File Explorer. /data/data/(your application package)/databasesHere you'll get your database file. And if you want to see inserted values in table.

2. Copy adb, aapt, AdbWinApi.dlland AdbWinUsbApi.dllfiles from platform_tools to tools folder.

3. Then Open Command Prompt and set your path to "Tools" directory where your android setup exist. It look like as :- (Local Drive):\Android\android-sdk-windows\tools

4. After set path you'll write command "adb shell" without quotes. E:\Developers\Android\android-sdk-windows\tools> adb shell

5. Press Enter and write path of DDMS told above:- # sqlite3 /data/data/(Your Application Package)/databases/name of database

6. Press Enter sqlite>.tables

7. Press Enter And you'll get all table's name existing in that database.

1. 在文件资源管理器中转到 DDMS。/data/data/(your application package)/databases在这里你会得到你的数据库文件。如果您想在表中查看插入的值。

2. 复制adb，aapt，AdbWinApi.dll和AdbWinUsbApi.dll文件从platform_tools到工具文件夹。

3. 然后打开命令提示符并将路径设置为您的 android 安装程序所在的“工具”目录。它看起来像：- (Local Drive):\Android\android-sdk-windows\tools

4. 设置路径后，您将编写不带引号的命令“adb shell”。 E:\Developers\Android\android-sdk-windows\tools> adb shell

5. 按 Enter 并写入上面提到的 DDMS 路径：- # sqlite3 /data/data/(Your Application Package)/databases/name of database

6. 按 Enter sqlite>.tables

7. 按 Enter 您将获得该数据库中存在的所有表的名称。

sqlite> Select * from table name

All data exists in that table will show.

将显示该表中存在的所有数据。

For any further query feel free to comment.

对于任何进一步的查询，请随时发表评论。

回答by bellum

What about this query?

这个查询呢？

SELECT CASE WHEN EXISTS (SELECT * FROM CAT_BUD_TAB) THEN 1 ELSE 0 END

It's slightly faster that SELECT COUNT(*).

它稍微快一点SELECT COUNT(*)。

P.S. Checked on tables with 9 million rows.

PS 检查有 900 万行的表。

回答by MCR

Here's what I did...

这就是我所做的...

Cursor cur = db.rawQuery("SELECT count(*) FROM " + SQLHelper.TABLE_CART, null);
if (cur != null && cur.moveToFirst() && cur.getInt(0) > 0) {
    Log.i(getClass().getName(), "table not empty");
} 
else {
    Log.i(getClass().getName(), "table is empty");
}

回答by Diego Fortes

My app was crashing trying the above codes, so I did this and now it's working perfectly!

我的应用程序在尝试上述代码时崩溃了，所以我这样做了，现在它运行良好！

public boolean checkIfEmpty()
{
    Cursor cursor = getDatabase().query(DatabaseHelper.Products.TABLE,
            DatabaseHelper.Products.COLUMNS, null, null, null, null, null);

    if (cursor != null)
    {
        try
        {
            //if it is empty, returns true.
            cursor.moveToFirst();
            if (cursor.getInt(0) == 0)
                return true;
            else
                return false;
        }

        //this error usually occurs when it is empty. So i return true as well. :)
        catch(CursorIndexOutOfBoundsException e)
        {
            return true;
        }

    }
    return false;
}

回答by DeathRs

Use this:

用这个：

public Boolean doesRecordExist(String TableName, String ColumnName, String ColumnData) {
    String q = "Select * FROM "+TableName+" WHERE "+ColumnName+"='"+ColumnData+"';";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(q, null);
    if (cursor.moveToFirst()) {
        return true;
    } else {
        return false;
    }
}

---

---

Example例子

if (doesRecordExist("student", "name", "John") == true)
 {
 //Do Something
 } else { //Do something
 }

---

---

Modify This according to your usage:

根据您的使用情况修改此：

String q = "Select * FROM "+TableName+" WHERE "+ColumnName+"='"+ColumnData+"';";