Android Database Example ,database usage, AsyncTask , Database Export

This is continuation of previous lessons on Android. In this article i would like to discuss Database Operations in Android and Concept of AsyncTask in android
AsyncTask enables proper and easy use of the UI thread. This class allows to perform background operations and publish results on the UI thread without having to manipulate threads and/or handlers. This can be used in Timeconsuming processes so that UI is not obstructed

Note: As previously defined i’ve been compiling opensource Tutorials on android and selecting and modifying them to create a easy tutorial on major topics

Before working with Database ,lets create a Utility classes which can create/update/drop/insert database/tables

Here are 2 utility classes which are self explanatory

package com.linkwithweb.sqllite;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.provider.BaseColumns;
import android.util.Log;

/**
 * @author Ashwin Kumar
 * 
 */
public class DataBaseHelper extends SQLiteOpenHelper {

	// The Android's default system path of your application database.
	private static String DB_PATH = "/data/data/linkwithweb/databases/";

	private static String DB_NAME = "myDBName.db";

	private static final int DATABASE_VERSION = 1;

	// Table name
	public static final String TABLE_NAME = "TEST";

	private SQLiteDatabase myDataBase;

	private final Context myContext;

	private SQLiteStatement insertStmt;
	private static final String INSERT = "insert into " + TABLE_NAME
			+ "(name) values (?)";

	/**
	 * Constructor
	 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
	 * 
	 * @param context
	 */
	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, DATABASE_VERSION);
		this.myContext = context;
		this.myDataBase = getWritableDatabase();
		this.insertStmt = this.myDataBase.compileStatement(INSERT);
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own database.
	 * */
	public void createDataBase() throws IOException {

		boolean dbExist = checkDataBase();

		if (dbExist) {
			// do nothing - database already exist
		} else {

			// By calling this method and empty database will be created into the default system path
			// of your application so we are gonna be able to overwrite that database with our database.
			this.getReadableDatabase();

			try {

				copyDataBase();

			} catch (IOException e) {

				throw new Error("Error copying database");

			}
		}

	}

	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {

		SQLiteDatabase checkDB = null;

		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (SQLiteException e) {

			// database does't exist yet.

		}

		if (checkDB != null) {

			checkDB.close();

		}

		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created empty database in the
	 * system folder, from where it can be accessed and handled.
	 * This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {

		// Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	/**
	 * @throws SQLException
	 */
	public void openDataBase() throws SQLException {
		// Open the database
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);

	}

	@Override
	public synchronized void close() {
		if (myDataBase != null)
			myDataBase.close();

		super.close();

	}

	public SQLiteDatabase getDb() {
		return myDataBase;
	}

	/**
	 * Compiled Insert Statement
	 * 
	 * @param name
	 * @return
	 */
	public long insert(String name) {
		this.insertStmt.bindString(1, name);
		return this.insertStmt.executeInsert();
	}

	/**
	 * 
	 */
	public void deleteAll() {
		this.myDataBase.delete(TABLE_NAME, null, null);
	}

	/**
	 * @return
	 */
	public List selectAll() {
		List list = new ArrayList();
		Cursor cursor = this.myDataBase.query(TABLE_NAME,
				new String[] { "name" }, null, null, null, null, "name desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table " + TABLE_NAME + "( " + BaseColumns._ID
				+ " integer primary key autoincrement, name text not null);";
		Log.d("EventsData", "onCreate: " + sql);
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion >= newVersion)
			return;

		String sql = null;
		if (oldVersion == 1)
			sql = "alter table " + TABLE_NAME + " add COLUMN_2 text;";
		if (oldVersion == 2)
			sql = "";

		Log.d("EventsData", "onUpgrade	: " + sql);
		if (sql != null)
			db.execSQL(sql);
	}

	// Add your public helper methods to access and get content from the database.
	// You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
	// to you to create adapters for your views.

}

Now let me define my database xml exporter. Just exports any database to XML format

package com.linkwithweb.sqllite;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;

import com.linkwithweb.MyApplication;

/**
 * Android DataExporter that allows the passed in SQLiteDatabase 
 * to be exported to external storage (SD card) in an XML format.
 * 
 * To backup a SQLite database you need only copy the database file itself
 * (on Android /data/data/APP_PACKAGE/databases/DB_NAME.db) -- you *don't* need this
 * export to XML step.
 * 
 * XML export is useful so that the data can be more easily transformed into
 * other formats and imported/exported with other tools (not for backup per se).  
 * 
 * The kernel of inspiration for this came from: 
 * http://mgmblog.com/2009/02/06/export-an-android-sqlite-db-to-an-xml-file-on-the-sd-card/. 
 * (Though I have made many changes/updates here, I did initially start from that article.)
 * 
 * @author ccollins
 *
 */
