Tutorial Online

In this post Im going to describe how we can read data from MySQL database and show them in a Android list view. You can download the complete Android project from here. To fetch data here I used a PHP script which encodes data into json format.
This project has three main parts.
1. MySQL database
2. PHP web service
3.Android web service client

1. MySQL database.
My database has only one table named "emp_info" and it has two columns. "employee name" and "employee no". "employee no" is the primary key.

2.PHP web service
Use following PHP script to fetch data from the database and to encode data in to json format.

<?php
$host="XXXXX"; //replace with database hostname 
$username="XXXXX"; //replace with database username 
$password="XXXXX"; //replace with database password 
$db_name="XXXXXX"; //replace with database name

$con=mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "select * from emp_info"; 
$result = mysql_query($sql);
$json = array();

if(mysql_num_rows($result)){
while($row=mysql_fetch_assoc($result)){
$json[emp_info][]=$row;
}
}
mysql_close($con);
echo json_encode($json); 
?> 

You can see the output of  php by clicking below url:
http://cpriyankara.coolpage.biz/employee_details.php

3.Android web service client.
This part is bit complected. Android activity is a combination of Async Task json and list view. If you are not familiar with those stuff look following tutorials.

Android Async Task and web service access
http://codeoncloud.blogspot.com/2013/07/android-web-service-access-using-async.html

Android list view
http://codeoncloud.blogspot.com/2013/07/how-to-populate-android-list-view-from.html

Here is the code for main Android activity.

package com.axel.mysqlphpjson;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;

public class MainActivity extends Activity {
 private String jsonResult;
 private String url = "http://cpriyankara.coolpage.biz/employee_details.php";
 private ListView listView;

 @Override
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_main);
  listView = (ListView) findViewById(R.id.listView1);
  accessWebService();
 }

 @Override
 public boolean onCreateOptionsMenu(Menu menu) {
  // Inflate the menu; this adds items to the action bar if it is present.
  getMenuInflater().inflate(R.menu.main, menu);
  return true;
 }

 // Async Task to access the web
 private class JsonReadTask extends AsyncTask<String, Void, String> {
  @Override
  protected String doInBackground(String... params) {
   HttpClient httpclient = new DefaultHttpClient();
   HttpPost httppost = new HttpPost(params[0]);
   try {
    HttpResponse response = httpclient.execute(httppost);
    jsonResult = inputStreamToString(
      response.getEntity().getContent()).toString();
   }

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

  private StringBuilder inputStreamToString(InputStream is) {
   String rLine = "";
   StringBuilder answer = new StringBuilder();
   BufferedReader rd = new BufferedReader(new InputStreamReader(is));

   try {
    while ((rLine = rd.readLine()) != null) {
     answer.append(rLine);
    }
   }

   catch (IOException e) {
    // e.printStackTrace();
    Toast.makeText(getApplicationContext(),
      "Error..." + e.toString(), Toast.LENGTH_LONG).show();
   }
   return answer;
  }

  @Override
  protected void onPostExecute(String result) {
   ListDrwaer();
  }
 }// end async task

 public void accessWebService() {
  JsonReadTask task = new JsonReadTask();
  // passes values for the urls string array
  task.execute(new String[] { url });
 }

 // build hash set for list view
 public void ListDrwaer() {
  List<Map<String, String>> employeeList = new ArrayList<Map<String, String>>();

  try {
   JSONObject jsonResponse = new JSONObject(jsonResult);
   JSONArray jsonMainNode = jsonResponse.optJSONArray("emp_info");

   for (int i = 0; i < jsonMainNode.length(); i++) {
    JSONObject jsonChildNode = jsonMainNode.getJSONObject(i);
    String name = jsonChildNode.optString("employee name");
    String number = jsonChildNode.optString("employee no");
    String outPut = name + "-" + number;
    employeeList.add(createEmployee("employees", outPut));
   }
  } catch (JSONException e) {
   Toast.makeText(getApplicationContext(), "Error" + e.toString(),
     Toast.LENGTH_SHORT).show();
  }

  SimpleAdapter simpleAdapter = new SimpleAdapter(this, employeeList,
    android.R.layout.simple_list_item_1,
    new String[] { "employees" }, new int[] { android.R.id.text1 });
  listView.setAdapter(simpleAdapter);
 }

 private HashMap<String, String> createEmployee(String name, String number) {
  HashMap<String, String> employeeNameNo = new HashMap<String, String>();
  employeeNameNo.put(name, number);
  return employeeNameNo;
 }
}

