Access mysql data base from another system using java

Question

I am working on a interface in java swing.we have four system connected with a lan.the interface is for accessing the database from the other system in the same local area network i used the following code to access the database by giving the ip address,database name,tablename but i could not connect the other systems database.how can i do this?

 public void dbconnection() {
 String name = "";
 String port = "3306";
 String user = "systech";
 String pass = "systech";
 String dbname = "cascade_demo";
 String host="192.168.1.61";
 try {
 Class.forName("com.mysql.jdbc.Driver");
 String url = "jdbc:mysql://"+host+":"+ port + "/" + dbname;
 System.out.println("URL:" + url);
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 Connection con = DriverManager.getConnection(url, user, pass);
 String qry2 = "select * from item_master";
 Statement st = con.createStatement();
 ResultSet rs = st.executeQuery(qry2);
 while (rs.next()) {
 name = rs.getString(1);
 System.out.println("Name:" + name);
 }
 rs.close();
 st.close();
 con.close();
 } catch (Exception e) {
 System.out.println("Exception:" + e);
 }
 }

• KV Prajapati: Remove this statement - Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
• fernandas: Exception:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure Last packet sent to the server was 0 ms ago.
• Fahim Parkar: Download mysql-connector-java-5.1.17-bin.jar
• Perception: Try the following command in a console and post the results here - telnet 192.168.1.61 3306.
• fernandas: Could not open connection to the host,on port 3306:Connection failed

Answer 1

Use below code

public void dbconnection() {
 String name = "";
 String port = "3306";
 String user = "systech";
 String pass = "systech";
 String dbname = "cascade_demo";
 String host="192.168.1.61";
 try {
 String url = "jdbc:mysql://"+host+":"+ port + "/" + dbname;
 Class.forName("com.mysql.jdbc.Driver").newInstance ();
 Connection con = DriverManager.getConnection(url, user, pass);
 String qry2 = "select * from item_master";
 Statement st = con.createStatement();
 ResultSet rs = st.executeQuery(qry2);
 while (rs.next()) {
 System.out.println("Name:" + rs.getString(1));
 }
 rs.close();
 st.close();
 con.close();
 } catch (Exception e) {
 System.out.println("Exception:" + e);
 }
}

Also, make sure to include jar file for connecting. You will get jar file here.

Update 1:

So, you have a

CommunicationsException: Communications link failure

I'm quoting from this answer which also contains a step-by-step MySQL+JDBC tutorial:

If you get a SQLException: Connection refused or Connection timed out or a MySQL specific CommunicationsException: Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:

1. IP address or hostname in JDBC URL is wrong.
2. Hostname in JDBC URL is not recognized by local DNS server.
3. Port number is missing or wrong in JDBC URL.
4. DB server is down.
5. DB server doesn't accept TCP/IP connections.
6. DB server has run out of connections.
7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.
   
   

To solve the one or the other, follow the following advices:

1. Verify and test them with ping.
2. Refresh DNS or use IP address in JDBC URL instead.
3. Verify it based on my.cnf of MySQL DB.
4. Start the DB.
5. Verify if mysqld is started without the --skip-networking option.
6. Restart the DB and fix your code accordingly that it closes connections in finally.
7. Disable firewall and/or configure firewall/proxy to allow/forward the port.
   
   

Update 2

1. If your system is Windows, go to Start>>Run.
2. Type command. This will open command prompt.
3. Type "ping 192.168.1.61"
4. You might get reply in below format.

Pinging 192.168.1.61 [192.168.1.61] with 32 bytes of data:

Reply from 192.168.1.61: bytes=32 time=101ms TTL=124

If you don't get something in above format, then your MYSQL Server with ip 192.168.1.61 is NOT REACHABLE. Ask your team to start the server first. :(

If you have Linux version, open terminal and follow step 3.

Also check below link. Those might help you...

1. http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

Answer 2

You should down load the jdbc driver and replace

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

by

Class.forName("com.mysql.jdbc.Driver");

If you still have issues asfter replacing Obdc stuff, please post the exception. ALso check firewall settings and DB permissions.

• fernandas: still am getting the same exception
• stacker: Can you ping the host 192.168.1.61 ? Try telnet host 3306, and use netstat to make sure mysql is in state LISTEN on port 3306 on your host.
• fernandas: when i try telnet host 3306 it gives could not open connection to the host on 3306:connection failed.then when i use netstat in that list 3306 was not present.but when i give netstat -an which shows 3306 and that state was in Listening.
• stacker: @fernandas It seems to be a firewall issue since you can't event connect with telnet to the servers ports. To make sure your programm works you could run it on the db host (if possible). Check also whether the server listens only on localhost or all network interfaces. good luck!

Answer 3

If you'll get exception your app will not free system resources. This will work better:

} finally {
 try { 
 rs.close();
 st.close();
 con.close();
 } catch( Exception e ) {
 e.printStackTrace();
 }
}