如何检查我的sqlite表中是否包含数据?

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).

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

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!

对不起，如果这是一个非常愚蠢的问题或方法，我对所有这些都是陌生的。任何答案,感谢!

13 个解决方案

#1

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

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

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

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

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_name simply 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):

要修复它，请保留您的查询(您不希望仅仅因为这是不必要的，可能是低效的)而选择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]);
        }
    }
}

#2

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

rawQuery返回位于第一个条目之前的游标对象(请参阅这里的更多信息)选择COUNT(*)将总是返回一个结果(考虑到该表存在)

So I would do:

所以我要做:

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

}

#3

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是我的columnname。

#4

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。

#5

You can check it manually :

你可以手动检查:

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

   到文件资源管理器中的DDMS。/data/(你的应用程序包)/数据库在这里你会得到你的数据库文件。如果你想在表中看到插入的值。

2. Copy adb, aapt, AdbWinApi.dll and AdbWinUsbApi.dll files from platform_tools to tools folder.

   复制亚行,aapt AdbWinApi。dll和AdbWinUsbApi。从platform_tools到tools文件夹的dll文件。

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

   然后打开命令提示符，将路径设置为“Tools”目录，其中包含android安装程序。它看起来像:-(本地驱动器):\Android\ Android -sdk-windows\工具。

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

   在设置了路径之后，您将在没有引号的情况下编写“adb shell”命令。Android E:\开发\ \ android-sdk-windows \工具>亚行壳

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

   按Enter和写DDMS的路径:- # sqlite3 /data/data/(你的应用程序包)/数据库/数据库名称。

6. Press Enter sqlite>.tables

   按下Enter sqlite > .tables

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

   按Enter键，您将获得该数据库中所有表的名称。

sqlite> Select * from table name

All data exists in that table will show.

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

For any further query feel free to comment.

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

#6

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 _id entries which in my case are auto-increment. If the DB is empty, isAfterLast for sure returns true.

我得到的_id项是自动递增的。如果DB为空，则isAfterLast肯定返回true。

#7

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(*).

选择COUNT(*)稍微快一些。

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

P.S.检查了900万排的桌子。

#8

Another alternative to the one already mentioned would be to use the function queryNumEntries from de class DatabaseUtils.

另一种替代前面提到的方法是使用de类DatabaseUtils中的函数queryNumEntries。

An example may be as follows:

例如:

public boolean checkEmpty(SQLiteDatabase db, String table){
        return DatabaseUtils.queryNumEntries(db, table) == 0;
}

#9

for java programs this worked for me:

对于java程序来说，这对我很有用:

    ResultSet rst = stm.executeQuery("SELECT * from Login");
           int i = 0;
           while(rst.next()){
               ++i;
               System.out.println(i);
           }
           System.out.println(i);
           if(i<1){
               //No data in the table
           }
           else{
               //data exists
           }

#10

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");
}

#11

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;
}

#12

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+"';";

#13

public boolean isEmpty(String TableName){

SQLiteDatabase database = this.getReadableDatabase();
return (int) DatabaseUtils.queryNumEntries(database,TableName) == 0;
}