Setting up Java and MySQL

Excerpt from Chapter 14 of Get It Done With MySQL, by Peter Brawley and Arthur Fuller


The client side of a Java web application may consist of only HTML and a web browser—a thin client—or may contain compiled Java code in client-side web application modules. Server-side components may reside on any number of servers, often in server plug-ins called containers. One or more servers will host the database. Server software will have to include database drivers and their APIs.

On the client side or tier, the Java platform has two parts, the Java Application Program-ming Interface (API), and the Java Virtual Machine (JVM). From a .java source file, the Java compiler writes a .class file containing executable bytecodes. The JVM executes these bytecode sequences. The existence of a JVM for an operating system makes it possible for any machine running that OS to run any Java program. Whence the slogan, "write once, run everywhere", or as cynics say, "write once, debug everywhere".

Fig 14-1: Some Java Application Models

Server tiers (Fig 14-1) may include web servers, application servers and database servers. Servers deliver Java modules to clients in various forms. When there are multiple database servers, data may be packaged for delivery to clients in Enterprise JavaBeans (EJB), in which case the beans generally reside on a business tier, the databases to which the EJBs relate likely reside in a separate database tier, and the web tier probably hosts small Java programs, Java Server Pages (JSPs) and servlets communicating with web clients, and optional clientside JavaBeans. Servlets and JSPs must run in HTTP plugin servlet containers. Oracle maintains a list of available servlet containers here.

A page received from the web tier can include an embedded applet—a small application written in Java, compiled by the Java compiler, and executing in the client's browser under control from the Web tier. It may also include JavaBeans.

When the client is an application client, it usually includes a graphical user interface (GUI) created from something like Swing or Java's Abstract Window Toolkit (AWT). It may open an HTTP connection to a servlet or JavaServer Page (JSP) in the web tier, or it may directly access JavaBeans in the web tier.

A thin client does not directly execute business rules, query databases, or connect to legacy applications, these jobs being assigned to modules running on a J2EE server.

JDBC, the specification for Java database connectivity, has two parts: 

·        java.sql, eighteen basic interfaces and seven basic classes for accessing and processing data stored in a data source, usually a relational database, and

·        javax.sql : 12 interfaces and two classes extending JDBC from client-side to server-side API, supporting connection pooling, distributed transactions, and an advanced data retrieval and update mechanism called RowSets.

There are four types of JDBC driver, only one of which, type 4, permits a client machine running only a browser and the JVM to call a DBMS directly. Type 4 drivers eliminate  custom client code and middleware. The MySQL Type 4 JDBC driver is Connector/J.

Pieces of the puzzle

To develop MySQL-driven Java web applications, you need to get as many as seven moving (but free) parts working together:

·        the client-side Java bundle, Java 2 Standard Edition (J2SE);

·        the server-side Java bundle, Java 2 Enterprise Edition (J2EE);

·        a web server, for example Apache (*Nix, Solaris, Windows), Internet Information Server (IIS, Windows), Sun Application Server (Linux/Unix, Solaris, Windows);

·        for many web servers including Apache, you need a servlet container, for example Tomcat;

·        the MySQL-Java JDBC driver, Connector/J;

·        for connection pooling in webapps you need either pooling resources from your application server or container, or a freestanding connection pool resource like Jakarta Commons DBCP;

·        for all but the simplest projects, you need an integrated development environment like Eclipse or NetBeans.

The first two, J2SE and J2EE, are available in a free bundle from Oracle. Installation of the third, Apache, is described in Chapter 12. For a reliable cross-platform servlet container, nothing beats the simplicity of Tomcat, which is also a standalone Java web server with an integrated copy of Jakarta Commons DBCP for connection pooling. So you can get away with downloading and installing just three bundles—J2SE/J2EE, Tomcat, and Connector/J.

Will a budget web hosting provider also serve Java applets and servlets? Till recently, the usual minimum webserver configuration for Java has been a Virtual Private Server, though that’s begun to change.

Download and install Java SDKs

The J2SE/J2EE Sun Server bundle is available here. Download the Application Platform and Java Development Kits. In Windows your Java development life will be simpler if you install into a path without spaces in it; otherwise let the installer pick the target folder. Here we refer to the base installation folder as <JAVA_INSTALL_DIR>. The installation may finish with a QuickStart web page which explains how to start the Default Server under both Linux and Windows. Tomcat is a suitable server for this chapter’s examples. After installation, ensure that these environment variables are set:

where VERSION will be something like 1.6.0_05. Ensure that <JAVA_INSTALL_DIR>\bin is in the system path so you can call java and javac from anywhere. Java was invented by UNIX hackers, so *Nix installation is usually as easy as the above suggests. Windows can be another story (see here). On a recent XP installation, these were our settings:

set J2EE_HOME=c:\program files\java

set JAVA_HOME=c:\program files\jdk1.6.0_05

set CLASSPATH=.;c:\program files\java\jre1.6.0_05\lib

Do not count on Windows accurately substituting environment variable contents in these settings. Test everything. When all settings check out, reboot the machine.

Download and install Tomcat

