Eclipse and JDBC Russell Bateman 2 March 2010 last update: 21 November 2011

Home | FAQ | Contact me

Note on source code

You're free to plagiarize this source code all you wish.

Note that the scheme by which I present source code in this article yields a slight deficiency I could not work around. (You'll encounter the problem later several times in this article: I'm warning you up front). It leaves a semi-colon in the CONNECTION_URL. This URL should appear correctly thus:

    private static final String CONNECTION_URL = "jdbc:mysql://localhost/dvdcatalog?user=root&password=test123"

...and will not work correctly if the ';' immediately preceeding "password" remains. So, don't merely copy and paste the code without being aware of this problem.

It isn't the purpose of this tutorial to iron out the challenges of setting up Eclipse, but rather, how to use JDBC from within Eclipse. For discussions on setting up Eclipse, see this link. Please set up Eclipse JEE if you intend to write a web application using a Dynamic Web Project instead of a simple Java Project.

At this point, there are some things you can do that will be made simpler later when you want to use them. In particular, Eclipse DTP will have the MySQL driver in it once you've downloaded and signaled the MySQL JDBC connector. If you want to set up Eclipse DTP, then please see my companion notes to another tutorial on this. You don't have to follow the steps to set up Derby and follow the rest of the tutorial if you don't want to (though it will make using MySQL from DTP easier).

If you choose to create and maintain your database here using MySQL or third-party tools, you can skip this activity. (Please don't be confused by what I've said here. I'm not really making use of DTP in this tutorial. I'm just telling you that it exists and it's potentially useful.)

russ@rhel-32:~> sudo bash                                  # get root
root@rhel-32:~> yum install mysql-server mysql             # install the server
root@rhel-32:~> chkconfig mysqld on                        # configure to come up when host boots
root@rhel-32:~> /etc/init.d/mysqld start                   # kick it off now
root@rhel-32:~> mysqladmin -u root password <new-password> # set the admin password
root@rhel-32:~> mysqld -u root -p                          # test connectivity to MySQL
root@rhel-32:~> exit                                       # unget root

A driver already exists with that name. Please provide a unique driver name.

Create a new Eclipse project

Now let's create a new project in Eclipse. Here, we'll just create a simple Java Project.

1. Right-click in the Package Explorer View and choose New, then Java Project. Let's call the new project, "DvdCatalog". Use the JRE/JDK that you set up when you installed Eclipse. Click Finish.
 
2. Add the JDBC connector as a library for this new project using Build Path. See Setting up the JDBC connector in Eclipse above which contains instructions on setting this up for the first time in your workspace.
 
3. Create a new package, com.etretatlogiciels.dvdcatalog. (As long as you keep your naming schemes consistent, there's no reason you have to keep my names.) Right-click on the project or src icon and choose New -> Package.
 
4. Create a new class, DvdJdbc, in that package. Right-click on the icon of the new package you just created, then choose New -> Class. Be sure to click the public static void main(String[] args) box so it will fill in a main() we'll use for testing the class.
 
5. Type the class code in. You can copy it from here if you wish. (As already noted, there's a spurious semi-colon displayed in CONNECTION_URL here, so beware!)

   DvdJdbc.java:

   package com.etretatlogiciels.dvdcatalog;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   /**
    * JDBC interface for DVD Catalog application.
    *
    * @author Russell Bateman
    */
   public class DvdJdbc
   {
       private static final String CONNECTION_URL = "jdbc:mysql://localhost/dvdcatalog?user=root&password=test123";
   
       public static void main( String[] args )
       {
           try
           {
               Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
   
               Connection conn = DriverManager.getConnection( CONNECTION_URL );
               Statement  stmt = conn.createStatement();
   
               try
               {
                   String query = "INSERT INTO ";
   
                   query += "dvdtitle (title, year, length, rating, url, stars)";
                   query += " VALUES ('";
   
                   for( int i = 0; i < args.length; i++ )
                   {
                       query += args[ i ];
   
                       if( i+1 < args.length )
                           query += "', '";
                   }
   
                   query += "')";
                   System.out.println( "Query: " + query );
                   stmt.executeUpdate( query );
               }
               catch( SQLException e )
               {
                   System.out.println( "SQLException: " + e.getMessage() );
                   System.out.println( "SQLState:     " + e.getSQLState() );
                   System.out.println( "VendorError:  " + e.getErrorCode() );
               }
           }
           catch( Exception e )
           {
               System.out.println( "Holy crap, Batman!" );
               System.out.println( e.getMessage() );
               e.printStackTrace();
               System.out.println( "Remember: mandatory fields include:" );
               System.out.println( "title, year, length, rating, url, stars" );
           }
       }
   }
   			

(Note: the following statement loads the driver class, creates an instance of it, and registers the driver.)

Class.forName( "com.mysql.jdbc.Driver" ).newInstance();

Holy crap, Batman!
Unknown database 'dvdcat'
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'dvdcat'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.Util.getInstance(Util.java:384)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:920)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4000)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1285)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:787))
	at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:49)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
	at java.sql.DriverManager.getConnection(DriverManager.java:582)
	at java.sql.DriverManager.getConnection(DriverManager.java:207)
	at com.etretatlogiciels.dvdcatalog.DvdJdbc.main(DvdJdbc.java:29)