public class DataXmlExporter {

   private static final String DATASUBDIRECTORY = "exampledata";

   private SQLiteDatabase db;
   private XmlBuilder xmlBuilder;

   public DataXmlExporter(SQLiteDatabase db) {
      this.db = db;
   }

   public void export(String dbName, String exportFileNamePrefix) throws IOException {
      Log.i(MyApplication.APP_NAME, "exporting database - " + dbName + " exportFileNamePrefix=" + exportFileNamePrefix);

      this.xmlBuilder = new XmlBuilder();
      this.xmlBuilder.start(dbName);

      // get the tables
      String sql = "select * from sqlite_master";
      Cursor c = this.db.rawQuery(sql, new String[0]);
      Log.d(MyApplication.APP_NAME, "select * from sqlite_master, cur size " + c.getCount());
      if (c.moveToFirst()) {
         do {
            String tableName = c.getString(c.getColumnIndex("name"));
            Log.d(MyApplication.APP_NAME, "table name " + tableName);

            // skip metadata, sequence, and uidx (unique indexes)
            if (!tableName.equals("android_metadata") && !tableName.equals("sqlite_sequence")
                     && !tableName.startsWith("uidx")) {
               this.exportTable(tableName);
            }
         } while (c.moveToNext());
      }
      String xmlString = this.xmlBuilder.end();
      this.writeToFile(xmlString, exportFileNamePrefix + ".xml");
      Log.i(MyApplication.APP_NAME, "exporting database complete");
   }

   private void exportTable(final String tableName) throws IOException {
      Log.d(MyApplication.APP_NAME, "exporting table - " + tableName);
      this.xmlBuilder.openTable(tableName);
      String sql = "select * from " + tableName;
      Cursor c = this.db.rawQuery(sql, new String[0]);
      if (c.moveToFirst()) {
         int cols = c.getColumnCount();
         do {
            this.xmlBuilder.openRow();
            for (int i = 0; i < cols; i++) {
               this.xmlBuilder.addColumn(c.getColumnName(i), c.getString(i));
            }
            this.xmlBuilder.closeRow();
         } while (c.moveToNext());
      }
      c.close();
      this.xmlBuilder.closeTable();
   }

   private void writeToFile(String xmlString, String exportFileName) throws IOException {
      File dir = new File(Environment.getExternalStorageDirectory(), DATASUBDIRECTORY);
      if (!dir.exists()) {
         dir.mkdirs();
      }
      File file = new File(dir, exportFileName);
      file.createNewFile();

      ByteBuffer buff = ByteBuffer.wrap(xmlString.getBytes());
      FileChannel channel = new FileOutputStream(file).getChannel();
      try {
         channel.write(buff);
      } finally {
         if (channel != null)
            channel.close();
      }
   }

   /**
    * XmlBuilder is used to write XML tags (open and close, and a few attributes)
    * to a StringBuilder. Here we have nothing to do with IO or SQL, just a fancy StringBuilder. 
    * 
    * @author ccollins
    *
    */
   class XmlBuilder {
      private static final String OPEN_XML_STANZA = "";
      private static final String CLOSE_WITH_TICK = "'>";
      private static final String DB_OPEN = "<database name='";
      private static final String DB_CLOSE = "";
      private static final String TABLE_OPEN = "<table name='";
      private static final String TABLE_CLOSE = "";
      private static final String ROW_OPEN = "";
      private static final String ROW_CLOSE = "";
      private static final String COL_OPEN = "<col name='";
      private static final String COL_CLOSE = "";

      private final StringBuilder sb;

      public XmlBuilder() throws IOException {
         this.sb = new StringBuilder();
      }

      void start(String dbName) {
         this.sb.append(OPEN_XML_STANZA);
         this.sb.append(DB_OPEN + dbName + CLOSE_WITH_TICK);
      }

      String end() throws IOException {
         this.sb.append(DB_CLOSE);
         return this.sb.toString();
      }

      void openTable(String tableName) {
         this.sb.append(TABLE_OPEN + tableName + CLOSE_WITH_TICK);
      }

      void closeTable() {
         this.sb.append(TABLE_CLOSE);
      }

      void openRow() {
         this.sb.append(ROW_OPEN);
      }

      void closeRow() {
         this.sb.append(ROW_CLOSE);
      }

      void addColumn(final String name, final String val) throws IOException {
         this.sb.append(COL_OPEN + name + CLOSE_WITH_TICK + val + COL_CLOSE);
      }
   }
}

