How to sync Remote MySQL DB to SQLite on Android?

In this post, I am going to explain how to sync data present in remote MySQL DB to Android SQLite database. The data present in the remote MySQL DB (or any Database) needs to be synced with SQLite DB in Android in order to maintain the data integrity.

If there is change in remote DB data, it can be synced with Android SQLite DB when User explicitly hits ‘Sync’ button or asynchronously by the application itself (in particular interval) when Internet connectivity is available.

Before we start developing the application, quickly take a look at the prerequisites below.

Prerequisite:

You must have knowledge of SQLite before you start creating the application. Have a glance at the below articles which will make you understand much about SQLite

What is SQLite?

Android SQLite CRUD example

If you are looking for tutorial on Syncing data from Android SQLite to Remote MySQL DB, please follow this link. Otherwise proceed with below tutorial.

Video Demo

Steps involved in Syncing SQLite with MySQL:

By looking at the video demo, you got to know what are we going to develop.

About Application

  • It is a simple application which displays Users names.
  • When ‘Sync’ action button in Action Bar is pressed, application syncs up data present in remote MySQL DB with Android SQLite DB.

Here are the steps involved in remote MySQL and SQLite DBs Sync up:

mysql sqlite sync steps

Developing the application involves two parts:

  1. Php/MySQL
  2. Android

You can download source code from here if you don’t want to create Application from scratch, otherwise please proceed with below listings.

Php/MySQL

Do follow below sections to create Php Classes and also to configure MySQL DB.

Create MySQL DB and Table:

I chose PHP/MySQL combo for creating API to connect and Android device to MySQL DB since the configuration is simple and easy and also it will be easy for you to start with.

I will write separate posts on how to handle Sync up using Java and .Net technologies.

  1. Create database called ‘db’ in phpMyAdmin or create it through command:
    create database db
  2. Select database db in phpMyAdmin or select it through command:
    use db
  3. Create table called ‘users’ in phpMyAdmin by pasting below SQL script in SQL Query box:
    CREATE TABLE IF NOT EXISTS `user` (
      `Id` int(11) NOT NULL AUTO_INCREMENT,
      `Name` varchar(100) NOT NULL,
      `syncsts` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`Id`)
    )

Build PHP Classes

Create following PHP Classes and place them under ‘www/mysqlsqlitesync’ folder which will provide interface between Android application and MySQL DB:

mysql_sqlite_sync_phps

config.php:

<?php
/**
 * DB configuration variables
 */
define("DB_HOST", "localhost");
define("DB_USER", "UserName");
define("DB_PASSWORD", "PWD");
define("DB_DATABASE", "db");
?>

db_connect.php:

<?php

class DB_Connect {

    // constructor
    function __construct() {

    }

    // destructor
    function __destruct() {
        // $this->close();
    }

    // Connecting to database
    public function connect() {
        require_once 'config.php';
        // connecting to mysql
        $con = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
        // selecting database
        mysql_select_db(DB_DATABASE);

        // return database handler
        return $con;
    }

    // Closing database connection
    public function close() {
        mysql_close();
    }

} 
?>

db_functions.php:

<?php
/**
 * DB operations functions
 */
class DB_Functions {

    private $db;

    //put your code here
    // constructor
    function __construct() {
        include_once './db_connect.php';
        // connecting to database
        $this->db = new DB_Connect();
        $this->db->connect();
    }

    // destructor
    function __destruct() {

    }

    /**
     * Storing new user
     * returns user details
     */
    public function storeUser($User) {
        // Insert user into database
        $result = mysql_query("INSERT INTO user(Name) VALUES('$User')");

        if ($result) {
			return true;
        } else {			
				// For other errors
				return false;
		}
    }
	 /**
     * Getting all users
     */
    public function getAllUsers() {
        $result = mysql_query("select * FROM user");
        return $result;
    }
	/**
     * Get Yet to Sync row Count
     */
    public function getUnSyncRowCount() {
        $result = mysql_query("SELECT * FROM user WHERE syncsts = FALSE");
        return $result;
    }
	/**
	 * Update Sync status of rows
	 */
	public function updateSyncSts($id, $sts){
		$result = mysql_query("UPDATE user SET syncsts = $sts WHERE Id = $id");
		return $result;
	}
}

