How to sync SQLite on Android to MySQL DB?

In this post, I am going to explain how to sync data present in Android SQLite database to remote MySQL DB. In most of the Android applications, the data present in the local SQLite database will be synced with remote DB in order to perform statistical operations at the server end, maintain backup of the offline data etc.

In such cases, offline data will be synced with remote DB when User explicitly hits ‘Sync’ button or asynchronously by the application itself (in particular interval) when Internet connectivity is available.

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 accepts Users’ names and store them in SQLite DB.
  • When ‘Sync’ action button in Action Bar is pressed, application syncs up data present in SQLite DB with remote MySQL DB.

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

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 'users' (
      'Id' int(11) NOT NULL,
      'Name' varchar(100) NOT NULL,
      PRIMARY KEY (`Id`)
    )

Build PHP Classes

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

sqlite mysql 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

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($Id,$User) {
        // Insert user into database
        $result = mysql_query("INSERT INTO users VALUES($Id,'$User')");

        if ($result) {
			return true;
        } else {
			if( mysql_errno() == 1062) {
				// Duplicate key - Primary Key Violation
				return true;
			} else {
				// For other errors
				return false;
			}            
        }
    }
	 /**
     * Getting all users
     */
    public function getAllUsers() {
        $result = mysql_query("select * FROM users");
        return $result;
    }
}

?>

insertuser.php:

<?php
include_once './db_functions.php';
//Create Object for DB_Functions clas
$db = new DB_Functions(); 
//Get JSON posted by Android Application
$json = $_POST["usersJSON"];
//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->storeUser($data[$i]->userId,$data[$i]->userName);
	//Based on inserttion, create JSON response
	if($res){
		$b["id"] = $data[$i]->userId;
		$b["status"] = 'yes';
		array_push($a,$b);
	}else{
		$b["id"] = $data[$i]->userId;
		$b["status"] = 'no';
		array_push($a,$b);
	}
}
//Post JSON response back to Android Application
echo json_encode($a);
?>

viewusers.php:

<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;
}
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;
}
</style>
<script>
function refreshPage(){
location.reload();
}
</script>
</head>
<body>
<center>
<div class="header">
Android SQLite and MySQL Sync Results
</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></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>
</tr>
<?php } ?>
</table>
<?php }else{ ?>
<div id="norecord">
No records in MySQL DB
</div>
<?php } ?>
<div class="refresh">
<button onclick="refreshPage()">Refresh</button>
</div>
</body>
</html>

Types of JSON

JSON posted to insertuser.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 returned by insertuser.php (after inserting Users in MySQL 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"}
]

Android Project

Step 1: Create Android Application Project

  • Create new android project [File >> New >> Android Application Project] with project name SQLiteMySQLSync
  • 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 two activities:

MainActivity – Home Screen of the Application (Launch screen)
NewUser – Insertion of User into SQLite takes place in the Activity

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">SQLiteMySQLSync</string>
    <string name="hello_world">Hello world!</string>
    <string name="action_settings">Settings</string>
	<string name="title">Users</string>
	<string name="plus">"+"</string>
	<string name="adduser">"Add User"</string>
	<string name="user">"User"</string>
	<string name="hint">"Enter User's Name"</string>
	<string name="save">"Save"</string>
	<string name="cancel">"Cancel"</string>
	<string name="adduseractivitylabel">"Add User Activity"</string>
</resources>

Create three 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="40dp"
        android:background="#cc0000"
        android:orientation="vertical" >

        <TextView
            android:id="@+id/textView1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="5dp"
            android:text="@string/title"
            android:textAppearance="?android:attr/textAppearanceLarge"
            android:textColor="#FFFFFF" />

        <Button
            android:id="@+id/button1"
            android:layout_width="41dp"
            android:layout_height="40dp"
            android:layout_alignParentRight="true"
            android:layout_alignParentTop="true"
            android:background="#ffd700"
            android:onClick="addUser"
            android:text="@string/plus"
            android:textColor="#cc0000"
            android:textSize="30sp" />

    </RelativeLayout>
     <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>

add_new_user.xml

