Saturday, 28 December 2013

Post 8: PC and Android database communication

Android PC communication Project

In this post I'll show you how an Android device can query information from a Database (DB), that is on the PC, and the PC responds back with the requested results.

The communication between Android and PC is realized via Sockets. The protocol is TCP. The DB is MySQL. As IDE I'm using NetBeans.

This project consists of three parts. I'll show you how to implement each of these parts:

First, we need to set up a Database that contains the table and information that our Android App will later retrieve respectively the Java App (on the PC) will send to the Android App.

Second, we will implement a Java Application that runs the PC. The Java App is a Proxy that accepts the request from the Android App, turns it into a SQL query and then communicates with the Database to retrieve the information requested from the App.

Last but not least we will build an Android App, so the user can type in the requests. 

Here I'll publish all of my code to this project. You can just copy and paste and it will work. If you encounter any problems along the way, don't hesitate to contact me. :) I try my best to get back to you as soon as possible.

Set up Database

We will set up a simple MySQL Database containing two tables: "test" and "list". The list-table has a foreign-key to the test-table.The structure of the two tables are as follows:

The content of the test-table is as follows:

The content of the list-table is as follows:


Java Application (on PC)

This Application is structured in MVC (Model View Controller). It uses five classes.


The View

The View only represents the Widgets, like Buttons, TexViews, etc. but no logic whatsoever. This form only contains a button to open a Socket on the Server (PC).

 

The code is just as simple: 

package socket_tcpserver_reference;

import java.awt.event.ActionListener;
import javax.swing.*;

/**
 *
 * @author pad
 */
public class serverView extends JFrame{
    private JButton bStart;
   
    public serverView(){
        super("Database Proxy");
       
        JPanel panelField=new JPanel();
        bStart=new JButton("Start");
        panelField.add(bStart);
        add(panelField);
    }
   
    void addStartButtonListener(ActionListener ListenForButton){
        bStart.addActionListener(ListenForButton);
    }
   
    public void setEnableStartButton(boolean b){
        bStart.setEnabled(b);
    }
}
 

The Model

The Model contains the so called "Business Logic". In our case it connects to the Database (DB) and gets the results from it.


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package socket_tcpserver_reference;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 *
 * @author pad
 */
public class serverModel {
    private String url = "jdbc:mysql://localhost:3306/";
    private String dbName = "androidpcdb";
    private String driver = "com.mysql.jdbc.Driver";// add library "MySQL JDBC Driver" in Folder "Library"
    private String userName = "root"; 
    private String password = "";
    
    private String resultDB="";

    public String getDBResults(String query){
        //calling mServer in the controller
        //here only getting "resultDB"
        Statement stmt=null;
        ResultSet rs=null;
        Connection conn=null;
        try{
            //this.query=query;
            Class.forName(driver).newInstance();
            conn=(Connection) DriverManager.getConnection(url+dbName, userName, password);
            stmt=(Statement) conn.createStatement();
            rs=stmt.executeQuery(query);
            
            ResultSetMetaData meta=(ResultSetMetaData) rs.getMetaData();
            int numberOfColumns=meta.getColumnCount();
            Object[] rowData=new Object[numberOfColumns];
            resultDB="";
            while(rs.next()){
                for (int i=0; i< rowData.length;++i){
                    resultDB+=String.valueOf(rs.getObject(i+1));
                    resultDB+="*";
                }
            }
            conn.close();
        }catch(Exception ex){
            ex.printStackTrace();
            
        }finally{
            try{
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex){
                ex.printStackTrace();
            }
        }
        //mServer.sendMessage(resultDB);
        return resultDB;
    }
}



In order for the DB connection to work, you have to add the MySQL JDBC Driver. Here's how it's done: Right click on the "Libraries" folder. Then select "MySQL JDBC Driver" and it should like this after it's done correctly:


The Controller

The Controller contains the logic of the View, e.g. whenever a button on the View is clicked the Controller executes an action. In our case, when the button is clicked the controller starts the TCP-Server. When the TCP-Server gets a message, the Model will be called. He, the model, will get the results from the DB and sends it back to the Controller. The controller then will forward the results to the TCP-Server. The TCP-Server will then send the results to the TCP-Client.


package socket_tcpserver_reference;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

/**
 *
 * @author pad
 */
public class serverController {
    private serverModel myServerModel;
    private serverView myServerView;
    private TCPServer mServer;
    
    public serverController(serverModel myServerModel, serverView myServerView){
        this.myServerModel=myServerModel;
        this.myServerView=myServerView;
        
        this.myServerView.addStartButtonListener(new startConnection());
    }
    
