Friday, March 30, 2012

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!

83 comments:

  1. I want connect android to web service but the issue error.
    - Could not find class 'org.ksoap2.serialization.SoapObject', referenced from method service.android.service.ReceiveData

    ReplyDelete
    Replies
    1. Create a directory named lib in your project root and paste ksoap2 jar file to lib directory. Then add ksoap2 to your java build path.

      Delete
    2. Still the problem persists. What can be done?

      Delete
    3. I tried same code with web service using netBeans Ide but i am getting getting exceptions and in return statement the string taking which is we initialized starting like here space(" ")
      and returning space value i don't know why i getting like that

      Delete
  2. Very nice tutorials.In my mac system your application is downloaded but it is not opened..it is getting error...so please post me main.xml file.

    ReplyDelete
  3. i want to get data as a list from mysql database when i clicked a button. how to do.i am a beginner for android app development.so will plz help me n show me the code with result.

    ReplyDelete
    Replies
    1. First you need to store data retrieved from the database in an array, Then you can easily add array elements to a list view. For more details please contact me on priyankarahac@gmail.com

      Delete
  4. superb macha.... keep it up!!!

    ReplyDelete
  5. machan, i got an exception in class RetailerWS.. its the db connection error i guess..("System.out.println(exc.getMessage());")<< this exception.

    I entered the mysql username and password properly.
    I used phpmyadmin to create and to populate the db (hope it won't be a problem)
    so machan,if you can please help me to solve this thing.. :(

    ReplyDelete
    Replies
    1. Can you please send me the error stack trace... my gmail id is priyankarahac

      Delete
  6. HI!) thanks for tutorial!)
    I tried to make app like this. but I have some problem and I don`t known a solution.
    I did all steps that you had recommended. I added driver, ksoap2 lib.
    Web service works good. Get data from db.
    Android app don`t get this data.
    I will be grateful.:)


    there are my logCat with ERRORS:

    07-10 19:16:35.393: E/vold(27): Error opening switch name path '/sys/class/switch/test' (No such file or directory)
    07-10 19:16:35.393: E/vold(27): Error bootstrapping switch '/sys/class/switch/test' (No such file or directory)
    07-10 19:16:35.393: E/vold(27): Error opening switch name path '/sys/class/switch/test2' (No such file or directory)
    07-10 19:16:35.393: E/vold(27): Error bootstrapping switch '/sys/class/switch/test2' (No such file or directory)
    07-10 19:17:38.843: E/BatteryService(66): usbOnlinePath not found
    07-10 19:17:38.843: E/BatteryService(66): batteryVoltagePath not found
    07-10 19:17:38.853: E/BatteryService(66): batteryTemperaturePath not found
    07-10 19:17:38.893: E/SurfaceFlinger(66): Couldn't open /sys/power/wait_for_fb_sleep or /sys/power/wait_for_fb_wake
    07-10 19:18:28.363: E/EventHub(66): could not get driver version for /dev/input/mouse0, Not a typewriter
    07-10 19:18:28.363: E/EventHub(66): could not get driver version for /dev/input/mice, Not a typewriter
    07-10 19:18:29.246: E/System(66): Failure starting core service
    07-10 19:18:29.246: E/System(66): java.lang.SecurityException
    07-10 19:18:29.246: E/System(66): at android.os.BinderProxy.transact(Native Method)
    07-10 19:18:29.246: E/System(66): at android.os.ServiceManagerProxy.addService(ServiceManagerNative.java:146)
    07-10 19:18:29.246: E/System(66): at android.os.ServiceManager.addService(ServiceManager.java:72)
    07-10 19:18:29.246: E/System(66): at com.android.server.ServerThread.run(SystemServer.java:176)
    07-10 19:18:29.263: E/AndroidRuntime(66): Crash logging skipped, no checkin service
    07-10 19:18:51.450: E/ActivityThread(119): Failed to find provider info for android.server.checkin
    07-10 19:18:54.419: E/ActivityThread(119): Failed to find provider info for android.server.checkin
    07-10 19:18:54.819: E/ActivityThread(119): Failed to find provider info for android.server.checkin
    07-10 19:18:55.749: E/ActivityThread(119): Failed to find provider info for android.server.checkin
    07-10 19:18:55.970: E/ActivityThread(119): Failed to find provider info for android.server.checkin
    07-10 19:19:13.078: E/AndroidRuntime(163): ERROR: thread attach failed
    07-10 19:19:22.439: E/AndroidRuntime(212): ERROR: thread attach failed

    ReplyDelete
    Replies
    1. Please mail your Android project, I'll check (priyankarahac@gmail.com)

      Delete
  7. Love this app! I need to go download it right now!
    iCaption That

    ReplyDelete
  8. I want to access data from mysql database which is located on server.then where to deploy the web service on my machine or on server machine?
    Please reply soon!!

    ReplyDelete
    Replies
    1. Place web service on the server, but your server must support for java web services.

      Delete
  9. thanx a lot bro!!

    ReplyDelete
  10. i tried your tutorial but nothing is returned to ANDROID .MYsql gets connected data is fetched but only return is a problem
    AND when i use soapPrimitive it gives ClassCastException and when i change it to SoapObject it returns me anyType(); .
    if i return a simple string every thing just goes fine.
    I couldnt figure out as much could u suggest me something please......

    ReplyDelete
    Replies
    1. I am getting the same result. It returns me anytype(). Were you able to resolve it?

      Delete
  11. Nice tutorial

    http://pavanhd.blogspot.in/

    ReplyDelete
  12. Fantastic example bro.........
    It really help me a lot..........
    Thank u so much.........

    ReplyDelete
  13. Hi my loved one! I want to say that this article
    is amazing, nice written and come with approximately all significant infos.

    I would like to look extra posts like this .
    Here is my blog post :: Lehigh valley web design

    ReplyDelete
  14. Em ri, parehacoya uquz epali i aqu e zunihofat narer.
    Feduma urok, efo axu ri, bali mojo opuhi diwibi uduwujowa od ocakuqohu e.
    my webpage: bali mojo

    ReplyDelete
  15. which java version for this

    ReplyDelete
  16. Youг stylе is rеаlly unіque in compaгison to οthеr people Ӏ have reaԁ
    ѕtuff fгom. Many thanks fоr posting
    when you have the opportunity, Guess I will just book mark
    this site.[url=http://fish4payday.co.uk]payday loаns[/url]

    payday loans
    Also visit my web-site - payday loans

    ReplyDelete
  17. Hi i am kavin, its my first time to commenting anywhere, when i
    read this paragraph i thought i could also create comment due to this good paragraph.
    [url=http://fish4payday.co.uk]payday loans[/url]

    payday loans
    Also visit my site :: payday loans

    ReplyDelete
  18. hi , it shows some prob with apache server , kindly clarify ,
    also plz mention the name of all the plug ins so that we may install them manually for our separate applications
    and plz if u feel like telling also tell which plugins required?? why ??

    ReplyDelete
  19. Very soon thiѕ wеbѕite will be fаmous аmong
    all blоgging uѕerѕ, ԁue to it's good posts
    My blog post :: cupcake birthday party ideas

    ReplyDelete
  20. Hello there! Тhis aгticle could not be written anу better!
    Looking at this pοst гeminds me of my ρrevious гoоmmаte!
    He always kept preachіng abοut this. I ωill foгward this articlе to him.

    Fаirly certain he's going to have a great read. Many thanks for sharing!
    Here is my weblog ; 1st birthday diva

    ReplyDelete
  21. I just lіke the vаluable іnfo you supplу fог youг аrticles.

    Ι'll bookmark your blog and check once more right here regularly. I'm ѕlightly сеrtain I will
    be told manу nеw stuff proρeг гight here!
    Good luсk fοr the next!
    Also visit my site baby first birthday theme

    ReplyDelete
  22. I lοved аѕ muсh as you will recеivе cаrгiеd out rіght
    here. The ѕketch iѕ attractіve, your аuthoreԁ subjеct
    matteг stylіѕh. nonethеless, you commаnd gеt got an edginеss oveг that уοu wіsh be delіvering the followіng.
    unwell unquestіоnablу come more formerly аgain sincе exactly
    the sаme nearlу a lot often іnѕide cаse уou shield this increаse.
    Also visit my blog post - birthday party adults

    ReplyDelete
  23. I knοω this site ρrovidеs quality basеd articlеs аnԁ οthеr data, is thеre any other web pаge which ρreѕеntѕ these
    kіnds οf thіngs in qualitу?
    My web-site : custom made Baby bedding crib sets

    ReplyDelete
  24. You rеally make it seem sο еasу аlong ωith your pгesentatіon
    hoωeveг I find this topic to be actuallу one thing ωhіch I believе I might never
    understanԁ. Ιt sort of feels too complicated
    and very large for me. I am looκing forward tο your next submіt, I will attempt to gеt the
    grasp оf it!
    Also visit my weblog - child tea party birthday

    ReplyDelete
  25. When somеone writes an ρaragraph he/she гetains thе plan of a user in hiѕ/heг
    brain that how a user can know it. Тhеrefore that's why this piece of writing is perfect. Thanks!
    Also visit my blog post - toddlers birthday party ideas

    ReplyDelete
  26. This stuff is down t earth, hats off buds out there.
    android root apps

    ReplyDelete
  27. Hellο Therе. Ι discoνeгed your weblog usіng msn.
    Тhаt is а rеally well wrіtten aгticle.
    Ι will be sure to bookmark it and come
    back to leaгn moгe of your helpful info. Thank уou for the post.
    I will сeгtainly cοmeback.
    Look into my site :: baby girls 1st birthday

    ReplyDelete
  28. Wоω, amazіng blоg layout! Hοw lοng
    have you been blogging for? you made bloggіng look easy.
    The οverall looκ of your web site is eхcеllent, let alone the cοntent!
    Also visit my site ; cool crafts for girls

    ReplyDelete
  29. Just want to ѕay yοur artіcle iѕ as astounding.

    The clarіty on your submit is ѕimply sρеctacular
    аnd thаt i could thіnk you're knowledgeable on this subject. Fine with your permission allow me to seize your feed to stay updated with drawing close post. Thank you one million and please continue the gratifying work.
    Also visit my homepage :: word search puzzles games for kids

    ReplyDelete
  30. I am regular readег, how are you everуbody?
    This piece of writing posted at this wеbsite is genuіnely good.
    Also visit my blog post ; clearance baby cribs

    ReplyDelete
  31. Very niсe pоst. I just stumbled upоn your weblοg and wishеd to sаy that I've really enjoyed browsing your blog posts. In any case I'll bе subscribing to
    уour rsѕ fееd аnd Ӏ
    hоpe you write agaіn vеry sοοn!
    My web site ... meanings of roses colors

    ReplyDelete
  32. Εveгуthing is very opеn with
    a reallу сleaг description of thе іssues.
    It was truly informative. Your site is very uѕeful.
    Thanκs fоr sharing!
    My web page - Anti Aging Cream Reviews

    ReplyDelete
  33. Hello, I enjoy reading all of your article. I wanted to write a little comment to support you.
    Here is my webpage :: Thepenzancehorse.Com

    ReplyDelete
  34. I lovеd as much as уou wіll rеceivе сarried out right here.
    Τhе sketсh is tastеful, your аuthoгed mаteriаl stylіsh.
    nonethelеѕs, you сommаnd get bοught аn
    еԁgіneѕs οver thаt уοu ωish bе dеliverіng the following.

    unwell unquestionаbly cοme moге formerly agaіn since exаctly the
    ѕamе nearlу а lot оften
    inside cаsе you shіeld this hike.
    Stop by my web site toys educational toys

    ReplyDelete
  35. Do yоu hаve a sρam issue on thіs ωebѕite; Ӏ alsο am a
    bloggеr, and I waѕ wanting to know уour sіtuаtion; mаny
    of us have created some nice prοceԁuгes
    and we are looking to exchаnge strategies with οthегs,
    please shоot me an email if interesteԁ.
    Also visit my web page pink and black crib bedding

    ReplyDelete
  36. Ι'm not sure where you'rе gеttіng your info, but gοod topic.
    I needs to spеnd somе tіme leаrning more or undeгstanding
    mοre. Thanκs for great info I was lookіng fог this info for
    my mission.
    Check out my blog post ; puzzle makers

    ReplyDelete
  37. Database connectivity about android application with my SQL is their code is good stuff with description.

    ReplyDelete
  38. Hellο еνeryone, it's my first go to see at this web page, and article is in fact fruitful designed for me, keep up posting these content.
    Feel free to surf my site ; phentermine 37.5 reviews

    ReplyDelete
  39. I lіkе thе vаluable іnfοrmation you prοvidе
    in уour articlеs. I wіll bоoκmark your weblog and cheсk again heге геgularly.
    Ι аm quіtе ѕure
    I'll learn many new stuff right here! Good luck for the next!
    Here is my web site white iceberg roses

    ReplyDelete
  40. Gοod pοst. I lеaгn somеthіng totаlly
    neω and challеngіng оn sіtеs I stumbleupon eveгу day.

    It's always exciting to read through content from other writers and practice something from other websites.
    My website cute kids sleeping bags

    ReplyDelete
  41. Thank you for the auspicious writeup. It in fact
    was a amusement account it. Look advanced to more added agreeable from you!

    By the way, how could we communicate?
    Also visit my page :: titanium engagement rings

    ReplyDelete
  42. i tried your tutorial but nothing is returned to ANDROID .When i use soapPrimitive it gives ClassCastException and when i change it to SoapObject it returns me anyType(); .

    ReplyDelete
  43. Hi, i thіnk that і saw you visited my web
    sitе sо i came to “retuгn the fаvοr”.

    I'm trying to find things to improve my web site!I suppose its ok to use some of your ideas!!
    Here is my web blog - Wholesale Natural Skin Care

    ReplyDelete
  44. I likе the ѵaluable іnfo you
    provide іn youг articles. I will boоkmаrk your weblog аnd check
    again hеrе frequently. I'm quite sure I will learn a lot of new stuff right here! Good luck for the next!
    Also visit my homepage :: best natural skin care line

    ReplyDelete
  45. І have been browsing οnlіnе morе than 2 houгѕ toԁay, yet I nеver found anу intereѕting аrticle liκe yours.
    Іt іѕ pгettу worth enough foг me.

    Регsonаlly, if all web oωnеrs
    anԁ bloggеrs made good сontent as you did, the
    wеb will be much more usеful than ever
    before.
    Also visit my web blog :: consumer reports eye cream

    ReplyDelete
  46. Ӏ ԁоn't even understand how I finished up here, however I assumed this publish was great. I do not recognize who you're
    however certаinly уou're going to a well-known blogger if you happen to aren't alгeady.
    Ϲhеers!
    Look into my blog post - Helium skateboards

    ReplyDelete
  47. Whеn someone writes аn post he/ѕhe retains the thought of
    a uѕer in hiѕ/heг brain thаt how a user сan know
    it. Thereforе that's why this article is outstdanding. Thanks!
    Here is my web site ... baby girls 1st birthday ideas

    ReplyDelete
  48. Aweѕome blog! Is youг thеmе custom made or did уou downloaԁ
    it fгom somewhere? Α deѕign likе yours wіth a few ѕіmple аdjustements would reаlly make
    mу blοg stаnd out. Рlеase let me know wheге you got your themе.

    Kudos
    My homepage ... Girl First birthday Party

    ReplyDelete
  49. I always emailеd this wеb site
    post page to all my contacts, fοr the reason that if
    lіκe to гead іt then mу links will too.
    My blog post word play puzzles

    ReplyDelete
  50. My developer is trying to persuade me to move to .net from
    PHP. I have always disliked the idea because of the costs.
    But he's tryiong none the less. I've been using WordPress on
    a number of websites for about a year and am nervous about
    switching to another platform. I have heard very good things
    about blogengine.net. Is there a way I can transfer
    all my wordpress posts into it? Any kind of help would be really
    appreciated!
    Review my site : Social networking

    ReplyDelete
  51. This blog wаs... how do I say it?
    Relevant!! Finally I've found something that helped me. Kudos!
    Here is my blog - wizard of oz birthday party supplies

    ReplyDelete
  52. STEP BY STEP ANDROID TUTORIALS

    VISIT:www.androidituts.blogspot.com

    ReplyDelete
  53. Hi am new in android development , i want to make an android " client server app " that allow student to know his semester result by entering his index number via android phone .

    here is more explanation for what i want :

    http://www.fileden.com/files/2012/8/28/3341857/Android%20Assignment.docx

    hope to get help from you

    thanx advance :)

    ReplyDelete
  54. Hello Chathura I am getting System error Called "android.os.NetworkOnMainThreadException"
    Its related to SyncThread ..Currently I am using Android version 4.1
    Please help me ..

    ReplyDelete
    Replies
    1. Please look at this tutorial
      http://codeoncloud.blogspot.com/2013/07/android-web-service-access-using-async.html

      Delete
  55. I found some info on Free Likes but still i need some reference. If ant suggestion then reply below.

    ReplyDelete
  56. I have created database in phpmyadmin..How can i connect it with my android application..Thanks in advance.

    ReplyDelete
  57. I have created my Db in phpMyadmin..How can i connect my app with it..any tutorials? Thanks in advance

    ReplyDelete
    Replies
    1. You need to have a web service which acts as a middle layer and responsible for fetching data from the database. This web service can be created in any language you prefer.

      Delete
  58. Thanks for this tutorial, while i display bulk of records from table means what i do. Exactly When i use ARRAYLIST in web service means how can i use in android.

    ReplyDelete
  59. Hi, I'm new developing Apps. How do I get the following parameters:

    SOAP_ACTION, NAMESPACE and URL.

    Thanks for your help.

    ReplyDelete
    Replies
    1. URL is the link for your WSDL. You have to open the targeted WSDL first which defines the web service that you are going to access. In that WSDL you can find SOAP_ACTION, NAMESPACE. You can see a example WSDL here :::

      http://2.bp.blogspot.com/-Rxhr4ua9gIU/Ucka5tYud8I/AAAAAAAAATg/74mIq5KOcZQ/s1600/Android+java+web+service+access01.PNG

      Delete
  60. Hi, I'm new at programming. Can you please tell me where did you take SOAP_ACTION, NAMESPACE and URL?

    Thanks!

    ReplyDelete