Synchronize MySQL to SQLite in Android Studio

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 * FROM tokenid WHERE tokenid = '$tokenid'";
if( ! mysqli_num_rows($conn, $sqlcheckadded) > 0){
$sql = "INSERT INTO tokenid (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 INTO messages (Title, Message) VALUES ('$title', '$message')";
if (mysqli_query($conn, $sql)){
echo "Message Saved Successfully";
$sqln = "SELECT * FROM tokenid";
$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 * FROM messages WHERE Synchstatus = 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 = "UPDATE messages Set Synchstatus = 0 WHERE ID = '$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