Spring Semester 2003


Lab Notes Twelve: JDBC, Content Syndication Reviewed, XSLT.

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

What you need:

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); 
        }

    }
} 
Here's how you populate the tables with data.

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); 
        }

    }
} 
Here's how you extract data from the database.

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); 
        }

    }
}
We will run these programs and verify their operation from the mysql command prompt.

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

When that works (notice that you need everything.jar) do this:


Last updated: Apr 17, 2003 by Adrian German for A348/A548