Connecting Java with MySQL on Windows

Sometimes, the hardest thing about learning something new is setting it up. This post describes how to write a simple "Hello World" Java application which connects to MySQL.

First things first - download and install the following:

• Java SE Development Kit (JDK)
• MySQL, or if you're in my case, and you're developing (or plan to develop) PHP applications on Windows - download WAMP

When working with MySQL - it's easiest to use a program which gives a GUI representation of everything. It's not completely necessary, but I suggest one of the following programs for this:

• MySQL Query Browser (free)
• Navicat (not free, but very nice)

Remember that your MySQL server needs to be running, in order to view/manipulate the database with Java. You'll also have to supply your host, username, and password which are usually set by default to localhost, root, ,respectively. Yes, the password field is usually left blank by default. I suggest changing this.

After you have Java installed and your MySQL server up and running, you need to download Connector/J 5.0. This is where most people get confused. What do you do with this? Well, you put it in a safe place on your hard disk. I happened to place it here: "C:\Program Files\Java", as shown in the picture below.

Now, you need to copy "mysql-connector-java-5.1.7-bin.jar" and place it in your JRE's library extension folder, which for me happens to be "C:\Program Files\Java\jdk1.6.0_13\jre\lib\ext", as shown below.

Now, I have heard that you shouldn't put this file in the extension folder, - but I havn't been able to find any other way to make this work. So, if you know - please post in the comments! After you have copied the jar file in your JRE's extension folder, you need to add the driver, "C:\Program Files\Java\mysql-connector-java-5.1.7/mysql-connector-java-5.1.7-bin.jar", to your CLASSPATH. To do this on Vista, open the Start menu, then right-click on Computer and click on Properties.

On the left-pan, click on "Advanced system settings". Make sure the Advanced tab is selected, and then click on the "Environment Variables" button.

This will open up an Environment Variables window, where you'll see an area with CLASSPATH variables. Do not delete anything in here. Simply append an ";" to the end of the list (unless it already ends with an ";") and add the JDBC driver's path ("C:\Program Files\Java\mysql-connector-java-5.1.7\mysql-connector-java-5.1.7-bin.jar" in my case).

After all of this, reboot your computer.

Finally - this is where the fun begins! Start your MySQL server, and then using MySQL Query Browser, or Navicat, create a database named "test". In "test", create a table called "person" and execute the following, by saving it as an sql file then importing it - or simply by running query:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`age` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `person` VALUES ('1', 'Rob', 'Keplin', '22');
INSERT INTO `person` VALUES ('2', 'Phill', 'Kessel', '21');
INSERT INTO `person` VALUES ('3', 'Milan', 'Lucic', '20');
INSERT INTO `person` VALUES ('4', 'Jane', 'Smith', '34');

Make sure that you have a person table in your test database with four entries from the queries above.

Create the following Java class:

import java.sql.*;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.net.*;

public class Test
{

    public static void main(String args[]) throws SQLException {

        Connection con = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");

            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM person");

            while(rs.next()) {
                System.out.println(rs.getString("id"));
                System.out.println(rs.getString("first_name"));
                System.out.println(rs.getString("age"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(con != null) {
                con.close();
            }
        }
    }
}

If everything was successful, it should output the following:

And now, you should have successfully accessed your MySQL database with Java! Let me know if you have any trouble.

Some things worth Noting:

If you get an error like this:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2120)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:723)
	at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
	at java.sql.DriverManager.getConnection(DriverManager.java:582)
	at java.sql.DriverManager.getConnection(DriverManager.java:185)
	at Test.main(Test.java:23)
	at __SHELL0.run(__SHELL0.java:7)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at bluej.runtime.ExecServer$3.run(ExecServer.java:819)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Then it's more than likely that you aren't running your MySQL server.

If you get an error like this:

java.lang.ClassNotFoundException: 
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:169)
	at Test.main(Test.java:22)
	at __SHELL4.run(__SHELL4.java:7)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at bluej.runtime.ExecServer$3.run(ExecServer.java:819)

That means that you more than likely didn't set your CLASSPATH to the JDBC driver, or didn't place a copy of the JDBC driver in your JRE library extension folder.

If you get an error like this:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:911)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3953)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1276)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2048)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:723)
	at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
	at java.sql.DriverManager.getConnection(DriverManager.java:582)
	at java.sql.DriverManager.getConnection(DriverManager.java:185)
	at Test.main(Test.java:23)
	at __SHELL5.run(__SHELL5.java:7)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at bluej.runtime.ExecServer$3.run(ExecServer.java:819)

Then your database name was wrong, or your database wasn't created.

If you get an error like this:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:911)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3953)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1276)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2048)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:723)
	at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
	at java.sql.DriverManager.getConnection(DriverManager.java:582)
	at java.sql.DriverManager.getConnection(DriverManager.java:185)
	at Test.main(Test.java:23)
	at __SHELL6.run(__SHELL6.java:7)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at bluej.runtime.ExecServer$3.run(ExecServer.java:819)

Then, you entered in the wrong database credentials (either the username, password, or both were wrong).