In this post I will show you how you can access MySQL from Android.

This post will be a continuation from the previous post on creating and using your own hosted MySQL database. You can view it from the link below.

Create And Use Your Own Hosted MySQL Databases

For a full version of a sample project featuring server side scripts and a more in depth Android client UI, view this post below.

MySQL Communication With Android

Accessing MYSQL Server From Android

Setup

This project is created in a test environment on a local machine running Windows with IIS. To access MySQL from Android, we are using PHP scripts on the web server.

Code

AndroidManifest.xml

First is adding the internet permission to your AndroidManifest.xml.

    <uses-permission android:name="android.permission.INTERNET" />

build.gradle

Then we will add a few dependencies that we need. I am using Retrofit to do the request. Inside your build.gradle ( module )

    //retrofit
    implementation(libs.retrofit)
    //GSON
    implementation(libs.converter.gson)

libs.versions.toml

Inside your libs.versions.toml.

[versions]
...

retrofit = "2.11.0"
converterGson = "2.10.0"
...

[libraries]
...
retrofit = { module = "com.squareup.retrofit2:retrofit", version.ref = "retrofit" }
converter-gson = { module = "com.squareup.retrofit2:converter-gson", version.ref = "converterGson" }

...

Project Structure

Right off the main package, we will create a new package to keep things organized. I named it retrofit. This is where we will keep our Retrofit classes.

com.example.sample.retrofit

RetrofitHelper

Inside the above package, we will create a RetrofitHelper class. The baseUrl needs to end in /. For my case, the baseUrl is the IP address of my localhost that is hosting the MySQL database and IIS.

object RetrofitHelper {
    val baseUrl = "<server_address>/"
    fun getInstance(): Retrofit {
        return Retrofit.Builder()
            .baseUrl(baseUrl)
            .addConverterFactory(GsonConverterFactory.create())
            .build()
    }
}

AccessAPI

Next we will create an AccessAPI in the same package. The @GET is the page that I am querying to get the data from. The data will be created via PHP in json format.

Note

The @GET could be used to pass an actual GET parameter when querying a server.

Ex. index.php?link=www.google.com the GET parameter is link

interface AccessApi {
    @GET("users.php")
    suspend fun getResults() : Response<List<UsersType>>
}

MySQL Table

Now we will create a data type for the returned json. In my example the MySQL table I created has:

Column Name	Column Type
id	INT
username	varchar(255)

UsersType

We will create a data type as follows. I named the class UsersType ( users is the name of the table I created in MySQL, keeping it simple )

data class UsersType(
    val id : Int,
    val username : String
)

MainActivity

Now we can start the actual task of querying the server and getting the data.

NOTE

I am doing this in the most simple way possible for proof of concept but I would advice to do this via ViewModel in a coroutine.

class MainActivity : ComponentActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        setContent {
            SyncOurListsTheme {
                var results by remember { mutableStateOf(emptyList<UsersType>()) }

                LaunchedEffect(Unit) {
                    val query = RetrofitHelper.getInstance().create(AccessApi::class.java)
                    val response = query.getResults()
                    if(response != null && response.isSuccessful) {
                        results = response.body()!!
                    }
                }

                Scaffold(modifier = Modifier.fillMaxSize()) { innerPadding ->

                    Column(
                        modifier = Modifier
                            .fillMaxSize()
                            .padding(innerPadding)
                            .background(Color.Black),
                        horizontalAlignment = Alignment.CenterHorizontally,
                        verticalArrangement = Arrangement.Center
                    ) {
                        results.forEach { user ->
                            Text(text = "${user.id} : ${user.username}", color = Color.White)
                        }

                        
                    }

                }
            }
        }
    }
}

I hope this was helpful.