我想能够,使用我的android设备,连接到mySQL数据库,发送一个参数,将在sql语句中使用,我想得到的结果回来,并能够呈现它。这听起来很简单,但我能找到的所有教程和示例都存在以下缺陷:
- 极度过度构建(至少10类,使完美的按钮)
- 令人难以置信的混乱(没有注释,解释和延迟变量名)
- 依赖于不存在的类
如果我去掉一些不必要的东西,一切都会崩溃,所以我不能提取真正重要的东西,使它远程可读/可理解。
所以,在最简单的方式可能:什么是需要在我的android应用程序连接到我的数据库?如何向php-script发送参数?我怎么能产生一个结果,从它的安卓应用程序可以读取?
UPDATE, STRIPPING ESSENTIALS TAKE 1因此,正如我在SoftCoder回答的一个评论中提到的那样,我将尝试使用他的完整应用程序,并剥离花哨的东西,只获得连接mySQL所需的东西。
首先,我在清单中添加了<uses-permission android:name="android.permission.INTERNET" />
。.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);
?>
这个脚本打印出表中的所有条目。
现在完成活动!
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或方法之间的跳转(利用良好的代码约定在这里并不重要)。因为所有东西都像其他人已经说过的那样随着"networkonmainthreadeexception"崩溃,所以不可能测试它。为什么它崩溃与此异常,即使我同时使用AsyncTask和调用严格的东西?
以下是示例
编辑:首先创建一个数据库名称,假设在MySql的wamp中或在您的服务器中dbname,并创建一个名为emp_info的表,其中添加两个字段id和 Name
这里的场景是将员工的ID和NAME从EDITTEXT插入到MYSQL服务器数据库
全局变量
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 + "'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();
}
<<p> accessWebService方法/strong> public void accessWebService() {
JsonReadTask task = new JsonReadTask();
task.execute(new String[] { url });
}
for 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
<<p> ListDrawaer方法/strong> // 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;
}
对于更新和删除,使用insert方法将值传递给服务器,只需更改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";
}
同样,当你学会了这个,下一个任务你应该学习线程和异步任务,使其更加完善,因为在主线程上工作在android是不好的。就像我在阅读方法中提到的那样,把这个插入方法放在Asyntask中,这样UI就不会受到干扰,互联网的东西是在后台完成的。
注意:
对于新版本的php,在<?php
代码段
error_reporting(E_ALL ^ E_DEPRECATED);