Using CursorLoader to query SQLite DB and populate AutoCompleteTextView

Question

I have a SQLite database I would like to query. I want to target Android 2.2 through ICS. I came across this article on how to do this, but it uses deprecated code (does not query asynchronously, but on the UI thread). I've since read that I can use CursorLoader along with LoaderManager to do this task the preferred, best practices way (as to not bog down the UI thread).

The problem is finding a concise example to explain to me how to do this. 1) Load the database, 2) query it, 3) use the result to populate an AutoCompletetextBox list view.

Does such an example exist?

Tiddly

Also trying to find an example like this. The ones I find seem to lack a cursor. – Spoony

I am doing something similar HERE!! #12854836 – Tetanize

Answer 1

I know this is an old question but for people who visit this page:

SimpleCursorAdapter has a new constructor:

SimpleCursorAdapter(Context context, int layout, Cursor c, String[] from, int[] to, int flags)

this cunstructor does not use UI thread. You can use it safey.

Cateran

Answer 2

I created a SQLiteHelper class. Im my case, I have an sqlite database that I copy from assets folder to the /data/data directory if not there:

private DatabaseHelper(Context context, String name, CursorFactory factory,
        int version) {
    super(context, DB_NAME, null, 1);
    this.mContext = context;
}

// getInstance() singleton
public static synchronized DatabaseHelper getInstance(Context context) {
    if (_instance == null) {
        _instance = new DatabaseHelper(context,null,null,1);
    }
    return _instance;
}

@Override
public void onCreate(SQLiteDatabase db) {
    // Leave it blank, we don't want to create.

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Leave it blank, we don't want to upgrade

}

public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();

    if(dbExist){
        openDataBase();
        // check the version number;
        SQLiteCursor cursor = runQuery("select versionNumber from version where VersionType = \"CURRENT\"");
        if (cursor!=null){
            cursor.moveToFirst();
            int version = cursor.getInt(cursor.getColumnIndex("versionNumber"));
            if (version!=SQL_VERSION){
                //TODO - grab the favorites and ingredients first.
                ArrayList<String> favorites = getFavorites();
                // I think I need to close the db before erasing it, then open new one.
                close();
                mContext.deleteDatabase(DB_NAME);
                this.getReadableDatabase();
                copyDataBase();
                openDataBase();
                for (int i = 0; i<favorites.size();i++){
                    insert(Constants.TABLE_FAVORITES,Constants.FAVORITE,favorites.get(i));
                }
                close();
            }
        }
    }else{
        //By calling this method and empty database will be created into the default system path
        //of your application so we are gonna be able to overwrite that database with our database.
        this.getReadableDatabase();

        copyDataBase();
    }
}

private void copyDataBase(){

    //Open your local db as the input stream
    InputStream myInput;
    try {
        myInput = mContext.getAssets().open(DB_NAME);
        // Path to the just created empty db
        String outFileName = LOCATION + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);

        //transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer))>0){
            myOutput.write(buffer, 0, length);
        }

        //Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

public void openDataBase() throws SQLException{
    //Open the database
    String myPath = LOCATION + DB_NAME;
    mDatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

}

@Override
public synchronized void close() {
    if(mDatabase != null)
        mDatabase.close();
    super.close();
}

public SQLiteCursor runQuery(String query){
    return (SQLiteCursor) mDatabase.rawQuery(query,null);
}

private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
        String myPath = LOCATION + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }catch(SQLiteException e){
        //database does't exist yet.
    }

    if(checkDB != null){
        checkDB.close();
    }

    return checkDB != null ? true : false;
}

// all insert does is insert to favorites and into your bar.
public void insert(String table, String column, String value) {
    ContentValues values = new ContentValues();
    values.put(column, value);
    mDatabase.insert(table, null, values);

}

public void delete(String table, String column, String value){
    mDatabase.delete(table,column+" = \""+value+"\"",null);
}

To fill the auto Autocomplete TextView on my activity:

startManagingCursor(mCursor);
    // get instance of database helper class
    mDatabaseHelper = DatabaseHelper.getInstance(this);
    // create database for first time
    try {
        mDatabaseHelper.createDataBase();
    } catch (IOException e) {
        //Log.i(TAG,"Could not create the database");
        e.printStackTrace();
    }
    // open the database
    mDatabaseHelper.openDataBase();
            mDrinks = this.populate();

Populate method:

//populates by drinks
private ArrayList<String> populate() {
    ArrayList<String> items = new ArrayList<String>();
    mCursor = mDatabaseHelper.runQuery(
            "select "+ Constants.TITLE +" from "
            +Constants.TABLE_DRINK+" order by "
            +Constants.TITLE);
    if (mCursor != null){
        mCursor.moveToFirst();
        while (!mCursor.isAfterLast()){
            items.add(mCursor.getString(mCursor.getColumnIndex(Constants.TITLE)));
            mCursor.moveToNext();
        }
    }
    return items;
}

Then I set it:

// when text changes, autocomplete happens
    mSearchTextView = (AutoCompleteTextView) findViewById(R.id.search_drink);
    mSearchTextView.setAdapter(
            new ArrayAdapter<String>(
                    this, R.layout.list_item, mDrinks));
    mSearchTextView.setClickable(true);
    // clear the text when the searchTextView is clicked. Necessary for 
    // clearing after pressing enter in an invalid drink.
    mSearchTextView.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            mSearchTextView.setText("");

        }
    });
    mSearchTextView.setOnItemClickListener(new OnItemClickListener(){

        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position,
                long arg3) {
            // TODO - here we need to get the name, then search for ingredients and show everything in
            // an alert dialog. Here is main logic.
            buildDialog(parent.getItemAtPosition(position).toString());
        }

    });
    mSearchTextView.setOnEditorActionListener(new OnEditorActionListener() {

        @Override
        public boolean onEditorAction(TextView v, int actionId, KeyEvent event) {
            if (event != null&& (event.getKeyCode() == KeyEvent.KEYCODE_ENTER)) {
                InputMethodManager in = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
                in.hideSoftInputFromWindow(mSearchTextView
                        .getApplicationWindowToken(),
                        InputMethodManager.HIDE_NOT_ALWAYS);
                Toast.makeText(v.getContext(), "Please Select a Drink from the Auto Complete or the List Shown", Toast.LENGTH_LONG).show();
            }

            return false;
        }
    });

Hope you understand. I can't give you my full source because this is in a marketplace app I developed. You can check it out before trying to do all the work: https://play.google.com/store/apps/details?id=com.life.plus.studios.bartender.drink.recipes.light

Salo

Thanks. I'll have to check out your implementation. Like your app too! – Tiddly

Np. If you have further questions let me know since I didnt put the full source some things might be confusing. – Salo

I would downnvote, but I don't have enough reputation, because the answer doesn't implement CursorLoader, which is the best practice and what was asked for. – Violate

@Violate I would like to see that as well. A tutorial or example where you load an existing database from the asset folder(with more than 20 tables) and then you perform queries in multiple activities using a LoaderManager, a CursorLoader and a ContentProvider. Even the Notepad example by Google is using the above(deprecated) method... – Coprophilous

@Jaun Acevedo Nice app, I did not find any lags despite you are querying a db with more than 6000 entries. I will go this way too. When Google will update their own tutorials to show us how to do it better I will give it a try. – Coprophilous

Answer 3

I do not have the code on hand, but I asked a similar question before:

Android db loading chat for chat application

If you read it carefully, you can figure out how to use a CursorLoader for your sqlite database ;)

Clathrate