Android MySQL - Spinner - Retrieve data and show

android networking

Android MySQL : Spinner - Retrieve data and show

MySQL is a fast open source relational database management system.

Android on the other hand is a software platform for a variety of devices most notable mobile.

This tutorial explores how to connect both via the HTTP.

You cannot directly connect Android and MySQL database as mysql runs on servers and desktops while android runs on mobile devices. To connect them we need the help of a server side programming language.

Then we can connect the two over the HTTP protocol. Android is written in Java while PHP is a popular server side language we can comfortable use.

Both languages define us simple APIs we can use for this task. Most notably is the HttpURLConnection on Java.

HttpURLConnection is the standard java networking class. It's an abstract class that derives from URLConnection.

To work achieve this we'll also need an AsyncTask class. AsyncTask is an abstract threading class that allows us to make these types of apps without renderring our UIs unresponsive.

We'll also use JSONObject and JSONArray to parse or process JSON. Our data will be sent over the HTTP as JSON string.

Both Java and PHP understand JSON so that's why we use it.

Let's go.

Spinner MySQL

1. Create Basic Activity Project

  1. First create an empty project in android studio. Go to File --> New Project.

  2. Type the application name and choose the company name. New Project Dialog

  3. Choose minimum SDK. Choose minimum SDK

  4. Choose Basic activity. Choose Empty Activity

  5. Click Finish. Finish

Basic activity will have a toolbar and floating action button already added in the layout

Normally two layouts get generated with this option:

No. Name Type Description
1. activity_main.xml XML Layout Will get inflated into MainActivity Layout.Typically contains appbarlayout with toolbar.Also has a floatingactionbutton.
2. content_main.xml XML Layout Will be included into activity_main.xml.You add your views and widgets here.
3. MainActivity.java Class Main Activity.

In this example I used a basic activity.

The activity will automatically be registered in the android_manifest.xml. Android Activities are components and normally need to be registered as an application component.

If you've created yours manually then register it inside the <application>...<application> as following, replacing the MainActivity with your activity name:


        <activity android:name=".MainActivity">

            <intent-filter>

                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />

            </intent-filter>

        </activity>

You can see that one action and category are specified as intent filters. The category makes our MainActivity as launcher activity. Launcher activities get executed first when th android app is run.

Advantage of Creating Basic Activity project

You can optionally choose empty activity over basic activity for this project.

However basic activity has the following advantages:

No. Advantage
1. Provides us a readymade toolbar which gives us actionbar features yet easily customizable
2. Provides us with appbar layout which implements material design appbar concepts.
3. Provides a FloatinActionButton which we can readily use to initiate quick actions especially in examples like these.
4. Decouples our custom content views and widgets from the templating features like toolbar.

Generated Project Structure

AndroidStudio will generate for you a project with default configurations via a set of files and directories.

Here are the most important of them:

No. File Major Responsibility
1. build/ A directory containing resources that have been compiled from the building of application and the classes generated by android tools. Such a tool is the R.java file. R.java file normally holds the references to application resources.
2. libs/ To hold libraries we use in our project.
3. src/main/ To hold the source code of our application.This is the main folder you work with.
4. src/main/java/ Contains our java classes organized as packages.
5. src/main/res/ Contains our project resources folders as follows.
6. src/main/res/drawable/ Contains our drawable resources.
7. src/main/res/layout/ Contains our layout resources.
8. src/main/res/menu/ Contains our menu resources XML code.
9. src/main/res/values/ Contains our values resources XML code.These define sets of name-value pairs and can be strings, styles and colors.
10. AndroidManifest.xml This file gets autogenerated when we create an android project.It will define basic information needed by the android system like application name,package name,permissions,activities,intents etc.
11. build.gradle Gradle Script used to build the android app.

2. Dependencies

We don't need any special dependency for this project. All our classes are standard Java/Android classes.

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

3. Create User Interface

User interfaces are typically created in android using XML layouts as opposed by direct java coding.

This is an example fo declarative programming.

######### Advantages of Using XML over Java

No. Advantage
1. Declarative creation of widgets and views allows us to use a declarative language XML which makes is easier.
2. It's easily maintanable as the user interface is decoupled from your Java logic.
3. It's easier to share or download code and safely test them before runtime.
4. You can use XML generated tools to generate XML

Here are our layouts for this project:

(a). activity_main.xml
  • This layout gets inflated to MainActivity user interface.
  • It includes the content_main.xml.

Here are some of the widgets, views and viewgroups that get employed"

No. View/ViewGroup Package Role
1. CordinatorLayout android.support.design.widget Super-powered framelayout that provides our application's top level decoration and is also specifies interactions and behavioros of all it's children.
2. AppBarLayout android.support.design.widget A LinearLayout child that arranges its children vertically and provides material design app bar concepts like scrolling gestures.
3. ToolBar <android.support.v7.widget A ViewGroup that can provide actionbar features yet still be used within application layouts.
4. FloatingActionButton android.support.design.widget An circular imageview floating above the UI that we can use as buttons.
<?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.spinnermysql.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>
(b). content_main.xml

This layout gets included in your activity_main.xml. We define our UI widgets here. In this case it's a simple spinner.