Now lets create Activity and AsyncTasks to Delete/Insert data into Database tables. Below one is sample Async Task

	private class InsertDataTask extends AsyncTask {
		private final ProgressDialog dialog = new ProgressDialog(
				DatabaseActivity.this);

		// can use UI thread here
		protected void onPreExecute() {
			this.dialog.setMessage("Inserting data...");
			this.dialog.show();
		}

		// automatically done on worker thread (separate from UI thread)
		protected Void doInBackground(final String... args) {
			DatabaseActivity.this.application.getDataHelper().insert(args[0]);
			return null;
		}

		// can use UI thread here
		protected void onPostExecute(final Void unused) {
			if (this.dialog.isShowing()) {
				this.dialog.dismiss();
			}
			// reset the output view by retrieving the new data
			// (note, this is a naive example, in the real world it might make sense
			// to have a cache of the data and just append to what is already there, or such
			// in order to cut down on expensive database operations)
			new SelectDataTask().execute();
		}
	}

Here is the Database Activity Listed. This just inserts some data into database on button click and deletes all data on another button click

/**
 * 
 */
package com.linkwithweb;

import java.util.List;

import android.app.Activity;
import android.app.ProgressDialog;
import android.content.Intent;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import com.linkwithweb.sqllite.ManageData;

/**
 * @author Ashwin Kumar
 * 
 */
public class DatabaseActivity extends Activity {

	private static final String NAME = "NAME";

	private static final int MENU_MANAGE = 0;

	private EditText input;
	private Button saveButton;
	private Button deleteButton;
	private TextView output;

	private MyApplication application;

	@Override
	public void onCreate(final Bundle savedInstanceState) {
		Log.d(MyApplication.APP_NAME, "onCreate");
		super.onCreate(savedInstanceState);

		this.setContentView(R.layout.dbview);

		// get "Application" object for shared state or creating of expensive resources - like DataHelper
		// (this is not recreated as often as each Activity)
		this.application = (MyApplication) this.getApplication();

		// inflate views
		this.input = (EditText) this.findViewById(R.id.in_text);
		this.saveButton = (Button) this.findViewById(R.id.save_button);
		this.deleteButton = (Button) this.findViewById(R.id.del_button);
		this.output = (TextView) this.findViewById(R.id.out_text);

		// initially populate "output" view from database
		new SelectDataTask().execute();

		// save new data to database (when save button is clicked)
		this.saveButton.setOnClickListener(new OnClickListener() {
			public void onClick(final View v) {
				new InsertDataTask().execute(DatabaseActivity.this.input
						.getText().toString());
				DatabaseActivity.this.input.setText("");
			}
		});

		// delete all data from database (when delete button is clicked)
		this.deleteButton.setOnClickListener(new OnClickListener() {
			public void onClick(final View v) {
				new DeleteDataTask().execute();
			}
		});
	}

	@Override
	public void onSaveInstanceState(final Bundle b) {
		Log.d(MyApplication.APP_NAME, "onSaveInstanceState");
		if ((this.input.getText().toString() != null)
				&& (this.input.getText().toString().length() > 0)) {
			b.putString(DatabaseActivity.NAME, this.input.getText().toString());
		}
		super.onSaveInstanceState(b);
	}

	@Override
	public void onRestoreInstanceState(final Bundle b) {
		super.onRestoreInstanceState(b);
		Log.d(MyApplication.APP_NAME, "onRestoreInstanceState");
		String name = b.getString(DatabaseActivity.NAME);
		if (name != null) {
			// use onSaveInstanceState/onRestoreInstance state to manage state when orientation is changed (and whenever restarted)
			// put some text in input box, then rotate screen, text should remain
			// COMMENT this out, and try again, text won't be there - you need to maintain this state - esp for orientation changes
			// (you can rotate the screen in the emulator by pressing 9 on numeric keypad)
			this.input.setText(name);
		}
	}

	@Override
	public boolean onCreateOptionsMenu(final Menu menu) {
		menu.add(0, DatabaseActivity.MENU_MANAGE, 1, "Manage Database")
				.setIcon(android.R.drawable.ic_menu_manage);
		return super.onCreateOptionsMenu(menu);
	}

	@Override
	public boolean onOptionsItemSelected(final MenuItem item) {
		switch (item.getItemId()) {
		case MENU_MANAGE:
			this.startActivity(new Intent(DatabaseActivity.this,
					ManageData.class));
			return true;
		default:
			return super.onOptionsItemSelected(item);
		}
	}

	private class InsertDataTask extends AsyncTask {
		private final ProgressDialog dialog = new ProgressDialog(
				DatabaseActivity.this);

		// can use UI thread here
		protected void onPreExecute() {
			this.dialog.setMessage("Inserting data...");
			this.dialog.show();
		}

		// automatically done on worker thread (separate from UI thread)
		protected Void doInBackground(final String... args) {
			DatabaseActivity.this.application.getDataHelper().insert(args[0]);
			return null;
		}

