Java and MySQL in Eclipse

Here's an example how to work with Java and MySQL in Eclipse, in my case on a Ubuntu machine. My original idea was to create an example with Java, Hibernate, Google Web Toolkit and MySQL in Eclipse, but because a lot of errors I decided to split up the project. Maybe later I'll post the intended example.

So, Eclipse, Java and MySQL, it don't sound that difficult, and actually it isn't.

Installing Eclipse
I started downloading Eclipse from http://www.eclipse.org/downloads/ I received the file eclipse-jee-galileo-SR1-linux-gtk.tar.gz
I extracted the file in ~/ so I got a directory ~/eclipse there.

MySQL
I already had MySQL installed. Information on installing MySQL is found over here http://dev.mysql.com/downloads/
I downloaded the MySQL Connector/J connector. It is the official JDBC driver for MySQL. (http://dev.mysql.com/downloads/connector/j/5.1.html)

Sun JDK
Make sure you are using the Sun java 6 JDK. As a Ubuntu user I installed the package sun-java6-jdk from the repository.

Now we have installed everything we need.

Create database
Login to MySQL as root:
$ mysql -u root -p
Create a new database:
mysql> CREATE DATABASE test2;
Open the database:
mysql> connect test2;
Create a new table:
mysql> CREATE TABLE phonebook (id INT(8) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL, number VARCHAR(32) NOT NULL, PRIMARY KEY(id));
The table is a very simple phonebook, containing name and number and a unique id.
Now insert 2 rows for testing:
mysql> INSERT INTO phonebook (name, number) VALUES ('Barry', '555-1234');
mysql> INSERT INTO phonebook (name, number) VALUES ('Bob', '555-5678');
If you type the query SELECT * FROM phonebook; you should see something like this:
mysql> SELECT * FROM phonebook;
+----+-------+----------+
| id | name | number |
+----+-------+----------+
| 1 | Barry | 555-1234 |
| 2 | Bob | 555-5678 |
+----+-------+----------+
2 rows in set (0.00 sec)


Starting Eclipse
First of all, set up Eclipse so it uses the Sun JDK, because the default GNU JDK will produce errors. A nice howto is over here: https://help.ubuntu.com/community/EclipseIDE#Eclipse%20and%20Sun%20Java
It took me a long long time to realise the default VM was the cause of all kind of connection errors. So learn from it.

Now it's time to create a new project. File > New > Project opens a wizard. Just choose Java project and Next. I named the project mysqltest.
Include the downloaded JDBC driver for MySQL. Right-click on the project and select properties. Choose Java Build Path > Libraries > Add External JARs. Include the file mysql-connector-java-.bin.jar from the location you extracted the JDBC driver earlier on.
In the directory src, create a new class Test1. Test1.java can be the following.

package com.example.mysqltst;

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


public class Test1 {
public static void main(String [] args) {
try {
// load the driver
Class.forName("com.mysql.jdbc.Driver").newInstance();

// create a connection to the db
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test2", "root", "password");

// get some results
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM phonebook");
while (rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3));
}

} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
} catch (Exception e) {
System.out.println("Other exception: " + e.getMessage());
}
}
}

Running the code will result on the stdout:

1 Barry 555-1234
2 Bob 555-5678


A useful link to get more in depth information (old, but still good): Writing JDBC Applications with MySQL