Add Internet permission to AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.axel.mysqlphpjson"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="17" />

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

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="com.axel.mysqlphpjson.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Code for main activity layout.

<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=".MainActivity" >

    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="14dp" >
    </ListView>

</RelativeLayout>

Quick demo of the application:


Was above information helpful?
Your comments always encourage me to write more...

Insert data to a database.

My first tutorial Android MySQL client is about read data from a MySQL database. Here Im going to insert data to MySQL database using an Android program. Concept is same as before (Before start test this read first tutorial.)

Program has three main parts.
1. Database (MySQL)
2. Java web service.
3. Android application.

Java web service deployed on Tomcat server has a method which accepts two values and it runs a quarry on database to insert data & also this method returns a string.
The Android application calls that web service method remotely with two values using ksoap library. Then web service runs a query on database table and inserts data

CREATE DATABASE login;

USE login;

CREATE TABLE users(
name varchar(20),
password  varchar(20)
);

Here is the content of my web service

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Users {
 
 public String insertData(String userName,String userPassword){
  
  try{
   
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/login","root","chathura");
   PreparedStatement statement =  con.prepareStatement("INSERT INTO users(name,password) VALUES ("+userName+","+userPassword+");");
   int result = statement.executeUpdate();
  }
  
   catch(Exception exc){
    System.out.println(exc.getMessage());
    }

  return "Insertion successfull!!";
  }

}

In line 12 root and chathura are user and the password. Change those with your username and the password.
This java web service has JDBC connector to access the database. Click here to download the connector.Import JDBC connector to your project. This tutorial is about importing the ksaop library. In the same way you can import JDBC library also. It is simple
You can implement the web service easily by following my these posts.
1. Create java web service in Eclipse using Axis2 (Part 01) 
2. Create java web service in Eclipse using Axis2 (Part 02) 

3. Android application.

The Android application uses ksoap2 library to access java web service. You can find More details about implementation of Android client applications from here. If you are planning to use new Android version read this tutorial.

Here is the code for Android application.
You need to change name space, url, soap action and method name according to your web service.

import org.ksoap2.SoapEnvelope;
import org.ksoap2.serialization.PropertyInfo;
import org.ksoap2.serialization.SoapObject;
import org.ksoap2.serialization.SoapPrimitive;
import org.ksoap2.serialization.SoapSerializationEnvelope;
import org.ksoap2.transport.HttpTransportSE;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class AndroidMySQLClientActivity extends Activity{
    private final String NAMESPACE = "http://ws.login.com";
    private final String URL = "http://175.157.3.42:8085/InsertToUsers/services/Users?wsdl";
    private final String SOAP_ACTION = "http://ws.login.com/insertData";
    private final String METHOD_NAME = "insertData";
    Button btninsert;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        btninsert = (Button)findViewById(R.id.btn_insert);
        btninsert.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {
             insertValues();
            }
        });
    }
    
    public void insertValues(){
     SoapObject request = new SoapObject(NAMESPACE, METHOD_NAME);
     EditText userName = (EditText) findViewById(R.id.editText1);
     String user_Name = userName.getText().toString();
     EditText userPassword = (EditText) findViewById(R.id.editText2);
     String user_Password = userPassword.getText().toString();
     
     //Pass value for userName variable of the web service
        PropertyInfo unameProp =new PropertyInfo();
        unameProp.setName("userName");//Define the variable name in the web service method
        unameProp.setValue(user_Name);//Define value for fname variable
        unameProp.setType(String.class);//Define the type of the variable
        request.addProperty(unameProp);//Pass properties to the variable
      
      //Pass value for userPassword variable of the web service
        PropertyInfo passwordProp =new PropertyInfo();
        passwordProp.setName("userPassword");
        passwordProp.setValue(user_Password);
        passwordProp.setType(String.class);
        request.addProperty(passwordProp);
         
        SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
        envelope.setOutputSoapObject(request);
        HttpTransportSE androidHttpTransport = new HttpTransportSE(URL);
     
        try{
         androidHttpTransport.call(SOAP_ACTION, envelope);
            SoapPrimitive response = (SoapPrimitive)envelope.getResponse();
            
            TextView result = (TextView) findViewById(R.id.textView2);
            result.setText(response.toString());
      
     }
     catch(Exception e){
      
     }
    }

}

5. Content of the Manifest.xml (Add internet permission)

Note : Click here to download the Android project.
Do you think this is helpful ? Please write your ideas :)