?>

insertuser.php:

<?php 
/**
 * Insert User into DB
 */ ?>
<style>
body {
  font: normal medium/1.4 sans-serif;
}
div.header{
padding: 10px;
background: #e0ffc1;
width:30%;
color: #008000;
margin:5px;
}
table {
  border-collapse: collapse;
  width: 25%;
  margin-left: auto;
  margin-right: auto;
}
form{
width: 30%;
  margin-left: auto;
  margin-right: auto;
padding: 10px;
border: 2px solid #edd3ff;
}
div#msg{
margin-top:10px;
width: 30%;
margin-left: auto;
margin-right: auto;
text-align: center;
}
</style>
<center>
<div class="header">
Android SQLite and MySQL Sync - Add Users
</div>
</center>
<form method="POST">
<table>
<tr>
<td>Name:</td><td><input name="username" /></td>
</tr>
<tr><td colspan="2" align="center"><input type="submit" value="Add User"/></td></tr>
</table>
</form>
<?php
include_once './db_functions.php';
//Create Object for DB_Functions clas
if(isset($_POST["username"]) && !empty($_POST["username"])){
$db = new DB_Functions(); 
//Store User into MySQL DB
$uname = $_POST["username"];
$res = $db->storeUser($uname);
	//Based on inserttion, create JSON response
	if($res){ ?>
		 <div id="msg">Insertion successful</div>
	<?php }else{ ?>
		 <div id="msg">Insertion failed</div>
	<?php }
} else{ ?>
 <div id="msg">Please enter name and submit</div>
<?php }
?>

viewusers.php:

<?php
/**
 * Displays User information
 */
?>
<html>
<head><title>View Users</title>
<style>
body {
  font: normal medium/1.4 sans-serif;
}
table {
  border-collapse: collapse;
  width: 20%;
  margin-left: auto;
  margin-right: auto;
}
tr > td {
  padding: 0.25rem;
  text-align: center;
  border: 1px solid #ccc;
}
tr:nth-child(even) {
  background: #FAE1EE;
}
tr:nth-child(odd) {
  background: #edd3ff;
}
tr#header{
background: #c1e2ff;
}
td#sync{
background: #fff;
}
div.header{
padding: 10px;
background: #e0ffc1;
width:30%;
color: #008000;
margin:5px;
}
div.refresh{
margin-top:10px;
width: 5%;
margin-left: auto;
margin-right: auto;
}
div#norecord{
margin-top:10px;
width: 15%;
margin-left: auto;
margin-right: auto;
}
img{
height: 32px;
width: 32px;
}
</style>
<script>
var val= setInterval(function(){
location.reload();
},2000);
</script>
</head>
<body>
<center>
<div class="header">
Android SQLite and MySQL Sync - View Users
</div>
</center>
<?php
    include_once 'db_functions.php';
    $db = new DB_Functions();
    $users = $db->getAllUsers();
    if ($users != false)
        $no_of_users = mysql_num_rows($users);
    else
        $no_of_users = 0;

?>
<?php
    if ($no_of_users > 0) {
?>
<table>
<tr id="header"><td>Id</td><td>Username</td><td>Sync Status</td></tr>
<?php
    while ($row = mysql_fetch_array($users)) {
?> 
<tr>
<td><span><?php echo $row["Id"] ?></span></td>
<td><span><?php echo $row["Name"] ?></span></td>
<td id="sync"><span>
<?php 
if($row["syncsts"])
{ 
echo "<img src='img/green.png'/>"; 
}else { 
echo "<img src='img/white.png'/>";
} 
?></span></td>
</tr>
<?php } ?>
</table>
<?php }else{ ?>
<div id="norecord">
No records in MySQL DB
</div>
<?php } ?>
</body>
</html>

