Android MySQL Tutorial to Perform Basic CRUD Operation

Hello friends, Here is another Android MySQL Tutorial, in this post we will learn the basic CRUD operation in MySQL database from Android Application. So without wasting time lets start our Android MySQL Tutorial.

What is CRUD?

I guess many of you already know that what is CRUD. But if anyone don’t know, CRUD is an Acronym for the Basic Database Operations. In any database we do the following basic operations.

  • Creating a Record -> In the database we insert a record.
  • Reading Stored Records -> No point of saving data when we can’t read it back 😛 (LOL). So the second operation is reading the stored data back.
  • Updating Stored Records -> We may also need to update the existing data. So the third operation is update.
  • Deleting Records -> Lastly we may also need to delete the existing data from the database.

So basically in any database we perform the above mentioned operations. In this tutorial I am going to use MySQL and PHP.

Why PHP and MySQL and Why Not SQLite?

Android gives us a feature of SQLite to use as the RDBMS for our app. But we can’t use SQLite only if we are building an app. This is because we need a centralize server. SQLite is local for every device so if a user is using your application you will not be able manage your users if it is only in SQLite. That is why we need a centralize database where we can store our app data.

If you want to learn about the SQLite using the Latest Architecture Component ROOM. Then here is a complete FREE COURSE for Android Room Database. 

Do we have only PHP and MySQL for this?

Obviously NO? You can use any server side scripting language or database application. I am using here PHP and MySQL because it is easily available and I already know it. 😛 (LOL) But if you are an expert in Python or JAVA or NodeJS or basically any other technology then you can go with it. It will change only the server side coding. Android part will always be the same.

Building Web APIs

The first step is building the required Web APIs. This is because from the android application, to communicate with our web server we need an interface called API.

So our android device will send a request to our API,  then our API will perform the requested task and it will give us the response related to the task. You can see the below diagram for more clarification.

android mysql tutorial

The response that the web server gives should be in a standard structure, so that in our android side we can easily parse and get the information from the structure. For this everyone now uses JSON. If you heard this term for the first time then you should consider watching this 7 minute quick JSON tutorial.

I guess you got a basic idea about the task that we are going to do in this tutorial.

Creating Database

It is obvious that we need a database first 😛 . So here I am using XAMPP (You can go with wamp or lamp as well). So first create the following database.

mysql database

  • So open localhost/phpmyadmin and run the following query to create the above table.

Creating PHP Project

Now to perform the Database Operations we will create a PHP Project.

  • So inside htdocs (c:/xampp/htdocs) create a new folder (You can also use an IDE like PHP Storm to create project but remember create the project inside c:/xampp/htdocs only). 
  • I have given the name HeroApi to my project. You can give any name. But I would say give the same name or else you may lead up to some errors following the post if you are a newbie. 
  • Inside the project create two more folders named includes and Api. You can see the below screenshot for the directory structure that I am using. (I am using Sublime Text for coding server part).

php project structure

  • You see we have four php files (3 inside includes and 1 inside v1). So you create these files as well.

Project Structure

  • So we have the following things in our PHP project.
    • includes
      • Constants.php: In this file we will define all the required constants e.g., database name, username, password etc. 
      • DbConnect.php: This fill will contain a class where we will connect to our MySQL database.
      • DbOperation.php: The actual CRUD operation is performed inside this file.
    • v1
      • Api.php: This is our API, we will send request to this file only from the android side. And this file will handle all the API calls.

Defining Constants

  • First come inside the file Constants.php and write the following code.

Connecting to Database

  • Now inside DbConnect.php write the following code. I have explained the code using comments.

Performing Database Operations

  • Now we will do the CRUD operation inside the DbOperation.php file.

Handling API Calls

  • Now here come the main part, which is handling the API calls. So come inside the Api.php which is inside the v1 folder.

  • Now lets test the APIs.

Testing the API Calls

  • For testing the API Calls here I am using POSTMAN. It is a REST API Client for Google Chrome.
Create Operation

testing api create operation

Read Operation

testing api read operation

Update Operation

Upate Operation

Delete Operation

delete operation

You see all the operations are working absolutely fine. Now we can move ahead in creating our Android Project. But before moving to android side below you can see our API URLs.

Finalizing API Calls

  • The below table displays our API URLs with the Parameter and Method. Remember using localhost in android side will not work. You need to find your IP. I have my IP in the below table, but in your case you need to find yours. So if you are using a windows you can use ipconfig command to find IP and for MAC or Linux use the ifconfig command. For more details you can visit this tutorial.
URL Method Parameter
http://192.168.101.1/HeroApi/v1/Api.php?apicall=createhero POST name, realname, rating, teamaffiliation
http://192.168.101.1/HeroApi/v1/Api.php?apicall=getheroes GET
http://192.168.101.1/HeroApi/v1/Api.php?apicall=updatehero POST id, name, realname, rating, teamaffiliation
http://192.168.101.1/HeroApi/v1/Api.php?apicall=deletehero&id=idvalue GET