    class startConnection implements ActionListener{

        @Override
        public void actionPerformed(ActionEvent e) {
            //disable Start Button
            myServerView.setEnableStartButton(false);
            
            //creates the object OnMessageReceived asked by the TCPServer constructor
            mServer =new TCPServer(new TCPServer.OnMessageReceived() {

                @Override
                //this method declared in the interface from TCPServer class is implemented here
                //this method is actually a callback method, because it will run every time when it will be called from
                //TCPServer class (at the while loop)
                public void messageReceived(String message) {
                    //get the Results from DB and sends it back to the Caller (Client)
                    mServer.sendMessage(myServerModel.getDBResults(message));
                }
            });
            mServer.start();
        }
    }
}

TCP Server

The TCP Server opens a socket and is responsible for getting the message, send from the Client. The TCP Server then "translates" this message into an SQL query and sends it the Controller (see "The Controller). 

package socket_tcpserver_reference;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;     //For sending messages over the network
import java.net.ServerSocket;   //Socket for Server
import java.net.Socket;         //Socket for Client

/**
 *
 * @author pad
 */
public class TCPServer extends Thread{
    public static final int SERVERPORT=4444;
    private boolean running=false;
    private PrintWriter mOut;
    
    private OnMessageReceived messageListener;
    
    private String query;
    
    //constructor
    public TCPServer(OnMessageReceived messageListener){
        this.messageListener=messageListener;
    }
    
    public void sendMessage(String message){
        if(mOut!=null && !mOut.checkError()){
            mOut.println(message);
            mOut.flush();
        }
    }
    
    @Override
    public void run(){
        super.run();
        running=true;
        try{
            System.out.println("Server: Connecting....");
            //This is the Socket for the Server
            //it waits until a client contacts the server.
            ServerSocket serverSocket=new ServerSocket(SERVERPORT);
            
            //This is the Socket for the Client
            //the method "accept()" of the Server Socket listens 
            //for a request for connection.
            Socket client =serverSocket.accept();
            System.out.println("Server: Connection accepted");
            
            try{
                //sends message to the client
                mOut=new PrintWriter(new BufferedWriter(new OutputStreamWriter(client.getOutputStream())), true);
                
                //reads meassage received from the client
                BufferedReader in=new BufferedReader(new InputStreamReader(client.getInputStream()));
                        
                //this infinite while loop listens 
                //for incming messages from the Client
                while(running){
                    //incoming message
                    String message=in.readLine();
                    //we need to extract the message and turn it into a SQL query
                    //So we have to split the message to it's "component"
                    String[] extractedMessage=message.split("\\*", -1);
                    
                    String extractedId=extractedMessage[0];
                    String extractedName=extractedMessage[1];
                    
                    if (message!=null&&messageListener!=null){
                        String queryStart;
                        String queryMiddle;
                        String queryEnd;
                        
                        query="";
                        queryStart="SELECT t.id, t.name, t.value,t.description, COUNT(*) FROM test t, list l WHERE l.test_id=t.id ";
                        queryMiddle="";
                        queryEnd="GROUP BY t.id;";
                        
                        if(!extractedId.equals(""))
                            queryMiddle+="AND t.id ='"+extractedId+"' ";
                        if(!extractedName.equals(""))
                            //search for strings
                            queryMiddle+="AND t.name LIKE '%"+extractedName+"%'";
                        
                        query=queryStart+queryMiddle+queryEnd;
                        //message received from the client
                        messageListener.messageReceived(query);
                    }
                }
            }catch(Exception ex){
                ex.printStackTrace();
                System.out.println("Server: Error");
            }finally{
                client.close();
                System.out.println("Server: Client Socket closed.");
            }
        }catch(Exception ex){
            ex.printStackTrace();
            System.out.println("Server: Error");
        }
    }
    
    //Declare the interface. The method messageReceived(String message) 
    //will must be implemented in the ServerBoard
    //class at on startServer button click
    public interface OnMessageReceived {
        public void messageReceived(String message);
    }
}

Main

In order for all of our classes to run, we need a main method. This main method is in a separate class:

package socket_tcpserver_reference;

/**
 *
 * @author pad
 */
public class Socket_TCPServer_reference {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here
        serverView myServerView=new serverView();
        serverModel myServerModel=new serverModel();
        
        serverController myServerController=new serverController(myServerModel, myServerView);
        myServerView.setVisible(true);
        myServerView.setSize(200, 200);
        
        
    }
}