getdbrowcount.php:

<?php
/**
 * Creates Unsynced MySQL DB rows count as JSON
 */
    include_once 'db_functions.php';
    $db = new DB_Functions();
    $users = $db->getUnSyncRowCount();
	$a = array();
	$b = array();
    if ($users != false){
        $no_of_users = mysql_num_rows($users);		
		$b["count"] = $no_of_users;
		echo json_encode($b);
	}
    else{
        $no_of_users = 0;
		$b["count"] = $no_of_users;
		echo json_encode($b);
	}
?>

getusers.php:

<?php
/**
 * Creates Unsynced rows data as JSON
 */
    include_once 'db_functions.php';
    $db = new DB_Functions();
    $users = $db->getUnSyncRowCount();
	$a = array();
	$b = array();
    if ($users != false){
		while ($row = mysql_fetch_array($users)) {		
			$b["userId"] = $row["Id"];
			$b["userName"] = $row["Name"];
			array_push($a,$b);
		}
		echo json_encode($a);
	}
?>

updatesyncsts.php:

<?php
/**
 * Updates Sync status of Users
 */
include_once './db_functions.php';
//Create Object for DB_Functions clas
$db = new DB_Functions(); 
//Get JSON posted by Android Application
$json = $_POST["syncsts"];
//Remove Slashes
if (get_magic_quotes_gpc()){
$json = stripslashes($json);
}
//Decode JSON into an Array
$data = json_decode($json);
//Util arrays to create response JSON
$a=array();
$b=array();
//Loop through an Array and insert data read from JSON into MySQL DB
for($i=0; $i<count($data) ; $i++)
{
//Store User into MySQL DB
$res = $db->updateSyncSts($data[$i]->Id,$data[$i]->status);
	//Based on inserttion, create JSON response
	if($res){
		$b["id"] = $data[$i]->Id;
		$b["status"] = 'yes';
		array_push($a,$b);
	}else{
		$b["id"] = $data[$i]->Id;
		$b["status"] = 'no';
		array_push($a,$b);
	}
}
//Post JSON response back to Android Application
echo json_encode($a);
?>

Types of JSON

JSON returned by getusers.php will look like below. JSON array contains array of Objects. Each object has User’s name and User’s Id.

[
{"userName":"Ram","userId":"1"},
{"userName":"Rahul","userId":"2"},
{"userName":"Sandy","userId":"3"},
{"userName":"Linda","userId":"4"}
]

JSON posted to updatesyncsts.php (after inserting Users in SQLite table) will look like below. JSON array contains array of Objects. Each object has User’s Id and Sync status.

[
{"Id":"1","status":"yes"},
{"Id":"2","status":"yes"},
{"Id":"3","status":"no"},
{"Id":"4","status":"yes"}
]

[pglinkadssmall1]

Android Project

Step 1: Create Android Application Project

  • Create new android project [File >> New >> Android Application Project] with project name MySQLSQLLiteSync
  • Enter package name as ‘com.prgguru.example’
  • Choose Minimum Required SDK, Target SDK and Compile with as shown in the below screen-print. If you don’t have latest SDK installed, they will not be populated in the dropdownlist, I would recommend you to update Android SDK manager with latest SDKs. Confused on choosing these options? Take a look at Minimum Required SDK – Target SDK – Compile With post.
  • Select Theme as ‘Holo Light with Dark Action Bar’ which will create Action Bar by default in Android 3.0 and above.
  • Click Next button and finally click ‘Finish’ to create project

ActionBar_1

Step 2: Add libraries to project

Add below third party libraries into project’s ‘lib’ folder. You can also download them if you don’t have them with you.

1. Android Asynchronous Http Client – An asynchronous callback-based Http client for Android built on top of Apache’s HttpClient libraries which is used by Pinterest, Instagram etc.,. Download