Want to Explore Building RESTful APIs?

Here we built a very basic API that demonstrates the CRUD operation. But in real world scenarios we use FRAMEWORKS to make the API creating easy and structured. If you want to dig a bit more about building RESTful APIs. Then you should check the following course. It is absolutely FREE.

Android MySQL Tutorial

We have our Web Services, and now we can build the android application. So lets start.

Creating a new Project

  • Create a new Android Studio Project. Here I have created a project named MyHeroApp.
  • Once your project is loaded inside the package we will create all the helper classes that is required for thie project.

Creating Helper Classes

Class To Store API URLs

  • First create a class named Api.java and write the following code.

Hero Model Class

  • We also need a model class for our Hero. So create a class named Hero.java and write the following code. It will have only properties, constructor and getters.

Request Handler

  • We need to send GET and POST request to our API URLs, and for this I am creating a new class that will perform these tasks. So create a new class named RequestHandler.java and write the following code.

Defining Internet Permission in AndroidManifest

  • As we need to perform network request from our Application, we need to define internet permission for this. And because we are working with localhost our APIs are not secured i.e. we have HTTP URLs and not HTTPS for our APIs.
  • And by default your application are restricted to communicate with non HTTPS URLs (For security reasons). So you need to explicitly define that your app should be allowed to communicate with HTTP URLs. And to do this you need to add usesCleartextTraffic=”true” inside your opening application tag. (See the below code that is AndroidManifest.xml for my project.).

Designing User Interface

  • The below image shows how our final application will look.
Application UI
Application UI
  • So we need to design the above mentioned things. I have already designed the activity_main.xml so you can directly use the below code.

  • For the spinner I have used static entries using xml array. So you also need to create this array. For this go inside values -> strings.xml

  • Thats it for the User Interface part.
  • Now lets perform the CRUD operations in our MySQL database.

Class to Perform Network Request

  • The point is we cannot directly perform a network request in the application’s main thread. So for this we need an AsyncTask to perform the task in a separate thread. Hence we will create an inner class inside MainActivity.java.

Create Operation

  • Now lets save a new hero to our database. First we will define all the views. And we will attach a clicklistener to the button and inside the click event we will call the method to create a new record in the database.

  • Now we need to create the method createHero().

  • Now run the application and try adding a new hero.
android mysql tutorial - create operation
Android MySQL – Create Operation
  • Its working fine, you can check the database as well. Now lets move to the read operation.

Read Operation

  • We will display all the heroes from the database in a ListView, the ListView also have the Update and Delete Button.
  • So for this first we will create a custom Layout for our ListView.

List Layout

  • Create a layout resource file named layout_hero_list.xml.

Custom Adapter Class

  • Create one more inner class inside MainActivity class. We will name it HeroAdapter.

Retrieving Heroes from the Database

  • Create a method named readHeroes().

  • Now one more method we need to refresh the Hero List. So create a method named refreshHeroList().

  • Now also uncomment the commented method inside PeformNetworkRequest class and onCreate() method. You need to uncomment readHeroes() inside onCreate() and refreshHeroList() inside PerformNetworkRequest class. 
  • Now you can try running the application.
android mysql tutorial - read operation
Android MySQL – Read Operation
  • You can see it is also working fine. Now lets do the UPDATE operation.

Update Operation

  • For updating we will create a new method named updateHero().

  • Now just uncomment the method updateHero() inside the click listener of buttonAddUpdate.
  • And now we can try updating a record.
android mysql tutorial - update operation
Android MySQL – Update Operation
  • So the update is also working absolutely fine. Now lets move to the last operation which is delete.

Delete Operation

  • For delete also we need a new method. So create a method named deleteHero().

  • Now uncomment the method deleteHero() inside the HeroAdapter class. 
  • And lets test the delete operation as well.
android mysql tutorial - delete operation
Android MySQL – Delete Operation
  • So it is working fine as well. And we have done with all the basic CRUD operations.

The complete code for MainActivity

  • If you had some confusions following the above steps here is the complete code for MainActivity.java.

  • So after following every step correctly your application will behave as shown below.
Android MySQL Tutorial
Android MySQL Tutorial

Android MySQL Tutorial – Source Code Download

  • If you are still facing troubles you can get my source code from below. It has everything, the server side scripts, database file and android project.

So thats all for this Android MySQL Tutorial friends. CRUD Operation is needed in almost every application so it is a very important thing. If you are having any confusions or queries regarding this Android MySQL tutorial don’t hesitate in asking on comments. And if you liked the post then PLEASE SHARE IT. Thank You 🙂