Android App

The Android App uses only two classes.But as we will later see we need in addition to these classes also xml files, because contrary to Java Application Android uses XML files to define the appearance of the software.


Android User Interface

The user interface on our Android will look like this:
In the first two EditText you can type in the attributes you want to search for. With button "Search" you start querying the Database. After clicking the button the content of the EditTexts will be deleted. The results of the query will be displayed in the table below the aforementioned widgets.

MainActivity

package com.example.socket_tcpclient_reference;

import android.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.content.Context;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TableLayout;
import android.widget.TextView;

public class MainActivity extends Activity {

 private TCPClient mTcpClient;
 
 //The content of the Table will be in an array
 //each column gets an array
 private Object[] colId={"ID"};
 private Object[] colName={"Name"};
 private Object[] colValue={"Value"};
 private Object[] colDescription={"Description"};
 private Object[] colAmount={"Amount"};
 
 
 @Override
 //Called when the activity is first created.
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_main);
  
  
  final EditText et_id=(EditText)findViewById(R.id.editText_id);
  final EditText et_name=(EditText)findViewById(R.id.editText_name);
  
  Button send=(Button)findViewById(R.id.button_search);
  
  new connectTask().execute("");
  fillTable();
  
  send.setOnClickListener(new View.OnClickListener(){
   
   public void onClick(View arg0){
    String message_id=et_id.getText().toString();
    String message_name=et_name.getText().toString();
    
    //message that will be sent to Server:
    //the "*" is to mark the later split
    String message=message_id+"*"+message_name;
    
    //sends message to the server
    if(mTcpClient!=null){
     mTcpClient.sendMessage(message);
    }
    et_id.setText("");
    et_name.setText("");
    
   }
  });
 }
 
 public class connectTask extends AsyncTask< String, String, TCPClient>{

  @Override
  protected TCPClient doInBackground(String... message) {
   //create a TCPClient object
   mTcpClient=new TCPClient(new TCPClient.OnMessageReceived(){

    @Override
    //here the messageReceived method is implemented!!!!
    public void messageReceived(String message) {
     publishProgress(message);
    }
   });
   mTcpClient.run();
   return null;
  }
  
  protected void onProgressUpdate(String... values){
   super.onProgressUpdate(values);
   
   String[] resultDB=values[0].split("\\*",-1);
   int rowCount=resultDB.length/5;
   
   //initialize string objects for table columns
   colId=new Object[rowCount];
   colName=new Object[rowCount];
   colValue=new Object[rowCount];
   colDescription=new Object[rowCount];
   colAmount=new Object[rowCount];
   
   // this is the index we need to go through the received object
   int index=0;
   for (int row=0; row< rowCount; row++){
    //column<5 data-blogger-escaped-0:="" data-blogger-escaped-1:="" data-blogger-escaped-2:="" data-blogger-escaped-3:="" data-blogger-escaped-4:="" data-blogger-escaped-5="" data-blogger-escaped-5th="" data-blogger-escaped-after="" data-blogger-escaped-because="" data-blogger-escaped-break="" data-blogger-escaped-case="" data-blogger-escaped-colamount="" data-blogger-escaped-coldescription="" data-blogger-escaped-colid="" data-blogger-escaped-colname="" data-blogger-escaped-column="" data-blogger-escaped-columns="" data-blogger-escaped-colvalue="" data-blogger-escaped-default:="" data-blogger-escaped-filltable="" data-blogger-escaped-for="" data-blogger-escaped-ill="" data-blogger-escaped-index="" data-blogger-escaped-int="" data-blogger-escaped-justify="" data-blogger-escaped-know="" data-blogger-escaped-line="" data-blogger-escaped-log.d="" data-blogger-escaped-mod="" data-blogger-escaped-public="" data-blogger-escaped-receive="" data-blogger-escaped-resultdb="" data-blogger-escaped-row="" data-blogger-escaped-rowcount="+rowCount);
  TableLayout table=(TableLayout)this.findViewById(R.id.tableLayout);
  //clears the table before new input comes in
  table.removeAllViews();
  
  for (int i=0; i< rowCount;i++){
   //fills the table with content
   fillRow(table, i);
  }
 }
 
 //fills the row with the values in the Object[]-Arrays
 public void fillRow(TableLayout table, int rowNumber){
  LayoutInflater inflater=(LayoutInflater) getSystemService(Context.LAYOUT_INFLATER_SERVICE);
  View fullRow=inflater.inflate(R.layout.row, null, false);
  
  TextView tvId=(TextView) fullRow.findViewById(R.id.rowId);
  tvId.setText(String.valueOf(colId[rowNumber]));
  
  TextView tvName=(TextView) fullRow.findViewById(R.id.rowName);
  tvName.setText(String.valueOf(colName[rowNumber]));
  
  TextView tvValue=(TextView) fullRow.findViewById(R.id.rowValue);
  tvValue.setText(String.valueOf(colValue[rowNumber]));
  
  TextView tvDescription=(TextView) fullRow.findViewById(R.id.rowDescription);
  tvDescription.setText(String.valueOf(colDescription[rowNumber]));
  
  TextView tvAmount=(TextView) fullRow.findViewById(R.id.rowAmount);
  tvAmount.setText(String.valueOf(colAmount[rowNumber]));
  
  table.addView(fullRow);
 }
 
 @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;
 }

}