Remember: mandatory fields include:
title, year, length, rating, url, stars

1. Directly write to the database from software (using Java).
 
2. Use the tools that we installed with MySQL.
 
3. Use a popular third-party tool like SQuirreL.
 
4. Install Eclipse Data Tools Platform tools and use Eclipse to manage this process.

In this tutorial, we'll give a very quick nod to doing this from Java. Then, we'll cover doing it using tools that came with MySQL. We won't discuss using a third-party tool, though the process is similar and/or just as easily accomplished in such a utility. Covering Eclipse Data Tools Platform is an interesting discussion, but best left for another tutorial.

A bit out of order, some of the code in this tutorial expects that you've created a new database (in MySQL) named dvdcatalog. If you don't do this up front, some of the examples may fail. Eventually, you are shown how to do this by hand. The following example shows how to get in trouble here: in order to connect to the database, you supply a URL that contains an expectation that dvdcatalog exists, that there is a user root, and that its password is test123. Missing any of this, you'll fail (and, if new at this, you might not immediately realize why).

private static final String CONNECTION_URL = "jdbc:mysql://localhost/dvdcatalog?user=root&password=test123";
.
.
.
Connection conn = DriverManager.getConnection( CONNECTION_URL );

Creating a table from Java

You should create a database and table(s) by hand using the command-line interface or, perhaps, a GUI administrator utility. This is because it would be fairly unusual to do it any other way, though you could.

To do this programmatically, from Java, you must have a connection to an already existing database table. The code for doing this is here. (Again, the caveat for the spurious semi-colon in CONNECTION_URL.)

JdbcCreate.java:

package com.etretatlogiciels.dvdcatalog;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * JDBC interface for DVD Catalog application.
 *
 * Create a new database and a table programmatically, from Java.
 *
 * @author Russell Bateman
 */
public class JdbcCreate
{
    private static final String CONNECTION_URL = "jdbc:mysql://localhost/dvdcatalog?user=root&password=test123";

