我正在尝试将所有数据获取在我的SQL Server表中并将其插入数组。
我的桌子的名字是"用户",我想获取所有名称和电子邮件并放置它们在数组中类似:
[名称,电子邮件,名称,电子邮件,名称,电子邮件......]
我在将数据发布到我的SQL Server时使用了凌空,但是我无法获取任何数据。这是我写的第一个代码,我不知道如何完成:
private static Array GetData() {
StringRequest strReq = new StringRequest(Method.POST,
AppConfig.URL_GETDATA, new Response.Listener<String>() { // URL_LOGIN is the php file which will extract the data
......
几乎一步一步。
您将需要您的Web服务的PHP代码,这看起来像这样:
<?php
/*
* Following code will get single User's details
* using the Student ID number
*/
// array for JSON response
$response = array();
// check for post data
if (isset($_POST['param1']) &&
isset($_POST['param2']) &&
isset($_POST['param3'])) {
$param1 = $_POST['param1'];
$param2 = $_POST['param2'];
$param3 = $_POST['param3'];
// include db connect class
require_once __DIR__ . '/db_config.php';
// set vars
$host = DB_SERVER;
$db = DB_DATABASE;
$user = DB_USER;
$pass = DB_PASSWORD;
$charset = 'utf8';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
// connecting to db
$pdo = new PDO($dsn, $user, $pass, $opt);
$sql = 'SELECT * FROM tblYourTable
WHERE ID = :id_STM
AND col2 >= :col2_STM
AND col3 >= :col3_STM
';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id_STM', $param1, PDO::PARAM_INT);
$stmt->bindParam(':col2_STM', $param2, PDO::PARAM_STR); // this could be a string parameter
$stmt->bindParam(':col3_STM', $param3, PDO::PARAM_STR); // oddly, Doubles are given as STR also!!
$res = $stmt->execute();
/* Check the number of rows that match the SELECT statement */
if ($res) {
// success
$response["success"] = 1;
// connection node
$response["data"] = array();
// This will retreive as many rows as your query has aquired
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$data = array(); // Your return array
$data["data1"] = $row["Col1"];
$data["data2"] = $row["Col2"];
$data["data3"] = $row["Col3"];
// ... and so on til you have all the data you need
$data["data_N"] = $row["Col_N"];
// pushes a new row onto your array with each iteration
array_push($response["data"], $data);
}
}
else {
/* No rows matched -- do something else */
// required field is missing
$response["success"] = 0;
$response["message"] = "No data returned!!";
}
}
else {
$response["success"] = 0;
$response["message"] = "Parameters are not correct";
}
// echoing JSON response
echo json_encode($response);
?>
在单独的文件中添加数据库连接数据。
数据库查询看起来与此相似:
public String selectYourData(int id, String param2, double param2){
String res = "";
try {
// add parameters to list which need to match the parameters in your PHP code
Map<String, String> params = new LinkedHashMap<String, String>();
params.put("param1", String.valueOf(id));
params.put("param2", String.valueOf(id));
params.put("param3", String.valueOf(id));
// get the json string from the result set from the central database
HttpRequestCommand http = new HttpRequestCommand();
// the return is a json string containing the url of the
res = http.makeHttpRequestJsonString("my_php_script.php", params);
JSONObject jObj = new JSONObject(res);
String success = jObj.getString("success");
if(success.contentEquals("1")){
//Data downloaded successfully!!
}
else{ // else maybe do something??
res = "MyWarningFlag"
}
}
catch (Exception ex){
Log.e(TAG, "selectData --- " + ex.getMessage());
}
return res;
}
,但是,为了使您的选择方法工作,您需要一个HTTP请求,这可能与此相似:网络服务器(
private static final String TAG = "YourClassNameHere"
public String makeHttpRequestJsonString(String phpScript, Map<String, String> params){
String json = "";
InputStream responseStream = null;
String logMess = "";
long startTime;
long stopTime;
long elapsedTime;
// Replace as needed (with subdirectories as needed)
String host = "Http://yourWebSite.com/dir1/dir2/";
host =+ "/" + phpScript
// I've changed my code here considerable to simplify it
// so you will need to check your URL
URL url = new URL(host);
try {
// Added to observe the performance of the download
startTime = System.currentTimeMillis();
StringBuilder postData = new StringBuilder();
for(Map.Entry<String, String> param : params.entrySet()){
param.getValue());
if(postData.length() != 0) postData.append('&');
postData.append(URLEncoder.encode(param.getKey(), "UTF-8"));
postData.append('=');
postData.append(URLEncoder.encode(String.valueOf(param.getValue()), "UTF-8"));
}
postData.toString());
byte[] postDataBytes = postData.toString().getBytes("UTF-8");
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setUseCaches(false);
conn.setDoOutput(true);
conn.setRequestMethod("POST");
conn.setRequestProperty("Connection", "Keep-Alive");
conn.setRequestProperty("Cache-Control", "no-cache");
conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
conn.setRequestProperty("Content-Length", String.valueOf(postDataBytes.length));
DataOutputStream request = new DataOutputStream(conn.getOutputStream());
request.write(postDataBytes);
request.flush();
//I've heard some people have issues if they close the request -- I have not
request.close();
//Check the response code of the server -
Integer replyCode = conn.getResponseCode();
logMess += " Reply Code: " + replyCode.toString();
responseStream = new BufferedInputStream(conn.getInputStream());
BufferedReader responseStreamReader = new BufferedReader(new InputStreamReader(responseStream));
stopTime = System.currentTimeMillis();
elapsedTime = stopTime - startTime;
logMess += " elapsed Time : " + elapsedTime + " ms";
// I dump this to the error log -- it is easier to see
// but in production all unnecessary logging should be removed
Log.e(TAG, "makeHttpRequestJsonString --- " + logMess);
String line = "";
StringBuilder stringBuilder = new StringBuilder();
while ((line = responseStreamReader.readLine()) != null) {
stringBuilder.append(line).append("n");
}
responseStreamReader.close();
json = stringBuilder.toString();
}
catch (UnsupportedEncodingException e) {
Log.e(TAG, "makeHttpRequestJsonString --- " + e.getMessage());
} catch (IOException e) {
Log.e(TAG, "makeHttpRequestJsonString --- " + e.getMessage());
}
// You are now returning the JSON encoded string results
return json;
}
最后,从背景任务中启动您活动的搜索:
/**
* Background Async Task to Load all Data from your HTTP Request
* */
class LoadServerData extends AsyncTask<String, String, String> {
@Override
protected void onPreExecute() {
super.onPreExecute();
}
protected String doInBackground(String... args) {
String result = "";
int id = 1; // your table ID value from somewhere
string param2 = "someValue"; // your param if you have one
double param3 = 1.1d; // your param if you have one maybe a double value??
try {
// call the class where your 'selectYourData' method is
ServerData dataServer = new ServerData(YourActivityClassName.this);
result = dataServer.selectYourData(id, param2, param3);
}
catch (Exception ex) {
Log.e(TAG, "LoadServerData --- " + ex.getMessage());
}
return result;
}
protected void onPostExecute(String result) {
final String value = result;
if(result.isEmpty()) {
postEmptyList();
return;
}
// If you have a lot of data you will want to organize it in its own thread
// So you won't block the UI thread
runOnUiThread(new Runnable() {
public void run() {
ListData data; // A custom class for holding your data - you will have to do this part
Map<String, ListData> emailList = new LinkedHashMap<>();
try {
JSONObject object = new JSONObject(value);
JSONArray array = object.getJSONArray("YourRootElementInJson");
int len = array.length();
if(len > 0) {
for (int i = 0; i < len; i++) {
final JSONObject o = array.getJSONObject(i);
data = new ListData();
int userId = o.getInt("col1");
data.setUserId(userID);
String userName = o.getString("col2");
data.setUserName(userName);
String email = o.getString("col3");
data.setUserEmail(email);
emailList.put(userName, data);
}
}
}
catch (JSONException jex){
Log.e(TAG, " onPostExecute --- " + jex.getMessage());
}
doSometingWithData(array);
}
});
}
}