This tutorial describes how to Synchronize MySQL to SQLite in Android Studio. I have designed the tutorial as simple chat app between web page and android application. In the following image, When User 1 send message from web or android device to the server, the message will be saved to MySQL database. At the same time Firebase notification will be send to the receiver (user 2). On message receive at user 2 device the app will collect message from MySQL and save it permanently to SQLite database.

Synchronize MySQL to SQLite in Android uses WAMP server as local host. Also, I designed simple web page to send message and notification to MySQL database. I have also used the Broadcast Receiver for internet connection to synchronize messages to SQLite database. The tutorial also depends on Firebase cloud messaging. So that when the user who receive the notification, Then all messages will be synchronized by onReceive method of firebase messaging service.
Synchronize MySQL to SQLite Tutorial Steps
Local Server and MySQL Database
Firstly, we need to install local server. In the tutorial video I am using WAMP server. The next link is the official link for installing WAMP server.
https://www.wampserver.com/en/
Then, open the server and wait until it`s tray icon become green. After that, open your browser and type (localhost/phpmyadmin) in address bar and use “root” as user name and leave password empty and click Go. Next, create a new database and users table.


You need to create a new android studio project. after gradle synch in android studio go to gradle file and add the following dependencies.
implementation 'androidx.cardview:cardview:1.0.0' implementation 'androidx.recyclerview:recyclerview:1.2.0-alpha04' implementation 'com.google.android.material:material:1.3.0-alpha01' implementation 'com.android.volley:volley:1.1.1'
The next step is to connect your project to firebase, you can you this link to see How to Connect Android Studio Project to Firebase.
In a new text document create simple web page to send messages. You can copy the following codes to files in WWW directory.
“conn.php” file by the following code
<?php $db_name = "synchmysql";
$username = "root";
$passowrd = "";
$servername = "127.0.0.1";
$conn = mysqli_connect($servername,$username ,$passowrd,$db_name);
mysqli_set_charset($conn, "utf-8"); ?>
“sendtokenid.php” file as following
<?php require "conn.php"; $tokenid = $_POST["tokenid"]; if($conn){ $sqlcheckadded = "SELECT * FROMtokenid
WHEREtokenid
= '$tokenid'"; if( ! mysqli_num_rows($conn, $sqlcheckadded) > 0){ $sql = "INSERT INTOtokenid
(tokenid
) VALUES ('$tokenid')"; if (mysqli_query($conn, $sql)){ echo "Token ID saved successfully"; } else{ echo "Token ID not saved"; } } } else{ echo "Connection Error"; }?>
The next file is “sendmessage.php file” this file requires your firebase project server key.
Go to firebase console and select your project, then click on project setting button (see following image).


You are a hero developer. Select on cloud messaging tab and copy the sever key and past it the following php script as ($serverKey).
<?php require "conn.php"; $title = $_POST['title']; $message = $_POST['message']; $pathtofcm = 'https://fcm.googleapis.com/fcm/send'; $serverkey = "past the server key here"; if ($conn){ $sql ="INSERT INTOmessages
(Title
,Message
) VALUES ('$title', '$message')"; if (mysqli_query($conn, $sql)){ echo "Message Saved Successfully"; $sqln = "SELECT * FROMtokenid
"; $resultn = mysqli_query($conn, $sqln); $headers = array('Authorization:key=' .$serverkey, 'Content-Type:application/json'); $key = array(); while ($row = mysqli_fetch_row($resultn)){ array_push($key, $row[0]); } $fields =array('registration_ids'=>$key, 'data'=>array('title'=>$title,'message'=>$message,'name'=>$message)); $payload = json_encode($fields); $curl_session = curl_init(); curl_setopt($curl_session, CURLOPT_URL, $pathtofcm); curl_setopt($curl_session, CURLOPT_POST, true); curl_setopt($curl_session, CURLOPT_HTTPHEADER, $headers); curl_setopt($curl_session, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl_session, CURLOPT_SSL_VERIFYPEER, false); curl_setopt($curl_session, CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4); curl_setopt($curl_session, CURLOPT_POSTFIELDS, $payload); $result = curl_exec($curl_session); curl_close($curl_session); } else{ echo "Message is not saved"; } } else{ echo "COnnection Error"; }?>
Synchronizemessages.php script is required now.
<?php require "conn.php"; if($conn){ $sql = "SELECT * FROMmessages
WHERESynchstatus
= 1"; $collectunsynchronized = mysqli_query($conn, $sql); $messageresponce = array(); while ($row = mysqli_fetch_array($collectunsynchronized )) { array_push($messageresponce, array('title'=>$row[1],'message'=>$row[2])); $id = $row[0]; $sqlup = "UPDATEmessages
SetSynchstatus
= 0 WHEREID
= '$id'"; mysqli_query($conn, $sqlup); } echo json_encode(array('message_response'=>$messageresponce)); } else{ echo "Connection Error"; } mysqli_close($conn);?>
Next, Go to android studio to build the app. In mainactivity.xml file past the following
<?xml version="1.0" encoding="utf-8"?> <LinearLayout 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" android:layout_width="match_parent" android:layout_height="match_parent" tools:context=".MainActivity"> <androidx.recyclerview.widget.RecyclerView android:layout_width="match_parent" android:layout_height="match_parent" android:id="@+id/recyclerView" /> </LinearLayout>
Create a new XML layout file and name it messages_rowlayout and past the following XML.

<?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="wrap_content" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools"> <androidx.cardview.widget.CardView android:layout_width="match_parent" android:layout_height="wrap_content" android:elevation="8dp" app:cardCornerRadius="8dp" tools:targetApi="lollipop" android:background="@android:color/darker_gray" android:layout_marginTop="10dp" > <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical" android:background="@android:color/holo_green_dark" android:padding="8dp"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Title" android:id="@+id/title" android:textColor="@android:color/black" android:paddingLeft="10dp"/> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Message" android:id="@+id/message" android:textColor="@android:color/black" android:paddingLeft="10dp"/> </LinearLayout> </androidx.cardview.widget.CardView> </LinearLayout>
Create a new java file and name it MessagesData and past the following code.
public class MessageData { String TITLE; String Message; public MessageData(String TITLE, String message) { this.TITLE = TITLE; Message = message; } public String getTITLE() { return TITLE; } public void setTITLE(String TITLE) { this.TITLE = TITLE; } public String getMessage() { return Message; } public void setMessage(String message) { Message = message; } }
New java file and name it MessagesRecyclerAdapte and use this code.
public class MessagesRecylerAdapter extends RecyclerView.Adapter<MessagesRecylerAdapter.MyViewHolder> { Context context; ArrayList<MessageData> arrayList; public MessagesRecylerAdapter(Context context, ArrayList<MessageData> arrayList) { this.context = context; this.arrayList = arrayList; } @Override public MyViewHolder onCreateViewHolder(ViewGroup parent, int viewType) { View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.messages_rowlayout,null); return new MyViewHolder(view); } @Override public void onBindViewHolder(MyViewHolder holder, int position) { MessageData messageData = arrayList.get(position); holder.Title.setText(messageData.getTITLE()); holder.Message.setText(messageData.getMessage()); } @Override public int getItemCount() { return arrayList.size(); } public class MyViewHolder extends RecyclerView.ViewHolder{ TextView Title,Message; public MyViewHolder(View itemView) { super(itemView); Title= (TextView)itemView.findViewById(R.id.title); Message=(TextView)itemView.findViewById(R.id.message); } } }
DBStructure jave class as following.
public class DbStrings { public static final String TABLE_NAME = "messages"; public static final String TITLE = "title"; public final static String MESSAGE = "message"; public static final String DATABASE_NAME = "Messages"; public static final String UI_SYNCHRONIZE_MESSAGE = "suecal.com.synchronizemysqltosqlitedatabase.UI_SYNCHRONIZE_SQLITE"; }
Then create Messages SQLiteOpenHelper class by the following code
public class MessagesSQliteOpenHelper extends SQLiteOpenHelper { private static final Integer DATABASE_VERSION = 1; private static final String Create_MessagesTable = "create table "+DbStrings.TABLE_NAME+ "(ID INTEGER PRIMARY KEY AUTOINCREMENT, "+DbStrings.TITLE+" TEXT, "+DbStrings.MESSAGE+" TEXT)"; private static final String DROP_MESSAGES_TABLE = "DROP TABLE IF EXISTS "+DbStrings.TABLE_NAME; public MessagesSQliteOpenHelper(Context context) { super(context, DbStrings.DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(Create_MessagesTable); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(DROP_MESSAGES_TABLE); onCreate(db); } public Cursor ReadMessages (SQLiteDatabase database){ String[] projections = {DbStrings.TITLE,DbStrings.MESSAGE}; return database.query(DbStrings.TABLE_NAME,projections,null,null,null,null,null); } public void SaveMessage(String title,String message, SQLiteDatabase database){ ContentValues contentValues = new ContentValues(); contentValues.put(DbStrings.TITLE,title); contentValues.put(DbStrings.MESSAGE,message); database.insert(DbStrings.TABLE_NAME,null,contentValues); } }
Firebase Messaging Service
Now, We need to create Firebase Messaging Service so create a new java file and name it MyFirebaseMessagingService. This class will inherits FirebaseMessagingService.
This inheritance needs to override onNewToken method. This method will be triggered when the app token id has been changed. The token id changes in each time the use install the app or upgrade it. so in this method we will save the token id as shared preferences and in main activity specifically in onCreate method we will make code that save or update the token it.
Also with this service will overrides another method. onMessageReceived Method, in this method we will create the notification and make the code that synchronize the messages from MySQL database to SQLite database. The next code is Singleton Class to initial volley and the second code is for Messaging Service class.
public class MySingleton { private static MySingleton mInstance; private RequestQueue requestQueue; private static Context mContext; private MySingleton(Context context){ mContext= context; requestQueue = getRequestQueue(); } public RequestQueue getRequestQueue(){ if(requestQueue == null){ requestQueue =Volley.newRequestQueue(mContext.getApplicationContext()); } return requestQueue; } public static synchronized MySingleton getmInstance(Context context){ if(mInstance ==null){ mInstance= new MySingleton(context); } return mInstance; } public <T> void addToRequestQueue(Request<T> request){ requestQueue.add(request); } }
public class MyFireBaseMessagingService extends FirebaseMessagingService { @Override public void onNewToken(@NonNull String s) { super.onNewToken(s); String Token_ID = FirebaseInstanceId.getInstance().getToken(); Log.d("TOKEN_ID",Token_ID); SharedPreferences sharedPreferences = getApplicationContext().getSharedPreferences(getResources().getString(R.string.FCM_Pref), Context.MODE_PRIVATE); SharedPreferences .Editor editor = sharedPreferences.edit(); editor.putString(getResources().getString(R.string.FCM_TOKEN),Token_ID); editor.commit(); } MessagesSQliteOpenHelper messagesSQliteOpenHelper; @Override public void onMessageReceived(RemoteMessage remoteMessage) { String SYNCHRONIZE_URL = "http://10.0.2.2/synchronizemessages.php"; Map<String,String> data = remoteMessage.getData(); String title= data.get("title"); String message = data.get("message"); String name = data.get("name"); if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) { int importance = NotificationManager.IMPORTANCE_HIGH; NotificationChannel channel = new NotificationChannel("Channel_ID", "Messages", importance); channel.setDescription("SynchronizeMessages"); // Register the channel with the system; you can't change the importance // or other notification behaviors after this NotificationManager notificationManager = getSystemService(NotificationManager.class); notificationManager.createNotificationChannel(channel); } Uri path = RingtoneManager.getDefaultUri(RingtoneManager.TYPE_NOTIFICATION); Notification newMessageNotification = new NotificationCompat.Builder(this, "Channel_ID") .setSmallIcon(android.R.mipmap.sym_def_app_icon) .setContentTitle(title) .setContentText(message) .setSound(path) .build(); NotificationManagerCompat notificationManager = NotificationManagerCompat.from(this); notificationManager.notify(0, newMessageNotification); StringRequest SynchronizeMessage = new StringRequest(Request.Method.POST, SYNCHRONIZE_URL, new Response.Listener<String>() { @Override public void onResponse(String response) { try { JSONObject jsonObject = new JSONObject(response); JSONArray jsonArray = jsonObject.getJSONArray("message_response"); int count = 0; while (count < jsonArray.length()){ JSONObject jo = jsonArray.getJSONObject(count); SaveMessage(jo.getString("title"),jo.getString("message")); count++; } Toast.makeText(getApplicationContext(),"New Message Recieved",Toast.LENGTH_LONG).show(); sendBroadcast(new Intent(DbStrings.UI_SYNCHRONIZE_MESSAGE)); } catch (JSONException e) { e.printStackTrace(); } } }, new Response.ErrorListener() { @Override public void onErrorResponse(VolleyError error) { Toast.makeText(getApplicationContext(), error.toString(), Toast.LENGTH_SHORT).show(); } }); MySingleton.getmInstance(getApplicationContext()).addToRequestQueue(SynchronizeMessage); } private void SaveMessage(String title,String message){ messagesSQliteOpenHelper = new MessagesSQliteOpenHelper(getApplicationContext()); SQLiteDatabase database = messagesSQliteOpenHelper.getWritableDatabase(); messagesSQliteOpenHelper.SaveMessage(title,message,database); messagesSQliteOpenHelper.close(); } }
Finally, Main Activity java file
public class MainActivity extends AppCompatActivity { ArrayList<MessageData> arrayList = new ArrayList<>(); RecyclerView recyclerView; RecyclerView.LayoutManager layoutManager; RecyclerView.Adapter adapter; String TOKEN_ID_URL = "http://10.0.2.2/sendtokenid.php"; BroadcastReceiver broadcastReceiver; MessagesSQliteOpenHelper messagesSQliteOpenHelper; String SYNCHRONIZE_URL = "http://10.0.2.2/synchronizemessages.php"; @Override protected void onStart() { super.onStart(); registerReceiver(broadcastReceiver,new IntentFilter(DbStrings.UI_SYNCHRONIZE_MESSAGE)); } @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); recyclerView= (RecyclerView)findViewById(R.id.recyclerView); layoutManager = new LinearLayoutManager(MainActivity.this); recyclerView.setHasFixedSize(true); recyclerView.setLayoutManager(layoutManager); adapter = new MessagesRecylerAdapter(MainActivity.this, arrayList); recyclerView.setAdapter(adapter); ReadMessages(); SharedPreferences sharedPreferences = getApplicationContext().getSharedPreferences(getResources().getString(R.string.FCM_Pref), Context.MODE_PRIVATE); final String token_id= sharedPreferences.getString(getResources().getString(R.string.FCM_TOKEN),""); Toast.makeText(this, token_id, Toast.LENGTH_SHORT).show(); StringRequest SendTokenID = new StringRequest(Request.Method.POST, TOKEN_ID_URL, new Response.Listener<String>() { @Override public void onResponse(String response) { Toast.makeText(MainActivity.this, response, Toast.LENGTH_LONG).show(); } }, new Response.ErrorListener() { @Override public void onErrorResponse(VolleyError error) { Toast.makeText(MainActivity.this, error.toString(), Toast.LENGTH_LONG).show(); } }){ @Override protected Map<String, String> getParams() throws AuthFailureError { Map<String,String> param = new HashMap<String, String>(); param.put("tokenid",token_id ); return param; } }; MySingleton.getmInstance(MainActivity.this).addToRequestQueue(SendTokenID); broadcastReceiver = new BroadcastReceiver() { @Override public void onReceive(final Context context, Intent intent) { ReadMessages(); } }; registerReceiver(broadcastReceiver,new IntentFilter(DbStrings.UI_SYNCHRONIZE_MESSAGE)); } private void ReadMessages(){ arrayList.clear(); messagesSQliteOpenHelper = new MessagesSQliteOpenHelper(MainActivity.this); SQLiteDatabase database = messagesSQliteOpenHelper.getReadableDatabase(); Cursor cursor = messagesSQliteOpenHelper.ReadMessages(database); while (cursor.moveToNext()){ String title = cursor.getString(cursor.getColumnIndex(DbStrings.TITLE)); String messgae= cursor.getString(cursor.getColumnIndex(DbStrings.MESSAGE)); MessageData messageData = new MessageData(title,messgae); arrayList.add(messageData); } adapter.notifyDataSetChanged(); } }
Thank You
Hello!!
Thanks for your awesome examples, they are just great.
I was following your “tutorial” for the SQLite and MySQL synchronization but I am having some problems, mainly with changes in the implementations of some of the process due to deprecation and upgrades of the libraries.
May I have the source code of a working example so I can compare what I am doing wrong?
I have a 16 tables database, so far I have been able to contact and select rows from MySQL using your processes but when I tried to get the SharedPreferences it returns me empty strings and when I try to get the Broadcast or send messages it just gets empty values.
My APP should work in an intranet but will use Firebase for the synchronization, at least there is another way to do the broadcasting.
If possible, thank you in advance