Connecting to MySQL database【转】

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

SDK Version: 

M3

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:

 

1. CREATE TABLE `people` (
2. `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
3. `name` VARCHAR ( 100 ) NOT NULL ,
4. `sex` BOOL NOT NULL DEFAULT '1',
5. `birthyear` INT NOT NULL
6. )

 

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:

 

1. <?php
2. mysql_connect ( "host", "username", "password" ) ;
3. mysql_select_db ( "PeopleData" ) ;
4.  
5. $q =mysql_query ( "SELECT * FROM people WHERE birthyear>'".$_REQUEST [ 'year' ]. "'" ) ;
6. while ($e =mysql_fetch_assoc ($q ) )
7.         $output [ ] =$e ;
8.  
9. print (json_encode ($output ) ) ;
10.  
11. mysql_close ( ) ;
12. ?>

 

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

 

1. String result = "" ;
2. //the year data to send
3. ArrayList <NameValuePair > nameValuePairs = new ArrayList <NameValuePair > ( ) ;
4. nameValuePairs. add ( new BasicNameValuePair ( "year", "1980" ) ) ;
5.  
6. //http post
7. try {
8.         HttpClient httpclient = new DefaultHttpClient ( ) ;
9.         HttpPost httppost = new HttpPost ( "http://example.com/getAllPeopleBornAfter.php" ) ;
10.         httppost. setEntity ( new UrlEncodedFormEntity (nameValuePairs ) ) ;
11.         HttpResponse response = httpclient. execute (httppost ) ;
12.         HttpEntity entity = response. getEntity ( ) ;
13.         InputStream is = entity. getContent ( ) ;
14. } catch ( Exception e ) {
15.         Log. e ( "log_tag", "Error in http connection " +e. toString ( ) ) ;
16. }
17. //convert response to string
18. try {
19.         BufferedReader reader = new BufferedReader ( new InputStreamReader (is, "iso-8859-1" ),8 ) ;
20.         StringBuilder sb = new StringBuilder ( ) ;
21.         String line = null ;
22.         while ( (line = reader. readLine ( ) ) != null ) {
23.                 sb. append (line + "\n" ) ;
24.         }
25.         is. close ( ) ;
26.  
27.         result =sb. toString ( ) ;
28. } catch ( Exception e ) {
29.         Log. e ( "log_tag", "Error converting result " +e. toString ( ) ) ;
30. }
31.  
32. //parse json data
33. try {
34.         JSONArray jArray = new JSONArray (result ) ;
35.         for ( int i = 0 ;i <jArray. length ( ) ;i ++ ) {
36.                 JSONObject json_data = jArray. getJSONObject (i ) ;
37.                 Log. i ( "log_tag", "id: " +json_data. getInt ( "id" ) +
38.                         ", name: " +json_data. getString ( "name" ) +
39.                         ", sex: " +json_data. getInt ( "sex" ) +
40.                         ", birthyear: " +json_data. getInt ( "birthyear" )
41.                 ) ;
42.         }
43. }
44. } catch (JSONException e ) {
45.         Log. e ( "log_tag", "Error parsing data " +e. toString ( ) ) ;
46. }

 

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.

<!-- google_ad_section_end -->