		// can use UI thread here
		protected void onPostExecute(final Void unused) {
			if (this.dialog.isShowing()) {
				this.dialog.dismiss();
			}
			// reset the output view by retrieving the new data
			// (note, this is a naive example, in the real world it might make sense
			// to have a cache of the data and just append to what is already there, or such
			// in order to cut down on expensive database operations)
			new SelectDataTask().execute();
		}
	}

	private class SelectDataTask extends AsyncTask {
		private final ProgressDialog dialog = new ProgressDialog(
				DatabaseActivity.this);

		// can use UI thread here
		protected void onPreExecute() {
			this.dialog.setMessage("Selecting data...");
			this.dialog.show();
		}

		// automatically done on worker thread (separate from UI thread)
		protected String doInBackground(final String... args) {
			List names = DatabaseActivity.this.application
					.getDataHelper().selectAll();
			StringBuilder sb = new StringBuilder();
			for (String name : names) {
				sb.append(name + "\n");
			}
			return sb.toString();
		}

		// can use UI thread here
		protected void onPostExecute(final String result) {
			if (this.dialog.isShowing()) {
				this.dialog.dismiss();
			}
			DatabaseActivity.this.output.setText(result);
		}
	}

	private class DeleteDataTask extends AsyncTask {
		private final ProgressDialog dialog = new ProgressDialog(
				DatabaseActivity.this);

		// can use UI thread here
		protected void onPreExecute() {
			this.dialog.setMessage("Deleting data...");
			this.dialog.show();
		}

		// automatically done on worker thread (separate from UI thread)
		protected Void doInBackground(final String... args) {
			DatabaseActivity.this.application.getDataHelper().deleteAll();
			return null;
		}

		// can use UI thread here
		protected void onPostExecute(final Void unused) {
			if (this.dialog.isShowing()) {
				this.dialog.dismiss();
			}
			// reset the output view by retrieving the new data
			// (note, this is a naive example, in the real world it might make sense
			// to have a cache of the data and just append to what is already there, or such
			// in order to cut down on expensive database operations)
			new SelectDataTask().execute();
		}
	}
}

Attached is the eclipse project with all examples(Rename .doc to .zip to extract to workspace)
AndroidLessons

You can also checkout code from
http://linkwithweb.googlecode.com/svn/trunk/Android/AndroidLessons/

Java Program to List Tables and Their Relationships in Database

Here is sample Java Program to List all Tables and their relationships in database


/**
*
*/
package com.research.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author Ashwin
*
*/
public class ObjectGrapher {

/**
* @param args
*/
public static void main(String[] args) {
try {
Connection con = DBUtils.getConnection();
DatabaseMetaData metaData = con.getMetaData();
ResultSet rs = metaData.getTables(null, null, "%", null);
List tableNamesList = new ArrayList();
Map tableMap = new HashMap();

while (rs.next()) {
String tableName = rs.getString(3);
tableNamesList.add(tableName);
}
if (rs != null) {
rs.close();
}

// Iterate Throught the List
for (int i = 0; i < tableNamesList.size(); i++) {

for (int j = 0; j < tableNamesList.size(); j++) {

// If both are same tablename we dnt need to find any
// relation
if (i == j) {
continue;
}

String primaryTable = (String) tableNamesList.get(i);
String foreignTable = (String) tableNamesList.get(j);

rs = metaData.getCrossReference(null, null, primaryTable,
null, null, foreignTable);

while (rs.next()) {
DBTable primaryDBTable = null;
DBTable foreignDBTable = null;

String primaryTableName = rs.getString("PKTABLE_NAME");
String foreignTableName = rs.getString("FKTABLE_NAME");

if (tableMap.get(primaryTableName) != null) {
primaryDBTable = (DBTable) tableMap
.get(primaryTableName);
} else {
primaryDBTable = new DBTable();
primaryDBTable.setTableName(primaryTableName);
}

if (tableMap.get(foreignTableName) != null) {
foreignDBTable = (DBTable) tableMap
.get(foreignTableName);
} else {
foreignDBTable = new DBTable();
foreignDBTable.setTableName(foreignTableName);
}

foreignDBTable.addRelations(primaryDBTable);

tableMap.put(foreignTableName, foreignDBTable);
tableMap.put(primaryTableName, primaryDBTable);

System.out.println("Primary Key Table Name :"
+ rs.getString("PKTABLE_NAME")
+ " Primary Key Column :"
+ rs.getString("PKCOLUMN_NAME"));
System.out.println("Foreign Key Table Name :"
+ rs.getString("FKTABLE_NAME")
+ " Foreign Key Column :"
+ rs.getString("FKCOLUMN_NAME"));
System.out
.println("--------------------------------------------------------------------");
}

if (rs != null) {
rs.close();
}
}
}

} catch (Exception e) {
e.printStackTrace();
}
}

}