Populate Android Spinner Data from Remote MYSQL Database

In this tutorial, we are going to learn how to populate android spinner data from remote mysql database. This post is in response to message I got from one of our community readers who wanted to see a tutorial like this. If you have any tutorial you will love to appear here, simple send the message to me.

In most cases, you will see yourself populate spinner controls with data stored in your application as a array-string or from your local database. If you have looking to fetch the spinner data from a local database, you can follow this tutorial I wrote in this topic – Populating android spinner from sqlite database

For this tutorial we are going to use two android third party libraries – Volley for network call and Gson for parsing Json.

Back-end Server Side

The server side of this application will make us of a web server and MYSQL database server. Feel free to use a local server like WAMP or remote server. If you are planning to use a remote server, I have a detailed tutorial that will teach you how to set it up.

Database

Open your database if you are using the design view and create a table. Name the table countries and the table will contain 2 columns – id and name columns. You can as well use the code below to create your table.

<string name="app_name">Android Spinner with Remote Database</string>

CREATE TABLE IF NOT EXISTS `countries` (

`id` int(20) NOT NULL AUTO_INCREMENT,

`name` varchar(300) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

We are going to populate this table with some values. The values will contain the name of different countries in the world. For the purpose of this tutorial, we are going to add only 10 records into the countries table.

You can execute the code below in other to add 10 records.

INSERT INTO `countries` (`id`, `name`) VALUES

(1, ‘Afghanistan’),

(2, ‘Albania’),

(3, ‘Algeria’),

(4, ‘Angola’),

(5, ‘Argentina’),

(6, ‘Armenia’),

(7, ‘Australia’),

(8, ‘Austria’),

(9, ‘Bangladesh’),

(10, ‘Barbados’);

Web Server

We are going to make a connection between our database and web server. So in our public folder in the web server, we are going to create a configuration file.

Config.php

Create a new file and name it config.php file. The file will hold the database configuration data. Add the following code to the page.

<?php

define(“DB_HOST”, “l”);

define(“DB_USER”, “”);

define(“DB_PASSWORD”, “”);

define(“DB_NAME”, “”);

?>

Database.php

Create another file and name it database.php in the same folder. Add the following code inside the file.

<?php

include_once ‘config.php’;

class database{

private $connect;

public function __construct(){

$this->connect = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if (mysqli_connect_errno($this->connect))

{

echo “Failed to connect to MySQL: ” . mysqli_connect_error();

}

}

public function getDb(){

return $this->connect;

}

}

?>

Query.php

We are going to create yet another php file which will house the method that will query the database.

Add the following code to this file.

<?php

include_once ‘database.php’;

class Query{

private $tables;

public function __constructor(){

$this->tables = “rating”;

}

public function getAllCountries(){

$countries = array();

$com = new database();

$sql =”select * from countries”;

$result = mysqli_query($com->getDb(), $sql);

$rows = mysqli_num_rows($result);

if($rows > 0){

while ($row = $result->fetch_assoc()) {

$countries[] = $row;

}

echo json_encode($countries, JSON_PRETTY_PRINT);

}

}

}

?>

Finally, we will create another php file that will output the content of our database. Name the file countries.php and add the code below to the file.

<?php

require_once ‘query.php’;

$queryObject = new Query();

$queryObject->getAllCountries();

?>

Android Java Code

Before we dive into more details, it is important for us to understand what we are planning to achieve. Below is the screen-shot of the application we will be creating.

android spinner

Lets start to soil our hands in code. Start up your IDE. For this tutorial, I am using the following tools and environment, feel free to use what works for you.

Windows 7

Android Studio

Sony Xperia ZL

Min SDK 14

Target SDK 23

To create a new android application project, follow the steps as stipulated below.

Go to File menu

Click on New menu

Click on Android Application

Enter Project name: AndroidSpinnerRemoteDatabase

Package: com.inducesmile.androidspinnerremotedatabase

Select Blank Activity

Name your activity : MainActivity

Keep other default selections

Continue to click on next button until Finish button is active, then click on Finish Button.

Strings.xml

We are going to update our project strings.xml file located in the values folder inside the res folder. Open the file and add the code below to it.

<resources>
    <string name="app_name">Android Spinner with Remote Database</string>
    <string name="title">Fetch data from remote Mysql database and populate the spinner</string>
</resources>

Colors.xml

Open the colors.xml file in the same location as the strings.xml file and add the code below to the file.

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <color name="colorPrimary">#3F51B5</color>
    <color name="colorPrimaryDark">#303F9F</color>
    <color name="colorAccent">#FF4081</color>
    <color name="colorBlack">#000000</color>
    <color name="colorDivider">#B6B6B6</color>
</resources>

Manifest.xml

Since we are going to make network call, our application will need internet. So we are going to add user permission for internet in Manifest.xml file. Open the Manifest.xml file located in the Manifest folder and add the below code.

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.inducesmile.androidspinnerremotedatabase">
    <uses-permission android:name="android.permission.INTERNET"/>
    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>
</manifest>

Gradle.build

We started earlier on that we are going to make use of two dependency libraries, we will add our project dependencies in the app gradle.build. Open this file and add the code below.

apply plugin: 'com.android.application'
android {
    compileSdkVersion 23
    buildToolsVersion "23.0.3"
    defaultConfig {
        applicationId "com.inducesmile.androidspinnerremotedatabase"
        minSdkVersion 14
        targetSdkVersion 23
        versionCode 1
        versionName "1.0"
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
        }
    }
}
dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    testCompile 'junit:junit:4.12'
    compile 'com.android.support:appcompat-v7:23.3.0'
    compile 'com.mcxiaoke.volley:library:1.0.19'
    compile 'com.google.code.gson:gson:2.6.1'
}

