Can we connect remote MySQL database in Android using JDBC? [closed]

ghz 1years ago ⋅ 6917 views

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 9 years ago.

Improve this 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

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.

#mysql