Setting up SQL and Servlets

This page details how to get your servlets that use JDBC to operate in a Linux environment. We assume you are trying to access a MySQL database. Much of the following information is taken from http://www.yolinux.com/TUTORIALS/LinuxTutorialTomcat.html and from Lianqun Cao's excellent write-up on this topic. (The former document also details how you can get Apache and Tomcat to work together. Those of you who are just using Tomcat can skip the section there about that.)

  1. Obtain a MySQL JDBC driver. MySQL.com supports a driver that you can get at http://www.mysql.com/downloads/api-jdbc-stable.html. (There are many different drivers. Each database provides its own. There are, indeed, several different drivers that are compatible with MySQL. You can see a list of all drivers at Sun's site.

First, we need to clarify a few parameters: This demo project uses the following names:
database name: wallstreet
username for mysql: cosc681
password for mysql user "681": password

(Of course, you should feel free to use whatever usernames you want with your MySQL database.)

Follow the following steps:

  1. Determine the structure of your directories.
    The demo resides on a system at which Tomcat has been installed into
    /usr/local/jakarta-tomcat-4.1.24/
    (You don't need delete Tomcat's files and directories, let them coexist with your files.)

    In /usr/local/jakarta-tomcat-4.1.24/webapps/examples/WEB-INF/classes,
    place all of your servlet files (.java and .class)

    In /usr/local/jakarta-tomcat-4.1.24/webapps/examples/servlets,
    place all of your html files (.htm)
    I use the extension '.htm', keep Tomcat's '.html' files

    In /usr/local/jakarta-tomcat-4.1.24/webapps/examples/jsp
    place all of your .jsp files

  2. get jdbc.zip and unzip it. Place the resulting 'org' folder in
    /usr/local/jakarta-tomcat-4.1.24/webapps/examples/WEB-INF/classes
    You will find Driver.java and Driver.class there. Their full pathnames are:
    /usr/local/jakarta-tomcat-4.1.24/webapps/examples/WEB-INF/classes/org/gjt/mm/mysql/Driver.java
    and
    /usr/local/jakarta-tomcat-4.1.24/webapps/examples/WEB-INF/classes/org/gjt/mm/mysql/Driver.class

    This is important because in servlet files we will set parameters according to it.

    If you don't have jdbc.zip, let me know and I will email it to you. (I don't access EMU's email every day!)

  3. In your servlet file, set parameters as following:

    // Create a database driver
    Class.forName("org.gjt.mm.mysql.Driver").newInstance();

    // Create a database connection
    Connection connection = DriverManager.getConnection(
    "jdbc:mysql://localhost.localdomain/wallstreet?user=cosc681&password=password");

    (Note: database name is 'wallstreet'
    username of mysql's user is 'cosc681'
    password of mysql's user is 'password')

    The demo's SQL server has two users: 'root' and 'cosc681'

    In Linux command line, check your hostname like this:
    [root@localhost bin]# hostname
    localhost.localdomain     <----- this tells me my hostname

    If your hostname is different from mine, you'll need to use that hostname in your "jdbc:mysql..." string, above.

  4. Now we need to prepare the MySQL server.
    Log onto MySQL as the 'root' SQL user:
    [root@localhost root]# mysql -u root -p
    Enter password:

    Type 'help' for help.

    mysql>

    Now grant user 'cosc681' permissions to alter and access the contents of our database. This is done via the following set of commands. (Note, again, that you'll need to use your own database name, username and password in the following commands.)

    mysql> grant all privileges on wallstreet.* to cosc681@localhost.localdomain identified by 'password';

    mysql> grant all privileges on wallstreet.* to cosc681@localhost identified by 'password';
    mysql> grant all privileges on wallstreet.* to cosc681@"%" identified by 'password';
    mysql> flush privileges;

    The 1st command grants only to hostname 'localhost.localdomain'. If your hostname is different, you need to change it.

    The 2nd command probably isn't necessary, but may be helpful.

    The 3rd command grants user cosc681 access from anywhere, even if they are not currently logged onto your machine.


Hopefully, the above steps will allow your website to work. If you experience trouble with one file being able to find another, you may need to set other parameters in your .java or .html files. If, for example, when you click a button in your webpage, and the result is a message indicating that a .class file cannot be found, the problem is probably in the file path.

Good Luck