Fall Semester 2002

The topic for today is: RDBMS and Java. JDBC.

What you need:

• You need a driver: mm.mysql-2.0.2-bin.jar. (I will give it to you).

• You also need to point your CLASSPATH variable to it.

• You need basic knowledge of SQL and mysql.
• You then need to see the following basic examples:

Here's how you connect, and create tables.

import java.sql.*; 

public class CreateTable { 
    public static void main(String[] args) {
        try { 
            Class.forName("org.gjt.mm.mysql.Driver"); 
        } catch (Exception e) { 
            System.out.println("Can't load JDBC Driver. Make sure classpath is correct."); 
        }
        String url = "jdbc:mysql://localhost/a348", 
            username = "a348", 
            password = "a348AG"; 
        Connection connection; 

        try {
            connection = DriverManager.getConnection(url, username, password); 

            Statement statement = connection.createStatement(); 
            ResultSet result; 
            System.out.println("Creating tables..."); 

            statement.executeUpdate(
              " create table dgerman_person ( "                  + 
              "   name      varchar(100) not null primary key, " + 
              "   age       int unsigned not null            , " + 
              "   lives_in  varchar(100) not null              " + 
              " ) " 
            ); 

            statement.close(); 
            connection.close(); 

        } catch (SQLException e) { 
            System.out.println("An SQLException occurred: " + e.getMessage()); 
        } catch (Exception e) {
            System.out.println("Exception: " + e); 
        }

    }
} 

import java.sql.*; 

public class InsertData { 
    public static void main(String[] args) {
        try { 
            Class.forName("org.gjt.mm.mysql.Driver"); 
        } catch (Exception e) { 
            System.out.println("Can't load JDBC Driver. Make sure classpath is correct."); 
        }
        String url = "jdbc:mysql://localhost/a348", 
            username = "a348", 
            password = "a348AG"; 
        Connection connection; 

        try {
            connection = DriverManager.getConnection(url, username, password); 

            Statement statement = connection.createStatement(); 
            ResultSet result; 
            System.out.println("Inserting data..."); 

            statement.executeUpdate(
              " insert into dgerman_person values              " + 
              "   ('David Beckham  ', 24, 'England'),          " + 
              "   ('Roger Milla    ', 25, 'Africa'),           " + 
              "   ('George Weah    ', 24, 'Africa'),           " + 
              "   ('Tony Meola     ', 25, 'USA'   ),           " + 
              "   ('Zinedine Zidane', 25, 'France')             " 

            ); 

            statement.close(); 
            connection.close(); 
            
        } catch (SQLException e) { 
            System.out.println("An SQLException occurred: " + e.getMessage()); 
        } catch (Exception e) {
            System.out.println("Exception: " + e); 
        }

    }
} 

import java.sql.*; 

public class SelectData { 
    public static void main(String[] args) {
        try { 
            Class.forName("org.gjt.mm.mysql.Driver"); 
        } catch (Exception e) { 
            System.out.println("Can't load JDBC Driver. Make sure classpath is correct."); 
        }
        String url = "jdbc:mysql://localhost/a348", 
            username = "a348", 
            password = "a348AG"; 
        Connection connection; 

        try {
            connection = DriverManager.getConnection(url, username, password); 

            Statement statement = connection.createStatement(); 
            ResultSet result; 
            System.out.println("Querying database..."); 

            result = statement.executeQuery(
              " select name, age from dgerman_person    " + 
              "        where lives_in = 'Africa'        "   
            ); 

            while (result.next()) {
                System.out.println(" " + result.getString(1) + 
                                   ", " + result.getString(2)); 
            }

            statement.close(); 
            connection.close(); 

        } catch (SQLException e) { 
            System.out.println("An SQLException occurred: " + e.getMessage()); 
        } catch (Exception e) {
            System.out.println("Exception: " + e); 
        }

    }
}

Next, here are the steps in Lecture Notes Twenty-Six:

(Notice these steps are almost correct, and it's for you to update them).

1. Get xerces.jar from /u/dgerman/public.
   (This is not the only one that would do, and this is not the only way you can get it either).

2. Place it in your $CATALINA_HOME/common/lib/ directory.

3. Add this place to your CLASSPATH environment variable.

   setenv CLASSPATH $CATALINA_HOME/common/lib/xerces.jar:$CLASSPATH

   This has to be done in your ~/.cshrc file.

   Here's how my CLASSPATH variable looks like now:

   burrowww.cs.indiana.edu% echo $CLASSPATH
   /u/dgerman/apache/jakarta-tomcat-3.2.3/lib/xerces.jar:.:/u/dgerman/apache/jakarta-tomcat-3.2.3/lib/servlet.jar
   burrowww.cs.indiana.edu%

   (You know this is an old line, and you don't care. Congratulations.)

4. While you're at it add these two handy shortcuts as well:

   setenv startTomcat $CATALINA_HOME/bin/startup.sh
   setenv stopTomcat  $CATALINA_HOME/bin/shutdown.sh

   This has to be done in your ~/.cshrc file.

   Now you can use $stopTomcat and $startTomcat from the command line.

   (Do it when you recompile and have to reload a servlet.)

5. Create a folder /u/username/CompanyA for your Company A Library filesystem.

6. Add a folder /u/username/CompanyA/data for the Company A 'database'.

7. Add interface.html into your apache_1.3.26/htdocs.

8. Add addBook.pl into your apache_1.3.26/cgi-bin/CompanyA folder.

9. Add a few books, check that /u/username/CompanyA/data/dataFile.txt gets created.

10. Add catalog.pl into your apache_1.3.26/cgi-bin/CompanyA folder.

11. Add DOMreport.pl to the cgi-bin/CompanyA folder.

12. At this stage I recommend you practice with XML a bit, but that's not a requirement.

13. So the next required step is this: place One.java in webapps/CompanyB.

    Come to think of this a whole new context is required.

14. Compile it.

15. Access http://burrowww.cs.indiana.edu:106xx/CompanyB/servlet/One

• Create a new XSLT stylesheet
• and use it with the Company B servlet
• so when the CompanyA books are looked at through the servlet
• the answer is retrieved like this.