    public static void main( String[] args )
    {
        try
        {
            Class.forName( "com.mysql.jdbc.Driver" ).newInstance();

            Connection conn = DriverManager.getConnection( CONNECTION_URL );
            Statement  stmt = conn.createStatement();

            try
            {
                System.out.println( "Create a new database..." );
                stmt.executeUpdate( "CREATE DATABASE tryit;" );
            }
            catch( SQLException e )
            {
                System.out.println( "SQLException: " + e.getMessage() );
                System.out.println( "SQLState:     " + e.getSQLState() );
                System.out.println( "VendorError:  " + e.getErrorCode() );
            }

            try
            {
                System.out.println( "Use the new database..." );
                stmt.executeUpdate( "USE tryit;" );
            }
            catch( SQLException e )
            {
                System.out.println( "SQLException: " + e.getMessage() );
                System.out.println( "SQLState:     " + e.getSQLState() );
                System.out.println( "VendorError:  " + e.getErrorCode() );
            }

            try
            {
                String table = "CREATE TABLE try_a_table( "
                             + "id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,"
                             + "year INTEGER(4)"
                             + ");";
                System.out.println( "Create a new table in the new database..." );
                stmt.executeUpdate( table );
            }
            catch( SQLException e )
            {
                System.out.println( "SQLException: " + e.getMessage() );
                System.out.println( "SQLState:     " + e.getSQLState() );
                System.out.println( "VendorError:  " + e.getErrorCode() );
            }
        }
        catch( Exception e )
        {
            System.out.println( "Holy crap, Batman!" );
            System.out.println( e.getMessage() );
            e.printStackTrace();
        }
    }
}

Mostly, we'll just use SQL statements to create a table with schema. SQL is not case sensitive, so you may use case to indicate keywords or just ignore it altogether. I use upper case for keywords in my coded SQL statements, but I don't use it when I'm typing at the command line.

1. Launch the text interface as shown.
 
2. Create a database: create database dvdcatalog;
 
3. Show the databases: show databases; dvdcatalog should appear along with others like mysql and test (and tryit if you ran the code to create a table from Java in the earlier example).
 
4. Create our table:

   
   mysql> create table dvdtitle
       -> (
       -> id integer auto_increment not null primary key,
       -> title varchar(255),
       -> year varchar(4),
       -> length int,
       -> rating int,
       -> url varchar(512),
       -> stars varchar(512)
       -> ); 

 
5. Assuming no errors, show that table: describe dvdtitle.
 
(If you create the table, but then decide later you've made a mistake, just remove it thus: )

mysql> drop table dvdtitle; 

You'll just recreate it the way you want it. If you want, you can create it in a file, create-dvd.sql, ...

CREATE TABLE dvdtitle
	(
			id     INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
			title  VARCHAR(255),
			year   VARCHAR(4),
			length INTEGER,
			rating INTEGER,
			url    VARCHAR(512),
			stars  VARCHAR(512)
	);

...then invoke the statement from the command line here:

mysql> source C:\Users\russ\dev\j2ee\create-dvdtitle.sql
mysql> Query OK, 0 rows affected (0.07 sec) 

The advantage to keeping important statements of schema in SQL scripts like this is that a) it's easier to correct and resubmit the statement and b) it provides a minor level of back-up for re-creating the database.

Query: INSERT INTO dvdtitle (title, year, length, rating, url, stars) VALUES ('')
SQLException: Column count doesn't match value count at row 1
SQLState:     21S01
VendorError:  1136

Statement stmt = conn.createStatement();
.
.
.
stmt.executeUpdate( "INSERT INTO dvdtitle( title, year, length, rating, url, stars ) VALUES( ... );" );

Read data rows (list DVD titles)

Now let's list our two DVD titles. Under the same package, create a new Java source file, DvdList.java.

1. Create a new class, DvdList. Right-click on the package icon, then choose New -> Class. Be sure to click the public static void main(String[] args) box so it will fill in a main() we'll use for testing the class.
 
2. Type the class code in. (Mind the earlier caveat for the spurious semi-colon in CONNECTION_URL.)

   DvdList.java:

   package com.etretatlogiciels.dvdcatalog;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   /**
    * JDBC interface for DVD Catalog application.
    *
    * @author Russell Bateman
    */
   public class DvdList
   {
       private static final String CONNECTION_URL = "jdbc:mysql://localhost/dvdcatalog?user=root&password=test123";
   
       public static void main( String[] args )
       {
           try
           {
               ResultSet result = null;
   
               Connection	conn = DriverManager.getConnection( CONNECTION_URL );
               Statement	stmt = conn.createStatement();
   
               try
               {
                   result = stmt.executeQuery( "SELECT title, year FROM dvdtitle ORDER BY year;" );
               }
               catch( SQLException e )
               {
                   System.out.println( "SQLException: " + e.getMessage() );
                   System.out.println( "SQLState:     " + e.getSQLState() );
                   System.out.println( "VendorError:  " + e.getErrorCode() );
               }
   
               /* Here we can traverse the results and list the movie titles...
                */
               while( result.next() )
               {
                   String    title = result.getString(  "title" );
                   String    year  = result.getString(  "year" );
   
                   System.out.println( year + "   " + title );
               }
           }
           catch( Exception e )
           {
               System.out.println( "Holy crap, Batman!" );
               System.out.println( e.getMessage() );
               e.printStackTrace();
           }
       }
   }
   		

