如何在java [android studio]中将所有SQL表数据插入到数组中

Question

I'm trying to get all the data in my sql server table and to insert it into an array.

My table's name is "users" and I want to get all the names and emails and put them inside an array like that:

[name,email,name,email,name,email......]

I used volley while POSTING data to my sql server but i could not get any data. This is the first code i wrote and i don't know how to complete it:

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

       ......

Answer 1

Pretty much step by step.

You will need the PHP code for your webservice which could look like this:

<?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);
?>

adding your database connection data in a separate file.

The database query could look similar to this:

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;
}

But, in order for your select method to work you need a http request, which could be similar to this: (you will need to put in your own host data, connection data, passwords and so-forth to gain access to the webserver)

     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("
");
            }
            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;
    }

And finally, kick-off the search from you activity from a background task:

/**
 * 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);
            }
        });
    }
}