2. Google – GSON – Gson is a Java library that can be used to convert Java Objects into their JSON representation. Download

Step 3: Design Screens

We are going to have only one activity:

MainActivity – Home Screen of the Application (Launch screen)

Before creating screens, make sure below String resources are added. Add string resources to strings.xml present under /res/values folder.

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">MySQLSQLLiteSync</string>
    <string name="action_settings">Settings</string>
</resources>

Create two layout XMLs under /res/layout folder:

activity_main.xml

Home Screen layout

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity" >

     <RelativeLayout
        android:id="@+id/relativeLayout1"
        android:layout_width="fill_parent"
        android:layout_height="match_parent"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/relativeLayout1"
        android:orientation="vertical"
        android:layout_marginTop="40dp">

    <ListView
         android:id="@android:id/list"
         android:layout_width="match_parent"
         android:layout_height="wrap_content"
         android:layout_alignParentLeft="true">
     </ListView>
     </RelativeLayout>

</RelativeLayout>

view_user_entry.xml

Layout for ListView which is used in Home screen. Will discuss about it more in the below sections. For now create it under /res/layout folder.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical" >
    	<TextView
        android:id="@+id/userId"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:visibility="gone" />

       <TextView
           android:id="@+id/userName"
           android:layout_width="fill_parent"
           android:layout_height="wrap_content"
           android:layout_marginBottom="5dp"
           android:layout_marginTop="5dp"
           android:paddingLeft="6dip"
           android:paddingTop="6dip"
           android:textColor="#008000"
           android:textSize="17sp"
           android:textStyle="bold" />

</LinearLayout>

Step 4: Create Action Bar Menu

Save Refresh icons under respective drawable folders:

mdpi hdpi xhdpi
ic_action_refresh ic_action_refresh ic_action_refresh

Create main.xml under /res/menu folder and fill it with below code:

<menu xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    tools:context="com.prgguru.actionbar.MainActivity" >

    <item
        android:id="@+id/refresh"
        android:icon="@drawable/ic_action_refresh"
        android:orderInCategory="100"
        app:showAsAction="always"/>

</menu>

Step 5: DBController.java – DB Handler Class

We are done with Layout designing, let us jump into Coding.

Create DBController.Java under the package com.prgguru.example and fill it with below code. It handles DB related operations. Each line of code is provided with exhaustive comment, if you still have any question or doubt raise it right from here .

package com.prgguru.example;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBController  extends SQLiteOpenHelper {

	public DBController(Context applicationcontext) {
        super(applicationcontext, "user.db", null, 1);
    }
	//Creates Table
	@Override
	public void onCreate(SQLiteDatabase database) {
		String query;
		query = "CREATE TABLE users ( userId INTEGER, userName TEXT)";
        database.execSQL(query);
	}
	@Override
	public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
		String query;
		query = "DROP TABLE IF EXISTS users";
		database.execSQL(query);
        onCreate(database);
	}

	/**
	 * Inserts User into SQLite DB
	 * @param queryValues
	 */
	public void insertUser(HashMap<String, String> queryValues) {
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("userId", queryValues.get("userId"));
		values.put("userName", queryValues.get("userName"));
		database.insert("users", null, values);
		database.close();
	}

	/**
	 * Get list of Users from SQLite DB as Array List
	 * @return
	 */
	public ArrayList<HashMap<String, String>> getAllUsers() {
		ArrayList<HashMap<String, String>> usersList;
		usersList = new ArrayList<HashMap<String, String>>();
		String selectQuery = "SELECT  * FROM users";
	    SQLiteDatabase database = this.getWritableDatabase();
	    Cursor cursor = database.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	HashMap<String, String> map = new HashMap<String, String>();
	        	map.put("userId", cursor.getString(0));
	        	map.put("userName", cursor.getString(1));
                usersList.add(map);
	        } while (cursor.moveToNext());
	    }
	    database.close();
	    return usersList;
	}

}

