php - 将 mySQL 与 Android 连接

Question

我希望能够使用我的 android 设备连接到 mySQL 数据库，发送一个将在 SQL 语句中使用的参数，然后我想取回结果并能够显示它。这听起来很简单，但我能找到的所有教程和示例都存在以下问题:

• 过度构建(至少 10 个类才能制作出完美的按钮)
• 令人难以置信的困惑(没有评论、解释和迟钝的变量名)
• 依赖于不存在的类

如果我删除一些不必要的东西，一切都会崩溃，所以我无法提取真正重要的东西来使其远程可读/可理解。

那么，以最简单的方式:我的 Android 应用程序需要什么来连接到我的数据库？如何将参数发送到 php 脚本？如何从中生成 Android 应用程序可以读取的结果？

更新，STIPPING ESSENTIALS TAKE 1因此，正如我在对 SoftCoder 的回答的评论之一中提到的那样，我将尝试使用他的完整应用程序并去除花哨的东西，只获得连接到 mySQL 所需的东西。

首先，我添加了 <uses-permission android:name="android.permission.INTERNET" />在 list 中。 .php 看起来像这样(主机、用户、密码等实际上是其他东西):

<?php
   $con = mysql_connect("HOST","USER","PASSWORD");
   if (!$con)
   {
      die('Could not connect: ' . mysql_error());
   }

   mysql_select_db("database_name", $con);

   $result = mysql_query("SELECT * FROM Table;");

   while($row = mysql_fetch_array($result))
   {
      echo $row['col1'];
      echo $row['col2'];
   }

   mysql_close($con);
?>

此脚本打印出表中的所有条目。

现在完成 Activity !

package com.example.project;

import java.io.*;

import org.apache.http.HttpResponse;
import org.apache.http.client.*;
import org.apache.http.client.methods.*;
import org.apache.http.impl.client.*;
import org.json.*;

import android.app.*;
import android.os.*;
import android.util.*;
import android.view.*;
import android.widget.*;

public class MainActivity extends Activity 
{
    private String jsonResult;
    private String url = "url_to_php";
    InputStream is=null;
    String result=null;
    String line=null;

    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        //supposedly the app wont crash with "NetworkOnMainThreadException". It crashes anyway
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);

        //create our Async class, because we can't work magic in the mainthread
        JsonReadTask task = new JsonReadTask();
        task.execute(new String[] { url });
    }
    private class JsonReadTask extends AsyncTask<String, Void, String> 
    {
        // doInBackground Method will not interact with UI 
        protected String doInBackground(String... params)
        {
            // the below code will be done in background
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(params[0]);
            try 
            {
                //not sure what this does but it sounds important
                HttpResponse response = httpclient.execute(httppost);

                //took the "stringbuilder" apart and shoved it here instead
                String rLine = "";
                StringBuilder answer = new StringBuilder();
                BufferedReader rd = new BufferedReader(new InputStreamReader(response.getEntity().getContent()));

                while ((rLine = rd.readLine()) != null)
                    answer.append(rLine);

                //put the string into a json, don't know why really
                jsonResult = answer.toString();
            }

            catch (ClientProtocolException e)
            {
                e.printStackTrace();
                Log.e("Fail 12", e.toString());
            } 
            catch (IOException e) 
            {
                Log.e("Fail 22", e.toString());
                e.printStackTrace();
            }
            return null;
        }
    }
    // after the doInBackground Method is done the onPostExecute method will be called
    protected void onPostExecute(String result) throws JSONException 
    {
        // I skipped the method "drwer"-something and put it here instead, since all this method did was to call that method
        // getting data from server 
        JSONObject jsonResponse = new JSONObject(jsonResult);
        if(jsonResponse != null)
        {
            //I think the argument here is what table we'll look at... which is weird since we use php for that
            JSONArray jsonMainNode = jsonResponse.optJSONArray("Tablename");

            // get total number of data in table
            for (int i = 0; i < jsonMainNode.length(); i++)
            {
                JSONObject jsonChildNode = jsonMainNode.getJSONObject(i);

                String name = jsonChildNode.optString("col1"); // here name is the table field
                String number = jsonChildNode.optString("col2"); // here id is the table field
                String outPut = name + number ; // add two string to show in listview 
                //output to log instead of some weird UI on the device, just to see if it connects
                Log.d("Log", outPut.toString());
            }
        }
    }
    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;
    }
    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);
    }
}

所以这是我到目前为止想出的，可以算作“尽可能简单”，没有花哨的 UI 或在方法之间跳转(在这里利用良好的代码约定并不重要)。由于一切都像其他人已经说过的那样因“NetworkOnMainThreadException”而崩溃，因此无法对其进行测试。为什么即使我同时使用 AsyncTask 并调用 Strict-thingy 也会因此异常而崩溃？

