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.
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>"
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.
