连接MySQL与Android [英] Connect mySQL with Android
问题描述
我希望能够用我的Android设备，连接到MySQL数据库，发送，这将在SQL语句中使用的参数，我希望得到的结果回来，能present它。 这听起来很简单，但所有的教程和例子我能找到患：
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?
更新，剥离ESSENTIALS取1 因此，正如我在，我会尽力把他的完整的应用程序，并去掉了花哨的东西是拿到什么需要连接到MySQL。软codeR的答案的评论人提
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.
首先，我已经加入＆LT;使用-权限的Android：名称=android.permission.INTERNET对/＆GT; 在清单中。 .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或方法之间跳跃（利用好code约定在这里并不重要）。既然一切都崩溃了NetworkOnMainThreadException像别人已经说过的那样，这是不可能的测试。 为什么有此异常崩溃即使我同时使用的AsyncTask，并调用严格，啄？
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?
推荐答案
下面是例子
编辑： 首先创建一个数据库名称假定的数据库名在MySQL中WAMP或在您的服务器并创建一个表名为 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到MySQL服务器数据库中插入ID员工和名称
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;
有关活动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();
}
});
}
}
插入数据的方法
Method of inserting data
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文件
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);
?>
第二个编辑： - 的
：用于读取数据我已经做asyntask
下面的数据显示在列表视图。
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文件code
<?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);
?>
如果您要检查你使用这种插入和读取使用此方法之前，互联网连接..即把这个方法，如果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;
}
有关Updation和删除使用Insert方法的值传递到服务器，只需要改变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的好。只是把我所提到的阅读方法，这样的UI没得到不安和互联网的事情是在后台完成的Asyntask这种插入方法。
请注意：
有关PHP的新版本后，加入这行＆LT; PHP 段
for new version of php add this line after <?php snippet
error_reporting(E_ALL ^ E_DEPRECATED);
这篇关于连接MySQL与Android的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
