将 mySQL 与 Android 连接 [英] Connect mySQL with Android
问题描述
我希望能够使用我的 android 设备连接到 mySQL 数据库，发送将在 SQL 语句中使用的参数，并且我希望返回结果并能够呈现它.这听起来很简单，但我能找到的所有教程和示例都受到影响:
I want to be able to, using my android device, connect to a mySQL database, send in a parameter that will be used in an SQL-statement, and I want to get the result back and be able to present it. It sounds easy, but all tutorials and examples I can find suffer from:
- 极度过度构建(制作完美按钮最少需要 10 个类)
- 令人难以置信的混乱(没有评论、解释和迟钝的变量名称)
- 依赖于不存在的类
如果我去掉一些不必要的东西，一切都会崩溃，所以我无法提取真正重要的东西来使其远程可读/可理解.
If I strip something unnecessary down everything crashes, so I can't extract what's actually important to make it remotely readable/understandable.
那么，以最简单的方式:我的 android 应用程序需要什么才能连接到我的数据库?如何将参数发送到 php 脚本?如何从中生成 Android 应用可以读取的结果?
So, in the simplest way possible: what is needed in my android app to connect to my database? How do I send a parameter to the php-script? How can I generate a result from it that the android app can read?
更新，删除要点需要 1因此，正如我在 SoftCoder 的回答的评论之一中提到的那样，我将尝试使用他的完整应用程序并去掉花哨的东西，以获取连接到 mySQL 所需的内容.
UPDATE, STRIPPING ESSENTIALS TAKE 1 So as I mentioned in one of the comments on SoftCoder's answer, I'll try and take his complete app and strip out the fancy stuff to just get what's needed to connect to mySQL.
首先，我在清单中添加了 <uses-permission android:name="android.permission.INTERNET"/>..php 看起来像这样(主机、用户、密码等实际上是其他东西):
First off, I have added <uses-permission android:name="android.permission.INTERNET" /> in the manifest. The .php looks like this (host, user, password etc is something else in reality):
<?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);
?>
此脚本打印出表格中的所有条目.
This script prints out all entries from the table.
现在进入完整的活动！
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，为什么它会因此异常而崩溃?
So this is what I came up with so far that would count as "simple as possible", no fancy UI or jumping between methods (utilizing good code conventions is not important here). Since everything crashes with a "NetworkOnMainThreadException" like someone else already said it would, it's impossible to test it. Why is it crashing with this exception even though I'm using both an AsyncTask and call the Strict-thingy?
推荐答案
示例如下
首先创建一个数据库名称假设 dbname 在 wamp 中的 MySql 中或在您的服务器中，并创建一个名为 emp_info 的表，其中添加了两个字段 id 和姓名
First Create A database Name suppose dbname in MySql in wamp or in your server and create a table named emp_info in which two fields are added id and name
这里的场景是将EDITTEXT中员工的ID和NAME插入到MYSQL服务器数据库中
Here the Scenario is to insert ID and NAME of employee from the EDITTEXT to MYSQL server database
全局变量是
String name;
String id;
InputStream is=null;
String result=null;
String line=null;
int code;
对于活动代码
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 + "
");
}
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文件
here is insert2.php file
<?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);
?>
第二次-
为了读取数据，我做了异步任务
这里的数据显示在列表视图中.
Second -
For reading data i have done asyntask
Here the data is displayed in a 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
ListDrawaer 方法
// 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 语句中
and if you want to check you internet connection before using this insertion and reading use this method .. i.e put this method in if else statement
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的查询即可更新这样的内容
For Updation and deletion use the insert method to pass the values to the server and just change the query of insert2.php to update ones like this
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 代码段
for new version of php add this line after <?php snippet
error_reporting(E_ALL ^ E_DEPRECATED);
这篇关于将 mySQL 与 Android 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
