Android JSON Parsing - Getting Data from MySQL Database

This Article is about How to Get JSON Data from MySQL Database into Android App using Android Studio. This is the Simple Light Weight and Easiest way to Retrieve Data from MySQL Database.

So, Here I’m going to Cover Different topics such as Basic of JSON, How to fetch and Retrieve into Android App.

Skip the Topic you Already know about.

Table of Contents

1 What is JSON?
2 Fetching Data using PHP from MySQL Database
- 2.1 Fetching Database Table Data(s) with PHP in JSON Format
3 Parsing JSON in Android – Android JSON parsing
4 Conclusion

What is JSON?

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language. (Wikipedia).

JSON Can be Declared by Two Type.

Object
Array

JSON Object

JSON Object starts with curly brackets and Ends with the Same, It holds the Single Object item.

{
  "Name":"Naveen",
  "age":21
}

{

"Name":"Naveen",

"age":21

}

JSON Array

Where JSON Array starts and ends with Square Brackets and it can hold multiple sets of JSON Objects.

[
  "Hello World",
  "Android",
  "Linux",
  "WebCheerz"
]

[

"Hello World",

"Android",

"Linux",

"WebCheerz"

]

JSON Object Along with JSON Array

This code contains Both JSON array and Object which we are going to use in our code.

[
  {
    "name":"Naveen",
    "age":21
  }
  {
    "name":"Kumar",
    "age":22
  }
]

[

{

"name":"Naveen",

"age":21

}

{

"name":"Kumar",

"age":22

}

]

Fetching Data using PHP from MySQL Database

Here I’m using XAMPP in my Linux System. You can also use WAMP, LAMPP or anything else.

Create a New Database with Anyname you want and Create Table too.

Look at Example.

You can use the Below Code in-order to Create a Table.

-- phpMyAdmin SQL Dump
-- version 4.6.5.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Oct 12, 2018 at 04:10 PM
-- Server version: 10.1.21-MariaDB
-- PHP Version: 7.1.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `webcheerz`
--

-- --------------------------------------------------------

--
-- Table structure for table `data`
--