Adding User screen layout

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#CCCCCC"
    android:orientation="vertical"
    android:paddingTop="1dp" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="#cc0000"
        android:padding="5dp"
        android:text="@string/adduser"
        android:textAppearance="?android:attr/textAppearanceLarge"
        android:textColor="#FFFFFF" />

       <RelativeLayout
     android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:background="#FFFFFF"
        android:orientation="vertical"
        android:padding="10dp" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:layout_marginLeft="24dp"
        android:layout_marginTop="30dp"
        android:text="@string/user" />

    <EditText
        android:id="@+id/userName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView1"
        android:layout_below="@+id/textView1"
        android:ems="10" android:hint="@string/hint">

        <requestFocus />
    </EditText>
    <Button
        android:id="@+id/btnadd"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/userName"
        android:layout_below="@+id/userName"
        android:layout_marginTop="32dp"
        android:text="@string/save"      
    	android:onClick="addNewUser" />
 <Button
        android:id="@+id/btnCancel"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/userName"
        android:layout_toRightOf="@+id/btnadd"
        android:layout_marginTop="32dp"
        android:text="@string/cancel"      
    	android:onClick="cancelAddUser" />

</RelativeLayout>

</LinearLayout>

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.

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;
import android.util.Log;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;

public class DBController  extends SQLiteOpenHelper {

	public DBController(Context applicationcontext) {
        super(applicationcontext, "androidsqlite.db", null, 1);
    }
	//Creates Table
	@Override
	public void onCreate(SQLiteDatabase database) {
		String query;
		query = "CREATE TABLE users ( userId INTEGER PRIMARY KEY, userName TEXT, udpateStatus 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("userName", queryValues.get("userName"));
		values.put("udpateStatus", "no");
		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>> wordList;
		wordList = 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));
                wordList.add(map);
	        } while (cursor.moveToNext());
	    }
	    database.close();
	    return wordList;
	}

	/**
	 * Compose JSON out of SQLite records
	 * @return
	 */
	public String composeJSONfromSQLite(){
		ArrayList<HashMap<String, String>> wordList;
		wordList = new ArrayList<HashMap<String, String>>();
		String selectQuery = "SELECT  * FROM users where udpateStatus = '"+"no"+"'";
	    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));
	        	wordList.add(map);
	        } while (cursor.moveToNext());
	    }
	    database.close();
		Gson gson = new GsonBuilder().create();
		//Use GSON to serialize Array List to JSON
		return gson.toJson(wordList);
	}

	/**
	 * Get Sync status of SQLite
	 * @return
	 */
	public String getSyncStatus(){
	    String msg = null;
	    if(this.dbSyncCount() == 0){
	    	msg = "SQLite and Remote MySQL DBs are in Sync!";
	    }else{
	    	msg = "DB Sync neededn";
	    }
	    return msg;
	}

	/**
	 * Get SQLite records that are yet to be Synced
	 * @return
	 */
	public int dbSyncCount(){
		int count = 0;
		String selectQuery = "SELECT  * FROM users where udpateStatus = '"+"no"+"'";
	    SQLiteDatabase database = this.getWritableDatabase();
	    Cursor cursor = database.rawQuery(selectQuery, null);
	    count = cursor.getCount();
	    database.close();
		return count;
	}

	/**
	 * Update Sync status against each User ID
	 * @param id
	 * @param status
	 */
	public void updateSyncStatus(String id, String status){
		SQLiteDatabase database = this.getWritableDatabase();	 
		String updateQuery = "Update users set udpateStatus = '"+ status +"' where userId="+"'"+ id +"'";
		Log.d("query",updateQuery);		
		database.execSQL(updateQuery);
		database.close();
	}
}

Step 6: MainActivity.java – Home Screen Activity