Step 6: MainActivity.java – Home Screen Activity

MainActivity – Home Screen displays list of Users already there in SQLite DB.

MainAcitivty.java is well commented, so by reading the comments you will understand the meaning of it. In case, if you still have question discuss it right away.

package com.prgguru.example;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.app.AlarmManager;
import android.app.PendingIntent;
import android.app.ProgressDialog;
import android.content.Context;
import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.loopj.android.http.AsyncHttpClient;
import com.loopj.android.http.AsyncHttpResponseHandler;
import com.loopj.android.http.RequestParams;

public class MainActivity extends ActionBarActivity {
	// DB Class to perform DB related operations
	DBController controller = new DBController(this);
	// Progress Dialog Object
	ProgressDialog prgDialog;
	HashMap<String, String> queryValues;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		// Get User records from SQLite DB
		ArrayList<HashMap<String, String>> userList = controller.getAllUsers();
		// If users exists in SQLite DB
		if (userList.size() != 0) {
			// Set the User Array list in ListView
			ListAdapter adapter = new SimpleAdapter(MainActivity.this, userList, R.layout.view_user_entry, new String[] {
							"userId", "userName" }, new int[] { R.id.userId, R.id.userName });
			ListView myList = (ListView) findViewById(android.R.id.list);
			myList.setAdapter(adapter);
		}
		// Initialize Progress Dialog properties
		prgDialog = new ProgressDialog(this);
		prgDialog.setMessage("Transferring Data from Remote MySQL DB and Syncing SQLite. Please wait...");
		prgDialog.setCancelable(false);
		// BroadCase Receiver Intent Object
		Intent alarmIntent = new Intent(getApplicationContext(), SampleBC.class);
		// Pending Intent Object
		PendingIntent pendingIntent = PendingIntent.getBroadcast(getApplicationContext(), 0, alarmIntent, PendingIntent.FLAG_UPDATE_CURRENT);
		// Alarm Manager Object
		AlarmManager alarmManager = (AlarmManager) getApplicationContext().getSystemService(Context.ALARM_SERVICE);
		// Alarm Manager calls BroadCast for every Ten seconds (10 * 1000), BroadCase further calls service to check if new records are inserted in 
		// Remote MySQL DB
		alarmManager.setRepeating(AlarmManager.RTC_WAKEUP, Calendar.getInstance().getTimeInMillis() + 5000, 10 * 1000, pendingIntent);
	}

	// Options Menu (ActionBar Menu)
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

	// When Options Menu is selected
	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		// Handle action bar item clicks here. 
		int id = item.getItemId();
		// When Sync action button is clicked
		if (id == R.id.refresh) {
			// Transfer data from remote MySQL DB to SQLite on Android and perform Sync
			syncSQLiteMySQLDB();
			return true;
		}
		return super.onOptionsItemSelected(item);
	}

	// Method to Sync MySQL to SQLite DB
	public void syncSQLiteMySQLDB() {
		// Create AsycHttpClient object
		AsyncHttpClient client = new AsyncHttpClient();
		// Http Request Params Object
		RequestParams params = new RequestParams();
		// Show ProgressBar
		prgDialog.show();
		// Make Http call to getusers.php
		client.post("http://192.168.2.4:9000/mysqlsqlitesync/getusers.php", params, new AsyncHttpResponseHandler() {
				@Override
				public void onSuccess(String response) {
					// Hide ProgressBar
					prgDialog.hide();
					// Update SQLite DB with response sent by getusers.php
					updateSQLite(response);
				}
				// When error occured
				@Override
				public void onFailure(int statusCode, Throwable error, String content) {
					// TODO Auto-generated method stub
					// Hide ProgressBar
					prgDialog.hide();
					if (statusCode == 404) {
						Toast.makeText(getApplicationContext(), "Requested resource not found", Toast.LENGTH_LONG).show();
					} else if (statusCode == 500) {
						Toast.makeText(getApplicationContext(), "Something went wrong at server end", Toast.LENGTH_LONG).show();
					} else {
						Toast.makeText(getApplicationContext(), "Unexpected Error occcured! [Most common Error: Device might not be connected to Internet]",
								Toast.LENGTH_LONG).show();
					}
				}
		});
	}

	public void updateSQLite(String response){
		ArrayList<HashMap<String, String>> usersynclist;
		usersynclist = new ArrayList<HashMap<String, String>>();
		// Create GSON object
		Gson gson = new GsonBuilder().create();
		try {
			// Extract JSON array from the response
			JSONArray arr = new JSONArray(response);
			System.out.println(arr.length());
			// If no of array elements is not zero
			if(arr.length() != 0){
				// Loop through each array element, get JSON object which has userid and username
				for (int i = 0; i < arr.length(); i++) {
					// Get JSON object
					JSONObject obj = (JSONObject) arr.get(i);
					System.out.println(obj.get("userId"));
					System.out.println(obj.get("userName"));
					// DB QueryValues Object to insert into SQLite
					queryValues = new HashMap<String, String>();
					// Add userID extracted from Object
					queryValues.put("userId", obj.get("userId").toString());
					// Add userName extracted from Object
					queryValues.put("userName", obj.get("userName").toString());
					// Insert User into SQLite DB
					controller.insertUser(queryValues);
					HashMap<String, String> map = new HashMap<String, String>();
					// Add status for each User in Hashmap
					map.put("Id", obj.get("userId").toString());
					map.put("status", "1");
					usersynclist.add(map);
				}
				// Inform Remote MySQL DB about the completion of Sync activity by passing Sync status of Users
				updateMySQLSyncSts(gson.toJson(usersynclist));
				// Reload the Main Activity
				reloadActivity();
			}
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// Method to inform remote MySQL DB about completion of Sync activity
	public void updateMySQLSyncSts(String json) {
		System.out.println(json);
		AsyncHttpClient client = new AsyncHttpClient();
		RequestParams params = new RequestParams();
		params.put("syncsts", json);
		// Make Http call to updatesyncsts.php with JSON parameter which has Sync statuses of Users
		client.post("http://192.168.2.4:9000/mysqlsqlitesync/updatesyncsts.php", params, new AsyncHttpResponseHandler() {
			@Override
			public void onSuccess(String response) {
				Toast.makeText(getApplicationContext(),	"MySQL DB has been informed about Sync activity", Toast.LENGTH_LONG).show();
			}

			@Override
			public void onFailure(int statusCode, Throwable error, String content) {
					Toast.makeText(getApplicationContext(), "Error Occured", Toast.LENGTH_LONG).show();
			}
		});
	}

	// Reload MainActivity
	public void reloadActivity() {
		Intent objIntent = new Intent(getApplicationContext(), MainActivity.class);
		startActivity(objIntent);
	}
}

Make sure the IP address has been changed in post method with IP address of your machine:

client.post("http://192.168.2.4:9000/mysqlsqlitesync/getusers.php", params, new AsyncHttpResponseHandler() {

Step 7: SampleBC.java – Broadcast Receiver Class looks for Update in MySQL DB

package com.prgguru.example;

import org.json.JSONException;
import org.json.JSONObject;

import android.content.BroadcastReceiver;
import android.content.Context;
import android.content.Intent;
import android.widget.Toast;

import com.loopj.android.http.AsyncHttpClient;
import com.loopj.android.http.AsyncHttpResponseHandler;
import com.loopj.android.http.RequestParams;

public class SampleBC extends BroadcastReceiver {
	static int noOfTimes = 0;

	// Method gets called when Broad Case is issued from MainActivity for every 10 seconds
	@Override
	public void onReceive(final Context context, Intent intent) {
		// TODO Auto-generated method stub
		noOfTimes++;
		Toast.makeText(context, "BC Service Running for " + noOfTimes + " times", Toast.LENGTH_SHORT).show();
		AsyncHttpClient client = new AsyncHttpClient();
        RequestParams params = new RequestParams();
        // Checks if new records are inserted in Remote MySQL DB to proceed with Sync operation
        client.post("http://192.168.2.4:9000/mysqlsqlitesync/getdbrowcount.php",params ,new AsyncHttpResponseHandler() {
            @Override
            public void onSuccess(String response) {
                System.out.println(response);
                try {
                	// Create JSON object out of the response sent by getdbrowcount.php
                    JSONObject obj = new JSONObject(response);
                    System.out.println(obj.get("count"));
                    // If the count value is not zero, call MyService to display notification 
                    if(obj.getInt("count") != 0){
                    	final Intent intnt = new Intent(context, MyService.class);
                    	// Set unsynced count in intent data
                    	intnt.putExtra("intntdata", "Unsynced Rows Count "+obj.getInt("count"));
                    	// Call MyService
                    	context.startService(intnt);
                    }else{
                    	Toast.makeText(context, "Sync not needed", Toast.LENGTH_SHORT).show();
                    }
                } catch (JSONException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }

            @Override
            public void onFailure(int statusCode, Throwable error,
                String content) {
                // TODO Auto-generated method stub
                if(statusCode == 404){
                	Toast.makeText(context, "404", Toast.LENGTH_SHORT).show();
                }else if(statusCode == 500){
                	Toast.makeText(context, "500", Toast.LENGTH_SHORT).show();
                }else{
                	Toast.makeText(context, "Error occured!", Toast.LENGTH_SHORT).show();
                }
            }
        });
	}	
}

Step 8: MyService.java – Service Class that triggers Notification when there is an update in MySQL

package com.prgguru.example;

import android.app.Notification;
import android.app.NotificationManager;
import android.app.PendingIntent;
import android.app.Service;
import android.content.Context;
import android.content.Intent;
import android.os.IBinder;
import android.support.v4.app.NotificationCompat;
import android.widget.Toast;

public class MyService extends Service {
	int numMessages = 0;

	public MyService() {
	}

	@Override
	public IBinder onBind(Intent intent) {
		throw new UnsupportedOperationException("Not yet implemented");
	}

	@Override
	public void onCreate() {
		Toast.makeText(this, "Service was Created", Toast.LENGTH_LONG).show();

	}

	@Override
	public void onStart(Intent intent, int startId) {
		Toast.makeText(this, "Service Started", Toast.LENGTH_LONG).show();
		Intent resultIntent = new Intent(this, MainActivity.class);
		PendingIntent resultPendingIntent = PendingIntent.getActivity(this, 0,
				resultIntent, PendingIntent.FLAG_UPDATE_CURRENT);
		NotificationCompat.Builder mNotifyBuilder;
		NotificationManager mNotificationManager;
		mNotificationManager = (NotificationManager) getSystemService(Context.NOTIFICATION_SERVICE);
		// Sets an ID for the notification, so it can be updated
		int notifyID = 9001;
		mNotifyBuilder = new NotificationCompat.Builder(this)
				.setContentTitle("Alert")
				.setContentText("You've received new messages.")
				.setSmallIcon(R.drawable.ic_launcher);
		// Set pending intent
		mNotifyBuilder.setContentIntent(resultPendingIntent);
		// Set Vibrate, Sound and Light
		int defaults = 0;
		defaults = defaults | Notification.DEFAULT_LIGHTS;
		defaults = defaults | Notification.DEFAULT_VIBRATE;
		defaults = defaults | Notification.DEFAULT_SOUND;
		mNotifyBuilder.setDefaults(defaults);
		// Set the content for Notification 
		mNotifyBuilder.setContentText(intent.getStringExtra("intntdata"));
		// Set autocancel
		mNotifyBuilder.setAutoCancel(true);
		// Post a notification
		mNotificationManager.notify(notifyID, mNotifyBuilder.build());
	}

	@Override
	public void onDestroy() {
		Toast.makeText(this, "Service Destroyed", Toast.LENGTH_LONG).show();

	}
}

Step 9: Add Broadcast Receiver and Service Classes and also Internet permission in AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.prgguru.example"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="19" />
	<uses-permission android:name="android.permission.VIBRATE" />
    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.RECEIVE_BOOT_COMPLETED" />
    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme">
        <activity
            android:name="com.prgguru.example.MainActivity"
            android:label="@string/app_name" android:noHistory="true">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <!-- Register MyService -->
        <service
            android:name=".MyService"
            android:enabled="true" />
		<!--  Register BroadCase Receiver -->
        <receiver
            android:name=".SampleBC"
            android:process=":remote" >
            <intent-filter>
                <action android:name="android.intent.action.BOOT_COMPLETED" />
                <category android:name="android.intent.category.HOME" />
            </intent-filter>
        </receiver>
    </application>
</manifest>

Syncing process – Quick Explanation

MySQL Table user structure:

Columns

Id – Denotes User ID (Auto incremented)

Name – Name of the user entered via webapp (insertuser.php)

syncsts – Update status (Sync status) of the records between MySQL and SQLite. Initially it will be ‘0’ which means sync status is false.

  1. Initially New record is inserted in users table with syncsts as ‘0’
  2. SampleBC.java – Broadcast receiver class gets called for every 10 seconds which checks for any update in MySQL by making HTTP call to getdbrowcount.php which returns unsync row count. Since a new record is inserted (unsync row count is non zero), Broadcast receiver calls MyService.java which starts the service.
  3. MyService class triggers notification with number of unsynced rows
  4. When User clicks on the notification, it takes User to Application home screen where User can perform sync operation by clicking on the Sync action button.
  5. When Sync button is clicked, application makes HTTP call to getusers.php to get Users details as JSON
  6. Application extracts the User details from the JSON returned by getusers.php and updates the local SQLite table
  7. After successful update, the sync status of each User is created as JSON and posted to updatesyncsts.php
  8. updatesyncsts.php updates the syncsts column against the User to ‘1’

Error Handling

When the status code returned by getusers.php is other 200, which means an error might have occurred at server end. Error can be easily handled inside onFailure() method of AsyncHttpClient’s post method as shown below:

@Override
public void onFailure(int statusCode, Throwable error,String content) {
	// TODO Auto-generated method stub
	prgDialog.hide();
	if(statusCode == 404){
		Toast.makeText(getApplicationContext(), "Requested resource not found", Toast.LENGTH_LONG).show();
	}else if(statusCode == 500){
		Toast.makeText(getApplicationContext(), "Something went wrong at server end", Toast.LENGTH_LONG).show();
	}else{
		Toast.makeText(getApplicationContext(), "Unexpected Error occcured! [Most common Error: Device might not be connected to Internet]", Toast.LENGTH_LONG).show();
	}
}

[pglinkadssmall]

Download Source Code

Entire project is zipped and is available for download. Unzip the downloaded project and to import the project into eclipse, launch eclipse >> File >> Import.. >> Choose downloaded project(How to import android project in eclipse).

Download Source Code

*apk in Android  is the installation file similar to exe in windows.

If you feel this article is helpful and interesting please spread a word about it to your friends and colleagues by sharing the article in Facebook or Twitter.


Share

You are always welcome to provide your comments and feedback from comment box.

Reference
Android SQLite
[pgwriteforus]
[pgfeedback]

Related Articles

Author: Udhay

Share This Post On
468 ad
Join now to get more access to our Android Tutorials

Join now to get more access to our Android Tutorials

Once you Join, you will receive Mail containing latest Android Tutorials once a month !!

I promise I wont spam you !!

You have Successfully Subscribed!

Pin It on Pinterest

Shares