歡笑的背後是看不見的悲傷

layout/main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >      

    <Button
        android:id="@+id/get_record"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        android:text="抓資料囉！" />


    <TableLayout
        android:id="@+id/user_list"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />

</LinearLayout>

先在本機端建立與資料庫連線的PHP檔
將ResultSet以JSON的格式回傳
www/AndroidConnectDB/android_connect_db.php

<?php
$db = mysql_pconnect("localhost","user","pwd");

mysql_query("SET CHARACTER SET 'UTF8';");
mysql_query('SET NAMES UTF8;');
mysql_query('SET CHARACTER_SET_CLIENT=UTF8;');
mysql_query('SET CHARACTER_SET_RESULTS=UTF8;');
mysql_select_db("database");

$sql = $_POST['query_string'];
$sql = stripslashes($sql);
$res = mysql_query($sql);
while($r = mysql_fetch_assoc($res))
    $output[] = $r;

print(json_encode($output));

mysql_close();

?>

建立一個JAVA類別來呼叫PHP
將JSON以String型態傳給Activity處理

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.apache.http.protocol.HTTP;

public class DBConnector {
    public static String executeQuery(String query_string) {
        String result = "";
        
        try {
            HttpClient httpClient = new DefaultHttpClient();
            HttpPost httpPost = new HttpPost("http://10.0.2.2/AndroidConnectDB/android_connect_db.php");
            ArrayList<NameValuePair> params = new ArrayList<NameValuePair>();
            params.add(new BasicNameValuePair("query_string", query_string));
            httpPost.setEntity(new UrlEncodedFormEntity(params, HTTP.UTF_8));
            HttpResponse httpResponse = httpClient.execute(httpPost);
            //view_account.setText(httpResponse.getStatusLine().toString());
            HttpEntity httpEntity = httpResponse.getEntity();
            InputStream inputStream = httpEntity.getContent();
            
            BufferedReader bufReader = new BufferedReader(new InputStreamReader(inputStream, "utf-8"), 8);
            StringBuilder builder = new StringBuilder();
            String line = null;
            while((line = bufReader.readLine()) != null) {
                builder.append(line + "\n");
            }
            inputStream.close();
            result = builder.toString();
        } catch(Exception e) {
            // Log.e("log_tag", e.toString());
        }
        
        return result;
    }
}

在Activity端，將JSON字串轉成JSON陣列
以迴圈建立TableRow物件，加到Layout中

import org.json.JSONArray;
import org.json.JSONObject;

import android.app.Activity;
import android.os.Bundle;
import android.os.StrictMode;
import android.util.Log;
import android.view.Gravity;
import android.view.View;
import android.view.ViewGroup.LayoutParams;
import android.widget.Button;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;


public class TestAndroidDBActivity extends Activity {

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        findViews();
        setListeners();
        
        StrictMode.setThreadPolicy(new StrictMode.ThreadPolicy.Builder()  
        .detectDiskReads()  
        .detectDiskWrites()  
        .detectNetwork()  
        .penaltyLog()  
        .build());  
        StrictMode.setVmPolicy(new StrictMode.VmPolicy.Builder()  
        .detectLeakedSqlLiteObjects()   
        .penaltyLog()  
        .penaltyDeath()  
        .build());  
    }
    
    private Button button_get_record;
    
    private void findViews() {
        button_get_record = (Button)findViewById(R.id.get_record);
    }
    
    private void setListeners() {
        button_get_record.setOnClickListener(getDBRecord);
    }
    
    private Button.OnClickListener getDBRecord = new Button.OnClickListener() {
        public void onClick(View v) {
            // TODO Auto-generated method stub
            TableLayout user_list = (TableLayout)findViewById(R.id.user_list);
            user_list.setStretchAllColumns(true);
            TableLayout.LayoutParams row_layout = new TableLayout.LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
            TableRow.LayoutParams view_layout = new TableRow.LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
            try {
                String result = DBConnector.executeQuery("SELECT * FROM user");
                
                /*
                    SQL 結果有多筆資料時使用JSONArray
                    只有一筆資料時直接建立JSONObject物件
                    JSONObject jsonData = new JSONObject(result);
                */
                JSONArray jsonArray = new JSONArray(result);
                for(int i = 0; i < jsonArray.length(); i++) {
                    JSONObject jsonData = jsonArray.getJSONObject(i);
                    TableRow tr = new TableRow(TestAndroidDBActivity.this);
                    tr.setLayoutParams(row_layout);
                    tr.setGravity(Gravity.CENTER_HORIZONTAL);
                    
                    TextView user_acc = new TextView(TestAndroidDBActivity.this);
                    user_acc.setText(jsonData.getString("account"));
                    user_acc.setLayoutParams(view_layout);
                    
                    TextView user_pwd = new TextView(TestAndroidDBActivity.this);
                    user_pwd.setText(jsonData.getString("pwd"));
                    user_pwd.setLayoutParams(view_layout);
                    
                    tr.addView(user_acc);
                    tr.addView(user_pwd);
                    user_list.addView(tr);
                }
            } catch(Exception e) {
                // Log.e("log_tag", e.toString());
            }
        }
    };
}

資料表內的資料


Activity執行前


Activity執行後