Android MySQL - Save/Insert(HTTPURLConnection).


Alot of mobile applications employ webservice to enrich their functionalities and capabilities.

Even though mobile devices are at their most powerful currently, they are still not comparable to servers. Servers understandably:

  • Have more memory.
  • Have more processing power.
  • Have more hard drives.

This then makes it imperatvie that delegate some jobs or storage services to the servers.

The most popular server side prgramming language is PHP. While the most popular Relational Database Management System(RDBMS) is MySQL. Combine those with Android the most popular mobile OS, then we have three technologies to build powerful mobile apps.

This tutorial is a start. We want to see how to connect to MySQL database from our android application via PHP. We'll save data to the database from our app.

PHP and Java will communicate via JSON data exchange format. We'll make use HTTPURLConnection to make webservice calls.

Android MySQL Save

Let's go.

Our MySQL Database

Let's start by creating a mysql database. You can use a GUI program like PHPMyAdmin.

Here's the database table structure
Android MySQL Save

Clearly you can see our table structure comprises:

  • id (Auto-incremented).
  • name (string)
  • propellant (string)
  • description (string)

Our PHP Script

Here's the php script

    <?php

    $host='127.0.0.1';
    $username='root';
    $pwd='';
    $db="spacecraftDB";

    $con=mysqli_connect($host,$username,$pwd,$db) or die('Unable to connect');

    if(mysqli_connect_error($con))
    {
        echo "Failed to Connect to Database ".mysqli_connect_error();
    }

    $name=$_POST['name'];
    $propellant=$_POST['propellant'];
    $description=$_POST['description'];

    $sql="INSERT INTO spacecraftTB(Name,Propellant,Description) VALUES('$name','$propellant','$description')";

    $result=mysqli_query($con,$sql);

    if($result)
    {
        echo ('Successfully Saved........');

    }else
    {
        echo('Not saved Successfully............');

    }

    mysqli_close($con);

    ?>

Gradle Scripts

We'll add two dependencies in our build.gradle.

Build.gradle

