Connecting to MySQL database【转】-优快云博客

本文链接：https://blog.youkuaiyun.com/lcdxwhj2003/article/details/83843761

http://www.helloandroid.com/tutorials/connecting-mysql-database

SDK Version:

The most spread method to connect to a remote MySQL database from an android device, is to put some kind of service into the middle. Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the database and run this script using HTTP protocol from the android system.

We can code the data in JSON format, between Android and PHP with the easy to use built in JSON functions in both languages.

I present some sample code, which selects data from a database depending on a given condition and creates a log message on the android side with the received data.

Lets suppose that we have a MySQL database named PeopleData, and a table int created, with the following SQL:

 
      CREATE TABLE `people` 
     ( 
    
      `id` INT NOT 
     NULL AUTO_INCREMENT PRIMARY KEY , 
    
      `name` VARCHAR 
     ( 100 
     ) NOT 
     NULL , 
    
      `sex` BOOL NOT 
     NULL 
     DEFAULT 
     '1', 
    
      `birthyear` INT NOT 
     NULL 
    
     )

We want to get all the data of the people, who were born after a specified year.
The PHP code will be very simple:
- connect to the database
- run an SQL query, with a WHERE block depending on data from POST/GET values
- output it in JSON format

For example we will have this functionality in the getAllPeopleBornAfter.php file:

 
   
     <?php 
    

      mysql_connect 
     ( 
     "host", 
     "username", 
     "password" 
     ) 
     ; 
    

      mysql_select_db 
     ( 
     "PeopleData" 
     ) 
     ; 
    

        
    

      $q 
     =mysql_query 
     ( 
     "SELECT * FROM people WHERE birthyear>'".$_REQUEST 
     [ 
     'year' 
     ]. 
     "'" 
     ) 
     ; 
    
 
     while 
     ($e 
     =mysql_fetch_assoc 
     ($q 
     ) 
     ) 
    

              $output 
     [ 
     ] 
     =$e 
     ; 
    

        
    

      print 
     (json_encode 
     ($output 
     ) 
     ) 
     ; 
    

        
    

      mysql_close 
     ( 
     ) 
     ; 
    
 
     ?> 
    
 
 

The Android part is only a bit more complicated:
-use a HttpPost to get the data, sending the year value
-convert response to string
-parse JSON data, and use it as you want

 
   
     String result 
     = 
     "" 
     ; 
    
 
     //the year data to send 
    

      ArrayList 
     <NameValuePair 
     > nameValuePairs 
     = 
     new ArrayList 
     <NameValuePair 
     > 
     ( 
     ) 
     ; 
    

      nameValuePairs. 
     add 
     ( 
     new BasicNameValuePair 
     ( 
     "year", 
     "1980" 
     ) 
     ) 
     ; 
    

        
    
 
     //http post 
    
 
     try 
     { 
    

              HttpClient httpclient 
     = 
     new DefaultHttpClient 
     ( 
     ) 
     ; 
    

              HttpPost httppost 
     = 
     new HttpPost 
     ( 
     "http://example.com/getAllPeopleBornAfter.php" 
     ) 
     ; 
    

              httppost. 
     setEntity 
     ( 
     new UrlEncodedFormEntity 
     (nameValuePairs 
     ) 
     ) 
     ; 
    

              HttpResponse response 
     = httpclient. 
     execute 
     (httppost 
     ) 
     ; 
    

              HttpEntity entity 
     = response. 
     getEntity 
     ( 
     ) 
     ; 
    

              
     InputStream is 
     = entity. 
     getContent 
     ( 
     ) 
     ; 
    
 
     } 
     catch 
     ( 
     Exception e 
     ) 
     { 
    

              Log. 
     e 
     ( 
     "log_tag", 
     "Error in http connection " 
     +e. 
     toString 
     ( 
     ) 
     ) 
     ; 
    
 
     } 
    
 
     //convert response to string 
    
 
     try 
     { 
    

              
     BufferedReader reader 
     = 
     new 
     BufferedReader 
     ( 
     new 
     InputStreamReader 
     (is, 
     "iso-8859-1" 
     ),8 
     ) 
     ; 
    

              StringBuilder sb 
     = 
     new StringBuilder 
     ( 
     ) 
     ; 
    

              
     String line 
     = 
     null 
     ; 
    

              
     while 
     ( 
     (line 
     = reader. 
     readLine 
     ( 
     ) 
     ) 
     != 
     null 
     ) 
     { 
    

                      sb. 
     append 
     (line 
     + 
     "\n" 
     ) 
     ; 
    

              
     } 
    

              is. 
     close 
     ( 
     ) 
     ; 
    

        
    

              result 
     =sb. 
     toString 
     ( 
     ) 
     ; 
    
 
     } 
     catch 
     ( 
     Exception e 
     ) 
     { 
    

              Log. 
     e 
     ( 
     "log_tag", 
     "Error converting result " 
     +e. 
     toString 
     ( 
     ) 
     ) 
     ; 
    
 
     } 
    

        
    
 
     //parse json data 
    
 
     try 
     { 
    

              JSONArray jArray 
     = 
     new JSONArray 
     (result 
     ) 
     ; 
    

              
     for 
     ( 
     int i 
     = 
     0 
     ;i 
     <jArray. 
     length 
     ( 
     ) 
     ;i 
     ++ 
     ) 
     { 
    

                      JSONObject json_data 
     = jArray. 
     getJSONObject 
     (i 
     ) 
     ; 
    

                      Log. 
     i 
     ( 
     "log_tag", 
     "id: " 
     +json_data. 
     getInt 
     ( 
     "id" 
     ) 
     + 
    

                              
     ", name: " 
     +json_data. 
     getString 
     ( 
     "name" 
     ) 
     + 
    

                              
     ", sex: " 
     +json_data. 
     getInt 
     ( 
     "sex" 
     ) 
     + 
    

                              
     ", birthyear: " 
     +json_data. 
     getInt 
     ( 
     "birthyear" 
     ) 
    

                      
     ) 
     ; 
    

              
     } 
    
 
     } 
    
 
     } 
     catch 
     (JSONException e 
     ) 
     { 
    

              Log. 
     e 
     ( 
     "log_tag", 
     "Error parsing data " 
     +e. 
     toString 
     ( 
     ) 
     ) 
     ; 
    
 
     } 
    
 
 

Of course it is possible to use HTTPS and send password to access data, or do more complex data processing on either side, or write more general code, which

does not include this much predefined parameters in the database accessing query.