[컴][안드로이드] Simple example of MySQL Database client for android

I've made and tested this source on my emulator Galaxy_Nexus with the MySQL DB on local host.

• MySQL Ver. 5.6.13
• Emulator : Galaxy_Nexus

Key points

1. Create DB account for remote control : remote 접속이 가능한 db 계정
2. Authorize the account to access DB : 계정이 db 에 접속할 수 있는 권한이 있어야 한다.
3. Do not make a DriverManager.getConnection() on UI Thread :그리고 UI Thread 에서 getConnection 을 해서는 안된다.(Android 3.x 부터 아마도.)[ref : http://stackoverflow.com/questions/6343166/android-os-networkonmainthreadexception]
4. Add a internet permission on AndroidManifets.xml : AndroidManifets.xml 에 internet permission

Database Set-up

create an account for remote control
원격접속을 위한 계정을 만들자.

c:\> mysql -u root
mysql> use mysql 
mysql> create user 'id'@'%' identified by 'password';

authorize the account to use database
계정이 db 에 접속할 수 있는 권한이 있어야 한다. (참고자료 : MySQL 원격 접속 활성화)

mysql> grant all privileges on db_name.* to user_id@'%' identified by 'user_password' with grant option; 
mysql> flush privileges;

Create a database named "test"

mysql> create database test; 

Create a table named "example" and add a item.

mysql> use test
mysql> CREATE TABLE example (id INT, data VARCHAR(100));
mysql> insert into example (id, data) values (1, "hello");

Android Example

AndroidManifets.xml

<uses-permission android:name="android.permission.INTERNET"/>




MainActivity

package com.example.DBDirectConnect;

import android.app.Activity;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class MainActivity extends Activity {
    /**
     * Called when the activity is first created.
     */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        myAsyncTask t = new myAsyncTask();
        t.execute();

    }


    class myAsyncTask extends AsyncTask<Void, Void, Void> {
        final   TextView tv=(TextView)findViewById(R.id.output);


        @Override
        protected Void doInBackground(Void... params) {


            String driver="com.mysql.jdbc.Driver";
            String url="jdbc:mysql://192.168.0.104:3306/test";
            String username="user_id";
            String password= "password"; //user password
            try{
                Class.forName(driver);
                Connection con = DriverManager.getConnection(url, username, password);


                Log.d("SimpleDBClient", "connection was successful");


                Statement st=con.createStatement();
                ResultSet rs=st.executeQuery("select * from example");
                while(rs.next())
                {
                    Log.d("SimpleDBClient", rs.getString(2));

                }
                con.close();


            }
            catch(Exception e){
                Log.d("SimpleDBClient", "error");
                e.printStackTrace();
            }

            return null;
        }

        @Override
        protected void onPostExecute(Void result) {

        }

        @Override
        protected void onPreExecute() {
            super.onPreExecute();
            tv.setText("Started Running….");
        }

    }

}

Download

Download full-source