开发者

Android: Problem with SQLite query

开发者 https://www.devze.com 2023-02-28 23:15 出处:网络
I have a database containing an Animal table, with columns for _id, name, biography. I am trying to query the database for a particular biography depending on the animal the user has selected(from a

I have a database containing an Animal table, with columns for _id, name, biography.

I am trying to query the database for a particular biography depending on the animal the user has selected(from a list), problem is the cursor will only ever return the first biography in the database, or none at all.

Can anyone see what the problem might be, or offer some helpful suggestions, I'd be most grateful.

 // Code for obtaining position
protected void onListItemClick(ListView l, View v, int position, long id){
        Intent animalIntent = new Intent(this, Animal.class);
        animalIntent.putExtra("pos", position);
        startActivity(animalIntent);    
    }

// query the database for bio, where KEY_ROWID is queried by "position" of item on list. myCursor = dbm.getBio(position);

    if(myCursor.moveToFirst()){

    // querying the cusor, retrieve in开发者_如何学编程 index of column BIOGRAPHY, store as column index.
        int bio_index = myCursor.getColumnIndexOrThrow(MyDBManager.KEY_BIOGRAPHY);

        // return the string from the cursor @ column index
    String bio = myCursor.getString(bio_index);

    // find textview 
        animalBio = (TextView)findViewById(R.id.bio);

        // Set textView to value of string returned from myCursor @ bio_index
    animalBio.setText(bio);
    }

// This method will always return the first value stored in the database biography stored in the database public Cursor getBio(int position){

    return qmDB.query(ANIMAL_TABLE, new String[]{
            KEY_ROWID,
            KEY_BIOGRAPHY,
            },
            KEY_ROWID,
            null,
            null,
            null,
            null);
}

I have tried other variations such as KEY_ROWID = "+" = position, KEY_ROWID = "?", converting the position to string, passing in position as type long, hard coding a number, etc.

So you see I feel the problem is with the query statement, it is simply not returning any other row than the first, or IF it does, it is not displayed!

SOLUTION:

Remember to update your database on the application.


Very much as in a JDBC ResultSet, you need to call the 'moveToNext()' method for your cursor to move. Example:

while(myCursor.moveToNext()){
   bio = myCursor.getString(bio_index);
   // etc...
}

Cursor JavaDoc: http://developer.android.com/reference/android/database/Cursor.html

public abstract boolean moveToNext ()

Since: API Level 1

Move the cursor to the next row. This method will return false if the cursor is already past the last entry in the result set.

Returns: whether the move succeeded


do something like this:

Cursor c=null;
c=;
try {
if (c!=null) {
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {

}

}
} finally {
if (c!=null) {
c.close();
}
}


I think the problem lies in the way you are making your sql query. The following code should solve your problem.

public Cursor getBio(int position) {
    return qmDB.query(ANIMAL_TABLE, new String[]{KEY_ROWID,KEY_BIOGRAPHY}, "KEY_ROWID=?",  new String[]{ position }, null, null, null);
}


This may seem insulting but use sqlitebrowser and make sure that your unique id's really are unique (perhaps you forgot to autoincrement?) Your code present looks fine.


Since you said you had a list, I assume you are using the onListItemClick method. So get the name (position 1 according to your table) from the item the user clicked.

@Override
    protected void onListItemClick(ListView l, View v, int position, long id) {
        super.onListItemClick(l, v, position, id);
        Cursor o = (Cursor) this.getListAdapter().getItem(position);
        dbm.getBio(o.getString(1));
    }

Your query using the position is flawed in my opinion. This is what I think you want to do in your getBio method:

public Cursor getBio(String name) {
    return this.dbm.query(ANIMAL_TABLE, new String[]{KEY_BIOGRAPHY},"name='" + name + "'", null, null, null, null);
}
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号