自己測試過的方法,供您參考:
Android 手機經Web Server(PHP),取得Oracle DB資料。
一開始是想由手機直接連結資料庫取得資料,查了依些資料發現,需經由Web Server,不同的資料庫,只需依DB的文件再修改即可。
程序流程: 1.主程式MainActivity -> 2.呼叫副程式DBConnector -> 3.經由Http方法聯結Web Server(PHP網頁) ->4.網頁取得資料庫資料後回傳顯示。
1. Android 主程式:
- package com.xxx.testandroiddbactivity;
- import android.os.Build;
- import android.os.Bundle;
- import android.annotation.SuppressLint;
- import android.annotation.TargetApi;
- import android.app.Activity;
- import android.view.Menu;
- import org.json.JSONArray;
- import org.json.JSONObject;
- 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;
- import android.widget.EditText;
- import android.widget.Toast;
- public class MainActivity extends Activity {
- @TargetApi(Build.VERSION_CODES.GINGERBREAD)
- @SuppressLint("NewApi")
- @Override
- protected void onCreate(Bundle savedInstanceState) {
- super.onCreate(savedInstanceState);
- setContentView(R.layout.activity_main);
-
- findViews();
- setListeners();
-
- StrictMode.setThreadPolicy(new StrictMode.ThreadPolicy.Builder()
- .detectDiskReads()
- .detectDiskWrites()
- .detectNetwork()
- .penaltyLog()
- .build());
-
- StrictMode.setVmPolicy(new StrictMode.VmPolicy.Builder()
- .detectLeakedSqlLiteObjects()
- .penaltyLog()
- .penaltyDeath()
- .build());
-
- }
- @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;
- }
-
- private Button button_get_record;
- private Button button_get_record2;
- private EditText username;
- private EditText password;
- private String s_username;
- private String s_password;
-
- private void findViews() {
- username = (EditText)findViewById(R.id.username);
- password = (EditText)findViewById(R.id.password);
- button_get_record = (Button)findViewById(R.id.get_record);
- button_get_record2 = (Button)findViewById(R.id.get_record2);
- }
-
- private void setListeners() {
- button_get_record.setOnClickListener(getDBRecord);
- button_get_record2.setOnClickListener(getDBRecord2);
- }
-
- 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 {
- s_username = username.getText().toString(); //取得輸入的帳號
- s_password = password.getText().toString(); //密碼
- //呼叫將要連結資料庫的函式DBConnector,並傳遞參數(SQL,帳號,密碼)
- String result = DBConnector.executeQuery("SELECT xxx16,xxx31f,xxx32f,xxx34f FROM xxx_table WHERE rownum < 10",s_username,s_password);
- //SQL 結果有多筆資料時使用JSONArray
- //只有一筆資料時直接建立JSONObject物件
- //JSONObject jsonData = new JSONObject(result);
- JSONArray jsonArray = new JSONArray(result);
- //System.out.print(result);
- user_list.removeAllViews(); //清空畫面
- TableRow tr2 = new TableRow(MainActivity.this);
- tr2.setLayoutParams(row_layout);
- tr2.setGravity(Gravity.CENTER_HORIZONTAL);
-
- TextView title_acc = new TextView(MainActivity.this);
- title_acc.setText("稅率");
- title_acc.setLayoutParams(view_layout);
-
- TextView title_pwd = new TextView(MainActivity.this);
- title_pwd.setText("未稅金額");
- title_pwd.setLayoutParams(view_layout);
-
- TextView title_tax = new TextView(MainActivity.this);
- title_tax.setText("稅額");
- title_tax.setLayoutParams(view_layout);
-
- TextView title_sum = new TextView(MainActivity.this);
- title_sum.setText("合計金額");
- title_sum.setLayoutParams(view_layout);
-
- tr2.addView(title_acc);
- tr2.addView(title_pwd);
- tr2.addView(title_tax);
- tr2.addView(title_sum);
- user_list.addView(tr2);
-
- for(int i = 0; i < jsonArray.length(); i++) {
- JSONObject jsonData = jsonArray.getJSONObject(i);
- TableRow tr = new TableRow(MainActivity.this);
- tr.setLayoutParams(row_layout);
- tr.setGravity(Gravity.CENTER_HORIZONTAL);
-
- TextView user_acc = new TextView(MainActivity.this);
- user_acc.setText(jsonData.getString("xxx16"));
- user_acc.setLayoutParams(view_layout);
-
- TextView user_pwd = new TextView(MainActivity.this);
- user_pwd.setText(jsonData.getString("xxx31F"));
- user_pwd.setLayoutParams(view_layout);
-
- TextView user_tax = new TextView(MainActivity.this);
- user_tax.setText(jsonData.getString("xxx32F"));
- user_tax.setLayoutParams(view_layout);
-
- TextView user_sum = new TextView(MainActivity.this);
- user_sum.setText(jsonData.getString("xxx34F"));
- user_sum.setLayoutParams(view_layout);
-
- tr.addView(user_acc);
- tr.addView(user_pwd);
- tr.addView(user_tax);
- tr.addView(user_sum);
- user_list.addView(tr);
- }
- //System.out.print("Activity端:" + result);
- } catch(Exception e) {
- //Log.e("log_tag", e.toString());
- String name = e.toString();
- Toast.makeText(getApplicationContext(), name, 1).show();
- }
- }
- };
-
- private Button.OnClickListener getDBRecord2 = 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 {
- s_username = username.getText().toString();
- s_password = password.getText().toString();
- String result = DBConnector.executeQuery("001",s_username,s_password);
- //SQL 結果有多筆資料時使用JSONArray
- //只有一筆資料時直接建立JSONObject物件
- //JSONObject jsonData = new JSONObject(result);
- JSONArray jsonArray = new JSONArray(result);
- //System.out.print(result);
- user_list.removeAllViews(); //清空畫面
-
- TableRow tr2 = new TableRow(MainActivity.this);
- tr2.setLayoutParams(row_layout);
- tr2.setGravity(Gravity.CENTER_HORIZONTAL);
-
- TextView title_acc = new TextView(MainActivity.this);
- title_acc.setText("傳票編號");
- title_acc.setLayoutParams(view_layout);
-
- TextView title_pwd = new TextView(MainActivity.this);
- title_pwd.setText("項次");
- title_pwd.setLayoutParams(view_layout);
-
- TextView title_tax = new TextView(MainActivity.this);
- title_tax.setText("發票號碼");
- title_tax.setLayoutParams(view_layout);
-
- TextView title_sum = new TextView(MainActivity.this);
- title_sum.setText("廠商統一編號");
- title_sum.setLayoutParams(view_layout);
-
- tr2.addView(title_acc);
- tr2.addView(title_pwd);
- tr2.addView(title_tax);
- tr2.addView(title_sum);
- user_list.addView(tr2);
-
- for(int i = 0; i < jsonArray.length(); i++) {
- JSONObject jsonData = jsonArray.getJSONObject(i);
- TableRow tr = new TableRow(MainActivity.this);
- tr.setLayoutParams(row_layout);
- tr.setGravity(Gravity.CENTER_HORIZONTAL);
-
- TextView user_acc = new TextView(MainActivity.this);
- user_acc.setText(jsonData.getString("xxx01"));
- user_acc.setLayoutParams(view_layout);
-
- TextView user_pwd = new TextView(MainActivity.this);
- user_pwd.setText(jsonData.getString("xxx02"));
- user_pwd.setLayoutParams(view_layout);
-
- TextView user_tax = new TextView(MainActivity.this);
- user_tax.setText(jsonData.getString("xxx03"));
- user_tax.setLayoutParams(view_layout);
-
- TextView user_sum = new TextView(MainActivity.this);
- user_sum.setText(jsonData.getString("xxx04"));
- user_sum.setLayoutParams(view_layout);
-
- tr.addView(user_acc);
- tr.addView(user_pwd);
- tr.addView(user_tax);
- tr.addView(user_sum);
- user_list.addView(tr);
- }
- //System.out.print("Activity端:" + result);
- } catch(Exception e) {
- //Log.e("log_tag", e.toString());
- String name = e.toString();
- Toast.makeText(getApplicationContext(), name, 1).show();
- }
- }
- };
- }
複製代碼
2.副程式:
- package com.xxx.testandroiddbactivity;
- 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;
- import android.database.SQLException;
- import android.util.Log;
- import android.widget.Toast;
- public class DBConnector {
- public static String executeQuery(String query_string,String username,String password) {
- String result = "";
-
- try {
- HttpClient httpClient = new DefaultHttpClient(); //調用HttpClient方法
- //建立Http POST連線
- HttpPost httpPost = new HttpPost("http://192.168.1.2/AndroidConnectDB/android_connect_db.php");
- //post運作傳送變數用NameValuePair[]陣列儲存
- ArrayList<NameValuePair> params = new ArrayList<NameValuePair>();
-
- params.add(new BasicNameValuePair("query_string", query_string)); //(輸入變數名稱,輸入變數名稱的值)
- params.add(new BasicNameValuePair("username", username)); //(輸入變數名稱,輸入變數名稱的值)
- params.add(new BasicNameValuePair("password", password)); //(輸入變數名稱,輸入變數名稱的值)
-
- httpPost.setEntity(new UrlEncodedFormEntity(params, HTTP.UTF_8)); //發出HTTP request
- HttpResponse httpResponse = httpClient.execute(httpPost); //取得HTTP response
- //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 + "
- ");
- }
- inputStream.close();
- result = builder.toString();
- } catch(Exception e) {
- //Log.e("log_tag", e.toString());
- }
- //System.out.print("Java端:" + result);
- return result;
- }
- }
複製代碼
3. PHP網頁
- <?php
- $db = OCILogon("test1","test1","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= 192.168.1.2 )(PORT=1521))(CONNECT_DATA=(SID=test)))");//oracle DB寫法
- $sql = $_POST["query_string"];
- if($_POST["query_string"] == "1" ){
- $sql = "SELECT XXX01,XXX02,XXX03,XXX04 FROM XXX_FILE";
- }
- $stid = OCIParse($db, $sql);
- OCIExecute($stid, OCI_DEFAULT);
- while($succ = OCIFetchInto($stid, $row, OCI_ASSOC)) //提取資料可用資料庫的欄位名稱。
- $output[] = $row;
- print(json_encode($output));
- OCILogoff($db);
- ?>
複製代碼 |