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: Android Guru

Share This Post On
  • siddhu

    wonderful code

  • Indra

    Excellent man………. No words to describe your effort….

  • Clay Martin

    Thanks for the article. Well articulated data synching strategy.

  • Noomnim Paritawa

    updatesyncsts.php Error

    ………………………………….
    ………………..

  • amine

    first all thanks for this tutorial it was so benefit for me ; but i had some problems ; for example i have created the database at my local machine so i used 127.0.0.1 as an address for the POST but it didn’t work !!

  • William

    When i import in eclipse, i have several errors on it and also on sqlite to mysql
    How can i fix it please ??

  • Aisha

    hi , i m having an error after importing the source code..Please help

    The import android.support.v7.app cannot be resolved..

  • Please update your Android SDK to include latest support libraries. Do follow below link to update SDK with newly added support libraries like v4 support and v7 appcompat libraries.

    https://developer.android.com/tools/support-library/setup.html

  • Your Android SDK configured in Eclipse should have all latest support libraries. Please update it in case if it doesn’t have v4 and v7 support libraries.

    Follow this link for more details:

    https://developer.android.com/tools/support-library/setup.html

  • leteo

    i added unicode data on mysql but on android device not show right :(

  • Eric Lim

    Hi there, well im having an error on the php side, how can i fix it?

    Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘root’@’localhost’ (using password: YES) in D:xampphtdocsinprogressBurnthis(WAD)Sample(MySQLSQLiteSync)db_connect.php on line 19

    Please ignore the image, as i have already fixed it :)

    • Vishnu Sharma

      i Have same problem

  • Nik

    Hi do you really need the php stuff for this to work? or can you just do it with the android project? I interpret that the php website is just so you can check if synced

  • Eric Lim

    hi sir, it says that i might not connect to internet, please help..

  • Jade

    Hie Android guru I am having a problem when i hit for Synchronization ,I am using Android Studio.In gradle console it is displaying this Note: /Users/Baanashe/AndroidStudioProjects/SparklingOfflineModel/app/src/main/java/com/sync/sparklingofflinemodel/MyActivity.java uses or overrides a deprecated API.

    Note: Recompile with -Xlint:deprecation for details.

    then In LogCat it is Displaying this:

    08-19 07:09:27.180 2599-2599/com.sync.sparklingofflinemodel W/System.err﹕ at com.sync.sparklingofflinemodel.MyActivity.updateSQLite(MyActivity.java:128)

    08-19 07:09:27.180 2599-2599/com.sync.sparklingofflinemodel W/System.err﹕ at com.sync.sparklingofflinemodel.MyActivity$1.onSuccess(MyActivity.java:101

    If i click On (MyActivity.java:128) it goes to:
    // Extract JSON array from the response
    JSONArray arr = new JSONArray(response);
    System.out.println(arr.length());

    In MainActivity,Will be grateful to hear from you.

  • Aisha

    Hi, i m having a problem in update file…its not working even after the syn , syncsts is not changing in my database

  • Hi Aisha,

    Double check whether you changed IP address across the Java files. Add debug statements and and see where exactly the update is missing.

    Do let me know once you solve the issue.

    • Aisha

      hi , i am done with this problem. Can you please tell me how make pulling fix. i mean currently in my application manager this application is running then disappearing and then again running , can i make it run at background forever like whatsapp.
      kindly help

  • If you look at the last else block of onFailure method in MainActivity Java Class, I simply displayed “Device might not be connected to Internet” because it is the most common error that usually occur but we cannot make sure that internet connectivity is the only issue here. There are so many reasons for your code to enter onFailure block so please try to print statusCode in onFailure method and see what http response code you are getting from your local server.

    If the http response code is 403/407 (Forbidden/Proxy authentication required) please resolve the issue accordingly.

    Here is the last else block code snippet:

    else{ Toast.makeText(getApplicationContext(), “Unexpected Error occcured! [Most common Error: Device might not be connected to Internet]”, Toast.LENGTH_LONG).show();
    }

    • novie

      So, what is the problem if the toast keep showing “Unexpected Error occcured! [Most common Error: Device might not be connected to Internet”? I already follow your suggestion about response code 403/407.

  • Php website is needed here to sync remote MySQL to Android SQLite DB since Php is acting as interface in syncing Database.

    • Nik

      Brilliant thanks for explaining it to me

  • Try to debug and resolve the error. Post the error message you are getting so that I may help you.

  • Can you change 127.0.0.1 to your System IP address?

    Follow this link to get system IP address:

    http://windows.microsoft.com/en-in/windows/find-computers-ip-address#1TC=windows-7

  • Please check if JSON API is deprecated in your case. It just works fine in Eclipse.

  • Please make sure the JSON composed by Php is taking care of creating valid unicode date.

    This link may help:

    http://www.sitepoint.com/bringing-unicode-to-php-with-portable-utf8/

  • Neji

    Hi Android Guru. you are really the best. Pls can u post tutorial of how to sync SQLITE DB with MS SQL Server using .NET Technologies. Thanks

    • Sure Neji. Will write a post on it and let you know.

  • Aminda127

    Hi. Im getting below error on my log and app gets crash. Please help with it

  • As I told many times here, please print response code and see what response code you are getting from server. Even you can try to print the stacktrace and see the place where exactly the error occurs.

  • Agustinus

    Hi, i’m getting many error when try to run, please see on image, how to fixed it?

  • Mohan Rajan

    thanks a lot “programmer Guru” it works perfectly!!!!!!!

  • Welcome. Spread a word about this blog to your friends and colleagues.

    • Mohan Rajan

      Sir,how can I do this for multiple users who access the same mysql database ,Please help.

  • Please update Android SDK so that v4 and v7 libraries will be included automatically.

  • Mohan Rajan

    how can we do this for multiple users accessing the same mysql database, share your idea or samples.

    • Stacey Gardner

      Did you ever find out a solution to this? I am looking for the same thing.

  • Saravana Kumar

    Hi, your code works for me, but I want multiple users to get the notifications simultaneously while synchronizing mysql with sqlite. Help me with this

  • shra03

    09-23 12:32:15.487: W/System.err(1675): org.json.JSONException: Value <br of type java.lang.String cannot be converted to JSONObject

    do you know.. what is wrong here…?

    • You are trying to convert String to JSON object which throw JSON Exception. Fix it.

  • Mihagin

    This code looks great, exactly what I’m needing help with :D! I’d be really interested to read the java and .net versions if you are still planning on doing them :)

  • Alan Gordon

    Hi, I am having some problems where it just looked on BC Service running for X times then saying error occured. My database and fileserver is with HTTPS, does this mean I cannot connect to it through the app, or how do I do this? I am not getting inside the on succes method at all.

    • Avashiva

      Hello Alan I’m having the same problem as you BC Service running and then error occurred. Can you tell me how you solved it?

      Kind regards

  • Manfred

    Hi, thanks for your code, bur i have a few problems.

    1st image) That is when i start the app (When 1 row inserted en bd). It doesn’t appears the new row logo in the notifications.
    2sd image) That is when i touch reload bottom, i know where error jumps, and i print that error.
    public void onFailure(int statusCode, Throwable error, String content) {

    statusCode = 0. What problem is it?

    PLEASE HELP ME T_T

  • Glad to hear that it helped you. Sure will write posts on Java and Dot Net as well.

  • Yes it will throw error because the connection made from Android App to Server is unsecured one.

    This link may answer your questions – https://developer.android.com/training/articles/security-ssl.html

    • Alan Gordon

      I was able to fix this perfectly, but now I am looking to have a bi directional sync. Where boththe SQLite and MySQL databases are kept perfectly up to date but the Android Application only ever reads from the local SQLite database. Do you have any guidance on how I would achieve this, I have used this tutorial as a proof of concept that I can indeed read from my database on my device.

      My idea is to use the SQLite database primarily for the App but updates can be pushed to the database using the MySQL database. This means that I wont ever need to update my app when I want to perform any updates to my database.

      Thanks

      • Asad Ali

        bro how did u solved it,,, i have the same problem..
        help me then i will work on having both mysql and sqlite synchronized…

  • Please check if below steps are taken care.

    1. Did you enable internet in your Android phone?
    2. Did you connect both Android phone and Web server to the same LAN Internet connection?
    3. Was web server up and running fine?

    You should be able to see notification when new row is inserted in MySQL DB if above said steps are followed.

  • Refer this tutorial for Syncing data in SQLite with MySQL DB.

    http://programmerguru.com/android-tutorial/how-to-sync-sqlite-on-android-to-mysql-db/

    • Alan Gordon

      I am looking for a combination of both tutorials I believe. I want the Sync to be as written in this tutorial, Like I add something to the MySQL database and it appears in the SQLite database, although if i delete something in the MySQL database it doesn’t delete it in the SQLite database. I want to be able to delete things from the SQLite database by removing it from the MySQL database and then hitting Sync.

  • Jonas

    Hello i am i successfully managed to launch you projects with DB this

    “Remote MySQL DB to SQLite” and other SQLite on Android to MySQL. But my purpose have app which can send and retrieve information from database. Could you explain how to get it? cause i almost don’t understand php at all

  • Hello,
    at first thanks for the tutorial, I guess yours is the best in the WWW. 😉
    But I don’t get the library working. My Android Studio doesn’t find “public void onSuccess(String response) {” and the onFailure-Method isn’t available, too. If I want to override the AsyncHttpResponseHandler behavior, I have to call the function as ” public void onSuccess(int statusCode, Header[] headers, byte[] responseBody) {”
    Is their maybe a change in the library? I load it like this: compile ‘com.loopj.android:android-async-http:1.4.5’
    Thanks in advance.

    Martin

    UPDATE:
    Hehe, yes there was the error, bringing this gradle line compile ‘com.loopj.android:android-async-http:1.4.5’ to compile ‘com.loopj.android:android-async-http:1.4.4’ solved the issue. Now the IDE says this mother is deprecated, but I don’t care and will give the whole story a new try.

    Btw Guru: Thanks for uploading the whole project, so your readers could follow your steps exactly. I’ll put your site to my Firefox<3Favorites :)

  • Glad that you got benefited by my article.
    Thanks !

  • Vishnu Sharma

    Hi there, well im having an error on the php side, how can i fix it?

    Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘root’@’localhost’ (using password: YES) in D:xampphtdocsMySQLSQLiteSyncdb_connect.php on line 19

  • Configure password for MySQL server. Otherwise take help from below link to access MySQL without password:

    http://stackoverflow.com/questions/23230729/warning-mysql-connect-access-denied-for-user-rootlocalhost-using-passw

  • Configure password for MySQL server. Otherwise try to access DB without password, take help from below link:

    http://stackoverflow.com/questions/23230729/warning-mysql-connect-access-denied-for-user-rootlocalhost-using-passw

  • Rajal Nigam

    Hi Android Guru… can you please provide me with the best sample code to get the data from Server MySQL table using JSON and preview in a List- view control. Thanks in advance.

  • I don’t find time in writing article these days due to hectic work. Will try for sure. If you find the way, don’t hesitate to post it as Guest post in my blog.

  • solosimpi

    Muchas gracias Amigo, thak you for this post :3

  • Misha

    Hi! Very cool guide, but when i had passed all this steps, several errors appear in java code((( Help please!!!

    1. For this string in MainActivity :
    DBController controller = new DBController(this); –

    error: DBController (android.content.Context) in DBController cannot be applied to …..MainActivity.

    2.For this string in MainActivity :
    ListAdapter adapter = new SimpleAdapter(MainActivity.this, userList, R.layout.activity_view_user_entry, new String[]

    error: SimpleAdapter() in SimpleAdapter cannot be applied to …..MainActivity.

    3. same for ProgressDialog.

    4. For all strings with “getApplicationContext” system shows:

    cannot resolve method “getApplicationContext”

    5. forr AsyncHttpResponse:

    error: Class ‘Anonymous class derived from AsyncHttpResponseHandler’ must ether be declared abstract or implement absract method ‘ onFailure(int, Header…..etc… in ‘AsyncHttpResponseHandler’

    Thank a lot!!!!

  • Jeppe

    Hi, great tutorial! I’m trying to upgrade my current method, but I’ve used the AsyncTask so far.. Eclipse doesn’t seem to recognize the AsyncHttpClient.. Also, is it possible to add the onSucces to AsyncTask? Thanks in advance. :-)

  • Anonymous

    how can I do this for multiple users who access the same mysql database ,Please help!!!!!

  • Михаил Бахрах

    it’s great!thanks a lot!

  • Stacey Gardner

    Thanks so much for your tutorials! I was successfully able to get a bi-directional sync by using your two tutorials. Yaay!

  • Costa

    It would be very useful to implement a method to update already inserted records in sqlite, if was updated in remote database. It would make this post complete. Can you see a quick update with it?

    Thank you for the above

  • AsyncTask is only for performing asynchronous tasks. You cannot use onSuccess in AsyncTask.

  • Welcome.

  • Hi Stacey,

    Would you care to share your code so that I will post it as new post which will help others?
    Thanks.

    • Maytham Fahmi

      Cool article, I would agree with you if Stacey would be sharing his knowledge.

  • Avashiva

    Really nice code! I’ve implemented on my app works amazing.

    But now I’ve tried to modify it for it to be synced with several devices and I don’t know a good way to do it. The issue is that after 1 update they value “syncts” on the data base change to 1 so the other devices don’t “need” to update.
    Any suggestions?

    Thanks

  • phan piseth

    /* My Override do not like you and can not work*/
    client.post(“url”, params, new AsyncHttpResponseHandler() {

    @Override
    public void onFailure(int i, org.apache.http.Header[] headers, byte[] bytes, Throwable throwable) {

    }

    @Override
    public void onSuccess(int i, org.apache.http.Header[] headers, byte[] bytes) {

    }
    });

  • Kehinde Adeoya

    Hi, i have followed your tutorial which is very intuitive but i keep getting this error “Unexpected error, ….. user might not be connected to the internet”. what I did is just to import the project, copy the php files into htdocs file of XAMPP, compile in Eclipse and deploy into Bluestacks, to my surprise it does not sync. what shall I do?

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