How to Sync SQLite Database on Android Phone with MySQL Database on Server

How to sync SQLite database on Android phone with MySQL database on server?

Create a webservice (REST is probably best) and serialize your SQLite/MySQL data and PUT/POST/GET it to/from your web service. This will give you a nice layer of abstraction in case you decide to switch from MySQL to something else server side.

How to sync data between mysql and sqlite?

Method #1 : Using Sync Adapter

You can sync the device and a server using a Sync Adapter in android. See the developer documents.

Creating a Sync Adapter

Basically this requires you to extend your class with AbstractThreadedSyncAdapter where you write your sync logic, create some xml files and a Content Provider to share your data.

With this method you dont have to handle network availability. All the work is basically done by android.

This is an android sample I wrote using AbstractThreadedSyncAdapter to sync data between device and a server. This is its server side written in Java using Servlet. Please have a look.

Method #2 : Using a simple API

If you don't need all that complexity, you can just create an API that both accepts the data from the device and outputs the data on the MySQL database. You will need to write some logic to sync the data.

Good luck :)

Android - Best way to sync SQLite with MySQL

you could use volley library by google or any alternative libraries, it depends on how you want to send the data, the best approach is that you use JSON to make your life easier, get the data from sqlite that you like to sync with your backend and send it over JsonObjectRequest using volley, for example your request can look like this

jsonObjectRequest postForm = new JsonObjectRequest(Request.Method.POST, URL, YourJsonData, 
    new Response.Listener<JSONObject>() {
        @Override
        public void onResponse(JSONObject response) {
            // here you can get your response.
        }
    }, 
    new Response.ErrorListener() {
        @Override
        public void onErrorResponse(VolleyError error) {
            // here you can tell there is something went wrong.
        }
    });

u could add a new value which indicates whether the value has been sync or no from your local database. for example, lets say you have a table called student and this table has three columns which are ID, NAME and synced in above code when your response return success update that row synced column with true|false which indicates whether this row synced with your backend or no. and to get the data from your database you should do something like this.

public String getStudents() {
    List<Student> students = new ArrayList<Student>();
    String query = "SELECT * FROM student WHERE synced = 0";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    if (cursor.moveToFirst()) {
        do {
            Student st = new Student();
            st.setId(cursor.getString(cursor.getColumnIndex(ID)));
            st.setName(cursor.getString(cursor.getColumnIndex(NAME)));
            st.setSynced(cursor.getInt(cursor.getColumnIndex(SYNCED)));
            students.add(st);
        } while (cursor.moveToNext());
    }
    db.close();
    return new Gson().toJson(students);
}

---

Related Topics