Dr Andrew Scott G7VAV

My photo
December 2022
Mo Tu We Th Fr Sa Su
28 29 30 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 1
2 3 4 5 6 7 8

Using JDBC
Installing MySQL and Connector/J
The following steps should already have been carried out on the lab machines; however, some installations may not have their CLASSPATH set correctly, so you might want to check this.
If you are sure the installation is ok, skip to the section on Creating a database.
The package version numbers were current as of the last week in October 2008, you should generally go for the latest stable versions.
Download and install MySQL from http://www.mysql.com/
MySQL installer
Download and unpack Connector/J from http://www.mysql.com/
Connector/J JDBC Driver
Connector/J can be extracted anywhere convenient -- on my XP box I chose to unpack it under C:\Program Files\Java\mysql-connector-java-5.1.7
Once unpacked, one of the files must be added to the Java CLASSPATH variable under Linux or Windows.
Set CLASSPATH to include: ...\mysql-connector-java-5.1.7\mysql-connector-java-5.1.7-bin.jar
where ...\mysql-connector-java-5.1.7\ is the location where you unpacked the Connector/J files. You should browse through the directory to ensure that you have the correct filesystem path for the .jar file.
C:\> set CLASSPATH=...\mysql-connector-java-5.1.7-bin.jar;%CLASSPATH%
Note that we append ;%CLASSPATH% to the end. This ensures that any pre-existing CLASSPATH directories are preserved, and will continue to be searched by the Java system. Note the semicolon used as a separator.

Creating a Database
After entering the command to start MySQL, you will be asked for the password matching the username given on the command line -- in this case the 'system administrator' (root). The default system administrator password is normally sysadmin.
C:\> mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> create database csc243;
Query OK, 1 row affected (0.00 sec)
mysql> use csc243;
Database changed

mysql> create table staff (
    -> id int unsigned not null auto_increment,
    -> firstname varchar(16) not null,
    -> lastname varchar(16) not null,
    -> primary key (id)
    -> ) Engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table staff;
| Table   | Create Table                             |
| staff | CREATE TABLE `staff` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `firstname` varchar(16) NOT NULL,
  `lastname` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1               |
1 row in set (0.01 sec)

mysql> insert into staff (firstname, lastname) values ('Andrew', 'Scott');
Query OK, 1 row affected (0.00 sec)

mysql> insert into staff (firstname, lastname) values ('John', 'Mariani');
Query OK, 1 row affected (0.00 sec)

mysql> select * from staff;
| id | firstname | lastname |
|  1 | Andrew    | Scott    |
|  2 | John      | Mariani  |
2 rows in set (0.00 sec)

Java Code: getStaff.java
//   Example for CSC243
//      Andrew Scott, 27 Oct 08

import java.io.*;
import java.sql.*;

class getStaff {
   public static void main ( String args [ ] )
      throws SQLException, IOException
      String dbDriver = "com.mysql.jdbc.Driver";
      String dbDBase  = "csc243";
      String dbUser   = "root";
      String dbPasswd = "sysadmin";
      String dbURL    = "jdbc:mysql://localhost/";

      //   Load Connector/J driver
      try { Class.forName(dbDriver); }
      catch (ClassNotFoundException e) {
         System.out.println (
            "Couldn't locate driver: " + dbDriver);

      //   Contact DBMS (MySQL) and open database
      Connection dbConn = null;
      try {
         dbConn = DriverManager.getConnection
            (dbURL        + dbDBase +
             "?user="     + dbUser +
             "&password=" + dbPasswd);
      } catch (SQLException ex) {
         System.out.println("SQLException: "+ ex.getMessage());
         System.out.println("SQLState: "    + ex.getSQLState());
         System.out.println("VendorError: " + ex.getErrorCode());

      //   Build generic database query
      String queryTemplate =
         "SELECT firstname, lastname FROM staff WHERE lastname = ?";

      //   Prepare a real query -- add search term to generic query
      //   -- set the first ? (parameter 1) in above select statement
      PreparedStatement ps = dbConn.prepareStatement (queryTemplate);
      ps.clearParameters ( );
      ps.setString ( 1"Scott" );

      //   Read result(s) returned from query
      String fname;
      String lname;

      ResultSet rs = ps.executeQuery ( );
      while (rs.next( ) ) {
         fname = rs.getString (1);   // or: fname = rs.getString("firstname");
         lname = rs.getString (2);   // or: lname = rs.getString("lastname");
         System.out.println (fname + " " + lname);

Compiling and Running the Code
Remember that the filename must match the name of the class, in this case getStaff.java and class getStaff { }.
C:\> javac getStaff.java

C:\> java getStaff
Andrew Scott

As you can see, the code produces the name Andrew Scott as output.
This is the result of the prepared select statement: SELECT firstname, lastname FROM staff WHERE lastname = 'Scott'

Error: Couldn't locate driver:
Assuming the driver has been downloaded, the likely cause of this error is an incorrect CLASSPATH variable.
C:\> echo %CLASSPATH%
C:\Program Files\Java\mysql-connector-java-5.1.7\mysql-connector-java-5.1.7-bin.jar;.

If the CLASSPATH variable doesn't include a reference to the mysql-connector-java .jar file similar to the above,
you'll need to set the CLASSPATH by following the set-up instructions at the top of this page.

Error: Communication failure...
The error returned will be something like:
SQLException: Communication failure during handshake. Is there a server running on localhost:3306?
SQLState: null
Ensure that the server is running and that the server is configured to use the same port as the client (library).
The easiest way to do this is to use the command netstat -a ...works from a command prompt on both Unix and Windows.
C:\>netstat -a

Active Connections

  Proto   Local Address     Foreign Address        State
  TCP     myhost:3306       myhost.com:0           LISTENING
Notice the line that includes 3306 indicating that the server is actively listening for new connections.
Unix/ Linux
root@myhost:/# netstat -a
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:mysql         *:*                     LISTEN
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ACC ]     STREAM     LISTENING     11013    /var/run/mysqld/mysqld.sock
On this Linux box the default mysql port number is being used (localhost:mysql) which is ok, and the server is also listening for internal (Unix domain) connections (.../mysqld.sock).
The next step requires admin rights on the machine, which you don't have on lab machines. The service should be running by default on lab machines; if it is not, the easiest solution might be to restart the machine to check that the server hasn't crashed. ...if a restart doesn't bring the service up, please contact the systems team, who should be able to sort the problem out for you.
Assuming you have admin rights for your machine, if the service isn't running/ Started try restarting it:
On Unix there should be a file called something like S20mysql in the /etc/rc2.d directory, try the command /etc/rc2.d/S20mysql start
ON windows go to Start -> Control Panel -> Administative Tools -> Services, select MySQL and start it.
My SQL Service.GIF
MySQL service on Windows
The above does depend a little on your installation so... :)

Error: Table 'csc243.staff' doesn't exist
If you get the following output when you run the code, you have called your database or table different things in MySQL and your code
-- check your typing and make sure they match.
C:\> java getStaff
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorExcep
tion: Table 'csc243.staff' doesn't exist
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.Util.getInstance(Util.java:381)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 1912)
        at getStaff.main(getStaff.java:59)
...and yes, the JDBC calls should all be in try/ catch blocks. :)

for client
© Andrew Scott 2006 - 2022,
All Rights Reserved
Andrew Scott