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);
        }
    }

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

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();
    }
}

Answer 3

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.