Accessing MySQL Databases with Java Cardiff School of Computer Science & Informatics Documentation & Facilities Toggle navigation Home Documentation Software Services Contact Accessing the MySQL Database with Java MySQL database schemas can be accessed from applications written in Java. MySQL Connector/J is a JDBC driver that allows Java applications to connect to a MySQL database. This Note gives an example of using MySQL Connector/J to connect to MySQL databases in the School. For information about the School's MySQL database server and to learn about your MySQL account and database schema, see MySQL in the School of Computer Science & Informatics Java and JDBC with MySQL Connector/J MySQL Connector/J is provided by MySQL.com and there is full documentation at http://dev.mysql.com/doc/connector-j/en/. Currently, version 5.1.36 is installed on the School's Linux and Windows workstations. The driver is in Jar file mysql-connector-java-5.1.36-bin.jar. Note that the name of the jar file given here is subject to change if a newer version is installed. To use it, you will need to add the full path of the file to your CLASSPATH environment variable, or specify it with the command line switch -cp when starting the JVM. Also, import java.sql.* into your source code. The following example program creates a class called MysqlExample which retrieves and prints the contents of the sample.systems table from the sample database. See the Note about the School's MySQL Sample Database.
import java.sql.*;
// Notice, do not import com.mysql.jdbc.*
// or you will have problems!
public class MysqlExample {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// The newInstance() call is a work around for some
// broken Java implementations
// register MySQL Connector/J
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
System.out.println("Failed to register MySQL Connector/J");
return;
}
try {
conn = DriverManager.getConnection(
"jdbc:mysql://csmysql.cs.cf.ac.uk/sample?user=exampleuser&password=passabc");
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
return;
}
try {
stmt = conn.createStatement();
if( stmt.execute("SELECT * FROM systems")) {
rs = stmt.getResultSet();
}
// Now do something with the ResultSet ....
while (rs.next ())
{
String nameVal = rs.getString ("name");
int osCode = rs.getInt ("oscode");
int ownerCode = rs.getInt ("ownercode");
System.out.println (
"name = " + nameVal
+ ", oscode = " + osCode
+ ", ownercode = " + ownerCode);
}
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
return;
} finally {
// it is a good idea to release
// resources in a finally{} block
// in reverse-order of their creation
// if they are no-longer needed
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) { /* ignore */ }
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) { /* ignore */ }
stmt = null;
}
}
}
}
On Linux Place this code in a file called MysqlExample.java. You can compile it with the javac command in a Linux terminal window, and run it with the java command:
$ javac MysqlExample.java
$ java MysqlExample
name = blue, oscode = 1, ownercode = 2
name = red, oscode = 2, ownercode = 2
name = green, oscode = 3, ownercode = 1
name = brown, oscode = 2, ownercode = 3
It is not necessary on the School's Linux Lab machines to set the classpath. This is done automatically when you log in. You can see what your classpath is with the command
$ echo $CLASSPATH
It should include the value /usr/share/java/mysql-connector-java-5.1.36-bin.jar. Warning: the name of the jar file will change if a different version of MySQL Connector/J is installed. On Windows On Windows, you can use the javac and java command in a Command (CMD) window. But you need to add a -cp argument to set the classpath.
H:\> javac -cp G:\mysql-connector-java-5.1.36-bin.jar;. MysqlExample.java
H:\> java -cp G:\mysql-connector-java-5.1.36-bin.jar;. MysqlExample
name = blue, oscode = 1, ownercode = 2
name = red, oscode = 2, ownercode = 2
name = green, oscode = 3, ownercode = 1
name = brown, oscode = 2, ownercode = 3
Note that the classpath includes the Connector/J JAR file and the current directory (.), separated by a semicolon. You can set the Windows CLASSPATH environment variable so that you don't have to type a -cp argument each time. Start a Command window by typing cmd in the Start menu Search programs and files text field. Use the setx command to set the CLASSPATH variable -
setx CLASSPATH "%CLASSPATH%;G:\mysql-connector-java-5.1.36-bin.jar"
The setx command will have set CLASSPATH for subsequent windows. So exit the cmd window and start another before running javac and java. Warning: the name of the jar file will change if a different version of MySQL Connector/J is installed. Overview Java and JDBC with MySQL Connector/J On Linux On Windows Back to top Tags database java jdbc mysql programming sql Tags database java jdbc mysql programming sql Cardiff University is a member of the Russell Group of Universities Cardiff University is a registered charity No. 1136855 Copyright © Cardiff University Accessibility Feedback Cookies Privacy Policy Terms of Use Welsh Language Scheme
