Querying an sql database on a remote machine using java code

Question

I am trying to run some queries on a mysql database on a remote machine. I don't have admin access on this remote machine, so I need to authenticate my credentials on this machine before I can access the database (which also needs my username and password authentication). After reading this post (Read remote file in java which needs username and password) I can see how I would use authentication to access the remote machine and I am familiar with using DriverManager to run queries on a local database; I am just not sure how I can combine the two functionalities. Any suggestions?

Answer 1

It looks that the user you are using for connecting with mysql database does not have remote access privileges, so you need to grant remote access to that user.

Execute following queries on mysql server as root user for creating a new user and granting remote access to that user -

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Comment below line in /etc/mysql/my.cnf file -

bind-address = 127.0.0.1

And restart mysql server -

sudo service mysql restart

Answer 2

Use JDBC http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html. How did you run MySQL querys before? In JDBC you access remote machines like the local one (EDIT: in code).

EDIT: The tutorial is a bit rich in information, so here is some code to get you started:

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static Connection co;
private Statement st;

public void connectDb(){
    try{
        String url = "jdbc:mysql://localhost/dataset"; //url to _DATABASE_ create as mysqlroot first!
    String user = "theuser";
    String password = "thepassword";

    Class.forName("com.mysql.jdbc.Driver");
    co = DriverManager.getConnection(url, user, password);
    st = co.createStatement();
    co.setAutoCommit(false);
} catch(Exception e){
    e.printStackTrace();
}
}

You need to get the JDBC driver before you can use this (see tutorial)!

This is how you could run a query:

private ResultSet executeQuery(String query){
    ResultSet rs = null;
    try{
         rs = st.executeQuery(query);
         return rs;
        } catch(Exception e){
            System.out.println(query);
            e.printStackTrace();
        }
    return rs;
}

And this is how an Update could be executed, notice the difference between an update and a query!

private void executeUpdate(String query){
    try{
        st.executeUpdate(query);//data manipulation = executeUpdate
        } catch(Exception e){
            System.out.println(query);
            e.printStackTrace();
        }
}

Hope this helps ^^-d