Android: Filling a table from resource file using string array (SQLite)

In this post we will be filling a table in our database with values from a resource xml file.
You can use this technique to create default rows in your table on creation/updating of the database.

Our resource file: res/values/mytable.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
 	<string-array
        name="mytable_records_v1">
 	    <item>First row</item>
   	    <item>Second row</item>
   	    <item>Third row</item>
	</string-array>    
</resources>

As you can see I have named the array mytable_records_v1. Put the version in the id so you can make a new string array for future versions. For example in the future you want to add “Fourth row” in your table, you will then name the array “mytable_records_v2” and only add the new records in your table in the update method.

We will be using a SQLiteOpenHelper to create/update the database.

/**
 * This class helps open, create, and upgrade the database file.
 */
private static class DatabaseHelper extends SQLiteOpenHelper {
	
	private final Context fContext;
	
	DatabaseHelper(Context context) {
		super(context, "sampledb", null, 1);
		fContext = context;
	} 

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE mytable ("
				+ "_id INTEGER PRIMARY KEY,"
				+ "title TEXT"
				+ ");");

		//Add default record to mytable
		ContentValues _Values = new ContentValues();        	                
		//Get string array from resource file
		Resources res = fContext.getResources();
		String[] _mytable_Records = res.getStringArray(R.array.mytable_records_v1);
		//Loop trough array and insert records into table
		int _Length = _mytable_Records .length;		
		for (int i = 0; i < _Length; i++) {
			_Values.put("title", _mytable_Records [i]);
			db.insert("mytable", null, _Values);        		
		}
	}

	/* Update database to latest version */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//Crude update, make sure to implement a correct one when needed.
					
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS _mytable_Records ");
		onCreate(db);
	}
}

This sample will be very useful when dealing with simple lookup tables. However the downside is that there is only room for one field in the string array, so it won’t work for more complex records.

Till next post. Happy coding! :)

Leave a Reply

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