Connecting to MySQL via MySQL Connector/J

Posted at Saturday, February 23, 2008
MySQL comes with a variety of connectors that allows you to access MySQL databases from the programming environment of your choice.

The following is a sample Java class that utilizes the JDBC driver that comes with MySQL Connector/J. Of course this is just a sample class, which you can edit and compile to verify the efficacy of your installation. This does not represent production-ready code.

Download the Connector/J software

MySQL Connector/J Software Downloads 5.1

Gunzip and untar the distribution, extracting the following jar to the location of your choice:

# ls -1 /opt/mysql/*jar
/opt/mysql/mysql-connector-java-5.1.5-bin.jar

As you can see, after the gunzip and untar, I have opted to place the aforementioned jar file in /opt/mysql

Edit the following class, altering the connection information and the query it executes as appropriate:

MySQLJTest.java

import java.io.*;
import java.sql.*;
public class MySQLJTest {
public static void main (String args[]){
Statement stmt = null;
ResultSet rs = null ;
Connection conn = null;
System.out.println("Beginning Test of MySQL Connector J");
System.out.println("Loading Driver...");
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e) {
System.out.println("Exception Loading MySQL Connector J Driver");
e.printStackTrace();
}
System.out.println("Obtaining Connection...");
try {
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3307/mysql?" + "user=oracle&password=password");
} catch (SQLException se) {
System.out.println("SQLException: " + se.getMessage());
System.out.println("VendorError: " + se.getErrorCode());
}
System.out.println("Testing Connection with a query...");
try { stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT User, Host, Password from user");
System.out.println("Processing the ResultSet...");
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\n");
}
} catch (SQLException se) {
System.out.println("SQLException: " + se.getMessage());
System.out.println("VendorError: " + se.getErrorCode());
} finally {
if (rs != null) {
try { rs.close();
} catch (SQLException se) { }
rs = null;
}

if (stmt != null) {
try { stmt.close();
} catch (SQLException se) { }
stmt = null;
}
} //Ends Finally
} //Ends Main Method
} //Ends Class Definition

After editing, you can use the following script to compile and run the program. Since, I am a heavy Oracle user, I have chosen to use the Java JDK that is in the databases' software home - you will want to modify the following script to account for the location of your JDK:

MySQLJTest.bsh

#!/bin/bash

JAVA_HOME=/u03/app/oracle/product/db/11.1.0.6/jdk
CLASSPATH=/opt/mysql/mysql-connector-java-5.1.5-bin.jar:.:$JAVA_HOME/jre/lib:$CLASSPATH

$JAVA_HOME/bin/javac -classpath $CLASSPATH MySQLJTest.java
$JAVA_HOME/jre/bin/java -version
$JAVA_HOME/jre/bin/java -classpath $CLASSPATH MySQLJTest

Once you have edited the scripts as appropriate, you can run the aforementioned script to compile and run the program:

# ./MySQLJTest.bsh
java version "1.5.0_11"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)
Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode)
Beginning Test of MySQL Connector J
Loading Driver...
Obtaining Connection...
Testing Connection with a query...
Processing the ResultSet...
root localhost 5d2e19393cc5ef67

root rac2.colestock.test 5d2e19393cc5ef67

root 127.0.0.1 5d2e19393cc5ef67

oracle rac2.colestock.com 5d2e19393cc5ef67

oracle localhost 5d2e19393cc5ef67

oracle 192.168.1.104 5d2e19393cc5ef67

oracle 127.0.0.1 5d2e19393cc5ef67

If successful, you should have output similar to mine.

Here are the source files from this posting: MySQLJTest.tar


Labels: ,