I have a simple database on my computer for testing purposed, and I'm trying to retrieve some information from the database, from my laptop. So I want my laptop to make a request to see the information inside my computers MySQL database. Below shows the java code I'm trying to run on my laptop to collect the first entry in the students table, which is located on my computer.
I have MySQL workbench installed on both my laptop and computer, is it necessary to be on both machines if the computer will store the data and the laptop only extracts data.
What I've learnt so far from researching is that the public ip should be used in the url instead of the ip for the computer, so I added that in but I received a CommunicationsException along with "Connection timed out" in the stack trace. I've read through this answer and this answer to a similar problem, but I'm having difficulty understanding both solutions, could someone refer me to a beginners guide to remotely accessing data from a database using MySQL.
public class TestRemote{
//JDBC variables
Connection connection;
Statement statement;
ResultSet resultSet;
//String variables
String url;
String user;
String password;
public static void main(String[] args) {
TestRemote sql = new TestRemote();
ArrayList<String> firstnames = sql.getColumn("students", "firstname", "studentid=4");
System.out.println(firstnames.get(0));
}
// Constructor
public TestRemote()
{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("couldnt find class");
}
url = "jdbc:mysql://81.159.3.167:3306/test"; //?autoReconnect=true&useSSL=false";
user = "user";
password = "pass123";
connection = null;
statement = null;
resultSet = null;
}
private void closeConnection(){
try{
if(connection != null)
connection.close();
if(statement != null)
statement.close();
if(resultSet != null)
resultSet.close();
connection=null; resultSet=null; statement=null;
}catch(Exception e){
e.printStackTrace();
}
}
public ArrayList<String> getColumn(String table, String column, String where) {
ArrayList<String> resultsArray = new ArrayList<String>();
try {
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
if(!where.equals(""))
resultSet = statement.executeQuery("SELECT "+column+" FROM "+table + " WHERE "+where);
else
resultSet = statement.executeQuery("SELECT "+column+" FROM "+table);
while(resultSet.next()) {
String val = resultSet.getString(1);
if(val==null)
resultsArray.add("");
else
resultsArray.add(val);
}
//resultsArray = (ArrayList<String>) resultSet.getArray(column);
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Model.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
closeConnection();
return resultsArray;
}
}
