Android MySQL Database Operations

In this post we are going to learn about how to connect to MySQL Database from your Android Application and perform database operations. Here we create an android app that contain some login Activity through which the user can retrieve information from database and a registration Activity through which the user can add information into the database. 

First you need to have the following components installed in your development machine. 

1. Database : Here we use the MySQL database.

2. Web Server : Here we use the Apache Web Server.

3. Server side Scripting Language :  Here we use PHP for server side scripting.

4. Android Development environment : You must install android sdk and android studio.  

I recommend you to download and install WAMPSERVER. The wamp server installer contains the following components.

Apache Server Application

MySQL Database

PHP/phpMyAdmin

First we have to create the database and table in MySQL. You can use the phpMyAdmin for mange your MySQL databases. Here our database name is "webappdb" and table name is "user_info".  

The table contains three columns "name", "user_name" and "user_pass". 

For our android application create a new folder inside the public directory of the wamp server and name the folder as "webapp".

First we have to write the needed php scripts and put it in the public directory of the wamp server. Here we need three php scripts, first one is for establish a connection with the database, second one for add informations into database and the last one for retrieve informations.

1. init.php

 <?php  
 $db_name = "webappdb";  
 $mysql_user = "root";  
 $mysql_pass = "root";  
 $server_name = "localhost";  
 $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);  
 ?>  

2. register.php

 <?php  
 require "init.php";  
 $name = $_POST["user"];  
 $user_name = $_POST["user_name"];  
 $user_pass = $_POST["user_pass"];  
 $sql_query = "insert into user_info values('$name','$user_name','$user_pass');";  
 ?>  

3. login.php

 <?php  
 require "init.php";  
 $user_name = $_POST["login_name"];  
 $user_pass =  $_POST["login_pass"];  
 $sql_query = "select name from user_info where user_name like '$user_name' and user_pass like '$user_pass';";  
 $result = mysqli_query($con,$sql_query);  
 if(mysqli_num_rows($result) >0 )  
 {  
 $row = mysqli_fetch_assoc($result);  
 $name =$row["name"];  
 echo "Login Success..Welcome ".$name;  
 }  
 else  
 {   
 echo "Login Failed.......Try Again..";  
 }  
 ?>  

Create an android application contain a Login Activity and Register Activity. 

activity_main.xml (Login Activity layout)

 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
   xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"  
   android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"  
   android:paddingRight="@dimen/activity_horizontal_margin"  
   android:paddingTop="@dimen/activity_vertical_margin"  
   android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity"  
   android:background="#0BB990"  
   android:orientation="vertical"  
   >  
   <TextView  
     android:layout_width="wrap_content"  
     android:layout_height="wrap_content"  
     android:text="Login Form"  
     android:textAppearance="?android:textAppearanceLarge"  
     android:textStyle="bold"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="10dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="User Name"  
     android:id="@+id/user_name"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="70dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="Password"  
     android:id="@+id/user_pass"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="20dp"  
     android:inputType="textPassword"  
     />  
   <Button  
     android:layout_width="100dp"  
     android:layout_height="wrap_content"  
     android:text="Login"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="10dp"  
     android:onClick="userLogin"  
   />  
   <Button  
     android:layout_width="wrap_content"  
     android:layout_height="wrap_content"  
     android:text="Register Now"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="50dp"  
     android:onClick="userReg"  
     />  
 </LinearLayout>  

register_layout.xml (Register Activity Layout)

 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
   xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"  
   android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"  
   android:paddingRight="@dimen/activity_horizontal_margin"  
   android:paddingTop="@dimen/activity_vertical_margin"  
   android:paddingBottom="@dimen/activity_vertical_margin"  
   tools:context="com.easyway2in.mysqlconnect.Register"  
   android:orientation="vertical"  
   android:background="#0BB990"  
   >  
   <TextView  
     android:layout_width="wrap_content"  
     android:layout_height="wrap_content"  
     android:text="Registration Form"  
     android:textAppearance="?android:textAppearanceLarge"  
     android:textStyle="bold"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="10dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="Name"  
     android:id="@+id/name"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="40dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="User Name"  
     android:id="@+id/new_user_name"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="20dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="Password"  
     android:id="@+id/new_user_pass"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="20dp"  
     android:inputType="textPassword"  
     />  
   <Button  
     android:layout_width="130dp"  
     android:layout_height="wrap_content"  
     android:text="Register"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="30dp"  
     android:onClick="userReg"  
     />  
 </LinearLayout>  

In this tutorial we use the HttpUrlConnection instead of HttpClient. HttpClient is deprecated form API level 22.
Ads By Google

---

---

Here the server is localhost and we test the application on an android virtual device. We need two URL. First one for registration purpose and the second one is for user Login.

Register URL : ""http://10.0.2.2/webapp/register.php";

Login URL : "http://10.0.2.2/webapp/login.php";

Here we use the IP (10.0.2.2) -because the android virtual device use this default IP for connect to the localhost. You can also use your local computer IP address. This application use an AsyncTask to perform the server operations. 

