samedi 5 septembre 2015

Searching and retrieving specific data from a previously created SQLite Database in Android?

I have an SQLite Database which I made in SQLite Browser and exported to my Android Application through SQLite Asset Helper Library. This database is for reading-only tasks and can't be upgraded or modified.

Now I want to search in an specific column any data the user input in a TextView, and in case the data matches with some value of the column retrieve all the values corresponding from the other columns.

For example:

         TABLE FRUITS
_id  NAME          URL
 1   apple   R.drawable.apple
 2   orange  R.drawable.orange
 3   kiwi    R.drawable.kiwi

Since my application only allows the user to input the NAME field, the query needs to search in the NAME column only, and retrieve the URL value only if exists in the database, otherwise will return nothing.

Here's my code for importing the database:

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class MyDatabase extends SQLiteAssetHelper {

    private static final String DATABASE_NAME = "fruitManager";
    private static final int DATABASE_VERSION = 1;

    public MyDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public Cursor answerRequest(String request) {

        SQLiteDatabase db = getReadableDatabase();
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

        String sqlTables = "FRUITS";

        qb.setTables(sqlTables);
        String[] sqlSelect = {"0 _id", "NAME", "0 URL"}; 

        Cursor c = qb.query(db, sqlSelect, null, null,
                null, null, null);

        return c;
    }
}

And from here I made the request:

public class Request  {

    private Cursor cursor;
    private MyDatabase db;
    String request;
    String url;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_sqlite_asset_helper);

        public void onClick(View v) {
            request = mEdit.getText().toString().toLowerCase();
            db = new MyDatabase(this);
            cursor = db.answerRequest(request);
            url = cursor.getString(c.getColumnIndex("NAME"));
            System.out.println("Your request was: " + request + 
                               " and has this URL: " + url);
        }
    }

    protected void onDestroy() {
        super.onDestroy();
        cursor.close();
        db.close();
    }

Still, loads of code are needed for do the query but I don't know exactly how to implement them in this case (due to imported database). Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire