Considerations for Android Studio remote connection to MySQL using JDBC (with example)

JDBC provides a unified interface for Java programs to access various types of relational databases, users do not have to write different code for different databases, but the use of JDBC must download the appropriate driver, such as I am here to connect MySQL, so go to the MySQL official website to download x corresponding driver HTTPS ://dev.mysql.com/downloads/connector/j/

Here i download unzip get Mysql-connector-java-5.1.43-bin.jar

Creating a new Java project in eclipse requires only build path--and add External archives the path to the jar package to be used. In Android Studio it is only necessary to copy and paste the App\libs into the project directory, and the IDE will load automatically. -It seems so.

Configuration of local Android studio

My Android studio version is Community Edition2016.2.5, the system is win7 64 bit. Build error after adding the jar file.

As prompted, you need to allprojects {...} in the outermost build.gradle file of the project. Add the following code within the area (see the picture below the code, then do not have the attached image)

    Tasks.withtype (javacompile) {        sourcecompatibility = ' 1.7 '        targetcompatibility = ' 1.7 '    }

This is not the end of the error:unable to find a JDK, at first I suspect the JDK was not added to the environment variable, but the cmd window was tested correctly, and the Android Studio new project can be compiled and run. Well, it's still a problem with the jar package. After a long time to find the solution, it seems that the java8 is incompatible, so you need to fully change to Java7 to compile

Android Studio2.1.2 JAVA8 Environment Reference Java Library compilation error

1. Android {...} in all module Build.gradle files Area to add

    compileoptions {        sourcecompatibility javaversion.version_1_7        targetcompatibility JavaVersion.VERSION_1_7    }

As for my new project, no module is added, so the files that need to be modified are only app\build.gradle