CREATE TABLE `data` (
  `id` int(99) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `data`
--

INSERT INTO `data` (`id`, `name`) VALUES
(1, 'WebCheerz'),
(2, 'Linux');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `data`
--
ALTER TABLE `data`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `data`
--
ALTER TABLE `data`
  MODIFY `id` int(99) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- phpMyAdmin SQL Dump

-- version 4.6.5.2

-- https://www.phpmyadmin.net/

-- Host: localhost

-- Generation Time: Oct 12, 2018 at 04:10 PM

-- Server version: 10.1.21-MariaDB

-- PHP Version: 7.1.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

-- Database: `webcheerz`

-- --------------------------------------------------------

-- Table structure for table `data`

CREATE TABLE `data` (

`id` int(99) NOT NULL,

`name` varchar(100) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `data`

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

(1, 'WebCheerz'),

(2, 'Linux');

-- Indexes for dumped tables

-- Indexes for table `data`

ALTER TABLE `data`

ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `data`

ALTER TABLE `data`

MODIFY `id` int(99) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Fetching Database Table Data(s) with PHP in JSON Format

Here we are using PHP to Fetch data from Database and Display in to JSON Format.

Create a New File named api.php & Paste the Below Code.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "webcheerz";


//This Creates a new connection object using MySQLi
$conn = new mysqli($servername, $username, $password, $database);

//if there is some error connecting to the database with die we will stop the further execution by displaying a message causing the error 
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//if everything is fine then create an array for storing the data 
$webchrz = array(); 

//this is our sql query 
$sql = "SELECT id, name FROM data;";

//creating an statment with the query
$stmt = $conn->prepare($sql);

//executing that statment
$stmt->execute();

//binding results for that statment 
$stmt->bind_result($id, $name);

//looping through all the records
while($stmt->fetch()){
	
	//pushing fetched data in an array 
	$temp = [
		'id'=>$id,
		'name'=>$name
	];
	
	//pushing the array inside the hero array 
	array_push($webchrz, $temp);
}

//displaying the data in json format 
echo json_encode($webchrz);

<?php

$servername = "localhost";

$username = "root";

$password = "";

$database = "webcheerz";

//This Creates a new connection object using MySQLi

$conn = new mysqli($servername, $username, $password, $database);

//if there is some error connecting to the database with die we will stop the further execution by displaying a message causing the error

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

//if everything is fine then create an array for storing the data

$webchrz = array();

//this is our sql query

$sql = "SELECT id, name FROM data;";

//creating an statment with the query

$stmt = $conn->prepare($sql);

//executing that statment

$stmt->execute();

//binding results for that statment

$stmt->bind_result($id, $name);

//looping through all the records

while($stmt->fetch()){

//pushing fetched data in an array

$temp = [

'id'=>$id,

'name'=>$name

];

//pushing the array inside the hero array

array_push($webchrz, $temp);

}

//displaying the data in json format

echo json_encode($webchrz);

Then visit http://localhost/FOLDER Name/api.php

Here is how you’ll get the Result.

As you can see above, We have got our result in JSON Format. (P.S: I’m JSON Handler Chrome Extension to display JSON in Colorful).

Parsing JSON in Android – Android JSON parsing

Creating New Android Studio Project and Choosing Activity

Fire up the Android Studio and Create a New Project and Use name as your choice and Choose Target SDK as your Choice, We are going to Implement Everything on our Own so, Choose EmptyActivity.

My Project Name : JSON Example
My Package Name : json.webcheerz.app

Crafting the UI (User Interface)

I’m changing the Activity Layout to Relative Layout, You can Copy paste the Below Code.

<?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"
    tools:context=".MainActivity">

    <ListView
        android:id="@+id/listView"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true" />


</RelativeLayout>

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

tools:context=".MainActivity">

<ListView

android:id="@+id/listView"

android:layout_width="match_parent"

android:layout_height="match_parent"

android:layout_alignParentLeft="true"

android:layout_alignParentStart="true"

android:layout_alignParentTop="true" />

</RelativeLayout>

Now it’s time to Fetch the Data from the Server.

Fetching the JSON Data from Server

This is the Core part of our Project. Here We are running the App in Outside of our HOST Machines. So we can’t access it by Localhost.

We Need IP Address to Run our Application.

If you’re using Online Server. You can use Domain Name too.

We Need IP Address to get JSON Data from the Server.

Finding IP Address of the Host Machine.

Finding IP Address in Linux

Open Terminal and Type ifconfig then Press Enter.

Here is my Localhost IP Address. You can check the Below Image.

Finding the IP Address in Windows

Open CMD (Command Prompt) and type ipconfig and Press enter. You’ll get the IP Address same as above image.

Accessing the API with IP Address

Before we have used localhost/foldername to access the API, As we are going to use in Remote Device we’ll use IP Address.

http://localhost/webcheerz/api.php

1	http://localhost/webcheerz/api.php

Now Access the API using IP Address of the Host Machine.

http://192.168.31.203/webcheerz/api.php

1	http://192.168.31.203/webcheerz/api.php

Here is my API access via Host IP Address

Setting up Internet Permission

Open Mainfest.xml and Add the Below line, above <application>

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

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

Fetching JSON Data from Server

Now we’re going to fetch Data as a JSON String from the MySQL Server through our API.

Here we’re using ASyncTask which let us to Perform Background Activity of Fetching data without affecting the Main Thread(UI).

We are Parsing the JSON from MySQL with the help of API and Showing in ListView inside Android Application.

Setting up Internet Permission

Open Mainfest.xml and Add the Below line, above <application>

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

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

Code Breakthrough

This Below part gets the JSON Data from MySQL Database and stores into the ListView

private void getJSON(final String urlWebService) {

        class GetJSON extends AsyncTask<Void, Void, String> {

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


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

                try {
                    loadIntoListView(s);
                } catch (JSONException e) {
                    e.printStackTrace();
                }
            }

private void getJSON(final String urlWebService) {

class GetJSON extends AsyncTask<Void, Void, String> {

@Override

protected void onPreExecute() {

super.onPreExecute();

}

@Override

protected void onPostExecute(String s) {

super.onPostExecute(s);

try {

loadIntoListView(s);

} catch (JSONException e) {

e.printStackTrace();

}

This Below Code Helps the App Activity to Perform things in Background.

 @Override
            protected String doInBackground(Void... voids) {
                try {
                    URL url = new URL(urlWebService);
                    HttpURLConnection con = (HttpURLConnection) url.openConnection();
                    StringBuilder sb = new StringBuilder();
                    BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(con.getInputStream()));
                    String json;
                    while ((json = bufferedReader.readLine()) != null) {
                        sb.append(json + "\n");
                    }
                    return sb.toString().trim();
                } catch (Exception e) {
                    return null;
                }
            }
        }
        GetJSON getJSON = new GetJSON();
        getJSON.execute();
    }

@Override

protected String doInBackground(Void... voids) {

try {

URL url = new URL(urlWebService);

HttpURLConnection con = (HttpURLConnection) url.openConnection();

StringBuilder sb = new StringBuilder();

BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(con.getInputStream()));

String json;

while ((json = bufferedReader.readLine()) != null) {

sb.append(json + "\n");

}

return sb.toString().trim();

} catch (Exception e) {

return null;

}

GetJSON getJSON = new GetJSON();

getJSON.execute();

}

Finally, This this Helps the Fetched JSON Data from MySQL Database to Render into the ListView of Android App.

   private void loadIntoListView(String json) throws JSONException {
        JSONArray jsonArray = new JSONArray(json);
        String[] webchrz = new String[jsonArray.length()];
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject obj = jsonArray.getJSONObject(i);
            webchrz[i] = obj.getString("name");
        }
        ArrayAdapter<String> arrayAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, webchrz);
        listView.setAdapter(arrayAdapter);
    }
}

