Courses and Teaching

Dr Andrew Scott G7VAV

MBCS, MIET, Member ACM, Member IEEE

Senior Lecturer
School of Computing
and Communications

D35 InfoLab21
Lancaster University
Lancaster, LA1 4WA
United Kingdom

April 2025
31	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	1	2	3	4
5	6	7	8	9	10	11

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-5.0.67-win32.zip: MySQL installer

Download and unpack Connector/J from http://www.mysql.com/

mysql-connector-java-5.1.7.zip: 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>

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);
         return;
      }

      //
      //   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());
         return;
      }

      //
      //   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

C:\>

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.

Windows

C:\> echo %CLASSPATH%
C:\Program Files\Java\mysql-connector-java-5.1.7\mysql-connector-java-5.1.7-bin.jar;.

C:\>

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
VendorError:0

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.

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.

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 (none)

http://www.andrew-scott.uk/

Andrew Scott

http://www.andrew-scott.co.uk/