2. Android {defaultconfig {...} in the main module's Build.gradle file (aka App\build.gradle) Area to add

jackoptions {        enabled True    }

The compilation succeeds, but note that this is the first step, just a successful reference to the Java library. Android Studio is not allowed to access the Internet by default.

So you need to add a line in the <manifest>...</manifest> area in App/src/main/androidmanifest.xml

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

Configuration of remote MySQL

Then my MySQL is installed on the Linux virtual machine (Ubuntu 16.04), the default MySQL is forbidden remote connection, so on Linux also configure

The first is to let MySQL support Chinese characters, log in to the MySQL user, enter the command status, you can see a few character sets are like this

Server characterset:latin1db     characterset:latin1client characterset:utf8conn.  Characterset:utf8

Therefore, you need to modify/ETC/MYSQL/MY.CNF with Superuser privileges, add the following code (the port is bound to 3306, although the general default port is also 3306)

[client]default-character-set=utf8[mysqld]default-storage-engine=innodbcharacter-set-server= Utf8collation-server=utf8_general_ci
port=3306

Then restart MySQL with the following command

$/etc/init.d//etc/init.d/mysql start

After you log in to MySQL and use the status command, you can see that the character set has all become UTF8.

Then MySQL default binding IP is 127.0.0.1, Port 3306, under the shell can be netstat-apn by Shell command | grep 3306 View port occupancy, if Port 3306 corresponds to the third item (local address) is 127.0.0.1:3306, then MySQL is bound to the local address, can not connect remotely.

or modify/ETC/MYSQL/MY.CNF with Superuser privileges, add the following code and restart MySQL

bind-address=0.0. 0.0

Restart and then use NETSTAT-APN | grep 3306 View the third item becomes 0.0.0.0:3306, then MySQL supports remote connection.

Not yet, although MySQL gives the remote connection permission, the MySQL user does not. My MySQL username is team, password is java123, log in to MySQL with root and enter the following command

 mysql>  grant  all  on  * . *  to  [email protected]  " %   " identified by   " java123   " with  grant  option   mysql  >  flush privileges  ; 
  

[email protected] '% ' on behalf of user team support remote connection, [email protected] ' localhost ' on behalf of user team support local connection

Code implementation

Well, finally can write the code, and then forced to Mainacitivity.java in the OnCreate () method inside Add the following code (try to connect first)

        //1. Load the JDBC driver        Try{class.forname ("Com.mysql.jdbc.Driver"); LOG.V (TAG,"Load JDBC Driver succeeded"); } Catch(ClassNotFoundException e) {log.e (TAG,"Load JDBC Driver failed"); return; }        //2. Set up the necessary connection information such as ip/port/database name/user name/password, etc.String IP = "192.168.183.134"; intPort = 3306; String DbName= "XYZ"; String URL= "jdbc:mysql://" + IP + ":" +Port+ "/" + dbName;//build a string that connects to MySQLString user = "Team"; String Password= "Java123"; //3. Connect to JDBC        Try{Connection conn=drivermanager.getconnection (URL, user, password);        Conn.close (); } Catch(SQLException e) {log.e (TAG,"Remote connection Failed!"); return; }

This uses andorid.util.Log instead of SYSTEM.OUT.PRINTLN to print the message, because filters can be set to filter out specific logs via tag, log level, and so on.

I filter by tag here, I can filter by the message and package, and support regular expressions, the program runs with a lot of logs mixed together, so it's better to use log instead of System.out to print the message.

Then, filtering through the log filter, you can find that the connection failed.

This problem tortured me more than a day, referring to a lot of online code, and my basic is the same, but also copy over the test is not, and finally found a solution on the StackOverflow

https://stackoverflow.com/questions/12233145/connecting-to-mysql-from-android-with-jdbchttps:// Stackoverflow.com/questions/12233145/connecting-to-mysql-from-android-with-jdbc

The connection to MySQL must be in an asynchronous task class, that is, you must create a new thread to connect to MySQL and not execute code in the main thread.

        FinalThread thread =NewThread (NewRunnable () {@Override Public voidrun () {//try connecting again and again until the connection succeeds and exits the loop                 while(!thread.interrupted ()) {                    Try{Thread.Sleep (100);//try to connect every 0.1 seconds}Catch(interruptedexception e) {log.e (TAG, e.tostring ()); }                    //2. Set up the necessary connection information such as ip/port/database name/user name/password, etc.String IP = "192.168.183.134"; intPort = 3306; String DbName= "XYZ"; String URL= "jdbc:mysql://" + IP + ":" +Port+ "/" + dbName;//build a string that connects to MySQLString user = "Team"; String Password= "Java123"; //3. Connect to JDBC                    Try{Connection conn=drivermanager.getconnection (URL, user, password); LOG.I (TAG,"Remote connection succeeded!");                        Conn.close (); return; } Catch(SQLException e) {log.e (TAG,"Remote connection Failed!");        }                }            }        }); Thread.Start ();

Success, and finally can continue to the next step, the remote connection to the MySQL Send command, the above code modified slightly

                    //3. Connect to JDBCConnection conn =NULL; Try{conn=drivermanager.getconnection (URL, user, password); LOG.I (TAG,"Remote connection succeeded!"); } Catch(SQLException e) {log.e (TAG,"Remote connection Failed!"); }                    if(Conn! =NULL) {String SQL= "SELECT * FROM Pokemon"; Try {                            //create an object to execute the SQL statementJava.sql.Statement Statement =conn.createstatement (); //Execute SQL query statement and get query informationResultSet RSet =statement.executequery (SQL); //Iterate print out query informationLOG.I (TAG, "Treasure Can Dream list"); LOG.I (TAG,"ID\TNAME\TATTR1\TATTR2");  while(Rset.next ()) {log.i (TAG, rset.getstring ("id") + "\ T" + rset.getstring ("name"))                                    + "\ T" + rset.getstring ("attr1") + "\ T" + rset.getstring ("ATTR2")); }                        } Catch(SQLException e) {log.e (TAG,"Createstatement Error"); }                        Try{conn.close (); } Catch(SQLException e) {log.e (TAG,"Close Connection Failed"); }

Do not bother to carefully study the Java format string, directly print out the results, this is only used to execute the query statement executequery, return the result to a set of iterative objects.

While execute can execute any SQL statement, more APIs can refer to the official JDBC tutorial http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

Considerations for remote connection to MySQL using JDBC from Android Studio (example)