Set up a user with permissions to connect:
Add a user to your database if you need to, or use root (not recommended) user name and password to connect.
Create
a new user and grant privileges to access database. For my test I'm
giving full access to the user but you'll obviously want to be more
selective on live systems and limit what your user can do with more
restrictive GRANT statement.
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newuser';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'
WITH GRANT OPTION;
You
can test out your user by connecting to your database on command line
or using MySQL workbench to make sure that's working independent of any
Java code.
Download MySQL Java driver
Go to the Oracle download page for the MySQL Java driver.
You will probably need to create a user name and password on Oracle site to access this.
http://dev.mysql.com/downloads/connector/j/
I changed the "select platform" to platform independent and chose zip file.
Expand the zip file.
In the root of the expanded folder find this jar (or something like it if there's a newer version by the time you read this): mysql-connector-java-5.1.27-bin.jar
If you want to you can move this jar to a different location where you store third party jars accessible to your Eclipse projects (a repository, a folder, etc) or just leave it where it is.
Set up Java project in Eclipse and connect to database using driver
Open eclipse and create a new Java project.
Right click on the project name and click properties.
Choose Java Build Path in the left menu of the dialog box.
Click on the Libraries tab.
Click on "Add external jars..." on right side of screen.
Navigate to and select the driver jar we located and/or moved above mysql-connector-java-5.1.27-bin.jar
Click open. Click OK.
Add a new class to your java project that makes the connection to the database. Some people will probably not like my error handling. Whatever. It's a test and will save that argument for later.
This class below assumes you are connecting to "localhost" which is the machine you're typing this all on and running an instance of MySQL on that machine on the default port (3306).
The things you may need to change if connecting to a database elsewhere are the things in red below:
localhost = IP address or domain name of server on which database is located
3306 = port your database is listening on
yourdatabasename = name of the database to which you want to connect
newuser, newuser = user name + password
If you have problems connecting to a remote database might be easier to test the code locally first. If connecting to a database on an EC2 or RDS you'll need to make sure your VPC allows connections from your IP address on the specified port. If a database on EC2 you'll also need to make sure the OS firewall allows this connection. If you don't want to allow connections from the Internet set up a bastion host and upload your Java code to that machine to test the connection.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectToMySql {
public boolean connect() {
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306/yourdatabasename";
con = DriverManager.getConnection(url, "newuser", "newuser");
System.out.println("connection Established");
return true;
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//close the connection in a finally block
//so we don't kill the database with open connections
close(con);
}
}
private void close(Connection con){
try{
if (con != null) con.close();
}catch(Exception e){
//do nothing ...failed to close connection but whatever just testing
}
}
}
Create a unit test to test the connection. Yes, I know unit tests typically don't have database connections and should use mock objects, but this was a handy, simple way to run my code and make sure I can connect to the database.
To get the JUnit jar added to your project the easiest way to create the test is to right click on the class above and select "New" and then "JUnit Test Case". Click the "Next" button, then check the "connect" method in the list. Then click "Finish".
Now alter the class just created to look like this:
import static org.junit.Assert.*;
import org.junit.Test;
public class ConnectToMySqlTest {
@Test
public void testConnect() {
boolean connected = false;
ConnectToMySql c = new ConnectToMySql();
connected = c.connect();
assertTrue(connected);
}
}
Right click on ConnectToMySqlTest and choose "Run As" and "JUnitTest".
If all is well then you should have a green bar that tells you the test ran successfully. If not look at the error message in the output window. If you can't connect make sure you have tested connecting with the user name and password you are trying to test with outside of the Java project to make sure there's not a general problem with the user name, password, firewall, database connection itself.