A preconfigured Tomcat package with step-by-step instructions is available here. If you prefer to learn the details, download version 5.5 or later from the Apache Tomcat website, run the installer, and set an environment variable CATALINA_HOME to the root installation path.


Fig.14-2: Tomcat 5.0 Welcome Page


If the Tomcat installer does not automatically copy <JAVA_INSTALL_DIR>/jdk/lib/tools. jar to $CATALINA_HOME/common/lib, do it. Tomcat is also easier to work with if you avoid spaces in folder names.

Once JAVA_HOME and CATALINA_HOME are set correctly, start Tomcat by invoking CATALINA_HOME/bin/ (Linux/UNIX) or .bat. (Windows). Now browse http://localhost:8080; you should see a page resembling Fig 14-2.

If you already have .jar files that will need to be visible both to your web applications and to Tomcat, copy them to $CATALINA_HOME/common/lib. If you have shared Java libraries that need to be visible to your web applications but not to internal Tomcat code, copy them to $CATALINA_HOME/shared/lib/. Add these two entries,



to the machine’s CLASSPATH.

Deploying full-featured web applications using Tomcat requires a tool called Ant. Return to the Apache Jakarta Project binaries page,, click on Ant under ex-Jakarta,  download a recent Ant version, unpack it preserving folder info to a convenient folder, and add <ANT_INSTALL_DIR>/bin to the PATH. Ant documentation is at <ANT_INSTALL_DIR>/docs/index.html.

For Tomcat documentation, browse http://localhost:8080/tomcat-docs if Tomcat is running, otherwise browse the Tomcat documentation entry point as an ordinary HTML file, $CATALINA_HOME/tomcat-docs/index.html.

Download and install Connector/J

Connector/J, is available at Unpack the latest stable version to a convenient folder. Here we refer to the Connector/J installation folder as <CONNJ_INSTALL_DIR>.

To use Connector/J standalone, the safest bet is to add the full path to the Connector/J .jar file to the CLASSPATH environment variable (you’d expect that just copying the .jar file into a folder named in CLASSPATH would suffice, but maddeningly, that often fails). Servlet engines and application servers generally ignore CLASSPATH, so to make Connector/J available to the servlet container …

If you plan to use Sun Application Server, also copy the Connector/J .jar file to <JAVA_ INSTALL_DIR>/domains/ domain1/lib/ext.

If you're troubled by finding no reference documentation in your Connector/J installation, relax. You now have a Java tool for generating your own. On a command line issue …

cd <CONNJ_INSTALL_DIR>/com/mysql/jdbc

javadoc *.java

Browse <CONNJ_INSTALL_DIR>/com/mysql/jdbc/index.html from this directory, and there is your Connector/J reference documentation (Fig 14-3).

Fig.14-3: Connector/J Reference Documentation home page

Install Java IDEs and connect them to MySQL

Dowload NetBeans here and follow installation instructions for your operating system. Run NetBeans, and in the Services window right-click on Databases, then expand the Drivers context submenu, which will include MySQL (Connector/J). Select Connect using…, set Host, Port, Username and Password for your database setup, and (for an example coming up) set Database to nwib, the database set up in Chapter 11.

Download Eclipse from here and unpack it into the installation drive root. For seamless MySQL integration, download the SQLExplorer plugin. Close Eclipse, install SQL Explorer by unpacking it over the Eclipse installation folder, then restart Eclipse and from the menu select Window | Open Perspective | Other | SQLExplorer. Now from the Window menu select Preferences, click on SQL Explorer in the left panel of the window which opens, select JDBC Drivers, and check the driver list in the right panel for a MySQL entry. Edit it if it is there, else add it, setting Example URL to jdbc:mysql://localhost/ test and Driver Class Name to com.mysql.jdbc.Driver. Click OK. For an elegant GUI database design tool, download and install Azzurri Clay Modeler.

Now you have two general purpose IDEs that speak MySQL.

JDBC Models

JDBC offers three models or standards for connecting to databases.

In JDBC 1, all connection and database specifications are in the Java code, and connections are made one at a time.

JDBC 2 introduces the DataSource, which encapsulates connection and database specifications in server-side configuration files, freeing Java code from such specifics. Now programs can distinguish physical and logical connections, so connection pools become possible. These are huge advances, but they leave specification of how to name and find database resources up to database vendors.

JDBC 3 solves this problem by defining a standard J2EE Connector Architecture (JCA) and a lookup mechanism for it, the Java Naming and Directory Interface JNDI). Fig 14-4 shows a few elements of this mechanism. Database connection Java code references a resource specified in the web application’s web.xml. The server/container’s server.xml specifies the details. With JDBC 3, it becomes trivial to change the RDBMS spec in server.xml without changing a line of Java code.

Further reading

“Core Servlets and JavaServer Pages”, ed. 2. Marty Hall, Larry Brown. Sun Microsystems Press/Prentice Hall, 2004.

“Managing the Java classpath”, Elliotte Harold,

 “Professional Tomcat 5”. Vivel Chopra, Amit Bakore, Jon Eaves, Ben Galbraith, Sin Li, Chanoch Wiggers. Wiley Publishing 2004, Indianopolis.

 “Standard: An Implementation of the JavaServer Pages™ Standard Tag Library (JSTL)”,