Programing

Android를 사용하여 sqlite에서 행 수를 얻는 방법은 무엇입니까?

lottogame 2021. 1. 9. 09:17
반응형

Android를 사용하여 sqlite에서 행 수를 얻는 방법은 무엇입니까?


작업 관리자를 만들고 있습니다. 작업 목록이 있고 비어있는 경우 특정 작업 목록 이름을 클릭하면 작업 추가 활동으로 이동하지만 2 개 또는 3 개의 작업이있는 경우 해당 작업을 목록 형식으로 표시합니다.

나는 목록에서 카운트를 얻으려고 노력하고 있습니다. 내 데이터베이스 쿼리는 다음과 같습니다.

public Cursor getTaskCount(long tasklist_Id) {

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor= db.rawQuery("SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
             new String[] { String.valueOf(tasklist_Id) });
    if(cursor!=null && cursor.getCount()!=0)
          cursor.moveToNext();
    return cursor;
}    

내 활동에서 :

list_tasklistname.setOnItemClickListener(new OnItemClickListener() {
    @Override
    public void onItemClick(AdapterView<?> arg0,
            android.view.View v, int position, long id) {
                db = new TodoTask_Database(getApplicationContext());
                Cursor c = db.getTaskCount(id);
                System.out.println(c.getCount());
                if(c.getCount()>0) {    
                System.out.println(c);
                Intent taskListID = new Intent(getApplicationContext(), AddTask_List.class);
                task = adapter.getItem(position);
                int taskList_id = task.getTaskListId();
                taskListID.putExtra("TaskList_ID", taskList_id);
                startActivity(taskListID);
            }
            else {
                Intent addTask = new Intent(getApplicationContext(), Add_Task.class);
                startActivity(addTask);
            }
        }
    });
    db.close();
}

하지만 작업 목록 이름을 클릭하면 1, 봇 수의 작업이 반환됩니다.


사용 DatabaseUtils.queryNumEntries():

public long getProfilesCount() {
    SQLiteDatabase db = this.getReadableDatabase();
    long count = DatabaseUtils.queryNumEntries(db, TABLE_NAME);
    db.close();
    return count;
}

또는 ( 더 비효율적으로 )

public int getProfilesCount() {
    String countQuery = "SELECT  * FROM " + TABLE_NAME;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = cursor.getCount();
    cursor.close();
    return count;
}

활동 중 :

int profile_counts = db.getProfilesCount();
    db.close();

android.database.DatabaseUtils사용 하여 개수를 가져옵니다.

public long getTaskCount(long tasklist_Id) {
        return DatabaseUtils.queryNumEntries(readableDatabase, TABLE_NAME);
}

데이터베이스 작업을 수행하기 위해 여러 래퍼 메서드가있는 쉬운 유틸리티입니다.


c.getCount() returns 1 because the cursor contains a single row (the one with the real COUNT(*)). The count you need is the int value of first row in cursor.

public int getTaskCount(long tasklist_Id) {

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor= db.rawQuery(
        "SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
         new String[] { String.valueOf(tasklist_Id) }
    );
    int count = 0;
    if(null != cursor)
        if(cursor.getCount() > 0){
          cursor.moveToFirst();    
          count = cursor.getInt(0);
        }
        cursor.close();
    }

    db.close();
    return count;
}   

I know it is been answered long time ago, but i would like to share this also:

This code works very well:

SQLiteDatabase db = this.getReadableDatabase();
long taskCount = DatabaseUtils.queryNumEntries(db, TABLE_TODOTASK);

BUT what if i dont want to count all rows and i have a condition to apply?

DatabaseUtils have another function for this: DatabaseUtils.longForQuery

long taskCount = DatabaseUtils.longForQuery(db, "SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
         new String[] { String.valueOf(tasklist_Id) });

The longForQuery documentation says:

Utility method to run the query on the db and return the value in the first column of the first row.

public static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs)

It is performance friendly and save you some time and boilerplate code

Hope this will help somebody someday :)


In order to query a table for the number of rows in that table, you want your query to be as efficient as possible. Reference.

Use something like this:

/**
 * Query the Number of Entries in a Sqlite Table
 * */
public long QueryNumEntries()
{
    SQLiteDatabase db = this.getReadableDatabase();
    return DatabaseUtils.queryNumEntries(db, "table_name");
}

Change your getTaskCount Method to this:

public int getTaskCount(long tasklist_id){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor= db.rawQuery("SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?", new String[] { String.valueOf(tasklist_id) });
    cursor.moveToFirst();
    int count= cursor.getInt(0);
    cursor.close();
    return count;
}

Then, update the click handler accordingly:

public void onItemClick(AdapterView<?> arg0, android.view.View v, int position, long id) {
    db = new TodoTask_Database(getApplicationContext());

    // Get task list id
    int tasklistid = adapter.getItem(position).getTaskListId();

    if(db.getTaskCount(tasklistid) > 0) {    
        System.out.println(c);
        Intent taskListID = new Intent(getApplicationContext(), AddTask_List.class);
        taskListID.putExtra("TaskList_ID", tasklistid);
        startActivity(taskListID);
    } else {
        Intent addTask = new Intent(getApplicationContext(), Add_Task.class);
        startActivity(addTask);
    }
}

Do you see what the DatabaseUtils.queryNumEntries() does? It's awful! I use this.

public int getRowNumberByArgs(Object... args) {
    String where = compileWhere(args);
    String raw = String.format("SELECT count(*) FROM %s WHERE %s;", TABLE_NAME, where);
    Cursor c = getWriteableDatabase().rawQuery(raw, null);
    try {
        return (c.moveToFirst()) ? c.getInt(0) : 0;
    } finally {
        c.close();
    }
}

Sooo simple to get row count:

cursor = dbObj.rawQuery("select count(*) from TABLE where COLUMN_NAME = '1' ", null);
cursor.moveToFirst();
String count = cursor.getString(cursor.getColumnIndex(cursor.getColumnName(0)));

Once you get the cursor you can do

Cursor.getCount()

I am a lazy coder, and don't go into all that extra methods, cursor creation, string concatenation, variable searching etc unless it is of benefit.

If all I want is a quick count of rows:

if ((db.rawQuery("SELECT column FROM table", null).getCount()) > 0) {
// Do stuff
}

I do find plain strings easier to read! Don't get me wrong I do code in a more verbose manner when it is required, but if I can do it one line I will!

Regards,

Jacko

EDIT:

Just looked at the date, you probably have sorted this by now :/

ReferenceURL : https://stackoverflow.com/questions/18097748/how-to-get-row-count-in-sqlite-using-android

반응형