How to use SQLiteDatabase.CursorFactory

Question

Does anyone understand how to use SQLiteDatabase.CursorFactory on Android? I am looking to obtain a persistent SQLiteCursor object which uses an SQL SELECT statement where several of it's WHERE clause expressions use parameters which can be changed programmatically before requerying.

For instance:

SELECT LocationID FROM Locations WHERE Latitude < northlimit AND Latitude > southlimit AND Longitude < eastlimit AND Longitude > westlimit;

Limit parameters will change dynamically, once every 200ms

I am trying to resolve a problem whereby if I just use SQLiteDatabase.rawQuery("SELECT ..."); the system creates a new CursorWindow for each new query, ultimately running out of memory. So I am trying to find a way to force the system not to create new CursorWindows.

Europium

Before re-querying you should close the old cursor. That way you shouldn't run out of memory. As far as I know SQLite does not support random row access on its query result sets. You can only iterate an SQLite result from start to end. The Android framework works around that issue by loading the whole result into a cursor in order to allow random access. If your query returns a lot of rows (or a lot of data) you also may run into an out of memory situation. – Hamann

Answer 1

You can check this tutorial.. its sum it up perfectly:

http://www.youtube.com/watch?v=j-IV87qQ00M

and you should close the DB , after any usage.. another way is to create Class variable Cursor cr and every time you use it check this: if(cr==null) cr = new .... else //do something //

cr.close();

Jock

Answer 2

I don't believe that using a CursorFactory will help or is needed.

In the following example a table based upon yours is created and is loaded with 100000 rows.

A single Cursor is used to extract a variable number of rows every 50ms 10000 times.

When the Cursor is returned the count is obtained (causing a scan of the Cursor and thus accessing the database) and a TextView is updated displaying the iteration and the number of rows obtained.

The App runs without crashing and the profiler shows memory being managed in waves as per :-

The code used being :-

The Database Helper :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    public static final String TBL_LOCATIONS = "Locations";
    public static final String COl_LOCATIONS_ID = "LocationId";
    public static final String COl_LATITUDE = "Lattitude";
    public static final String COl_LONGITUDE = "Longitude";
    public static final String COL_OTHER = "other";


    public DBHelper(@Nullable Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TBL_LOCATIONS +
                "(" +
                COl_LOCATIONS_ID + " INTEGER PRIMARY KEY," +
                COl_LATITUDE + " REAL," +
                COl_LONGITUDE + " REAL," +
                COL_OTHER + " TEXT," +
                " UNIQUE(" + COl_LONGITUDE + "," +  COl_LATITUDE + ")" +
                ")"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertLocation(double longitude, double latitude, String other) {
        ContentValues cv = new ContentValues();
        cv.put(COl_LONGITUDE,longitude);
        cv.put(COl_LATITUDE,latitude);
        cv.put(COL_OTHER,other);
        return this.getWritableDatabase().insert(TBL_LOCATIONS,null,cv);
    }

    public Cursor getLocations(double northlimit, double southlimt, double eastlimit, double westlimit) {
        return this.getWritableDatabase().query(
                TBL_LOCATIONS,
                new String[]{COl_LOCATIONS_ID},
                COl_LATITUDE + " <? AND " + COl_LATITUDE + ">? AND " + COl_LONGITUDE + "<? AND " + COl_LONGITUDE + "<?",
                new String[]{
                        String.valueOf(northlimit),
                        String.valueOf(southlimt),
                        String.valueOf(eastlimit),
                        String.valueOf(westlimit)},
                null,null,null );
    }
}

The activity being :-

public class MainActivity extends AppCompatActivity {

    private int mInterval = 50;
    private int mCounter = 0;
    private final int mLimit = 10000;
    private Handler mHandler;
    private DBHelper mDBHlpr;
    private Cursor mCsr; //<<<<<<<<<< Just this 1 Cursor is used.
    TextView mTV;
    Random mRnd;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mTV = this.findViewById(R.id.tv);
        mRnd = new Random();
        mDBHlpr = new DBHelper(this);
        addSomeDataIfNone();
        mHandler = new Handler();
        mRunIt.run();

    }

    private void addSomeDataIfNone() {

        SQLiteDatabase db = mDBHlpr.getWritableDatabase();
        if (DatabaseUtils.queryNumEntries(db,DBHelper.TBL_LOCATIONS) > 0) return;
        Random rnd = new Random();
        db.beginTransaction();
        for (int i=0; i < 100000; i++ ) {
            mDBHlpr.insertLocation(abs(mRnd.nextDouble()),abs(mRnd.nextDouble()),"LOCATION" + String.valueOf(i));
        }
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    Runnable mRunIt = new Runnable() {
        @Override
        public void run() {
            if (mCounter > mLimit) return;
            getData(mCounter);
            mCounter++;
            mHandler.postDelayed(mRunIt, mInterval);
        }
    };

    private void getData(int iteration) {
        Log.d("GETTINGDATA","Getting data for iteration " + String.valueOf(iteration));
        double latlim1 = abs(mRnd.nextDouble());
        double latlim2 = abs(mRnd.nextDouble());
        double  lnglim1 = abs(mRnd.nextDouble());
        double lnglim2 = abs(mRnd.nextDouble());
        double nlim = 0.0, slim = 0.0, elim = 0.0, wlim = 0.0;

        if (latlim1 > latlim2) {
            nlim = latlim1;
            slim = latlim2;
        } else {
            nlim = latlim2;
            slim = latlim1;
        }
        if (lnglim1 > lnglim2) {
            elim = lnglim1;
            wlim = lnglim2;
        } else {
            elim = lnglim2;
            wlim = lnglim1;
        }

        mCsr = mDBHlpr.getLocations(nlim,slim,elim,wlim);
        mTV.setText("Iteration " + String.valueOf(iteration) + " Rows returned = " + String.valueOf(mCsr.getCount()));
    }
}

Milzie