dozabg1616
2017-06-02 16:02
浏览 329

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

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

       ......

图片转代码服务由CSDN问答提供 功能建议

我正在尝试获取sql server表中的所有数据并将其插入数组中。

我的表名是“用户”,我想获取所有的名称和电子邮件,并将 them放入数组中: \ n

[姓名,电子邮件,姓名,电子邮件,姓名,电子邮件......]

我在将数据发送到我的sql server时使用了凌空但我无法获得任何数据 数据。 这是我写的第一个代码,我不知道如何完成它:

  private static Array GetData(){
 
 StringRequest strReq = new  StringRequest(Method.POST,
 AppConfig.URL_GETDATA,new Response.Listener&lt; String&gt;(){// URL_LOGIN是将提取数据的php文件
 
 ...... 
    
 
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongya6395 2017-06-02 16:52
    已采纳

    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);
                }
            });
        }
    }
    
    点赞 打赏 评论

相关推荐 更多相似问题