mercoledì 23 aprile 2014

JSON da Mysql ad Android

Per poter effettuare lo scambio dati (unidirezionale) tra un server ed un client mobile Android puo' essere utile impiegare JSon

Per prima cosa si deve configurare il lato server installando Apache,Php e le sue estensioni (in particolare php5-json) e mysql


sudo apt-get install apache2
sudo apt-get install php5
sudo apt-get install libapache2-mod-php5
sudo apt-get install php5-json
sudo /etc/init.d/apache2 restart
sudo apt-get install mysql-server phpmyadmin

A questo punto si crea la base dati. Nell'esempio e' stato creato un database "test" con una sola tabella "test" con una struttura di questo tipo
----------------------------------------------------
-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 23, 2014 at 04:15 PM
-- Server version: 5.5.35
-- PHP Version: 5.3.10-1ubuntu3.11

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 utf8 */;

--
-- Database: `test`
--

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

--
-- Table structure for table `test`
--

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(50) NOT NULL,
  `cognome` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

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

INSERT INTO `test` (`id`, `nome`, `cognome`) VALUES
(1, 'Luca', 'Innocenti'),
(2, 'Alessio', 'Parauda');

/*!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 */;
----------------------------------------------------

per interrogare il db sul lato server e' stato inserito il seguente semplice script Python che effettua una query e crea un oggetto JSon con i dati risultanti dalla query
----------------------------------------------------
<?php
$mysql_db_hostname = "localhost";
$mysql_db_user = "xxxxx";
$mysql_db_password = "xxxxxx";
$mysql_db_database = "test";

$con = @mysqli_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password,$mysql_db_database);

if (!$con) {
echo('Could not connect to MySQL: ' . mysqli_connect_error());
}

$var = array();
$sql = "SELECT * FROM test";

$result = mysqli_query($con, $sql);
while($obj = mysqli_fetch_object($result)) {
$var[] = $obj;
}
echo json_encode($var);
?>
----------------------------------------------------
si puo' testare l'output chiamando la pagina da browser


L'applicazione Android di seguito riportata apre una connessione Http verso la pagina php precedentemente predisposta ed effettua il parsing dell'oggetto JSon
Ovviamente deve essere impostato nel file Manifest il permesso per l'uso di Internet

----------------------------------------------------
package com.luca.json;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

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

import android.support.v7.app.ActionBarActivity;
import android.support.v4.app.Fragment;
import android.os.Bundle;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Toast;


public class MainActivity extends ActionBarActivity {

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);

 
setContentView(R.layout.activity_main);

if (savedInstanceState == null) {
getSupportFragmentManager().beginTransaction()
.add(R.id.container, new PlaceholderFragment()).commit();
}

Thread thread = new Thread(new Runnable(){
   @Override
   public void run() {
     String result = readJson();
     JSONArray jArray = null;
try {
jArray = new JSONArray(result);
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
     JSONObject json_data=null;
       for(int i=0;i<jArray.length();i++){
               try {
json_data = jArray.getJSONObject(i);
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
               try {
String nr = Integer.toString(json_data.getInt("id"));
String nome = json_data.getString("nome");
String cognome = json_data.getString("cognome");
Log.d("JSON",nr+","+nome+","+cognome);
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
   }   
});
thread.start(); 
}

private String readJson() {
      String line = null;
  StringBuilder builder = new StringBuilder();
   HttpClient client = new DefaultHttpClient();
   HttpGet httpGet = new HttpGet("http://192.168.0.100/json.php");
   try {
     HttpResponse response = client.execute(httpGet);
     StatusLine statusLine = response.getStatusLine();
     int statusCode = statusLine.getStatusCode();
     if (statusCode == 200) {
       HttpEntity entity = response.getEntity();
       InputStream content = entity.getContent();
       BufferedReader reader = new BufferedReader(new InputStreamReader(content));
       while ((line = reader.readLine()) != null) {
         builder.append(line);
       }
     } else {
       Log.d("JSON", "Failed to download file");
     }
   } catch (ClientProtocolException e) {
     e.printStackTrace();
   } catch (IOException e) {
     e.printStackTrace();
   }

//Log.d("JSON",line);
   return builder.toString();
}

@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;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();
if (id == R.id.action_settings) {
return true;
}
return super.onOptionsItemSelected(item);
}

/**
* A placeholder fragment containing a simple view.
*/
public static class PlaceholderFragment extends Fragment {

public PlaceholderFragment() {
}

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
View rootView = inflater.inflate(R.layout.fragment_main, container,
false);
return rootView;
}
}

}