This tutorial will show you how to connect to a MySQL database using JSP (JavaServer Pages) under Windows and Tomcat web server. If you run into problems or find errors, please let me know so I can fine-tune this document. This document will probably be most useful for those who have done web scripting with MySQL databases before but would like to get started with JSP/Servlets programming. Database connectivity provides a good foundation for learning any new language, as you can practice making real-world applications in a database environment.

Last updated: April 23, 2003

Requirements

• MySQL
  http://www.mysql.com
• Tomcat – version 4.1.12 Standard used for this tutorial
  http://jakarta.apache.org/site/binindex.html
• Java 2 JRE – version 1.4.1 used for this tutorial
  http://java.sun.com/j2se/1.4.1/download.html
• MySQL Connector/J – version 2 used for this tutorial
  http://www.mysql.com/downloads/api-jdbc.html

Assumptions

1. It is assumed that you already have a MySQL database installed and a table to pull data from.
2. It assumes you understand SQL, and probably have done some web/database scripting with other languages.
3. The author uses the folder C:\Tomcat as the folder where Tomcat will be extracted, however, you can place the distribution files anywhere you wish.
4. You know the basics of programming Java. If you do not, I highly recommend you check out Sun’s Java Tutorial.

Section A – Installation

1. Install the Java 2 JRE. I put mine in C:\java\jre, which will be used in this tutorial, but you can put your anywhere you like.
2. Extract the Tomcat distribution files to a folder. The default is jakarta-tomcat-4.1.12, but I chose to put the files in C:\Tomcat.
3. Copy the MySQL Connector JAR file to the C:\Tomcat\common\lib folder (ie, mysql-connector-java-2.0.14.jar).

Section B – Environmental Variables

Add the following environmental variables to Windows:

JAVA_HOME=C:\java\jre
TOMCAT_HOME=C:\Tomcat

You can set environmental variables in Windows 2000/XP by going to:
Righy-click My Computer -> Properties -> Advanced -> Environmental Variables

You can set environmental variables in Windows NT 4 by going to:
Righy-click My Computer -> Properties -> Environment

Section C – Working with Tomcat

To start the server, execute startup.bat. To stop the server, execute shutdown.bat in the C:\Tomcat\bin folder.

By default, the Tomcat web server is access at this URL:

http://localhost:8080/

The root folder of the server is located in:
C:\Tomcat\webapps\ROOT

The root and default port can be changed in this file:
C:\Tomcat\conf\server.xml

Section D – Write Your Code!

You can now start writing your JSP scripts. Save it in a file with a JSP extension and place it in the ROOT folder. I have provided a simple JSP script that demonstrates how to connect to a list data from a MySQL database.

<%@ page import="java.sql.*" %>
<%
   String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
   Connection connection = null;
   Statement statement = null;
   ResultSet rs = null;
   %>

   

    <%
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    connection = DriverManager.getConnection(connectionURL, "", "");
    statement = connection.createStatement();
    rs = statement.executeQuery("SELECT * FROM mytable");

    while (rs.next()) {
       out.println(rs.getString("myfield")+"");
    }

    rs.close();
    %>

Obviously, you will want to change the username and password to match your database.
Also, the mydatabase value in the connectionURL represents the name of the MySQL database.
Change appropriately. Finally, change the mytable and myfield values in the script to match
a table and field that exist within your database. If the public is granted access to the
database, you can use this as your connectionURL:

String connectionURL = "jdbc:mysql://localhost:3306/mydatabase";