Can we connect remote MySQL database in Android using JDBC?

Question

I am developing a client-server application which requires remote database connection.

I know that tutorials on the web and more people are using PHP to interact with MySQL. But, I am not so good at PHP and my prior experience is with Core Java, Swing and JDBC.

Can anyone guide me if it is possible to connect remote MySQL database using JAVA JDBC APIs in Android application?

Answer 1

Basically: you can connect to your MySQL (or whatever you use) server, but you should not do this directly from your Android application.

Reasons:

1. Android applications can be decompiled, and the client will have credentials to access to your database. If using the right hacking tools like Backtrack, then this malicious client can access, connect and exploit the data in your database.

2. If your application is for clients all around the world, then the clients should open and maintain a connection to your database per operation or set of operations. Opening a physical database connection takes a lot of time, even when your pc client is in a LAN next to the database engine server. Now, imagine opening a connection from a country in the other side of the world e.g. China or Japan or from a country in South America like Brazil or Peru (where I live).

For these 2 reasons I can come up with, it's a bad idea even trying to connect to MySQL or any other database engine directly from your phone device.

How to solve this problem? Use a service oriented architecture where you will have at least two applications:

1. Service provider application. This application will create and publish web services (preferably RESTful) and may establish policies to consume the web services like user authentication and authorization. This application will also connect to the database and execute CRUD operations against it.

2. Service consumer application. This would be your Android (or any other mobile) application.

From your question, you're focusing on the point 1. As I've said in my comments, you can create a Web application in Java, create a RESTful service there, which boils down to a POJO (plain old java object) that has a method per service. In this method, since it's plain Java after all, you can add other functionality like JDBC usage.

Here's a kickoff example using Jersey, Jackson (JSON library) and JDBC:

@Path("/product")
public class ProductRestService {

    @GET
    @Path("/list")
    @Produces(MediaType.APPLICATION_JSON)
    public List<Product> getProducts() {
        List<Product> productList = new ArrayList<>();
        Connection con = ...; //retrieve your database connection
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT id, name FROM product");
        while (rs.next()) {
            Product product = new Product();
            product.setId(rs.getInt("id"));
            product.setName(rs.getString("name"));
            productList.add(product);
        }
        //ALWAYS close the resources
        rs.close();
        stmt.close();
        conn.close();
        return productList;
    }
}

You can check for further configurations of the Java web application in a tutorial like mkyong's or Vogella's or any other of your like (it's too much info to place in this answer).

Note that then this application can evolve into a layered application, and the JDBC code will go in a DAO class, and then the ProductRestService class will access to the database through this DAO class. Here's another kickoff example:

public class ProductDao {
    public List<Product> getProducts() {
        List<Product> productList = new ArrayList<>();
        Connection con = ...; //retrieve your database connection
        //the rest of the code explained above...
        return productList;
    }
}

@Path("/product")
public class ProductRestService {
    @GET
    @Path("/list")
    @Produces(MediaType.APPLICATION_JSON)
    public List<Product> getProducts() {
        ProductDao productDao = new ProductDao();
        return productDao.getProducts();
    }
}

And you can apply other changes to this project as well as is evolving.

Can you say me what PHP does here? (if I develop with PHP)

Instead of writing the Service provider application in Java (as shown above), you can do it in PHP. Or in Python, Ruby, C#, Scala or any other programming language that provides this technology to you. Again, I'm not sure what kind of tutorial you're reading, but this should be explained somewhere and explain that for the purposes of that tutorial you will create the services using PHP. If you feel more comfortable writing these services in Java rather than in PHP or any other language, there's no problem. Your android app doesn't really care which technology is used to produce the web services, it will only care about consuming the services and that the data from them can be consumed.

Answer 2

It is possible to do it but not recommended. I have done it before as I was in the same boat as you so I will share some code.

I used this jdbc jar specifically: https://www.dropbox.com/s/wr06rtjqv0q1vgs/mysql-connector-java-3.0.17-ga-bin.jar?dl=0

now for the code:

    package com.example.test.databaseapp;
    import java.sql.DriverManager;
    import java.sql.SQLException;

    import android.app.Activity;
    import android.content.Context;
    import android.os.AsyncTask;

    public class MainActivity extends Activity {
        static final String url = "jdbc:mysql://x.x.x.x:xxxx/DBNAME";
        static final String user = "client";
        static final String pass = "password";
        public static List<objClass> objList;


        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            new Download(MainActivity.this, internalUrl).execute(); //async task for getting data from db
        }
    }

Now for my async task:

public class Download extends AsyncTask<Void, Void, String> {
    ProgressDialog mProgressDialog;
    Context context;
    private String url;

    public Download(Context context, String url) {
        this.context = context;
        this.url = url;
    }

    protected void onPreExecute() {
        mProgressDialog = ProgressDialog.show(context, "",
                "Please wait, getting database...");
    }

    protected String doInBackground(Void... params) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            java.sql.Connection con = DriverManager.getConnection(url, user, pass);
            java.sql.Statement st = con.createStatement();
            java.sql.ResultSet rs = st.executeQuery("select * from table");
            list = new ArrayList<objClass>();

            while (rs.next()) {
                String field= rs.getString("field");
                MainActivity.playerList.add(new objectClass(field));
            }      
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return "Complete";
    }

    protected void onPostExecute(String result) {
        if (result.equals("Complete")) {
            mProgressDialog.dismiss();
        }
    }
}

Make sure to include internet permissions in the manifest. Feel free to ask more questions about my code if you have any.

Answer 3

You can't access a MySQL DB from Android natively. EDIT: Actually you may be able to use JDBC, but it is not recommended (or may not work?) ... see Android JDBC not working: ClassNotFoundException on driver

See

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

http://www.basic4ppc.com/forum/basic4android-getting-started-tutorials/8339-connect-android-mysql-database-tutorial.html

Android cannot connect directly to the database server. Therefore we need to create a simple web service that will pass the requests to the database and will return the response.

http://codeoncloud.blogspot.com/2012/03/android-mysql-client.html

For most [good] users this might be fine. But imagine you get a hacker that gets a hold of your program. I've decompiled my own applications and its scary what I've seen. What if they get your username / password to your database and wreak havoc? Bad.