Using Java to Connect to MySQL Connection from multiple computers-CodePudding

A few classmates and I are creating a Java project which requires a database. I have created a connection in MySQL and connected it to my Java project successfully using the following Connect class:

package com.example.javaworkoutgame.Model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect {

    static Connection con;

    public Connect() {
        connect();
    }

    // attempt to connect to MySQL database
    public static void connect() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver Loaded Successfully");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lab3", "root",
                    "**********"); // not the actual password
            System.out.println("Successful Connection");
        } catch (ClassNotFoundException cnfe) {
            System.err.println(cnfe);
        } catch (SQLException sqle) {
            System.err.println(sqle);
        }
    }

}

This code runs properly on my machine.

I committed and pushed the code to Bitbucket so my partners could access it. However, when they run the code on their computers, they get the following error message:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

Is there something I need to change in MySQL workbench in order for other people to be able to access the database? I could not find any information on this.

The only thing I was able to try was found at this thread:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

I opened a new .sql file and tried running the command:

GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY '%password%' WITH GRANT OPTION; (I replaced '%password%' with the actual password)

When I tried that I got the following error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '*********' WITH GRANT OPTION'

CodePudding user response：

No, and you need to stop this line of thought and do some research first.

Your current configuration says that the mysql server is on the very same physical machine that the code is running on. You installed mysql on your dev machine, your friends need to install it on theirs, and each has their own unique database (nothing is shared).

You could, instead, take your mysql server, open it up to the world (which, for virtually all ways internet is made available in residential connections, requires messing with your router to 'open a port').

But then you have an open mysql server, and the username and password are on a public bitbucket site.

It also requires either a permanent IP (which few residential internet providers offer) or a dyndns service. More generally, hosting open MySQL servers that see lots of traffic gets your internet shut down, for good reason. You'd end up hosting a whole bunch of hackers. All hardware in your network will be p0wned and turned into bot nets. Hence, very very bad idea.

Good ways to solve this problem:

Everybody installs their own MySQL server. This is sensible; you're writing code and bound to make mistakes, it'd be real bad if all code you write is first-run and tested on live data. You don't want one of your friends to wipe your database. If you need some initial data to test with, set it up properly, and read up on how to make an SQL dump. With such a dump file you can reset any mysql server to that exact state - and that'd be how you and your friends develop: Set up the DB to be in that known state, write some code, and if you ruin the db by doing so, no problem. Just reset it again.
Set up a VPN between your friends. NOW you can share the IP your system has within the VPN (it'll be 10., 172.16., 192.168.* - if it's 127.0.0.1, it's localhost, i.e. everybody needs to install mysql on their own and nothing is shared, and if it's anything else, you're opening it to the world, which you don't want to do). Do not put the VPN username/password info anywhere in that bitbucket. And you need to trust your friends.

CodePudding user response：

You should have a properties type file so that each person who is going to interact with the code has their local data without the need to replicate yours, in the same way you can have different values in the properties for test or production environments.

example of a property file:

system.properties

#BD
db.driver=com.mysql.cj.jdbc.Driver
db.user=user
db.pass=password
db.server=server_IP
db.port= port_IP
db.db = DB

Then you should have a procedure to read from java the properties inside the file

Utils.java

package com.example.javaworkoutgame.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public final class Utils {

    public static Properties getProperties() {
        String path = String.format("PATH to your properties FILE/system.properties",
                System.getProperty("user.dir"));
        Properties properties = new Properties();
        try (InputStream is = new FileInputStream(new File(path))) {
            properties.load(is);
        } catch (IOException e) {
            throw new IllegalStateException(e);
        }

        return properties;
    }
}

And finally you make a call to the function that gets the properties from your connection class

Connect.java

package com.example.javaworkoutgame.Model;


import com.example.javaworkoutgame.util.Utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect {

    Properties properties = Utils.getProperties();

    static Connection con;

    public Connect() {
        connect();
    }

    // attempt to connect to MySQL database
    public static void connect() {
        try {
            String driver = properties.getProperty("db.driver");
            String ip = properties.getProperty("db.ip");
            String port = properties.getProperty("db.port"); 
            String db = properties.getProperty("db.db");
            String user = properties.getProperty("db.user");
            String pass = properties.getProperty("db.pass"):

            Class.forName(driver);
            System.out.println("Driver Loaded Successfully");
            con = DriverManager.getConnection("jdbc:mysql://" ip ":" port "/" db, user,
                    pass);             
            System.out.println("Successful Connection");
        } catch (ClassNotFoundException cnfe) {
            System.err.println(cnfe);
        } catch (SQLException sqle) {
            System.err.println(sqle);
        }
    }

}

About the MYSQL error, if your partners do not have a local mysql environment with the same values as you, they will experience the error you describe, since your configuration is a local configuration, if you need your partners to connect to your pc, you must open the ports of mysql and give them your public IP (not recommended)

I hope this answer helps you!