How to use a Random SQLite calling in Android

SQLiteDatabase is your friend

On Android systems, default database is SQLite. It is a nice way to save and use data, it’s also so similar to SQL and it’s opensource. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. SQLiteDatabase Android Reference provides a huge reference to implement it in your Android application.Exposes methods to manage a SQLite database: create, delete, execute SQL commands, and perform other common database management tasks.

SQLiteCursor is the key for your success

Pay attention to multiple threads because SQLiteCursor is not internally synchronized.

SQLiteCursor, like many other public class, ha a lot of constructors and methods. I don’t want to explain them here, because you can find a lot of spared documentation everywhere on the Internet and also a lot of well-written books on Amazon (like the SQLite companion). Execute a query and provide access to its result set through a Cursor interface, so you can use literally the SQL command to investigate your database. Actually, Cursor has method to move forward and behind throught your tables. Take a look at SQLite Vogella Tutorial for a better understanding of it.

Just a random method to query the database

If I want to extract random row from the application SQLite database in Android, I can SELECT a random row with:


private Cursor mCurRandom;
String sql ="SELECT * FROM table_test ORDER BY RANDOM() LIMIT 1";
mCurRandom = mDb.rawQuery(sql, null);

I have implemented a DBHelper.java, to work with the Android SQLite database:


package net.cialu.simplesql;
/**

Created by cialu on 05/10/15.
**/

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import static android.database.sqlite.SQLiteDatabase.deleteDatabase;

public class DBHelper extends SQLiteOpenHelper {
private static String TAG = "DBHelper"; // Tag just for the LogCat window
private static String DB_PATH = "";//destination path (location) of database on device
private static String DB_NAME ="test.db";// Database name
private static final int DB_VERSION = 1;// Database Version
private SQLiteDatabase mDataBase;
private final Context mContext;
private final boolean OVERWRITE = true;// Put true if you need to update your DB

public DBHelper(Context context)
{
    super(context, DB_NAME, null, DB_VERSION);
    if(android.os.Build.VERSION.SDK_INT >= 17){
        DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
    }
    else
    {
        DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
    }
    this.mContext = context;

}

public void createDataBase() throws IOException
{
    //If database not exists copy it from the assets

    boolean mDataBaseExist = checkDataBase();
    if(!mDataBaseExist)
    {
        /*if (OVERWRITE==true) {
        //Overwrite the database from assests

            mContext.deleteDatabase(DB_NAME);
            copyDataBase();
            Log.e(TAG, "createDatabase database overwritten");
        }
        {*/
            this.getReadableDatabase();
            this.close();
        }{
        try
        {
            //Copy the database from assests
            copyDataBase();
            Log.e(TAG, "createDatabase database created");
        }
        catch (IOException mIOException)
        {
            throw new Error("ErrorCopyingDataBase");
        }
    }
}
//Check that the database exists here: /data/data/your package/databases/DB_Name
private boolean checkDataBase()
{
    File dbFile = new File(DB_PATH + DB_NAME);
    //Log.v("dbFile", dbFile + "   "+ dbFile.exists());
    return dbFile.exists();
}

//Copy the database from assets
private void copyDataBase() throws IOException
{
    InputStream mInput = mContext.getAssets().open(DB_NAME);
    String outFileName = DB_PATH + DB_NAME;
    OutputStream mOutput = new FileOutputStream(outFileName);
    byte[] mBuffer = new byte[1024];
    int mLength;
    while ((mLength = mInput.read(mBuffer))>0)
    {
        mOutput.write(mBuffer, 0, mLength);
    }
    mOutput.flush();
    mOutput.close();
    mInput.close();
}

//Open the database, so we can query it
public boolean openDataBase() throws SQLException
{
    String mPath = DB_PATH + DB_NAME;
    //Log.v("mPath", mPath);
    mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
    //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
    return mDataBase != null;
}

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

@Override
public void onCreate(SQLiteDatabase db) {
    // tables creation

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // tables updates
}
}

After, I have implemented a DBAdapter.java with the query methods, so use this:


package net.cialu.simplesql;
/**

Created by cialu on 05/10/15.
**/
import java.io.IOException;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBAdapter {
protected static final String TAG = "DataAdapter";
private final Context mContext;
private SQLiteDatabase mDb;
private DBHelper mDbHelper;
private Cursor mCur;
private Cursor mCurRandom;

public DBAdapter(Context context)
{
    this.mContext = context;
    mDbHelper = new DBHelper(mContext);
}

public DBAdapter createDataBase() throws SQLException
{
    try
    {
        mDbHelper.createDataBase();
    }
    catch (IOException mIOException)
    {
        Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
        throw new Error("UnableToCreateDatabase");
    }
    return this;
}

public DBAdapter open() throws SQLException
{
    try
    {
        mDbHelper.openDataBase();
        mDbHelper.close();
        mDb = mDbHelper.getReadableDatabase();
    }
    catch (SQLException mSQLException)
    {
        Log.e(TAG, "open >>"+ mSQLException.toString());
        throw mSQLException;
    }
    return this;
}

public void close()
{
    mDbHelper.close();
}

public Cursor getData()
{
    try
    {
        String sql ="SELECT * FROM table_test";

        mCur = mDb.rawQuery(sql, null);
        if (mCur!=null)
        {
            mCur.moveToNext();
        }
        return mCur;
    }
    catch (SQLException mSQLException)
    {
        Log.e(TAG, "getData >>"+ mSQLException.toString());
        throw mSQLException;
    }
}

public Cursor getNewData()
{
    try
    {

        if ((mCur != null) && !mCur.isLast())
        {
            mCur.moveToNext();
            return mCur;
        }
        {
            if ((mCur != null) && !mCur.isAfterLast()) {
                getData();
            }
            {
                if (mCur.isLast()) {
                    // Prevent CursorIndexOutOfBoundsException
                    mCur.moveToFirst();
                    mCur.moveToNext();
                    return mCur;
                }
                return mCur;
            }
        }
    }
    catch (SQLException mSQLException)
    {
        Log.e(TAG, "getNewData >>"+ mSQLException.toString());
        throw mSQLException;
    }
}

public Cursor getRandomData()
{
    try
    {
        String sql ="SELECT * FROM table_test ORDER BY RANDOM() LIMIT 1";

        mCurRandom = mDb.rawQuery(sql, null);
        if (mCurRandom!=null) {
            mCurRandom.moveToNext();
            return mCurRandom;
        }
            {

                    getRandomData();

            }

    }
    catch (SQLException mSQLException)
    {
        Log.e(TAG, "getTestData >>"+ mSQLException.toString());
        throw mSQLException;
    }
    return mCurRandom;
}
}

You can find all the code on this SimpleSQL repository on my GitHub.

And enjoy it!

Leave a Reply

Your email address will not be published. Required fields are marked *