Show Me the Data, Dude or Dudette (as the Case May Be)
Sometimes you just want to quickly see what the contents of a given SQLite table is, without going through the rigamarole of starting up
DDMS and then copying files hither and yon, etc., as I showed how to do here.
Doing so is easy; you need to know the data type of the column you're retrieving, but there are only five possibilities in SQLite (null, Int,
Float, String, and BLOb), and the Cursor class has a getType() method that tells you what that is, so it's rather easy to stuff the entire
contents of any SQLite table into a StringBuilder and return that, as shown here:
public class SQLiteOpenHelperPlatypus extends SQLiteOpenHelper {
. . .
public StringBuilder getAllRecordsFrom(String tblName) {
final int NULLVAL = 0;
final int INTVAL = 1;
final int FLOATVAL = 2;
final int STRINGVAL = 3;
final int BLOBVAL = 4;
String query = "Select * FROM " + tblName;
StringBuilder results = new StringBuilder();
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db != null ? db.rawQuery(query, null) : null;
if (cursor == null) return results;
int colCount = cursor.getColumnCount();
cursor.moveToFirst();
int typeVal;
while (true) {
for (int i = 0; i < colCount; i++) {
typeVal = cursor.getType(i);
switch (typeVal) {
case NULLVAL:
break;
case INTVAL:
results.append(cursor.getInt(i)).toString();
break;
case FLOATVAL:
results.append(cursor.getFloat(i)).toString();
break;
case STRINGVAL:
results.append(cursor.getString(i));
break;
case BLOBVAL:
results.append("BLOb" + String.valueOf(i));
break;
}
}
if (cursor.isLast()) break;
cursor.moveToNext();
}
cursor.close();
if (null != db) {
db.close();
}
return results;
}
SQL Injection Considered More Harmful than the Zombie Apocalypse
This is probably not something you'd want to expose to your users, based on the possibility of a SQL Injection attack (which would be
worse than a Zombie attack, because SQL Injection is real), but for a quick-and-dirty way of seeing what a particular table holds during
development, it's the cat's meow. As indicated in the code above, declare this method in a class that extends SQLiteOpenHelper, such
as:
public class SQLiteOpenHelperPlatypus extends SQLiteOpenHelper {
...and you're "off to the races." You can call it like so (this assumes you have a Spinner widget that contains the names of the SQLite
tables):
private ShowLocalDataTask _showLocalDataTask;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sqlite);
final Spinner spin = (Spinner) findViewById((R.id.tableSpinner));
Button selectAllBtn = (Button) findViewById(R.id.SelectStarBtn);
selectAllBtn.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
String tblName = String.valueOf(spin.getSelectedItem());
_showLocalDataTask = new ShowLocalDataTask();
_showLocalDataTask.execute(tblName);
}
});
}
. . .
private class ShowLocalDataTask extends AsyncTask<String, String, String> {
@Override
protected String doInBackground(String... strings) {
String tbl = strings[0];
SQLiteOpenHelperHHS sqliteHHS = new SQLiteOpenHelperPlatypus(SQLiteActivity.this, null);
StringBuilder sb = sqliteHHS.getAllRecordsFrom(tbl);
return sb.toString();
}
@Override
protected void onPostExecute(String result) {
if (result == null) return;
Log.i("QueryResults", result);
Toast.makeText(SQLiteActivity.this, result, Toast.LENGTH_LONG).show();
}
}
You can find my tip on how to populate a Spinner with values here.