[팁]AutoCompleteTextView랑 DB랑 싱크시키기

출처 : http://www.gaanza.com/blog/android-autocompletetextview-mysql/
 
Well i have been playing around a lot with android client connectivity with remote MySQL database. Lately i wanted a UI/View, something input textfield, i wanted to show completion suggestions automatically in a drop down menu while the user is typing, suggestions would be coming from MySQL database. Basically whenever i type, i am calling php sending the input and php will query database and return the values(in my case just names) that have “input” as substring (like if i type ‘pa’ then it would return names that have ‘pa’ as substring). I am sending results to android client via encoded json. In android side i am decoding json and filling my ArrayAdapter. Lets see the points that are being done:

1) PHP

<?php
mysql_connect(“localhost”,“root”,“”);
mysql_select_db(“dalalstreet”);
 
$st = $_REQUEST[‘st’];

$q= mysql_query(“SELECT * FROM world WHERE company LIKE ‘%”.$st.“%’”);

while($e = mysql_fetch_assoc($q))
        $output[]=$e;
 
print(json_encode($output));
 
mysql_close();
?>

I somehow looking at google i wrote this code. This php gets the input text from my android and it runs query at database for records whose company names has “input text” as a substring. You can see the query statement above. In the end it encodes the result in json.

2) Main.xml (my layout)
Here is the xml layout file for my ui.

<?xml version=“1.0″ encoding=“utf-8″?>
<LinearLayout xmlns:android=“http://schemas.android.com/apk/res/android”
    android:orientation=“vertical”
    android:layout_width=“fill_parent”
    android:layout_height=“fill_parent”
    >
<TextView  
    android:layout_width=“wrap_content” 
    android:layout_height=“wrap_content” 
    android:text=“@string/country_label”
    />
<CustomAutoCompleteView
        android:id=“@+id/autoCompleteCountry”
        android:layout_width=“fill_parent”
        android:layout_height=“wrap_content” />
</LinearLayout>

3) Main Activity class
Here is the main class that extends Activity. Here is the code of calling php module and parsing json. I have explained this all in my previous post so you could go through it. i’l explain just AutoCompleteTextView part here.

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.app.Activity;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.util.Log;
import android.widget.ArrayAdapter;

public class AutoComptest extends Activity {
    
        private CustomAutoCompleteView autoComplete;        
         
        private ArrayAdapter<String> autoCompleteAdapter;
        
        /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        autoCompleteAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_dropdown_item_1line); 
        autoCompleteAdapter.setNotifyOnChange(true); // This is so I don’t have to manually sync whenever changed  
        autoComplete = (CustomAutoCompleteView)  findViewById(R.id.autoCompleteCountry);
        autoComplete.setHint(“Country”);
        autoComplete.setThreshold(3);
        autoComplete.setAdapter(autoCompleteAdapter);
        
        autoComplete.addTextChangedListener(textChecker);
        
    }
    
    final TextWatcher textChecker = new TextWatcher() {
        public void afterTextChanged(Editable s) {}
 
        public void beforeTextChanged(CharSequence s, int start, int count, int after) {}
 
        public void onTextChanged(CharSequence s, int start, int before, int count)
        {
                   
                autoCompleteAdapter.clear();
                
                callPHP();
                               
        }     
    };
    
    private void callPHP(){
        String result = “”;         
        InputStream is=null;        
        try{
                ArrayList<NameValuePair> nameValuePairs = newArrayList<NameValuePair>();
                nameValuePairs.add(newBasicNameValuePair(“st”,autoComplete.getText().toString()));
                HttpClient httpclient = new DefaultHttpClient();
                HttpPost httppost = newHttpPost(“http://10.0.2.2/gaanza_android/android_database.php”);
                httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
                HttpResponse response = httpclient.execute(httppost);
                HttpEntity entity = response.getEntity();
                is = entity.getContent();
        }catch(Exception e){
                Log.e(“log_tag”, “Error in http connection “+e.toString());
        }
        
        try{                        
                BufferedReader reader = new BufferedReader(newInputStreamReader(is,“iso-8859-1″),8);
                StringBuilder sb = new StringBuilder();
                String line = null;
                while ((line = reader.readLine()) != null) {                                    
                            sb.append(line + “\n“);
                }
                is.close();
         
                result=sb.toString();           
        }catch(Exception e){
                Log.e(“log_tag”, “Error converting result “+e.toString());
        }
        
        //parse json data
        try{
                JSONArray jArray = new JSONArray(result);
                for(int i=0;i<jArray.length();i++){
                        JSONObject json_data = jArray.getJSONObject(i);                                
                      
                        autoCompleteAdapter.add(json_data.getString(“country”));
                }
        }
        catch(JSONException e){
                Log.e(“log_tag”, “Error parsing data “+e.toString());
        }
    }
}