MainActivity – Home Screen displays list of Users already inserted 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.HashMap;

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

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

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;

	@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(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);
			//Display Sync status of SQLite DB
			Toast.makeText(getApplicationContext(), controller.getSyncStatus(), Toast.LENGTH_LONG).show();
		}
		//Initialize Progress Dialog properties
		prgDialog = new ProgressDialog(this);
		prgDialog.setMessage("Synching SQLite Data with Remote MySQL DB. Please wait...");
		prgDialog.setCancelable(false);
	}

	@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;
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		// Handle action bar item clicks here. The action bar will
		// automatically handle clicks on the Home/Up button, so long
		// as you specify a parent activity in AndroidManifest.xml.
		int id = item.getItemId();
		//When Sync action button is clicked
		if (id == R.id.refresh) {
			//Sync SQLite DB data to remote MySQL DB
			syncSQLiteMySQLDB();
			return true;
		}
		return super.onOptionsItemSelected(item);
	}
	//Add User method getting called on clicking (+) button
	public void addUser(View view) {
		Intent objIntent = new Intent(getApplicationContext(), NewUser.class);
		startActivity(objIntent);
	}

	public void syncSQLiteMySQLDB(){
		//Create AsycHttpClient object
		AsyncHttpClient client = new AsyncHttpClient();
		RequestParams params = new RequestParams();
		ArrayList<HashMap<String, String>> userList =  controller.getAllUsers();
		if(userList.size()!=0){
			if(controller.dbSyncCount() != 0){
				prgDialog.show();
				params.put("usersJSON", controller.composeJSONfromSQLite());
				client.post("http://192.168.2.4:9000/sqlitemysqlsync/insertuser.php",params ,new AsyncHttpResponseHandler() {
					@Override
					public void onSuccess(String response) {
						System.out.println(response);
						prgDialog.hide();
						try {
							JSONArray arr = new JSONArray(response);
							System.out.println(arr.length());
							for(int i=0; i<arr.length();i++){
								JSONObject obj = (JSONObject)arr.get(i);
								System.out.println(obj.get("id"));
								System.out.println(obj.get("status"));
								controller.updateSyncStatus(obj.get("id").toString(),obj.get("status").toString());
							}
							Toast.makeText(getApplicationContext(), "DB Sync completed!", Toast.LENGTH_LONG).show();
						} catch (JSONException e) {
							// TODO Auto-generated catch block
							Toast.makeText(getApplicationContext(), "Error Occured [Server's JSON response might be invalid]!", Toast.LENGTH_LONG).show();
							e.printStackTrace();
						}
					}

					@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();
						}
					}
				});
			}else{
				Toast.makeText(getApplicationContext(), "SQLite and Remote MySQL DBs are in Sync!", Toast.LENGTH_LONG).show();
			}
		}else{
				Toast.makeText(getApplicationContext(), "No data in SQLite DB, please do enter User name to perform Sync action", Toast.LENGTH_LONG).show();
		}
	}

}

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

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

Step 7: NewUser.java

Add User Activity which allows us to add User name into SQLite database. Create NewUser.java under com.prgguru.example and fill it with below code:

package com.prgguru.example;

import java.util.HashMap;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

public class NewUser extends Activity {
	EditText userName;
	DBController controller = new DBController(this);

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.add_new_user);
		userName = (EditText) findViewById(R.id.userName);
	}

	/**
	 * Called when Save button is clicked 
	 * @param view
	 */
	public void addNewUser(View view) {
		HashMap<String, String> queryValues = new HashMap<String, String>();
		queryValues.put("userName", userName.getText().toString());
		if (userName.getText().toString() != null
				&& userName.getText().toString().trim().length() != 0) {
			controller.insertUser(queryValues);
			this.callHomeActivity(view);
		} else {
			Toast.makeText(getApplicationContext(), "Please enter User name",
					Toast.LENGTH_LONG).show();
		}
	}

    /**
     * Navigate to Home Screen 
     * @param view
     */
	public void callHomeActivity(View view) {
		Intent objIntent = new Intent(getApplicationContext(),
				MainActivity.class);
		startActivity(objIntent);
	}

	/**
	 * Called when Cancel button is clicked
	 * @param view
	 */
	public void cancelAddUser(View view) {
		this.callHomeActivity(view);
	}
}

Step 8: Add Activities in AndroidManifest.xml

 <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>
          <activity
            android:name="com.prgguru.example.NewUser"
            android:label="@string/adduseractivitylabel" android:noHistory="true">
        </activity>

Step 9: Add Permission in AndroidManifest.xml

Don’t forget to add internet permission in AndroidManifest.xml:

    <!-- Permission: Allow application to connect to Internet -->
    <uses-permission android:name="android.permission.INTERNET" />

Syncing process – Quick Explanation

SQLite Table users structure:

Columns

userId – Denotes User ID (Auto incremented)
userName – Name of the user entered via NewUser activity
udpateStatus- Update status (Sync status) of the records in MySQL

  1. Initially New record is inserted in users table with updateStatus as ‘no’
  2. When Sync button is clicked, records with updateStatus as ‘no’ are picked, composes JSON out of the selected records and finally the JSON is posted to insertuser.php via http
  3. The insertuser.php decodes the incoming JSON and creates Array out of it. It then inserts array values into MySQL table and generate response JSON which contains insertion status (yes/no).
  4. Application reads the JSON response and update the users table with status values(yes/no) against the User ID

[pglinkadssmall1]

Error Handling

When the status code returned by insertuser.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();
	}
}

Demo

That’s all. It’s time to test our code.

Run the application using emulator or device by right clicking on the project >> Run as >> Android applicaiton >> Choose emulator or device.



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.

[pglinkadssmall]

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