CODEWITHSUNDEEP
androiddatabasesqlite
kzs
kzs

Reputation: 1111

android sqlite database cursor

I have an android application which has a small database as a db file in the asset folder. The file is called nametoareamap.db.It has a single table named 'map'. The table has two columns(Names and Areas) as following: 

Names       Areas

Aaron        A

Chris        A 

Helen        B 

Tim          B

My application takes names as input from the user. Suppose some user had input: Aaron, Tim. In this case, in terms of name there are two matches with the database. But they come from different areas. Aaron from A and Tim from B. I want to implement the following logic. 

If match > = 2 && the area of the matches are same

{ i take a decision}

else

 {i decide something else }

Can anyone kindly provide me the code required to do this with cursor and sqlite databases on Android. I have a database adapter already.Thanks in advance

Upvotes: 0

Views: 1147

Answers (1)

Jens
Jens

Reputation: 17087

Assuming the following table layout

CREATE TABLE name_area (
    _id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    area TEXT NOT NULL,
    UNIQUE(name, area)
)

And the following values

name      area
----      ----
Aaron     A
Chris     A
Bunny     A
Ron       A
Burgundy  B
Helen     B 
Tim       B

Say you want to know if Aaron, Ron and Burgundy all are in the same area or not:

SELECT COUNT(*), area FROM name_area 
    WHERE name='Aaron' OR name='Ron' OR name='Burgundy' GROUP BY area

This would return two rows.

2   A
1   B

i.e. two of the are in the same area (A), one is in another (B):

Expressed as a Cursor you could check it like this:

Cursor cursor = ...; // Format your query & do the SELECT
try {
    if (cursor.moveToNext()) {
        int count = cursor.getCount();
        if (count < 2) {
            // Everyone is in the same area
            int n = cursor.getInt(0);
            // Now verify 'n' against the number of people you queried for
            // if it doesn't match one or more didn't exist in your table.
        } else {
            // People are in different areas
            int n = 0;
            do {
               n += cursor.getInt(0);
            } while (cursor.moveToNext());
            // Now verify 'n' against the number of people you queried for
            // if it doesn't match one or more didn't exist in your table.
        }
    } else {
        // Oops nothing was found.
    }
} finally {
    cursor.close();
}

Upvotes: 1

Related Questions