MainActivity.java

 package com.easyway2in.mysqldbdemo;  
 import android.app.Activity;  
 import android.content.Intent;  
 import android.os.StrictMode;  
 import android.support.v7.app.ActionBarActivity;  
 import android.os.Bundle;  
 import android.view.Menu;  
 import android.view.MenuItem;  
 import android.view.View;  
 import android.widget.EditText;  
 public class MainActivity extends Activity{  
   EditText ET_NAME,ET_PASS;  
   String login_name,login_pass;  
   @Override  
   protected void onCreate(Bundle savedInstanceState) {  
     super.onCreate(savedInstanceState);  
     setContentView(R.layout.activity_main);  
    ET_NAME = (EditText)findViewById(R.id.user_name);  
     ET_PASS = (EditText)findViewById(R.id.user_pass);  
   }  
 public void userReg(View view)  
 {  
   startActivity(new Intent(this,Register.class));  
 }  
   public void userLogin(View view)  
   {  
   login_name = ET_NAME.getText().toString();  
     login_pass = ET_PASS.getText().toString();  
     String method = "login";  
     BackgroundTask backgroundTask = new BackgroundTask(this);  
     backgroundTask.execute(method,login_name,login_pass);  
   }  
 }  

BackgroundTask.java

 package com.easyway2in.mysqldbdemo;  
 import android.app.AlertDialog;  
 import android.content.Context;  
 import android.os.AsyncTask;  
 import android.widget.Toast;  
 import java.io.BufferedReader;  
 import java.io.BufferedWriter;  
 import java.io.IOException;  
 import java.io.InputStream;  
 import java.io.InputStreamReader;  
 import java.io.OutputStream;  
 import java.io.OutputStreamWriter;  
 import java.net.HttpURLConnection;  
 import java.net.MalformedURLException;  
 import java.net.URL;  
 import java.net.URLEncoder;  
 /**  
  * Created by prabeesh on 7/14/2015.  
  */  
 public class BackgroundTask extends AsyncTask<String,Void,String> {  
  AlertDialog alertDialog;  
   Context ctx;  
   BackgroundTask(Context ctx)  
   {  
    this.ctx =ctx;  
   }  
   @Override  
   protected void onPreExecute() {  
   alertDialog = new AlertDialog.Builder(ctx).create();  
     alertDialog.setTitle("Login Information....");  
   }  
   @Override  
   protected String doInBackground(String... params) {  
     String reg_url = "http://10.0.2.2/webapp/register.php";  
     String login_url = "http://10.0.2.2/webapp/login.php";  
     String method = params[0];  
     if (method.equals("register")) {  
       String name = params[1];  
       String user_name = params[2];  
       String user_pass = params[3];  
       try {  
         URL url = new URL(reg_url);  
         HttpURLConnection httpURLConnection = (HttpURLConnection) url.openConnection();  
         httpURLConnection.setRequestMethod("POST");  
         httpURLConnection.setDoOutput(true);  
         //httpURLConnection.setDoInput(true);  
         OutputStream OS = httpURLConnection.getOutputStream();  
         BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(OS, "UTF-8"));  
         String data = URLEncoder.encode("user", "UTF-8") + "=" + URLEncoder.encode(name, "UTF-8") + "&" +  
             URLEncoder.encode("user_name", "UTF-8") + "=" + URLEncoder.encode(user_name, "UTF-8") + "&" +  
             URLEncoder.encode("user_pass", "UTF-8") + "=" + URLEncoder.encode(user_pass, "UTF-8");  
         bufferedWriter.write(data);  
         bufferedWriter.flush();  
         bufferedWriter.close();  
         OS.close();  
         InputStream IS = httpURLConnection.getInputStream();  
         IS.close();  
         //httpURLConnection.connect();  
         httpURLConnection.disconnect();  
         return "Registration Success...";  
       } catch (MalformedURLException e) {  
         e.printStackTrace();  
       } catch (IOException e) {  
         e.printStackTrace();  
       }  
     }  
     else if(method.equals("login"))  
     {  
       String login_name = params[1];  
       String login_pass = params[2];  
       try {  
         URL url = new URL(login_url);  
         HttpURLConnection httpURLConnection = (HttpURLConnection)url.openConnection();  
         httpURLConnection.setRequestMethod("POST");  
         httpURLConnection.setDoOutput(true);  
         httpURLConnection.setDoInput(true);  
         OutputStream outputStream = httpURLConnection.getOutputStream();  
         BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(outputStream,"UTF-8"));  
       String data = URLEncoder.encode("login_name","UTF-8")+"="+URLEncoder.encode(login_name,"UTF-8")+"&"+  
           URLEncoder.encode("login_pass","UTF-8")+"="+URLEncoder.encode(login_pass,"UTF-8");  
         bufferedWriter.write(data);  
         bufferedWriter.flush();  
         bufferedWriter.close();  
         outputStream.close();  
         InputStream inputStream = httpURLConnection.getInputStream();  
         BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream,"iso-8859-1"));  
         String response = "";  
         String line = "";  
         while ((line = bufferedReader.readLine())!=null)  
         {  
           response+= line;  
         }  
         bufferedReader.close();  
         inputStream.close();  
         httpURLConnection.disconnect();  
         return response;  
       } catch (MalformedURLException e) {  
         e.printStackTrace();  
       } catch (IOException e) {  
         e.printStackTrace();  
       }  
     }  
     return null;  
   }  
   @Override  
   protected void onProgressUpdate(Void... values) {  
     super.onProgressUpdate(values);  
   }  
   @Override  
   protected void onPostExecute(String result) {  
    if(result.equals("Registration Success..."))  
    {  
      Toast.makeText(ctx, result, Toast.LENGTH_LONG).show();  
    }  
     else  
    {  
     alertDialog.setMessage(result);  
      alertDialog.show();  
    }  
   }  
 }  

Add the Internet permission in Manifest file.

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

Watch Video Tutorial Of this Topic

Download This Android Studio Project

   
This is how an android application connect to MySQL database run on a remote server and perform basic database operations. I hope you got the concepts. Please share your experience via comments.