private void loadIntoListView(String json) throws JSONException {

JSONArray jsonArray = new JSONArray(json);

String[] webchrz = new String[jsonArray.length()];

for (int i = 0; i < jsonArray.length(); i++) {

JSONObject obj = jsonArray.getJSONObject(i);

webchrz[i] = obj.getString("name");

}

ArrayAdapter<String> arrayAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, webchrz);

listView.setAdapter(arrayAdapter);

}

The Complete MainActivity.java

Open MainActivity.java and paste the Following code below, It carries all the Activities like Fetching the Data & Showing in the ListView Adapter.

package json.webcheerz.app;

import android.os.AsyncTask;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.ListView;

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

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;

public class MainActivity extends AppCompatActivity {

    ListView listView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        listView = (ListView) findViewById(R.id.listView);
        getJSON("http://192.168.31.203/webcheerz/api.php");
    }


    private void getJSON(final String urlWebService) {

        class GetJSON extends AsyncTask<Void, Void, String> {

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


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

                try {
                    loadIntoListView(s);
                } catch (JSONException e) {
                    e.printStackTrace();
                }
            }

            @Override
            protected String doInBackground(Void... voids) {
                try {
                    URL url = new URL(urlWebService);
                    HttpURLConnection con = (HttpURLConnection) url.openConnection();
                    StringBuilder sb = new StringBuilder();
                    BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(con.getInputStream()));
                    String json;
                    while ((json = bufferedReader.readLine()) != null) {
                        sb.append(json + "\n");
                    }
                    return sb.toString().trim();
                } catch (Exception e) {
                    return null;
                }
            }
        }
        GetJSON getJSON = new GetJSON();
        getJSON.execute();
    }

    private void loadIntoListView(String json) throws JSONException {
        JSONArray jsonArray = new JSONArray(json);
        String[] webchrz = new String[jsonArray.length()];
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject obj = jsonArray.getJSONObject(i);
            webchrz[i] = obj.getString("name");
        }
        ArrayAdapter<String> arrayAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, webchrz);
        listView.setAdapter(arrayAdapter);
    }
}

package json.webcheerz.app;

import android.os.AsyncTask;

import android.support.v7.app.AppCompatActivity;

import android.os.Bundle;

import android.widget.ArrayAdapter;

import android.widget.ListView;

import org.json.JSONArray;

import org.json.JSONException;

import org.json.JSONObject;

import java.io.BufferedReader;

import java.io.InputStreamReader;

import java.net.HttpURLConnection;

import java.net.URL;

public class MainActivity extends AppCompatActivity {

ListView listView;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

listView = (ListView) findViewById(R.id.listView);

getJSON("http://192.168.31.203/webcheerz/api.php");

}

private void getJSON(final String urlWebService) {

class GetJSON extends AsyncTask<Void, Void, String> {

@Override

protected void onPreExecute() {

super.onPreExecute();

}

@Override

protected void onPostExecute(String s) {

super.onPostExecute(s);

try {

loadIntoListView(s);

} catch (JSONException e) {

e.printStackTrace();

}

@Override

protected String doInBackground(Void... voids) {

try {

URL url = new URL(urlWebService);

HttpURLConnection con = (HttpURLConnection) url.openConnection();

StringBuilder sb = new StringBuilder();

BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(con.getInputStream()));

String json;

while ((json = bufferedReader.readLine()) != null) {

sb.append(json + "\n");

}

return sb.toString().trim();

} catch (Exception e) {

return null;

}

GetJSON getJSON = new GetJSON();

getJSON.execute();

}

private void loadIntoListView(String json) throws JSONException {

JSONArray jsonArray = new JSONArray(json);

String[] webchrz = new String[jsonArray.length()];

for (int i = 0; i < jsonArray.length(); i++) {

JSONObject obj = jsonArray.getJSONObject(i);

webchrz[i] = obj.getString("name");

}

ArrayAdapter<String> arrayAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, webchrz);

listView.setAdapter(arrayAdapter);

}

Conclusion

When you Run this Application, You’ll see like this below screenshot.

I’m Running it on Real Device, You can Run on AVD Also (Android Virtual Device)

That’s all for Now !! Will share Source Code to Download soon. 🙂 Thanks.

Was this article helpful?

Yes, thanks!

Not really

Thanks

Thanks for your Feeback. It will helps us to improve our Article quality

Share this on WhatsApp