activity_main.xml

Let’s open our project main layout file, we are going to add a TextView and Spinner controls. Copy and paste the code below inside the layout file.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 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:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.inducesmile.androidspinnerremotedatabase.MainActivity">
    <TextView
        android:id="@+id/title"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_alignParentTop="true"
        android:layout_marginTop="16dp"
        android:textColor="@color/colorBlack"
        android:text="@string/title" />
    <Spinner
        android:id="@+id/spinner"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="24dp"
        android:padding="13dp"
        android:layout_centerHorizontal="true"
        android:layout_below="@+id/title"
        android:background="@drawable/bottom_border"
        android:visibility="gone"
        android:textColor="@color/colorBlack" />
</RelativeLayout>

Custom BaseAdapter

Next, we are going to create a custom BaseAdapter which will use to bind our remote data source with the spinner items. Right click on the project package folder and choose create a new java file. Name the file SpinnerAdapter.java. This class will inherit from Android BaseAdapter and will inflate a layout file that will represents the item view of the spinner. Open the file and add the code below to the file.

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
import java.util.List;
public class SpinnerAdapter extends BaseAdapter{
    private LayoutInflater layoutInflater;
    private List<DataObject> listData;
    private Context context;
    public SpinnerAdapter(Context context, List<DataObject> listData) {
        this.context = context;
        layoutInflater =(LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        this.listData = listData;
    }
    @Override
    public int getCount() {
        return listData.size();
    }
    @Override
    public Object getItem(int position) {
        return (DataObject)listData.get(position);
    }
    @Override
    public long getItemId(int position) {
        return 0;
    }
    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        ViewHolder spinnerHolder;
        if(convertView == null){
            spinnerHolder = new ViewHolder();
            convertView = layoutInflater.inflate(R.layout.spinner_list, parent, false);
            spinnerHolder.spinnerItemList = (TextView)convertView.findViewById(R.id.spinner_list_item);
            convertView.setTag(spinnerHolder);
        }else{
            spinnerHolder = (ViewHolder)convertView.getTag();
        }
        spinnerHolder.spinnerItemList.setText(listData.get(position).getName());
        return convertView;
    }
    class ViewHolder{
        TextView spinnerItemList;
    }
}

Spinner_list.xml

Our SpinnerAdapter inflates a custom android BaseAdapter and the layout contains a TextView which will hold the names of countries. Right click on the layout folder located in res folder, select create new layout and name it spinner_list.xml. Copy and paste the below code inside this layout file.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent">
    <TextView
        android:id="@+id/spinner_list_item"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/app_name"
        android:layout_margin="16dp"
        android:textColor="@color/colorBlack"/>
</LinearLayout>

DataObject.java

Create a new java file and name it DataObject.java. This is an entity class which will hold individual country names. Open the java file and add the below code to the file.

import com.google.gson.annotations.SerializedName;
public class DataObject {
    @SerializedName("name")
    private String name;
    public DataObject(){}
    public DataObject(String name) {
        this.name = name;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

MainActivity.java

In the MainActivity class, we will add the path to our server as a string and create a server request method that connect with our server, obtain the server output and convert it to the response’s object.

Open the MainActivity.java file and add the below code to this class.

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Spinner;
import com.android.volley.Request;
import com.android.volley.RequestQueue;
import com.android.volley.Response;
import com.android.volley.VolleyError;
import com.android.volley.toolbox.StringRequest;
import com.android.volley.toolbox.Volley;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import java.util.Arrays;
import java.util.List;
public class MainActivity extends AppCompatActivity {
    private Spinner spinner;
    private static final String PATH_TO_SERVER = "Path to your server file";
    protected List<DataObject> spinnerData;
    private RequestQueue queue;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        queue = Volley.newRequestQueue(this);
        requestJsonObject();
    }
    private void requestJsonObject(){
        RequestQueue queue = Volley.newRequestQueue(this);
        StringRequest stringRequest = new StringRequest(Request.Method.GET, PATH_TO_SERVER, new Response.Listener<String>() {
            @Override
            public void onResponse(String response) {
                GsonBuilder builder = new GsonBuilder();
                Gson mGson = builder.create();
                spinnerData = Arrays.asList(mGson.fromJson(response, DataObject[].class));
                //display first question to the user
                if(null != spinnerData){
                    spinner = (Spinner) findViewById(R.id.spinner);
                    assert spinner != null;
                    spinner.setVisibility(View.VISIBLE);
                    SpinnerAdapter spinnerAdapter = new SpinnerAdapter(MainActivity.this, spinnerData);
                    spinner.setAdapter(spinnerAdapter);
                }
            }
        }, new Response.ErrorListener() {
            @Override
            public void onErrorResponse(VolleyError error) {
            }
        });
        queue.add(stringRequest);
    }
}

This brings us to the end of this tutorial. I hope that you have learn something. Run your app and see for yourself.

You can download the code for this tutorial below. If you are having hard time downloading the tutorials, kindly contact me.

Remember to subscribe with your email address so that you will be among the first to receive my new android blog post once it is published.

Please if you love this tutorial, kindly download my android app – Complete Mathematics – in Google Play Store and let me know what you think about it.

OTHER INTERESTING POSTS:

One Response

Add a Comment