Now run the new Java source in the project by right-clicking on DvdList.java and choosing Run As -> Java Application. You should get something like the following error output in your console window:

1999   The 13th Warrior
2003   The Lord of the Rings: The Return of the King

Now that we've got the two titles and read them back, let's look at some more command-line examples of things to do. These are statements you can code in Java, as part of a larger, more useful program.

(For a much more extensive example of PreparedStatements than the one given here, see http://www.jdbc-tutorial.com/jdbc-prepared-statements.htm.)

With two sample entries entered into our table by hand, we could use Java to do that instead. However, let's learn how to make a change to one of them and, at the same time, learn how to use a prepared statement—which would be very useful in creating rows too. Here's the code. (Mind the earlier caveat for the spurious semi-colon in CONNECTION_URL.)

DoPreparedStatements.java:

package com.etretatlogiciels.dvdcatalog;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * JDBC interface for DVD Catalog application.
 *
 * @author Russell Bateman
 */
public class DoPreparedStatements
{
    private static final String CONNECTION_URL = "jdbc:mysql://localhost/dvdcatalog?user=root&password=test123";

    public static void main( String[] args )
    {
        int    count     = 0;
        String statement = "UPDATE dvdtitle set title = ? WHERE title = ? ;";

        try
        {
            Connection conn = DriverManager.getConnection( CONNECTION_URL );

            try
            {
                PreparedStatement pstmt = conn.prepareStatement( statement );

                pstmt.setString( 1, "The 13th Warrior" );
                pstmt.setString( 2, "The Thirteenth Warrior" );

                System.out.println( "SQL statement: " + pstmt.toString() );

                count = pstmt.executeUpdate();

                if( count == 0 )
                    System.out.println( "There were no rows matching the criteria" );
                else if( count == 1 )
                    System.out.println( "1 row matched the criteria and was updated" );
                else
                    System.out.println( count + " rows matched the criteria and were updated" );
            }
            catch( SQLException e )
            {
                System.out.println( "SQLException: " + e.getMessage() );
                System.out.println( "SQLState:     " + e.getSQLState() );
                System.out.println( "VendorError:  " + e.getErrorCode() );
            }

            System.out.println();

            /* Here we can traverse the results and see what changed...
             */
            try
            {
                ResultSet result = null;

                Class.forName( "com.mysql.jdbc.Driver" ).newInstance();

                Statement    stmt = conn.createStatement();

                try
                {
                    result = stmt.executeQuery( "SELECT title, year FROM dvdtitle ORDER BY year;" );
                }
                catch( SQLException e )
                {
                    System.out.println( "SQLException: " + e.getMessage() );
                    System.out.println( "SQLState:     " + e.getSQLState() );
                    System.out.println( "VendorError:  " + e.getErrorCode() );
                }

                /* Here we can traverse the results and list the movie titles...
                 */
                while( result.next() )
                {
                    String    title = result.getString(  "title" );
                    String    year  = result.getString(  "year" );

                    System.out.println( year + "   " + title );
                }
            }
            catch( Exception e )
            {
                System.out.println( "Holy crap, Batman!" );
                System.out.println( e.getMessage() );
                e.printStackTrace();
            }
        }
        catch( Exception e )
        {
            System.out.println( "Holy crap, Batman!" );
            System.out.println( e.getMessage() );
            e.printStackTrace();
        }
    }
}

What we've done is to replace the Statement of DvdJdbc with PreparedStatement, then use question marks as variables in place of hard title names. Then, we perform variable replacement using some methods inside this class. (We also jettison the use of command-line arguments for gathering data for the rows.)

Instead of running this example as we have others (like DvdList.java), let's debug it to see what's happening. We first need to set a breakpoint so that we can stop in the debugger. Set a breakpoint (in your code) on line 28 below (assuming it's the same line number in your code: change it if it's not).

You do this by double-clicking in the column to the left of the one in which you see the line numbers. Or, you can right-click in that column and choose Toggle Breakpoint. When the breakpoint is set, it appears something like this:

For more on SQL, you could read O'Reilly's Head First SQL, download some pre-made table scripts and/or try some of my Head First, chapters 5-9 notes and scripts.

Or, you can continue reading here to see where you should go in your personal toolstack.

Modern database use in an application falls into two categories. First, persistence, where an application's state is stored over time. Second, substance, the actual business data that gives the application a raison d'être. You have just examined to the ground-floor level of how this is done.

A database may be used to save state (persistance) from one instance of an application to another. This can include anything from the position and size of windows on a desktop to the name of the last client profile under treatment. This information may or may not be deemed worthy of residing in the principal application data, a full featured relational database manager such as provided by MySQL, PostgreSQL, Microsoft SQL, Oracle, etc. In this case, it may be relegated to a separate instance (table) in one of those or to a different, small-scale database like Apache Derby altogether.

Decisions about application data design are made and implemented to benefit the purpose of the application and the setting in which the application is used (web clients, office thick clients, formal, informal uses, etc.). But, in the end, it's just data. Successful exploitation of data is measured for a developer by how easily data can be created, read, manipulated and otherwise maintained.

When table data is used by the developer, it's necessarily promoted to a data construct such as struct in C or a special class in Java. The JDBC example shown here has no such structure and is impractical beyond the purposes of this tutorial. Indeed, the row of data, one per film, suggests structure to any developer. Reaching an organized and useful state requires a great deal of effort. JDBC does nothing to help this. Hence, the existence of great frameworks such as Hibernate.

At the JDBC level, the Java developer has no more tools at his fingertips than the C programmer. He must read a row, then dredge the data out of the row, field by field, type by type and populate the fields in a class instance he creates for this purpose. That's no different than what is done in non-object oriented languages like C.

A framework for doing this such as Hibernate takes over JDBC—already shielding the developer from variations between the SQL spoken by different vendor databases (MySQL versus Microsoft SQL, for instance)—and solves the problem of presenting the data as an instance of a class you prepare for it. There are tutorials to take you beyond today's level.

In the meantime, check for additional bits of information I have added (and will continue to add) in appendices here.

For more MySQL-specific commands, see http://www.pantz.org/software/mysql/mysqlcommands.html.

For a tutorial on writing SQL statements, visit w3Schools.com.

As shown by some of the error (exception) handling in the Java code, you can get some pretty good detail on what's going on wrong. For example, if you got something like:

    SQLException: Column 'interests_id' not found.
    SQLState:     S0022
    VendorError:  0

...you could Google for the SQL state. At Mapping MySQL Error Numbers to SQLStates, you'd find ER_BAD_FIELD_ERROR suggesting, in this case, that the exception occurred as the result of calling ResultSet.getInt( "interests_id" ) though the ResultSet being traversed doesn't have a field by that name in it.

• Companion notes on Setting Up and Using Eclipse Data Tools Platform; mine as notes on a tutorial linked to by Lars Vogel.
• Tutorial on the Eclipse Data Tools Platform.
• Java Persistance API (JPA) by Lars Vogel; this is a non-Hibernate, yet standardized approach.
• sqlzoo.com appears to be a useful site.

Hibernate and Spring...

• Christian Bauer and Gavin King, Java Persistence with Hibernate, Manning Publications, November, 2006, New York. This is the bible.

• Craig Walls, Ryan Beidenbach, Spring in Action, Manning Publications, August, 2007, New York.

• Hibernate home
• SpringSource home
• Hibernate and Spring Tutorial

Appendix: A JOIN example

(Note: I didn't want to leave this tutorial without a reference to two SQL constructs, JOIN and UNION, even if I simply cannot cover every possible feature of SQL.)

Imagine these tables that are part of a scheme to host subscribers to a service that coordinates them with their favorite collegiate teams. (It's a stretch to think such a service would be useful, but it helps create an example. I just saw Social Networking, so I'm thinking about colleges, students, etc.)

t_subscribers
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| name          | varchar(64)  | NO   | PRI | NULL    |                |
| gender        | char(1)      | YES  |     | NULL    |                |
| age_group     | char(1)      | YES  |     | NULL    |                |
| subscriber_id | int(11)      | NO   | UNI | NULL    | auto_increment |
+---------------+--------------+------+-----+---------+----------------+

t_schools
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| school_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name          | varchar(64)  | YES  |     | NULL    |                |
| teamname      | varchar(64)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

t_schoollist
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| school_id     | int(11)      | NO   |     | NULL    |       |
| subscriber_id | int(11)      | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

The intermediate table, t_schoollist, is set up such that any subscriber can elect interest in 0, 1, or more teams.

How to generate a list for a given person of his or her favorite schools? We perform a JOIN of the school_id fields of the school list and schools tables plus of the subscriber_id fields in the subscriber and school list tables. Then using the resulting table we select the name of the school(s) out of the school table where (on that row) the name of the person is the one for whom we're generating the list.

SELECT school.name
   FROM t_schools AS school
   INNER JOIN t_schoollist AS list
      ON list.school_id = school.school_id
   INNER JOIN t_subscribers AS person
      ON person.subscriber_id = list.subscriber_id
   WHERE person.name = 'Russ';

The intermediate table, t_schoollist, contains two schools that interested Russ when he signed up for the service. If Russ is subscriber 1 and Georgia Tech is 103 and Brigham Young University is 79. The filled tables might appear thus:

t_subscribers
+---------+--------+-----------+---------------+
| name    | gender | age_group | subscriber_id |
+---------+--------+-----------+---------------+
| Russ    | m      + 5         |             1 |
| Julene  | f      + 5         |             2 |
| Thayne  | m      + 3         |             3 |
| Allan   | m      + 4         |             4 |
     ...    .        .                     ...
| Richard | m      + 2         |          1039 |
+---------+--------+-----------+---------------+

t_schools
+-----------+--------------------------+--------------+
| school_id | name                     | teamname     |
+-----------+--------------------------+--------------+
        ...                        ...            ...
|        54 | University of Utah       | Running Utes |
        ...                        ...            ...
|        79 | Brigham Young University | Cougars      |
        ...                        ...            ...
|       103 | Georgia Tech             | Bulldogs     |
        ...                        ...            ...
|       248 | Utah Valley University   | Wolverines   |
+-----------+--------------------------+--------------+

t_schoollist
+-----------+---------------+
| school_id | subscriber_id |   Notes:
+-----------+---------------+
|       103 |             1 |   Russ likes school #103 (GT)
|        79 |             1 |   Russ likes school #79 (BYU)
|        79 |             2 |   Julene likes school #79 (BYU)
|        54 |             4 |   Allan likes school #54 (UofU)
        ...            ....     Thayne has no entry(ies) in this table
|       248 |          1039 |   Richard likes school #248 (UVU)
+-----------+---------------+

Note that Thayne's subscriber_id was not put into t_schoollist because he didn't choose a favorite team. (This is obvious only because we're assuming he didn't go back to redo his preferences later and so no team for him is found somewhere in the rows of t_schoollist that are represented by the ellipsis.)

The reason this example is interesting is to illustrate how to implement what would be a variable-length array in Java or C of, in this case, favorite schools in your database table. The output from the JOIN statement for subscriber Russ is:

+--------------------------+
| name                     |
+--------------------------+
| Brigham Young University |
| Georgia Tech             |
+--------------------------+

I would like to draw attention to the observation that a JOIN...ON statement's primary function is to equate associated fields in two different tables.

For more on JOIN see here.

Appendix: A UNION example

It's somewhat more difficult to justify the example of a UNION. It is used to coordinate (conjoin) two SELECTs and is a very special and seldomly operation that only makes sense if:

• both tables have the same number of columns
• both tables are basically identical
• uniting both tables leads to important functionality

To continue the JOIN example above, let's suppose I create, in addition to the concept of an array of school teams a subscriber is interested in, also an array of sports that those schools participate in. If I then have two tables with the same number and types of columns such as you see below, there's little point in using UNION to create anything.

t_sportlist
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| sport_id      | int(11)      | NO   |     | NULL    |       |
| subscriber_id | int(11)      | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

t_schoollist
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| school_id     | int(11)      | NO   |     | NULL    |       |
| subscriber_id | int(11)      | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

mysql> SELECT * FROM t_schoollist UNION SELECT * FROM t_sportlist;

+-----------+---------------+
| school_id | subscriber_id |
+-----------+---------------+
|       103 |             1 |
|        79 |             1 |
|        79 |             1 |
|        54 |             1 |
|         4 |             1 |   4 was a sports_id; it's not meaningful as a school_id
|         4 |             2 |   ibid
|         4 |             3 |   ibid
+-----------+---------------+

This only extends t_schoollist by a fallacious and useless set of rows. MySQL is happy to allow me to do this despite it being a meaningless operation.

However, if, as in the current w3schools quick-reference guide coverage of UNION, we had two tables of employees, one set from the United States and another from Norway, uniting them into a worldwide employee list would make perfect sense. Note that only the first row of any field (column) duplicates will be retained, so additional coumns, if any must be used in order not to lose anything.

t_employees_norway
+------+-------------------+
| e_id | e_name            |
+------+-------------------+
|   01 | Hansen, Ola       |
|   02 | Svendson, Tove    |
|   03 | Svendson, Stephen |
|   04 | Pettersen, Kari   |
    ..                 ...
+------+-------------------+

t_employees_usa
+------+-------------------+
| e_id | e_name            |
+------+-------------------+
|   01 | Turner, Sally     |
|   02 | Kent, Clark       |
|   03 | Svendson, Stephen |
|   04 | Scott, Stephen    |
    ..                 ...
+------+-------------------+

mysql> SELECT e_name FROM t_employees_norway UNION SELECT e_name FROM t_employees_usa;

+--------------------+
| e_name             |   Notes:
+--------------------+
| Hansen, Ola        |
| Svendson, Tove     |
| Svendson, Stephen  |
| Pettersen, Kari    |
                 ...
| Turner, Sally      |
| Kent, Clark        |
| Scott, Stephen     |   the Stephen Svendson from the USA table has been lost (he
                 ...          might be a different person than the one in Norway!)
+--------------------+

So, if there were an additional column in these tables, such as a discrete employee id, an address or telephone number, and we retained that column in the SELEcT statement above, Stephen Svendson would not have been lost from the resulting table.

Appendix: Stored procedures

Stored procedures are used to keep a piece of frequently used SQL statements locally (in the database) for whatever advantage that can be: a reduction in the amount of redundant SQL code on the wire, faster execution thereof, etc. (It isn't my purpose here to educate on this question, but only to show a handy example.)

In order to create a stored procedure, you must change the SQL statement delimiter from semicolon (;) to something else since you'll be needing the normal delimiter inside the procedure code. Not changing it will prevent you from creating the stored procedure. Here, we'll use "//" as our termination delimiter.

Conceptually, ...

DELIMITER //

stored procedure definition...

DELIMITER ;

Here's how to define a simple stored procedure. We'll assume that t_subscribers is a table containing a list of subscribers to a service (names, telephone numbers, other information). We'll define a procedure to get a list of those. This isn't a really good example of a stored procedure in terms of important or compelling functionality, but it's simple and easy to understand. The actual SQL code for the procedure is in blue here:

DELIMITER //

CREATE PROCEDURE GetAllSubscribers()
BEGIN
   SELECT email, telephone FROM t_subscribers;
END //

DELIMITER ;

Then we run the stored procedure to make certain GetAllSubscribers() works. (Remember, SQL isn't case-sensitive and neither does it matter how you write getallsubscribers.) Note the output here from a sample table I already have:

mysql> CALL GetAllSubscribers();
+-------------------------+------------+
| email                   | telephone  |
+-------------------------+------------+
| [email protected]            | 8013710431 |
| [email protected]          | 8013681717 |
| [email protected]       | 8015551213 |
| [email protected]      | 8015551212 |
+-------------------------+------------+

mysql> DELIMITER //
mysql> CREATE PROCEDURE GetAllSports()
    -> BEGIN
    -> SELECT name FROM t_sports;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL GetAllSports();
+------------+
| name       |
+------------+
| Baseball   |
| Basketball |
| Football   |
| Lacrosse   |
| Soccer     |
| Volleyball |
+------------+
6 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

A good tutorial on store statements as well as all other MySQL topics is MySQL Tutorial.

There are good reasons for NOT resorting to stored procedures in SQL. Some are religious, some are very concrete. Make certain you really want to use this feature.

mysql> SHOW CREATE PROCEDURE GetAllSubscribers;
+-------------------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure         | sql_mode | Create Procedure                                                                                         | character_set_client | collation_connection | Database Collation |
+-------------------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| getallsubscribers |          | CREATE DEFINER=`russ`@`localhost` PROCEDURE `getallsubscribers`() begin select * from t_subscribers; end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-------------------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.routines;
+-------------------+-----------------+----------------+-------------------+--------------+----------------+--------------+----------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME     | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME      | ROUTINE_TYPE | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                     | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+-------------------+-----------------+----------------+-------------------+--------------+----------------+--------------+----------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
| GetAllSports      | NULL            | test_tmts      | GetAllSports      | PROCEDURE    | NULL           | SQL          | BEGIN SELECT name FROM t_sports; END   | NULL          | NULL              | SQL             | NO               | CONTAINS SQL    | NULL     | DEFINER       | 2010-11-05 09:56:00 | 2010-11-05 09:56:00 |          |                 | russ@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| GetAllSubscribers | NULL            | test_tmts      | GetAllSubscribers | PROCEDURE    | NULL           | SQL          | begin select * from t_subscribers; end | NULL          | NULL              | SQL             | NO               | CONTAINS SQL    | NULL     | DEFINER       | 2010-11-04 10:59:55 | 2010-11-04 10:59:55 |          |                 | russ@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-------------------+-----------------+----------------+-------------------+--------------+----------------+--------------+----------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

Appendix: MySQL in the real world

I thought it might be interesting for many to understand how, after writing your Java application and creating its database, a commercial host might help you install and run it in a real world setting.

Appendix: Oracle

I'm not too interested in Oracle; it's certainly not affordable. However, I use it in my profession, so I thought I'd add some things I want to remember here. These are commands you do in, for example, SQuirreL. In Oracle, unlike MySQL, there is only "one" database, so you don't "use" a database of more than one.

If you see error codes, for example in SQuirreL, you can visit Oracle Error Codes for a handy look-up and explanation of them.

CREATE TABLE t_partner
(
    oid NUMBER PRIMARY KEY,
    name VARCHAR2(64),
    public_key VARCHAR2(64),
    private_key VARCHAR2(64)
);
CREATE SEQUENCE partner_sequence START WITH 2000000 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER partner_trigger BEFORE INSERT ON t_partner *
    FOR EACH ROW
    BEGIN
        SELECT partner_sequence.nextval INTO :new.oid FROM dual;
    END;
    /

INSERT INTO t_partner ( name, public_key, private_key )
    VALUES ( 'ParrotWorld', 'Polly want a cracker?', 'Chocolate' );

SELECT * FROM t_partner;

DROP TRIGGER partner_trigger;