TCP Client


package com.example.socket_tcpclient_reference;


//you just could "import java.io.*" or "import java.net.*"
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.net.InetAddress;
import java.net.Socket;

import android.util.Log;

public class TCPClient {
 private String serverMessage;
 public static final String SERVERIP="192.168.173.1";// to be replaced with your IP-address
 public static final int SERVERPORT=4444;
 private OnMessageReceived mMessageListener=null;
 private boolean mRun=false;
 PrintWriter out;
 BufferedReader in;
 
 //constructor
 public TCPClient(OnMessageReceived listener){
  mMessageListener=listener;
 }
 
 public void sendMessage(String message){
  if(out!=null && !out.checkError()){
   out.println(message);
   out.flush();
  }
 }
 
 public void stopClient(){
  mRun=false;
 }
 
 public void run(){
  mRun=true;
  try{
   InetAddress serverAddr=InetAddress.getByName(SERVERIP);
   Log.e("TCP Client", "Client: Connecting....");
   Socket socket=new Socket(serverAddr, SERVERPORT);
   try{
    out=new PrintWriter(new BufferedWriter(new OutputStreamWriter(socket.getOutputStream())), true);
    
    Log.e("TCP Client", "Client: Sent.");
    Log.e("TCP Client", "Client: Done.");
    
    in=new BufferedReader(new InputStreamReader(socket.getInputStream()));
    
    while(mRun){
     serverMessage=in.readLine();
     if(serverMessage!=null && mMessageListener!=null){
      mMessageListener.messageReceived(serverMessage);
     }
     serverMessage=null;
    }
    Log.e("RESPONSE FROM SERVER", "Server: Received message: '"+serverMessage+"'");
   }catch(Exception ex){
    Log.e("TCP", "Client: Error",ex);
   }finally{
    //Closing the socket is important!!!
    socket.close();
   }
  }catch(Exception ex){
   Log.e("TCP", "Client: Error",ex);
  }
 }
 
 public interface OnMessageReceived{
  public void messageReceived(String message);
 }
}
As you could see we used the IP-address "192.168.173.1". The IP-address may differ on your computer. To find out your IP-address, go to the windows start button and type in "cmd". Start the cmd (also called "Command Prompt").


After the Command Prompt is open, type in this line:
ipconfig
 At "IPv4" you'll see your IP-address. Copy it and replace it with yours.

For our App's appearance, we need to define three XML-files.

activity_main



    
    
        
        
      
       
       
    
   
      
          
          
       
           
       
      
      
           
       
     
   

row


     
     
     
     
     
     
     

list_item


 
 
    
 
Last but not least we have to add this line in our "AndroidManifest.xml"-file. You can find the manifest file here.
The line of code you should enter is below "uses-sdk", in order for your App to connect with the server via TCP.
    

    

Start Network

In order to start the network, go to the start button in Windows and type in "cmd". Right click on the icon and run it as an Administrato:



If you've done this the Command Prompt should be open. Then type these lines in:

netsh wlan set hostednetwork mode=allow ssid=test02 key=jjjjjjjj

Then press enter and type this in. After that press enter. 

netsh wlan start hostednetwork

The network should start. This means that we create a network called "test02". The password for connecting into this network is in this case "jjjjjjjj". You should see something similar to this:

 In order to connect your Android device to this network, go to "Settings"-> "Wi-Fi" and then select the network we just created in the Command Prompt and type in the password "jjjjjjjj". Now you are good to go. I'll post video that will demonstrate how it works:

Here's a more detailed Screen cast of the same procedure:


Source Code:
The Source code can be found here:
Server
Client
Tweet