Answer 1

这是例子

编辑:首先创建一个数据库名称假设dbname in MySql in wamp or in your server and create a table named emp_info 其中两个字段添加id 和姓名

这里的场景是将员工的 ID 和 NAME 从 EDITTEXT 插入到 MYSQL 服务器数据库

全局变量是

  String name;
    String id;
    InputStream is=null;
    String result=null;
    String line=null;
    int code;

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.json.JSONObject;

import android.app.Activity;
import android.os.Bundle;
import android.os.StrictMode;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class add extends Activity {

    String name;
    String id;
    InputStream is=null;
    String result=null;
    String line=null;
    int code;
    String tobed = null;



    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.add);
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        final EditText e_id=(EditText) findViewById(R.id.editText1);
        final EditText e_name=(EditText) findViewById(R.id.editText2);
        Button insert=(Button) findViewById(R.id.button1);

        insert.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub

            id = e_id.getText().toString();
            name = e_name.getText().toString();

            insert();
        }
    });
    }
}

插入数据的方法

public void insert()
    {
        ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();

      // put the values of id and name in that variable
    nameValuePairs.add(new BasicNameValuePair("id",id));
    nameValuePairs.add(new BasicNameValuePair("name",name));

        try
        {
        HttpClient httpclient = new DefaultHttpClient();

          // here is the php file
         // for local use for example if you are using wamp just put the file in www/project folder
        HttpPost httppost = new HttpPost("http://10.0.2.2/project/insert2.php");
        // if the file is on server
        HttpPost httppost = new HttpPost("http://example.com/insert2.php");
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpclient.execute(httppost); 
            HttpEntity entity = response.getEntity();
            is = entity.getContent();
            Log.e("pass 1", "connection success ");
    }
        catch(Exception e)
    {
            Log.e("Fail 1", e.toString());
            Toast.makeText(getApplicationContext(), "Invalid IP Address",
            Toast.LENGTH_LONG).show();
    }     

        try
        {
            BufferedReader reader = new BufferedReader
            (new InputStreamReader(is,"iso-8859-1"),8);
            StringBuilder sb = new StringBuilder();
            while ((line = reader.readLine()) != null)
        {
                sb.append(line + "\n");
            }
            is.close();
            result = sb.toString();
        Log.e("pass 2", "connection success ");
    }
        catch(Exception e)
    {
            Log.e("Fail 2", e.toString());
    }     

    try
    {

            // get the result from php file
            JSONObject json_data = new JSONObject(result);
            code=(json_data.getInt("code"));

            if(code==1)
            {
        Toast.makeText(getBaseContext(), "Inserted Successfully",
            Toast.LENGTH_SHORT).show();
            }
            else
            {
         Toast.makeText(getBaseContext(), "Sorry, Try Again",
            Toast.LENGTH_LONG).show();
            }
    }
    catch(Exception e)
    {
            Log.e("Fail 3", e.toString());
            Log.i("tagconvertstr", "["+result+"]");
    }
    }

这里是insert2.php文件

<?php
    // this variables is used for connecting to database and server
    $host="yourhost";
    $uname="username";
    $pwd='pass';
    $db="dbname";

     // this is for connecting
    $con = mysql_connect($host,$uname,$pwd) or die("connection failed");
    mysql_select_db($db,$con) or die("db selection failed");

    // getting id and name from the client
     if(isset($_REQUEST)){
    $id=$_REQUEST['id'];
    $name=$_REQUEST['name'];}

    $flag['code']=0;

    // query for insertion
    // table name emp_info and its fields are id and name
    if($r=mysql_query("insert into emp_info values('$name','$id') ",$con))
    {
        // if query runs succesfully then set the flag to 1 that will be send to client app
        $flag['code']=1;
        echo"hi";
    }
      // send result to client that will be 1 or 0
    print(json_encode($flag));
    //close
    mysql_close($con);


 ?>

第二次编辑:-

为了读取数据我做了asyntask

此处数据显示在 ListView 中。

public class read extends Activity {
     private String jsonResult;//
      // use this if your file is on server
     private String url = "http://exmaple.com/read.php";
     // use this if you are locally using
     // private String url = "http://10.0.2.2/project/read.php";
     private ListView listView;
     Context context;
     String name;
        String id;
        InputStream is=null;
        String result=null;
        String line=null;
        int code;
     @Override
     protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.read);
      StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
      StrictMode.setThreadPolicy(policy);
      context = this;
      listView = (ListView) findViewById(R.id.listView1);
      accessWebService();
     }

accessWebService方法

       public void accessWebService() {
       JsonReadTask task = new JsonReadTask();
       task.execute(new String[] { url });
         }

对于 JsonReadTask 类

