Android Populating Spinner data from SQLite Databaseexplained how to pupulate spinner data from SQLite database. In this tutorial we are going to learn how to do the same, except the spinner data is being loaded from MySQL database. I choose PHP as server side technology which acts as a connecting layer between android app and mysql database.
If you haven’t tried connecting android with PHP and MySQL, How to connect Android with PHP, MySQL will give you complete overview of communicating android app with MySQL.
Installing WAMP Server
WAMP lets you install Apache, PHP and MySQL with a single installer which reduces burden of installing & configuring them separately. Alternatively you can use XAMP, LAMP (on Linux) and MAMP (on MAC). WAMP also provides you phpmyadmin to easily interact with MySQL database.
Download & install WAMP from http://www.wampserver.com/en/. Choose the correct version which suits your operating system (32bit or 64bit). Once you have installed it, open the program from Start -> All Programs -> Wamp Server -> Start WampServer.
Open http://localhost/ and http://localhost/phpmyadmin/ to verify WAMP is installed successfully or not.
Creating MySQL Database
To demonstrate this tutorial I have taken an example of Food Categories API where we do two operations. Once is getting list of food categories and other is creating new food category. So let’s start creating a database for this.
Open phpmyadmin from http://localhost/phpmyadmin/ and create a database and tables using following SQL queries. Also you can use phpmyadmin GUI to create database and tables.
Creating database
CREATE DATABASE food;
Creating categories table & inserting some data.
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `categories` (`name`) VALUES
('Beverages'),
('Bread'),
('Cereals'),
('Cheese'),
('Citrus Fruits');
Creating PHP Project
On windows PC default location of the WAMP installation is C:\wamp. Go to this directory and open www directory. All the PHP projects will go into this directory.
The PHP project we are going to create will have following list of files.
Config.php – All the configuration variables like database name, username, password and other things goes here.
DbConnect.php – A class to open and close the database connection.
get_categories.php – To fetch list of food categories
new_category – To create a new food category in database.
DbConnect.php – A class to open and close the database connection.
get_categories.php – To fetch list of food categories
new_category – To create a new food category in database.
I have used Netbeans IDE to develop my php project. This is optional, you can use your favorite IDE.
1. Create a new folder named food_api under C:\wamp\www directory for our project.
2. Create a new file named Config.php and write following code. Here we mentioned database name, username, password and host name. If your mysql database is having a password, don’t forget to define it here.
<?php
/**
* Database configuration
*/
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
define('DB_NAME', 'food');
?>
3. The first API call is listing all food categories. So create a file namedget_categories.php with following content. Here we simply open the database connection, run a select query and build a response array and echo the json.
<?php
include_once './DbConnect.php';
function getCategories(){
$db = new DbConnect();
// array for json response
$response = array();
$response["categories"] = array();
// Mysql select query
$result = mysql_query("SELECT * FROM categories");
while($row = mysql_fetch_array($result)){
// temporary array to create single category
$tmp = array();
$tmp["id"] = $row["id"];
$tmp["name"] = $row["name"];
// push category to final json array
array_push($response["categories"], $tmp);
}
// keeping response header to json
header('Content-Type: application/json');
// echoing json result
echo json_encode($response);
}
getCategories();
?>
You can see the output of this file by openinghttp://localhost/food_api/get_categories.php
4. The second API call is creating a new food category. So create a file named new_category with following content. This api call acceptsPOST method and check for parameter name which is name of the new category to be created in database.
<?php
include_once './DbConnect.php';
function createNewCategory() {
if (isset($_POST["name"]) && $_POST["name"] != "") {
// response array for json
$response = array();
$category = $_POST["name"];
$db = new DbConnect();
// mysql query
$query = "INSERT INTO categories(name) VALUES('$category')";
$result = mysql_query($query) or die(mysql_error());
if ($result) {
$response["error"] = false;
$response["message"] = "Category created successfully!";
} else {
$response["error"] = true;
$response["message"] = "Failed to create category!";
}
} else {
$response["error"] = true;
$response["message"] = "Category name is missing!";
}
// echo json response
echo json_encode($response);
}
createNewCategory();
?>
The url for this api call is http://localhost/food_api/new_category.php.
Until now we are done with server side part. It’s time to move on to android part.
Creating Android Project
1. Create a new project in Eclipse from File ⇒ New ⇒ Android Application Project. I had left my main activity name asMainActivity.java and gave the package name asinfo.androidhive.spinnermysql
2. As this application is going to use internet, we need to add INTERNET permission in AndroidManifest.xml file. Open yourAndroidManifest.xml file and add following permission.
<uses-permission android:name=”android.permission.INTERNET”/>
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="info.androidhive.spinnermysql"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="18" />
<!-- Internet Permissions -->
<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="info.androidhive.spinnermysql.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>
3. Create a class file named Category.php. This model class will be useful to convert json data into objects.
package info.androidhive.spinnermysql;
public class Category {
private int id;
private String name;
public Category(){}
public Category(int id, String name){
this.id = id;
this.name = name;
}
public void setId(int id){
this.id = id;
}
public void setName(String name){
this.name = name;
}
public int getId(){
return this.id;
}
public String getName(){
return this.name;
}
}
4. I am creating another class to take care of making API calls and handling the response. Create a class named ServiceHandler.java and write the following code. makeServiceCall() method should be called to make http calls.
package info.androidhive.spinnermysql;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.util.List;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.utils.URLEncodedUtils;
import org.apache.http.impl.client.DefaultHttpClient;
import android.util.Log;
public class ServiceHandler {
static InputStream is = null;
static String response = null;
public final static int GET = 1;
public final static int POST = 2;
public ServiceHandler() {
}
/**
* Making service call
* @url - url to make request
* @method - http request method
* */
public String makeServiceCall(String url, int method) {
return this.makeServiceCall(url, method, null);
}
/**
* Making service call
* @url - url to make request
* @method - http request method
* @params - http request params
* */
public String makeServiceCall(String url, int method,
List<NameValuePair> params) {
try {
// http client
DefaultHttpClient httpClient = new DefaultHttpClient();
HttpEntity httpEntity = null;
HttpResponse httpResponse = null;
// Checking http request method type
if (method == POST) {
HttpPost httpPost = new HttpPost(url);
// adding post params
if (params != null) {
httpPost.setEntity(new UrlEncodedFormEntity(params));
}
httpResponse = httpClient.execute(httpPost);
} else if (method == GET) {
// appending params to url
if (params != null) {
String paramString = URLEncodedUtils
.format(params, "utf-8");
url += "?" + paramString;
}
HttpGet httpGet = new HttpGet(url);
httpResponse = httpClient.execute(httpGet);
}
httpEntity = httpResponse.getEntity();
is = httpEntity.getContent();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (ClientProtocolException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
BufferedReader reader = new BufferedReader(new InputStreamReader(
is, "UTF-8"), 8);
StringBuilder sb = new StringBuilder();
String line = null;
while ((line = reader.readLine()) != null) {
sb.append(line + "\n");
}
is.close();
response = sb.toString();
} catch (Exception e) {
Log.e("Buffer Error", "Error: " + e.toString());
}
return response;
}
}
5. Now design the interface for your main activity. I have created simple layout to insert new category and a spinner to show the categories fetched from MySQL database. Open activity_main.xml file and insert the following code.
<LinearLayout 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:orientation="vertical"
android:padding="10dp" >
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="New food category" />
<EditText android:id="@+id/txtCategory"
android:layout_width="fill_parent"
android:layout_height="wrap_content"/>
<Button android:id="@+id/btnAddNewCategory"
android:text="Create"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="40dp"/>
<TextView android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="Select food category"/>
<Spinner android:id="@+id/spinFood"
android:layout_width="fill_parent"
android:layout_height="wrap_content"/>
</LinearLayout>
The above xml produces following UI
6. Open your MainActivity.java file add the basic code like declaring required variables, button click event, spinner listener etc.,
URL_NEW_CATEGORY – Url to create new category
URL_CATEGORIES – Url to get list of categories
URL_CATEGORIES – Url to get list of categories
public class MainActivity extends Activity implements OnItemSelectedListener {
private Button btnAddNewCategory;
private TextView txtCategory;
private Spinner spinnerFood;
// array list for spinner adapter
private ArrayList<Category> categoriesList;
ProgressDialog pDialog;
// API urls
// Url to create new category
private String URL_NEW_CATEGORY = "http://10.0.2.2/food_api/new_category.php";
// Url to get all categories
private String URL_CATEGORIES = "http://10.0.2.2/food_api/get_categories.php";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
btnAddNewCategory = (Button) findViewById(R.id.btnAddNewCategory);
spinnerFood = (Spinner) findViewById(R.id.spinFood);
txtCategory = (TextView) findViewById(R.id.txtCategory);
categoriesList = new ArrayList<Category>();
// spinner item select listener
spinnerFood.setOnItemSelectedListener(this);
// Add new category click event
btnAddNewCategory.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
if (txtCategory.getText().toString().trim().length() > 0) {
// new category name
String newCategory = txtCategory.getText().toString();
// Call Async task to create new category
new AddNewCategory().execute(newCategory);
} else {
Toast.makeText(getApplicationContext(),
"Please enter category name", Toast.LENGTH_SHORT)
.show();
}
}
});
new GetCategories().execute();
}
}
Getting list of categories and showing them in Spinner
7. I defined an Async method to fetch list of categories from MySQL and showing them in spinner. Add the following code after onCreate block inMainActivity.java. I also added another method populateSpinner()to takes care of loading the data into spinner. This async method should be called in onCreate method like new GetCategories().execute()
/**
* Async task to get all food categories
* */
private class GetCategories extends AsyncTask<Void, Void, Void> {
@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(MainActivity.this);
pDialog.setMessage("Fetching food categories..");
pDialog.setCancelable(false);
pDialog.show();
}
@Override
protected Void doInBackground(Void... arg0) {
ServiceHandler jsonParser = new ServiceHandler();
String json = jsonParser.makeServiceCall(URL_CATEGORIES, ServiceHandler.GET);
Log.e("Response: ", "> " + json);
if (json != null) {
try {
JSONObject jsonObj = new JSONObject(json);
if (jsonObj != null) {
JSONArray categories = jsonObj
.getJSONArray("categories");
for (int i = 0; i < categories.length(); i++) {
JSONObject catObj = (JSONObject) categories.get(i);
Category cat = new Category(catObj.getInt("id"),
catObj.getString("name"));
categoriesList.add(cat);
}
}
} catch (JSONException e) {
e.printStackTrace();
}
} else {
Log.e("JSON Data", "Didn't receive any data from server!");
}
return null;
}
@Override
protected void onPostExecute(Void result) {
super.onPostExecute(result);
if (pDialog.isShowing())
pDialog.dismiss();
//populateSpinner();
}
}
/**
* Adding spinner data
* */
private void populateSpinner() {
List<String> lables = new ArrayList<String>();
txtCategory.setText("");
for (int i = 0; i < categoriesList.size(); i++) {
lables.add(categoriesList.get(i).getName());
}
// Creating adapter for spinner
ArrayAdapter<String> spinnerAdapter = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, lables);
// Drop down layout style - list view with radio button
spinnerAdapter
.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
// attaching data adapter to spinner
spinnerFood.setAdapter(spinnerAdapter);
}
If you run the project now, you should see the mysql data loaded into spinner. Use LogCat to debug the errors.
Inserting a new food category into MySQL
8. I added another async method to insert a new food category into MySQL database. This method is called in create new button click event using new AddNewCategory().execute(newCategory)
/**
* Async task to create a new food category
* */
private class AddNewCategory extends AsyncTask<String, Void, Void> {
boolean isNewCategoryCreated = false;
@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(MainActivity.this);
pDialog.setMessage("Creating new category..");
pDialog.setCancelable(false);
pDialog.show();
}
@Override
protected Void doInBackground(String... arg) {
String newCategory = arg[0];
// Preparing post params
List<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("name", newCategory));
ServiceHandler serviceClient = new ServiceHandler();
String json = serviceClient.makeServiceCall(URL_NEW_CATEGORY,
ServiceHandler.POST, params);
Log.d("Create Response: ", "> " + json);
if (json != null) {
try {
JSONObject jsonObj = new JSONObject(json);
boolean error = jsonObj.getBoolean("error");
// checking for error node in json
if (!error) {
// new category created successfully
isNewCategoryCreated = true;
} else {
Log.e("Create Category Error: ", "> " + jsonObj.getString("message"));
}
} catch (JSONException e) {
e.printStackTrace();
}
} else {
Log.e("JSON Data", "Didn't receive any data from server!");
}
return null;
}
@Override
protected void onPostExecute(Void result) {
super.onPostExecute(result);
if (pDialog.isShowing())
pDialog.dismiss();
if (isNewCategoryCreated) {
runOnUiThread(new Runnable() {
@Override
public void run() {
// fetching all categories
new GetCategories().execute();
}
});
}
}
}
Run and the test the app again to check insertion.
Complete Code
Following is the complete code of MainActivity.java
package info.androidhive.spinnermysql;
import java.util.ArrayList;
import java.util.List;
import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.app.Activity;
import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity implements OnItemSelectedListener {
private Button btnAddNewCategory;
private TextView txtCategory;
private Spinner spinnerFood;
// array list for spinner adapter
private ArrayList<Category> categoriesList;
ProgressDialog pDialog;
// API urls
// Url to create new category
private String URL_NEW_CATEGORY = "http://10.0.2.2/food_api/new_category.php";
// Url to get all categories
private String URL_CATEGORIES = "http://10.0.2.2/food_api/get_categories.php";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
btnAddNewCategory = (Button) findViewById(R.id.btnAddNewCategory);
spinnerFood = (Spinner) findViewById(R.id.spinFood);
txtCategory = (TextView) findViewById(R.id.txtCategory);
categoriesList = new ArrayList<Category>();
// spinner item select listener
spinnerFood.setOnItemSelectedListener(this);
// Add new category click event
btnAddNewCategory.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
if (txtCategory.getText().toString().trim().length() > 0) {
// new category name
String newCategory = txtCategory.getText().toString();
// Call Async task to create new category
new AddNewCategory().execute(newCategory);
} else {
Toast.makeText(getApplicationContext(),
"Please enter category name", Toast.LENGTH_SHORT)
.show();
}
}
});
new GetCategories().execute();
}
/**
* Adding spinner data
* */
private void populateSpinner() {
List<String> lables = new ArrayList<String>();
txtCategory.setText("");
for (int i = 0; i < categoriesList.size(); i++) {
lables.add(categoriesList.get(i).getName());
}
// Creating adapter for spinner
ArrayAdapter<String> spinnerAdapter = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, lables);
// Drop down layout style - list view with radio button
spinnerAdapter
.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
// attaching data adapter to spinner
spinnerFood.setAdapter(spinnerAdapter);
}
/**
* Async task to get all food categories
* */
private class GetCategories extends AsyncTask<Void, Void, Void> {
@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(MainActivity.this);
pDialog.setMessage("Fetching food categories..");
pDialog.setCancelable(false);
pDialog.show();
}
@Override
protected Void doInBackground(Void... arg0) {
ServiceHandler jsonParser = new ServiceHandler();
String json = jsonParser.makeServiceCall(URL_CATEGORIES, ServiceHandler.GET);
Log.e("Response: ", "> " + json);
if (json != null) {
try {
JSONObject jsonObj = new JSONObject(json);
if (jsonObj != null) {
JSONArray categories = jsonObj
.getJSONArray("categories");
for (int i = 0; i < categories.length(); i++) {
JSONObject catObj = (JSONObject) categories.get(i);
Category cat = new Category(catObj.getInt("id"),
catObj.getString("name"));
categoriesList.add(cat);
}
}
} catch (JSONException e) {
e.printStackTrace();
}
} else {
Log.e("JSON Data", "Didn't receive any data from server!");
}
return null;
}
@Override
protected void onPostExecute(Void result) {
super.onPostExecute(result);
if (pDialog.isShowing())
pDialog.dismiss();
//populateSpinner();
}
}
/**
* Async task to create a new food category
* */
private class AddNewCategory extends AsyncTask<String, Void, Void> {
boolean isNewCategoryCreated = false;
@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(MainActivity.this);
pDialog.setMessage("Creating new category..");
pDialog.setCancelable(false);
pDialog.show();
}
@Override
protected Void doInBackground(String... arg) {
String newCategory = arg[0];
// Preparing post params
List<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("name", newCategory));
ServiceHandler serviceClient = new ServiceHandler();
String json = serviceClient.makeServiceCall(URL_NEW_CATEGORY,
ServiceHandler.POST, params);
Log.d("Create Response: ", "> " + json);
if (json != null) {
try {
JSONObject jsonObj = new JSONObject(json);
boolean error = jsonObj.getBoolean("error");
// checking for error node in json
if (!error) {
// new category created successfully
isNewCategoryCreated = true;
} else {
Log.e("Create Category Error: ", "> " + jsonObj.getString("message"));
}
} catch (JSONException e) {
e.printStackTrace();
}
} else {
Log.e("JSON Data", "Didn't receive any data from server!");
}
return null;
}
@Override
protected void onPostExecute(Void result) {
super.onPostExecute(result);
if (pDialog.isShowing())
pDialog.dismiss();
if (isNewCategoryCreated) {
runOnUiThread(new Runnable() {
@Override
public void run() {
// fetching all categories
new GetCategories().execute();
}
});
}
}
}
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position,
long id) {
Toast.makeText(
getApplicationContext(),
parent.getItemAtPosition(position).toString() + " Selected" ,
Toast.LENGTH_LONG).show();
}
@Override
public void onNothingSelected(AdapterView<?> arg0) {
}
}
No comments:
Post a Comment