MySQL and JDBC

This describes how I set up a MySql database to talk with JDBC. The MySql database and server were installed on a Windows XP machine. The database was being accessed using JDBC on a remote Trusted Solaris machine running Java 1.2. The following instructions should apply to more configurations than just that though.


- MySql Database and Server -

1. Download MySql Server from http://www.mysql.org. For my installation, I used MySql Server 4.1, which I downloaded from http://www.mysql.org/downloads/mysql/4.1.html.
2. Install MySql Server using all the default options. Afterwards, you can add the MySql bin path to your PATH enviroment variable for convenience if you want.
3. It's time to create your Database. I used the mysql tool, which can be found in the bin folder of the MySql installation. It's a command line tool. Here is an example of session for setting your database up.

1) C://> mysql -u root -p
2) > create database test;
3) > select host,user,password from mysql.user;
4) > GRANT ALL PRIVILEGES ON *.* to 'user'@'%' identified by 'pass';
5) > select host,user,password from mysql.user;

The first line logs you in. The second line creates a database called test. The fourth line grants priviliges to the database. '*.*' means these all privilges will be granted on everything, 'user' is the username, 'pass' is the password, '%' is a wildcard for the host; Altogether, line 4 allows user to access any mysql database from any host using the password pass. You can limit the host to just localhost or a specific IP and you can limit privileges to a specific database. Lines 3 and 5 will allow you to see if your user was set up correctly: you'll see new entries in the table.


- JDBC Access for MySql -

1. Download the MySql JDBC Driver (called Connector/J) from http://www.mysql.org. It was under Downloads, Connectors, Connector/J. Because I needed to use Java 1.2, I needed to download the older Connector/J 3.0. If you're using a newer JVM, you can use a higher version.
2. Extract the archive and add the jar found at the top level to java's classpath. The mysql jar shouldn't have '-g' in its name anywhere.
3. Now you can write your java code. Use the following Driver and Url:
Driver: com.mysql.jdbc.Driver
Url: jdbc:mysql://[host, default localhost]:[port, default 3306]/[database_name]
4. This isn't a JDBC guide, so I'll end here. Happy coding. And good luck.


TroubleShooting

1. Error 'Can not connect to host': You don't have permission to connect to the database from the machine you're using. This probably means you didn't grant permission to your database correctly.
2. "ClassDefNotFound: java/lang/reflect/InvocationHandler": I go this error when I was trying to use the 5.0 Connector/J with Java 1.2. The 3.0 Connector/J is the only version that supports such an old JVM.