CODEWITHSUNDEEP
androidsqliterandom
Suika
Suika

Reputation: 660

Get random record in SQLite

I fetch records in my SQLite database like this.

spellId = extra.getString("spellId");
DBase db = new DBase(this);
db.open();
String[] data = db.getRecord(Integer.parseInt(spellId));
db.close();

Can I get random data like this without using raw queries and cursor?

Upvotes: 5

Views: 7979

Answers (2)

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

You can use Random#nextInt() like

String[] data = db.getRecord(new Random().nextInt(num));

where num falls in the range of your record IDs. You would need to adapt this solution in case your Ids are fragmented and do not form a consecutive range.

One of the ways to do that would be to first create a query to fetch all the primary keys and store the values in a set somewhere. Then pick a random key by generating an index using Random.

String[] data = db.getRecord(IDSet.get(new Random().nextInt(IDSet.size())));

Check out the docs for more information.

Returns a pseudorandom, uniformly distributed int value between 0 (inclusive) and the specified value (exclusive), drawn from this random number generator's sequence.

If you're considering a DB query solution

A better alternative to using ORDER BY RANDOM() (which is known to not scale well as the number of rows in your table grows) is to let SQLite return a random row using an OFFSET.

First save the total number of rows num somewhere.

SELECT COUNT(*) AS num FROM spells;

Then choose a random number rnum between (0, num) using Random and use the query

SELECT * FROM spells LIMIT 1 OFFSET rnum;

Upvotes: 3

Rathan Kumar
Rathan Kumar

Reputation: 2577

try like this:

db.rawQuery("SELECT * FROM mainTable ORDER BY RANDOM() LIMIT 1", null);

Upvotes: 13

Related Questions