How to make MySQL Connector/J working on android?

Question

I've got a MySQL server running on a local Linux machine. I haven't been faced to any problems communicating with it and executing SQL statements through a simple Java program (running inside of eclipse) using the MySQL Connector/J. Here is my simple Java program:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.lang.*;


public class main{
    private static void request(){
        String url = "jdbc:mysql://myadress:3306/mydatabase";
        String user = "user";
        String passwd = "passwd";

        Connection conn = null;
        try{
            /* Initializing the connection */
            conn = DriverManager.getConnection(url, user, passwd);

            Statement statement = conn.createStatement();

            ResultSet resultset = statement.executeQuery(/* MY SQL reqquest */);

            while(resultset.next()){
                System.out.println(resultset.getString(/* THE COLUMN AND ROW I WANTED IN MY REQUEST */));       
            }

        }catch(SQLException e){
            System.out.println("SQL connection error: " + e.getMessage());
        }finally {
            if(conn != null){
                try{
                    /* CLosing connection */
                    conn.close();
                }catch (SQLException e){
                    System.out.println("Error while closing the connection: " + e.getMessage());
                }
            }
        }
    }
    public static void main(String[] args) {
        try{ // Loading the MySQL Connector/J driver
            Class.forName("com.mysql.jdbc.Driver");
        }catch(ClassNotFoundException e){
            System.out.println("Error while loading the Driver: " + e.getMessage());
        }
        request();
    }

}

This code works perfectly inside of eclipse and can perform as much SELECT as UPDATE SQL requests without any problem. When I tried to adapt this request method to my MainActivity.java class, my android application does load the driver correctly but can't communicate with my MySQL server and returns me an error.

If I enter a local IP adress to my server in the previous code and connect my android phone to the Wi-Fi, then my app returns me:

"Communication link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server."

But if I try to enter an external IP adress to my server in the same previous code (don't worry the MySQL port is open to the internet and my server is listening to any IP), use my android phone over an LTE internet connection, I get a different error:

"Could not create connection to database server"

Up until now, none of my research has helped me out. Does anyone has any idea of where my problem could come from ? Thanks in advance :)

Answer 1

Generally, the recommended approach is to post to a web service, and allow the web service to perform the required actions on the database. However, there is an open source library on GitHub which allows you to connect directly to the database server without the need of a web service.

The library is fairly new and has been tested against MySQL 5.1 -> 5.7 which should also be compatible with the equivalent MySQL version to MariaDB.

Disclaimer: I wrote it.

Answer 2

It is possible, if you use the correct connector. I use the connector 3.0.17, in some others examples the connector is mysql-connector-java-5.1.24-bin.jar

1. Just download the right connector.
2. Past the connector-java.x.x.x-bin.jar on libs

3. Import the java.sql

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

   Use the next method to connect

   public void conectarBDMySQL (String user, String password, 
               String ip, String port, String cat)
       {
           if (conexionMySQL == null)      
           {
               String urlConexionMySQL = "";
               if (cat!= "")
                   urlConexionMySQL = "jdbc:mysql://" + ip + ":" + port+ "/" + cat;
               else
                   urlConexionMySQL = "jdbc:mysql://" + ip + ":" + port;
               if (user!= "" & password!= "" & ip != "" & port!= "")
               {
                   try 
                   {
                       Class.forName("com.mysql.jdbc.Driver");
                       conexionMySQL = DriverManager.getConnection(urlConexionMySQL, 
                               user, password);                    
                   } 
                   catch (ClassNotFoundException e) 
                   {
                         Toast.makeText(getApplicationContext(),
                                   "Error: " + e.getMessage(),
                                   Toast.LENGTH_SHORT).show();
                   } 
                   catch (SQLException e) 
                   {
                         Toast.makeText(getApplicationContext(),
                                   "Error: " + e.getMessage(),
                                   Toast.LENGTH_SHORT).show();
                   }
               }
           }
       }
   

It works perfect for me, and i have had no problems!!!

Answer 3

The general misconception among Java developers who is moving to Android world is that everything that has been created for old good Java can be used on Android as well. Since Android usage of Java has violated the major Java principle, "write once, run everywhere", it's simply not true. I've seen people trying to use common Apache jars with Android, which resulted to weird errors (see Can't import Apache HTTP in Eclipse).

The same problems seem to be true for Java JDBC drivers and API's, e.g. see answers here: Connecting to MySQL from Android with JDBC

The common advice - each time when you try to use a 3rd party JAR with Android, check if it's compatible with the latter or if there is a port, which is specific for Android.