Android MySQL Client (Part 1)

This is a simple implementation of Android MySQL connection. The idea is to read data from a MySQL database through the internet using a simple Android application & a java web service.

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

Concept is simple, Java web service deployed on Tomcat server has a method which can run a quarry on database to retrieve data & this method returns results as a string output. To connect web service & database I have used JDBC bridge.
The Android application calls that web service method remotely using ksoap library. Then web service runs a query on database table to retrieve data & returns data as a string to Android app. Android app display this data.

1. Creating the database.

First we need to create a new database & customer table. We will access customer table in Android application. In order to create database & customer table run following sql queries.
1. Create the database

CREATE DATABASE retailer;

2.Create table customers

CREATE TABLE customers(
name varchar(20),
C_ID int NOT NULL AUTO_INCREMENT,
address  varchar(20),
email varchar(50),
PRIMARY KEY(C_ID)
);

3. Populate the database

INSERT INTO customers(name,address,email)
VALUES ('Chathura','221B,Akuressa','priyankarahac@gmail.com');

Here I have used auto increment customer ID & it is the primary key of the table. I'm going read data from customer table where C_ID has maximum value.

2. Java web service

package com.retailer.ws;

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

public class RetailerWS {
 public String customerData(){
  String customerInfo = "";
  try{
  Class.forName("com.mysql.jdbc.Driver");
  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/retailer","root","chathura");
  //Find customer information where the customer ID is maximum
  PreparedStatement statement =  con.prepareStatement("SELECT * FROM customers WHERE C_ID = (SELECT MAX(C_ID) FROM customers)");
  ResultSet result = statement.executeQuery();
  
   while(result.next()){
    customerInfo = customerInfo + result.getString("name") + "&" + result.getString("C_ID") + "&"+result.getString("address") + "&"+result.getString("email");
  //Here "&"s are added to the return string. This is help to split the string in Android application 
   }
  }
  
  catch(Exception exc){
   System.out.println(exc.getMessage());
   }
  
  return customerInfo;
  }

}

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

package com.retailer.client;

import android.app.Activity;
import android.os.Bundle;
import org.ksoap2.SoapEnvelope;
import org.ksoap2.serialization.SoapObject;
import org.ksoap2.serialization.SoapPrimitive;
import org.ksoap2.serialization.SoapSerializationEnvelope;
import org.ksoap2.transport.HttpTransportSE;
import android.widget.TextView;

public class RetailerActivity extends Activity {
    private static final String SOAP_ACTION = "http://ws.retailer.com/customerData";
    private static final String METHOD_NAME = "customerData";
    private static final String NAMESPACE = "http://ws.retailer.com/";
    private static final String URL = "http://175.157.234.156:8085/ForBlog/services/RetailerWS?wsdl";
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        SoapObject request = new SoapObject(NAMESPACE, METHOD_NAME);  
        SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
         
        envelope.setOutputSoapObject(request);
 
        HttpTransportSE ht = new HttpTransportSE(URL);
        try {
            ht.call(SOAP_ACTION, envelope);
            SoapPrimitive response = (SoapPrimitive)envelope.getResponse();
            SoapPrimitive s = response;
            String str = s.toString();
            String resultArr[] = str.split("&");//Result string will split & store in an array
           
            TextView tv = new TextView(this);
      
            for(int i = 0; i<resultArr.length;i++){
            tv.append(resultArr[i]+"\n\n");
           }
            setContentView(tv);
   
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Result :

The Android application shows details about the customer which has maximum customer ID

Database table :

You can download updated Android project here . Password : tomcat  
You can download webservice project here. Password : tomcat

This tutorial demonstrate how we can insert data to a MySQL database using an Android application.
If you find this post helpful don't forget to leave a comment. your comments encourage me to write more!