private class JsonReadTask extends AsyncTask<String, Void, String> {
      // doInBackground Method will not interact with UI 
      @Override

      protected String doInBackground(String... params) {
       // the below code will be done in background
       HttpClient httpclient = new DefaultHttpClient();
       HttpPost httppost = new HttpPost(params[0]);
       try {
        HttpResponse response = httpclient.execute(httppost);
        jsonResult = inputStreamToString(
          response.getEntity().getContent()).toString();
       }

       catch (ClientProtocolException e) {
        e.printStackTrace();
        Log.e("Fail 12", e.toString());
       } catch (IOException e) {
           Log.e("Fail 22", e.toString());
        e.printStackTrace();
       }
       return null;
      }

      private StringBuilder inputStreamToString(InputStream is) {
       String rLine = "";
       StringBuilder answer = new StringBuilder();
       BufferedReader rd = new BufferedReader(new InputStreamReader(is));

       try {
        while ((rLine = rd.readLine()) != null) {
         answer.append(rLine);
        }
       }

       catch (IOException e) {
        // e.printStackTrace();
        Toast.makeText(getApplicationContext(),
          "Error..." + e.toString(), Toast.LENGTH_LONG).show();
       }
       return answer;
      }

      // after the doInBackground Method is done the onPostExecute method will be called
      @Override
      protected void onPostExecute(String result) {
      // here you can interact with UI
       ListDrwaer();
      }
     }// end async task

列表抽屉方法

 // build hash set for list view
     public void ListDrwaer() {
      List<Map<String, String>> employeeList = new ArrayList<Map<String, String>>();

      try {
        // getting data from server 
       JSONObject jsonResponse = new JSONObject(jsonResult);
       if(jsonResponse != null)
       {
       JSONArray jsonMainNode = jsonResponse.optJSONArray("emp_info");

       // get total number of data in table
       for (int i = 0; i < jsonMainNode.length(); i++) {
        JSONObject jsonChildNode = jsonMainNode.getJSONObject(i);

        String name = jsonChildNode.optString("name"); // here name is the table field
        String number = jsonChildNode.optString("id"); // here id is the table field
        String outPut = name + number ; // add two string to show in listview 
        employeeList.add(createEmployee("employees", outPut));
       }
       }
      } catch (JSONException e) {
       Toast.makeText(getApplicationContext(), "Error" + e.toString(),
         Toast.LENGTH_SHORT).show();
      }

      SimpleAdapter simpleAdapter = new SimpleAdapter(this, employeeList,
        android.R.layout.simple_list_item_1,
        new String[] { "employees" }, new int[] { android.R.id.text1 });
      listView.setAdapter(simpleAdapter);
     }

     private HashMap<String, String> createEmployee(String name, String number) {
      HashMap<String, String> employeeNameNo = new HashMap<String, String>();
      employeeNameNo.put(name, number);
      return employeeNameNo;
     }
    }

和你的 read.php 文件代码

<?php
$host="localhost"; //replace with database hostname
$username="root"; //replace with database username
$password=""; //replace with database password
$db_name="dbname"; //replace with database name

$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "select * from emp_info";
$result = mysql_query($sql);
$json = array();

if(mysql_num_rows($result)){
while($row=mysql_fetch_assoc($result)){
$json['emp_info'][]=$row;
}
}
mysql_close($con);
echo json_encode($json);
?> 

如果你想在使用这个插入和阅读之前检查你的互联网连接使用这个方法..即将这个方法放在 if else 语句中

 public boolean isOnline() {
                ConnectivityManager cm =
                    (ConnectivityManager) getSystemService(Context.CONNECTIVITY_SERVICE);
                NetworkInfo netInfo = cm.getActiveNetworkInfo();
                if (netInfo != null && netInfo.isConnectedOrConnecting()) {
                    return true;
                }
                return false;
            }

对于更新和删除，使用插入方法将值传递给服务器，只需更改 insert2.php 的查询以更新这样的值

if($r=mysql_query("UPDATE emp_info SET employee_name = '$name' WHERE employee_name = '$id'",$con))
    {
        $flag['code']=1;
    } 

删除

if($r=mysql_query("DELETE FROM emp_info WHERE employee_name = '$name'",$con))
    {
        $flag['code']=1;
        echo"hi";
    }

此外，当你学习了这个之后，下一个任务你应该学习线程和 Asyntask 以使其更加改进，因为在主线程上工作在 android 中并不好。正如我在阅读方法中提到的那样，只需将此插入方法放在 Asyntask 中，这样 UI 就不会受到干扰，并且互联网的事情在后台完成。

注意:

对于新版本的 php，在 <?php 之后添加这一行 fragment

error_reporting(E_ALL ^ E_DEPRECATED);