This is the app level(it's found in the app folder of your project) build.gradle.

We'll add two dependencies: com.android.support:appcompat-v7 and com.android.support:design.

    dependencies {
        compile fileTree(dir: 'libs', include: ['*.jar'])
        testCompile 'junit:junit:4.12'
        compile 'com.android.support:appcompat-v7:23.3.0'
        compile 'com.android.support:design:23.3.0'
    }

AndroidManifest.xml

In your androidmanifest.xml add the permission for internet. Otherwise you application won't be able to make network calls:

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

Our Layouts

We have two layout files:

1. activity_main.xml

This is our MainActivity's layout.It will get inflated into MainActivity's user interface. Our root tag is android.support.design.widget.CoordinatorLayout.

This layout is responsible for the following roles:

  1. It defines our appbar layout using the android.support.design.widget.AppBarLayout tag.
  2. It defines us toolbar using the android.support.v7.widget.Toolbar tag.
  3. It defines us a floating action button using the android.support.design.widget.FloatingActionButton.
  4. It will hold content_main.xml.
    <?xml version="1.0" encoding="utf-8"?>
    <android.support.design.widget.CoordinatorLayout 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"
        android:fitsSystemWindows="true"
        tools:context="com.tutorials.hp.androidmdmysqlsave.MainActivity">

        <android.support.design.widget.AppBarLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:theme="@style/AppTheme.AppBarOverlay">

            <android.support.v7.widget.Toolbar
                android:id="@+id/toolbar"
                android:layout_width="match_parent"
                android:layout_height="?attr/actionBarSize"
                android:background="?attr/colorPrimary"
                app:popupTheme="@style/AppTheme.PopupOverlay" />

        </android.support.design.widget.AppBarLayout>

        <include layout="@layout/content_main" />

        <android.support.design.widget.FloatingActionButton
            android:id="@+id/fab"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_gravity="bottom|end"
            android:layout_margin="@dimen/fab_margin"
            android:src="@android:drawable/ic_dialog_email" />

    </android.support.design.widget.CoordinatorLayout>

2. content_main.xml

Our content_main.xml. The root tag is RelativeLayout.
It has the following roles:

  1. It defines our input edittexts and buttons.
  2. It will get included as part of activity_main.xml which will be inflated into our MainActivity UI.
    <?xml version="1.0" encoding="utf-8"?>
    <RelativeLayout 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"
        android:paddingBottom="@dimen/activity_vertical_margin"
        android:paddingLeft="@dimen/activity_horizontal_margin"
        android:paddingRight="@dimen/activity_horizontal_margin"
        android:paddingTop="@dimen/activity_vertical_margin"
        app:layout_behavior="@string/appbar_scrolling_view_behavior"
        tools:context="com.tutorials.hp.androidmdmysqlsave.MainActivity"
        tools:showIn="@layout/activity_main">

        <LinearLayout
            android:layout_width="fill_parent"
            android:layout_height="match_parent"
            android:layout_marginTop="?attr/actionBarSize"
            android:orientation="vertical"
            android:paddingLeft="15dp"
            android:paddingRight="15dp"
            android:paddingTop="50dp">

            <android.support.design.widget.TextInputLayout
                android:id="@+id/nameLayout"
                android:layout_width="match_parent"
                android:layout_height="wrap_content">

                <EditText
                    android:id="@+id/nameEditTxt"
                    android:layout_width="match_parent"
                    android:layout_height="wrap_content"
                    android:singleLine="true"
                    android:hint= "Name" />
            </android.support.design.widget.TextInputLayout>

            <android.support.design.widget.TextInputLayout
                android:id="@+id/propellantLayout"
                android:layout_width="match_parent"
                android:layout_height="wrap_content">

                <EditText
                    android:id="@+id/propellantEditTxt"
                    android:layout_width="match_parent"
                    android:layout_height="wrap_content"

                    android:hint="Propellant" />
            </android.support.design.widget.TextInputLayout>

            <android.support.design.widget.TextInputLayout
                android:id="@+id/descLayout"
                android:layout_width="match_parent"
                android:layout_height="wrap_content">

                <EditText
                    android:id="@+id/descEditTxt"
                    android:layout_width="match_parent"
                    android:layout_height="wrap_content"

                    android:hint="Description" />

            </android.support.design.widget.TextInputLayout>

            <Button android:id="@+id/saveBtn"
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:text="Save"
                android:clickable="true"
                android:background="@color/colorAccent"
                android:layout_marginTop="40dp"
                android:textColor="@android:color/white"/>
        </LinearLayout>
    </RelativeLayout>

Our Java Classes

Let's now come to our Java classes.

1. Spacecraft.java

This is our data object. It's a POJO class.

This class will define for us a single spacecraft. The spacecraft will have name, propellant and destination.

All the three will be saved into mysql database.

The roles of this class include:

  1. Define a Spacecraft object and its properties.
    package com.tutorials.hp.androidmdmysqlsave.mDataObject;

    public class Spacecraft {

        int id;
        String name;
        String propellant;
        String description;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getPropellant() {
            return propellant;
        }

        public void setPropellant(String propellant) {
            this.propellant = propellant;
        }

        public String getDescription() {
            return description;
        }

        public void setDescription(String description) {
            this.description = description;
        }
    }

2. Connector.java

This is our Connector class.

As the name suggests it will connect to our URL address and return us a HTTPURLConnection object. We'll catch java.net.MalformedURLException and java.io.IOException.

It roles include:

  1. Receive a URL address and attempt to connect to it.
  2. Return to the caller a java.net.HttpURLConnection object or null.
    package com.tutorials.hp.androidmdmysqlsave.mMySQL;

    import java.io.IOException;
    import java.net.HttpURLConnection;
    import java.net.MalformedURLException;
    import java.net.URL;

    public class Connector {

        public static HttpURLConnection connect(String urlAddress)
        {
            try {

                URL url=new URL(urlAddress);
                HttpURLConnection con= (HttpURLConnection) url.openConnection();

                //SET PROPS
                con.setRequestMethod("POST");
                con.setConnectTimeout(20000);
                con.setReadTimeout(20000);
                con.setDoInput(true);
                con.setDoOutput(true);

                return con;

            } catch (MalformedURLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return null;
        }
    }

3. DataPackager.java

This is our datapackager class.

It's roles include:

  1. Obtain data from a POJO object and put them into the org.json.JSONObject instance.
  2. Obtain that data's keys and values from our JSONObject and encode it into a UTF-8 URL String to be sent via the network.
  3. Catch org.json.JSONException and java.io.UnsupportedEncodingException.
  4. Return the encoded UTF-8 string or null.
    package com.tutorials.hp.androidmdmysqlsave.mMySQL;

    import com.tutorials.hp.androidmdmysqlsave.mDataObject.Spacecraft;

    import org.json.JSONException;
    import org.json.JSONObject;

    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.Iterator;

    public class DataPackager {

        Spacecraft spacecraft;

        public DataPackager(Spacecraft spacecraft) {
            this.spacecraft = spacecraft;
        }

        public String packData()
        {
            JSONObject jo=new JSONObject();
            StringBuffer sb=new StringBuffer();

            try {
                jo.put("Name",spacecraft.getName());
                jo.put("Propellant",spacecraft.getPropellant());
                jo.put("Description",spacecraft.getDescription());

                Boolean firstvalue=true;
                Iterator it=jo.keys();

                do {
                    String key=it.next().toString();
                    String value=jo.get(key).toString();

                    if(firstvalue)
                    {
                        firstvalue=false;
                    }else
                    {
                        sb.append("&");
                    }

                    sb.append(URLEncoder.encode(key,"UTF-8"));
                    sb.append("=");
                    sb.append(URLEncoder.encode(value,"UTF-8"));

                }while (it.hasNext());

                return sb.toString();

            } catch (JSONException e) {
                e.printStackTrace();
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            return null;
        }
    }

4. Sender.java

Data was packaged in the previous class: DataPackager.java.

It's now time to send it.

This class will be responsible for:

  1. Receive context, url address and edittexts via the constructor from the MainActivity.
  2. Retrieve typed texts from the Edittexts.
  3. Show a progress dialog.
  4. Send data via the network in a background thread using Aynctask.
  5. Return response.
    package com.tutorials.hp.androidmdmysqlsave.mMySQL;

    import android.app.ProgressDialog;
    import android.content.Context;
    import android.os.AsyncTask;
    import android.widget.EditText;
    import android.widget.Toast;

    import com.tutorials.hp.androidmdmysqlsave.mDataObject.Spacecraft;

    import java.io.BufferedReader;
    import java.io.BufferedWriter;
    import java.io.IOException;
    import java.io.InputStreamReader;
    import java.io.OutputStream;
    import java.io.OutputStreamWriter;
    import java.net.HttpURLConnection;

    public class Sender extends AsyncTask<Void,Void,String> {

        Context c;
        String urlAddress;
        EditText nameTxt,propellantTxt,descTxt;

        Spacecraft spacecraft;

        ProgressDialog pd;

        public Sender(Context c, String urlAddress, EditText nameTxt, EditText propellantTxt, EditText descTxt) {
            this.c = c;
            this.urlAddress = urlAddress;
            this.nameTxt = nameTxt;
            this.propellantTxt = propellantTxt;
            this.descTxt = descTxt;

            spacecraft=new Spacecraft();
            spacecraft.setName(nameTxt.getText().toString());
            spacecraft.setPropellant(propellantTxt.getText().toString());
            spacecraft.setDescription(descTxt.getText().toString());
        }

        @Override
        protected void onPreExecute() {
            super.onPreExecute();

            pd=new ProgressDialog(c);
            pd.setTitle("Send");
            pd.setMessage("Sending...Please wait");
            pd.show();
        }

        @Override
        protected String doInBackground(Void... params) {
            return this.send();
        }

        @Override
        protected void onPostExecute(String s) {
            super.onPostExecute(s);

            pd.dismiss();

            if(s==null)
            {
                Toast.makeText(c,"Unsuccessful,Null returned",Toast.LENGTH_SHORT).show();
            }else
            {
                if(s=="Bad Response")
                {
                    Toast.makeText(c,"Unsuccessful,Bad Response returned",Toast.LENGTH_SHORT).show();

                }else
                {
                    Toast.makeText(c,"Successfully Saved",Toast.LENGTH_SHORT).show();

                    //CLEAR UI
                    nameTxt.setText("");
                    propellantTxt.setText("");
                    descTxt.setText("");
                }
            }
        }

        private String send()
        {
            HttpURLConnection con=Connector.connect(urlAddress);
            if(con==null)
            {
                return null;
            }
            try {

                OutputStream os=con.getOutputStream();

                //WRITE
                BufferedWriter bw=new BufferedWriter(new OutputStreamWriter(os,"UTF-8"));
                bw.write(new DataPackager(spacecraft).packData());

                bw.flush();
                //RELEASE
                bw.close();
                os.close();

                //SUCCESS OR NOT??
                int responseCode=con.getResponseCode();
                if(responseCode==con.HTTP_OK)
                {
                    BufferedReader br=new BufferedReader(new InputStreamReader(con.getInputStream()));
                    StringBuffer response=new StringBuffer();

                    String line;
                    while ((line=br.readLine()) != null)
                    {
                        response.append(line);
                    }

                    br.close();

                    return response.toString();
                }else {
                    return "Bad Response";
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

            return null;
        }
    }

5. MainActivity.java

So here's our MainActivity, a class that derives from AppCompatActivity.
The roles for this class include:

  1. It's our launcher activity.
  2. Define the url address to our php script.
  3. Declare and initialize our views and widgets. These include : edittexts and buttons.
  4. Override OnCreate() method, inflate the XML layout and set it as content view.
  5. Reference the toolbar and set it to actionbar.
  6. Instantiate Sender class and execute it.
    package com.tutorials.hp.androidmdmysqlsave;

    import android.os.Bundle;
    import android.support.design.widget.FloatingActionButton;
    import android.support.design.widget.Snackbar;
    import android.support.v7.app.AppCompatActivity;
    import android.support.v7.widget.Toolbar;
    import android.view.View;
    import android.view.Menu;
    import android.view.MenuItem;
    import android.widget.Button;
    import android.widget.EditText;

    import com.tutorials.hp.androidmdmysqlsave.mMySQL.Sender;

    public class MainActivity extends AppCompatActivity {

        String urlAddress="http://10.0.2.2/android/spacecraft.php";
        EditText nameTxt,propellantTxt,descTxt;
        Button saveBtn;

        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
            setSupportActionBar(toolbar);

            FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
            fab.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
                            .setAction("Action", null).show();
                }
            });

            nameTxt= (EditText) findViewById(R.id.nameEditTxt);
            propellantTxt= (EditText) findViewById(R.id.propellantEditTxt);
            descTxt= (EditText) findViewById(R.id.descEditTxt);
            saveBtn= (Button) findViewById(R.id.saveBtn);

            saveBtn.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    Sender s=new Sender(MainActivity.this,urlAddress,nameTxt,propellantTxt,descTxt);
                    s.execute();
                }
            });
        }
    }

Download the code here.

How do You Feel after reading this?

According to scientists, we humans have 8 primary innate emotions: joy, acceptance, fear, surprise, sadness, disgust, anger, and anticipation. Feel free to tell us how you feel about this article using these emotes or via the comment section. This feedback helps us gauge our progress.

Help me Grow.

I set myself some growth ambitions I desire to achieve by this year's end regarding this website and my youtube channel. Am halfway. Help me reach them by:




Recommendations


What do You Think

Dear readers drop us your comments below. We are building a community of students and learners. Start by dropping us your suggestions below. What tutorials do you want us to do for example? Where can we improve? What are some awesome resources out there? Do you have any code you want to share with us?
By the way that example or snippet you have lying in your computer can really help beginner programmers. We can share it here with other students.

Previous Post Next Post