Android MySQL - Select and Show in ListView(HTTPURLConnection).

July 16, 2017 Oclemy Android MySQL, Android ListView, Android HttpURLConnection 8 minutes, 15 seconds

Previously we had seen how to connect to mysql and isnert data into database from android. Well that was a HTTP POST request we were making.

We can also retrieve data and show them into a ListView.

Still we use the standard Android Networking API that is java.net.HTTURLConnection.

MySQL ListView

While fetching data we will show a progress dialog.
Progress Dialog MySQL

Our MySQL Database

Here's our MySQL table structure:
MySQL Tabe Structure

We have the following fields:

  1. Id(AutoIncremented) - Integer.
  2. Name - String.
  3. Position - String.

Project Structure

Here's the project structure:
Project Structure

Let's go.

1. Gradle Scripts

In our app level build.gradle we add some dependencies.

Build.gradle

We add android.support:appcompat-v7 and android.support:design.

    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'
    }

2. Our PHP Script

Here's the PHP script that will connect to the MySQL database.

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

AndroidManifest.xml

Add the permission for internet in the androidmanifest.xml.

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

Layouts

Let's create some layouts.

1. activity_main.xml

Our MainActivity's primary layout. Will get inflated into MainActivity user interface.
At the root we have android.support.design.widget.CoordinatorLayout.

This layout has the following responsibilities: No. Responsibility
1. Define the AppbarLayout using android.support.design.widget.AppBarLayout tag.
2. Define the ToolBar using the android.support.v7.widget.Toolbar tag.
3. Define the FloatingActionButton using the android.support.design.widget.FloatingActionButton tag.
4. Include the content_main.xml which will render our other views and widgets.
    <?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.mysqlselector.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 layout. The root tag is RelativeLayout.

It has the following responsibilities:

No. Responsibility
1. Define/Hold the ListView that will display our data from MySQL database.
2. It will be included inside the activity_main.xml to form the MainActivity layout.
    <?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.mysqlselector.MainActivity"
        tools:showIn="@layout/activity_main">

        <TextView
            android:padding="5dp"
            android:id="@+id/textView"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Players List!" />
        <ListView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/lv"
            android:layout_below="@+id/textView"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />
    </RelativeLayout>

Java Classes

We have three custom java classes.

(a). Downloader.java

Our Downloader.java class, a public class that will derive from android.os.AsyncTask. This provides us with easy to use threading capabilities.

The purposes of this class includes:

No. Responsibility
1. Receive an android.content.Context object, a URL address and a ListView widget from the MainActivity.
2. Instantiate,show and dismiss progress dialog.
3. Establish connection to the provided URL via the java.net.HTTPURLConnection.
4. Download data and return it as a string in the background thread.
5. Catch IOException and MalformedURLException.
6. Instantiate Parser object, pass Context, downloaded string and listview.And execute it.
    package com.tutorials.hp.mysqlselector;

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

    import java.io.BufferedInputStream;
    import java.io.BufferedReader;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.net.HttpURLConnection;
    import java.net.MalformedURLException;
    import java.net.URL;

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

        Context c;
        String address;
        ListView lv;

        ProgressDialog pd;

        public Downloader(Context c, String address, ListView lv) {
            this.c = c;
            this.address = address;
            this.lv = lv;
        }

        //B4 JOB STARTS
        @Override
        protected void onPreExecute() {
            super.onPreExecute();

            pd=new ProgressDialog(c);
            pd.setTitle("Fetch Data");
            pd.setMessage("Fetching Data...Please wait");
            pd.show();
        }

        @Override
        protected String doInBackground(Void... params) {
            String data=downloadData();
            return data;
        }

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

            pd.dismiss();;

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

            }else
            {
                Toast.makeText(c,"Unable to download data",Toast.LENGTH_SHORT).show();
            }
        }

        private String downloadData()
        {
            //connect and get a stream
            InputStream is=null;
            String line =null;

            try {
                URL url=new URL(address);
                HttpURLConnection con= (HttpURLConnection) url.openConnection();
                is=new BufferedInputStream(con.getInputStream());

                BufferedReader br=new BufferedReader(new InputStreamReader(is));

                StringBuffer sb=new StringBuffer();

                if(br != null) {

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

                }else {
                    return null;
                }

                return sb.toString();

                } catch (MalformedURLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                if(is != null)
                {
                    try {
                        is.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        return null;
        }
    }

(b) Parser.java

This is our custom Parser class. The class still extends android.os.AsyncTask.

This class will parse our JSON string and fill our listview.

It has the following roles:

No. Responsibility
1. Receive Context object,ListView and the downloaded data from Downloaded class.
2. Define an arraylist to hold the downloaded data.
3. Instantiate, show and dismiss the progress dialog.
4. Parse the json string and populate our ArrayList. We do this in the background thread inside the doInbackground() method.
5. Bind our arraylist to our ListView.
    package com.tutorials.hp.mysqlselector;

    import android.app.ProgressDialog;
    import android.content.Context;
    import android.os.AsyncTask;
    import android.support.design.widget.Snackbar;
    import android.view.View;
    import android.widget.AdapterView;
    import android.widget.ArrayAdapter;
    import android.widget.ListView;
    import android.widget.Toast;

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

    import java.util.ArrayList;
    import java.util.List;

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

        Context c;
        ListView lv;
        String data;

        ArrayList<String> players=new ArrayList<>();
        ProgressDialog pd;

        public Parser(Context c, String data, ListView lv) {
            this.c = c;
            this.data = data;
            this.lv = lv;
        }

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

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

        @Override
        protected Integer doInBackground(Void... params) {

            return this.parse();
        }

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

            if(integer == 1)
            {
                //ADAPTER
                ArrayAdapter<String> adapter=new ArrayAdapter<String>(c,android.R.layout.simple_list_item_1,players);

                //ADAPT TO LISTVIEW
                lv.setAdapter(adapter);

                //LISTENET
                lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                    @Override
                    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                        Snackbar.make(view,players.get(position),Snackbar.LENGTH_SHORT).show();;
                    }
                });

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

            pd.dismiss();
        }

        //PARSE RECEIVED DATA
        private int parse()
        {
            try
            {
                //ADD THAT DATA TO JSON ARRAY FIRST
                JSONArray ja=new JSONArray(data);

                //CREATE JO OBJ TO HOLD A SINGLE ITEM
                JSONObject jo=null;

                players.clear();

                //LOOP THRU ARRAY
                for(int i=0;i<ja.length();i++)
                {
                    jo=ja.getJSONObject(i);

                    //RETRIOEVE NAME
                    String name=jo.getString("Name");

                    //ADD IT TO OUR ARRAYLIST
                    players.add(name);
                }

                return 1;

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

            return 0;
        }
    }

MainActivity.java

Our public class that will be the launcher activity. It will derive from android.support.v7.app.AppCompatActivity.

This class the following responsibilities:

No. Responsibility
1. Allow itself to become an android activity component by

deriving from android.support.v7.app.AppCompatActivity.| |2.|Listen to activity creation callbacks by overrding the onCreate() method.| |3.|Invoke the onCreate() method of the parent Activity

class and tell it of a Bundle we've received. | |4.|Inflate the activity_main.xml into a View object and set

it as the content view of this activity.| |5.| Define our URL address. This URL address leads to ou PHP script| |6.| Reference the android.support.v7.widget.Toolbar and set it to the SupportActionBar.| |7.| Reference ListView from layout.| |8.| Instantiate Downloader class. Pass in the URL addtess, ListView and Context.| |9.| Execute the download operation.|

    package com.tutorials.hp.mysqlselector;

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

    public class MainActivity extends AppCompatActivity {

        String url="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);

            final ListView lv= (ListView) findViewById(R.id.lv);
            final Downloader d=new Downloader(this,url,lv);

            FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
            fab.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View view) {
                    //EXECUTE DOWNLOAD
                    d.execute();
                }
            });
        }
    }

You can download the code here.

Comments