Introduction
JDBC is a set of classes and interfaces written in Java that allows Java programs to access a database. MySQL has several types of drivers, this how-to is refering to the Connector/J JDBC type 4 thin driver.
Download driver package
At the time of testing, I got a package called mysql-connector-java-5.0.4.zip. I found that latest driver here We can verify the content of the file:
fm@susie:~> /usr/java/jdk1.5.0_09/bin/jar -tf mysql-connector-java-5.0.4.zip mysql-connector-java-5.0.4/ mysql-connector-java-5.0.4/debug/ mysql-connector-java-5.0.4/docs/ mysql-connector-java-5.0.4/docs/release-test-output/ ...
Install the driver package
In order to work with the driver, Java must be able to find it when called. We can achieve this by adding the drivers zip file location to the Java classpath, or by simply placing the driver file into the Java standard directory for extensions, for JAVA JRE this is the $JAVA_HOME/jre/lib/ext (i.e. /usr/lib64/jvm/jre-1.6.0-ibm/lib/ext) directory. We need to copy the extracted mysql-connector-java-5.0.4.jar.
Use the driver to access MySQL through Java
The following example code JdbcTestMySQL.java can be used to quickly access and test the JDBC connection.
vi JdbcTestMySQL.java
//JdbcTestMySQL.java
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.ResultSet ;
import java.sql.Statement ;
import java.sql.SQLException;
class JdbcTestMySQL {
public static void main (String args[]) {
try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e) {
System.err.println (e) ;
System.exit (-1) ;
}
try {
// open connection to database
Connection connection = DriverManager.getConnection(
// "jdbc:mysql://destinationhost/dbname?user=dbuser&password=dbpassword"
// MySQL internal data catalog is in a database called "mysql"
"jdbc:mysql://localhost/mysql?user=root&password=test") ;
// build query, here we use table "User" in db "mysql"
String query = "show variables where variable_name = 'version'";
// execute query
Statement statement = connection.createStatement () ;
ResultSet rs = statement.executeQuery (query) ;
// return query result
while ( rs.next () )
// display content from column "User"
System.out.println ("MySQL Query result: " + rs.getString(2)) ;
connection.close () ;
}
catch (java.sql.SQLException e) {
System.err.println (e) ;
System.exit (-1) ;
}
}
}Compile and run the test program
fm@susie:~> javac JdbcTestMySQL.java fm@susie:~> java JdbcTestMySQL MySQL Query result: 5.0.67
Should this test fail, typical reasons are:
- Wrong database port? MySQL typically uses port tcp/3306
- Wrong database user, or database user does not have admin rights
- Database user exists, but the connection is made from a remote system? Check the correct user access rights, MySQL has tight controls from which IP or hostname users can connect from
Additional Literature: dev.mysql.com