<?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.spinnermysql.MainActivity"
    tools:showIn="@layout/activity_main">

    <Spinner
        android:id="@+id/sp"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />
</RelativeLayout>

4. Java Classes

Here are our Java classes

Our MySQL classes

######## (a). Connector.java As the name suggests this is our Connection class. It's role is to establish a connection to network and return a HttpURLConnection instance.

It does this by first obtaining the connection url, passing it to a java.net.URL constructor and using the resulting java.net.URL instance to open a connection, then casting that connection from URLConnection to HttpURLConnection.

package com.tutorials.hp.spinnermysql.MySQL;

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();

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

            //RETURN
            return con;

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

        return null;
    }

}

######## (b). Downloader.java As the name suggests this class is responsible for downloading data from the networking.

We do this download in the background thread as this class derives from AsyncTask.

Meanhwile we show a progress dialog and dismiss automatically when the download is over.

The downloaded json string is then sent to Parser class to parsed and rendered in a spinner widget.

package com.tutorials.hp.spinnermysql.MySQL;

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

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;

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

    Context c;
    String urlAddress;
    Spinner sp;
    ProgressDialog pd;

    public Downloader(Context c, String urlAddress, Spinner sp) {
        this.c = c;
        this.urlAddress = urlAddress;
        this.sp = sp;
    }

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

        pd=new ProgressDialog(c);
        pd.setTitle("Downloading..");
        pd.setMessage("Retrieving data...Please wait");
        pd.show();
    }

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

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

        pd.dismiss();

        sp.setAdapter(null);

        if(s != null)
        {
            Parser p=new Parser(c,s,sp);
            p.execute();

        }else {
            Toast.makeText(c,"No data retrieved",Toast.LENGTH_SHORT).show();
        }
    }

    private String retrieveData()
    {
        HttpURLConnection con=Connector.connect(urlAddress);
        if(con==null)
        {
            return null;
        }
        try
        {
            InputStream is=con.getInputStream();

            BufferedReader br=new BufferedReader(new InputStreamReader(is));
            String line;
            StringBuffer receivedData=new StringBuffer();

            while ((line=br.readLine()) != null)
            {
                receivedData.append(line+"\n");
            }

            br.close();
            is.close();

            return receivedData.toString();

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

######## (c). Parser.java This class is responsible for data parsing. That is processing a JSON string to retrieve the contents and display in a Spinner.

Parsing JSON can also be pretty intense if you are working with a huge blob of data so we do this in a background thread as well.

We still use AsyncTask.

We are using native Java classes JSONArray and JSONObject to parse our data.

package com.tutorials.hp.spinnermysql.MySQL;

import android.content.Context;
import android.os.AsyncTask;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.Toast;

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

import java.util.ArrayList;

public class Parser extends AsyncTask<Void,Void,Integer> {

    Context c;
    String data;
    ArrayList names=new ArrayList();
    Spinner sp;

    public Parser(Context c, String data,Spinner sp) {
        this.c = c;
        this.data = data;
        this.sp=sp;
    }

    @Override
    protected Integer doInBackground(Void... params) {
        return this.parse();
    }

    @Override
    protected void onPostExecute(Integer integer) {
        super.onPostExecute(integer);

        if(integer==1)
        {
            ArrayAdapter adapter=new ArrayAdapter(c,android.R.layout.simple_list_item_1,names);
            sp.setAdapter(adapter);

        }else {
            Toast.makeText(c,"Unable To Parse",Toast.LENGTH_SHORT).show();
        }
    }

    private int parse()
    {
        try
        {
            JSONArray ja=new JSONArray(data);
            JSONObject jo=null;

            for (int i=0;i<ja.length();i++)
            {
                jo=ja.getJSONObject(i);
                String name=jo.getString("Name");
                names.add(name);
            }

            return 1;

        } catch (JSONException e) {
            e.printStackTrace();
        }

        return 0;
    }
}
Our Activity

######## MainActivity.java This is our Main activity, our launcher activity.

Launcher activities launch android applications when the individual application is run. In that it's the first activity to be invoked once the application component has been started.

Our activity will have a spinner to render our data from mysql database.

package com.tutorials.hp.spinnermysql;

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.Spinner;

import com.tutorials.hp.spinnermysql.MySQL.Downloader;

public class MainActivity extends AppCompatActivity {

    Spinner sp;
    static String urlAddress="http://10.0.2.2/android/players.php";

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

        sp= (Spinner) findViewById(R.id.sp);

        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
               Downloader d=new Downloader(MainActivity.this,urlAddress,sp);
                d.execute();
            }
        });

    }

}

5. AndroidManifest.xml

Proceed to androidmanifest.xml and add the permission for internet connectovity.

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

6. PHP Script

Here's a simple php script to retrieve data from mysql database. You place this script in the root directory of your server.

For instance in the www directory of Wamp or htdocs in Xampp.

But first you must have a database and replace your database credentials in this file:

<?php

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

$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();
}

$query=mysqli_query($con,"SELECT * FROM playerstb");

if($query)
{
    while($row=mysqli_fetch_array($query))
    {
        $flag[]=$row;
    }

    print(json_encode($flag));
}
mysqli_close($con);
?>

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