Note: remember “http://10.0.2.2/” in my last post. I had to use 10.0.2.2 instead of localhost because emulator consider itself as localhost and my xammp local server is not in emulator localhost.

Ok so i want to detect an event when a text is typed/deleted from my autocompletetextview, basically i want a listener which detects a change in text of my view. So what should we do ? Well in my case i searched the android developer documentation , there i found something called TextWatcher. What i am doing is attach a TextWatcher object to editable like my AutoCompleteTextView so whenever any text changes in my editable the functions/methods of TextWatcher will be called and in these methods i had to write my logic codes which is what i want to achieve if text changes in autocompletetextview ?. There are three abstract methods in TextWatcher class so you have to override them, well anyway thats what we wanted. Methods are:

afterTextChanged(Editable s)
beforeTextChanged(CharSequence c, int start, int count, int after)
onTextChanged(CharSequence c, int start, int before, int count)

Best way to understand them is going through the document here. Anyway i had to write my logic code on beforeTextChanged() method so that it performs action whenever there is a change in text of autocompletetextview.

In above code sample i am calling PHP whenever i am typing something. So i am calling callPHP function at onTextChanged() method. So whenever i type something it will call PHP giving text to the function in PHP and PHP will query MySql database for results and PHP will send results to me in json. This is it. So i decode json and fill the results in ArrayAdapter after clearing it, whenever ArrayAdapter changes it notify the changes being made and AutoCompleteView will show the new drop down suggestions.

There was still one very minor problem and i solved it with my another very stupid solution  So problem was that whenever the results from database gets filled in ArrayAdapter and AutoCompleteTextView shows drop down suggestions, it showed me filtered suggestions like suppose i type “can” and i get results(which has “can” as substring) from database, so for “can” ArrayAdapter contents get filtered and i get suggestion starting with text “can” which i didn’t wanted because i already has got filtered results from database and i wanted to show all results that i am receiving from database. So for this i had to write my custom AutoCompleteTextView. It isn’t hard at all, i just had to extend AutoCompleteTextView and override the methods.

4) CustomAutoCompleteView which extends AutoCompletetextView

public class CustomAutoCompleteView extends AutoCompleteTextView {
        
        public CustomAutoCompleteView(Context context) {
                super(context);
                // TODO Auto-generated constructor stub
        }

        public CustomAutoCompleteView(Context context, AttributeSet attrs) {
                super(context, attrs);
                // TODO Auto-generated constructor stub
        }

        public CustomAutoCompleteView(Context context, AttributeSet attrs,
                        int defStyle) {
                super(context, attrs, defStyle);
                // TODO Auto-generated constructor stub
        }
        
        @Override
        protected void performFiltering(final CharSequence text, final int keyCode) {
                String filterText = “”;
                super.performFiltering(filterText, keyCode);
        }
    /**
    * After a selection, capture the new value and append to the existing
    * text
    */
    @Override
    protected void replaceText(final CharSequence text) {
        super.replaceText(text);
    }

}

How to remove the filter on an ArrayAdapter used in an AutoCompleteTextView? I had to override performFiltering() method. In this method i am telling AutoCompletetextView to not filter any suggestions, just show all suggestions. I searched on google for this topic but i didn’t find any good solution, maybe i didn’t searched it properly. Whatever, i just wrote a stupid trick to remove any filter, actually instead of technically removing filter i am adding a filter which does a job to show all suggestions  . I defined a filter which filters all suggestion based on “” yeah a blank string  . But this works like a charm. Just try it.

So now your are getting results suggestion from database dynamically to AutoCompleteTextView.

But But But But But ok enough, In above code way of fetching data from database to AutoCompletetextView just sucks. Never never ever call a http PHP call in UI Thread. I did a lot of testing with this code. When i was getting small number of results from Database(live database not local) it was working fine but when i was getting large number of results say more than 150 and it was taking a bit of time which caused my UI to be non-responsive and thus famous FORCE CLOSE occured. So never call it from UI thread because u don’t know how much time it will take to complete whole http connection. So always call it in separate thread that i will discuss in Part II of this subject. yes i’l be posting new better version of same problem.