Second Summer 2002

Before we forget, the problem for today is:

Implement the following behaviour

• as a standalone Java program and
• as a servlet/JSP available over the web.

burrowww.cs.indiana.edu% ./number
Hi there, are you ready?
I have chosen a number (between 1 and 100). 
You need to guess it in at most 10 tries.
Guess1> 50
Try lower.
Guess2> 25
Try higher.
Guess3> 27
Try higher.
Guess4> 36
Try lower.
Guess5> 30
Try lower.
Guess6> 28
Try higher.
Guess7> 29
Congratulations!You guessed in 7 attempts.
burrowww.cs.indiana.edu% 

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 need basic knowledge of SQL and mysql. (Here's an introduction).

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

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

    }
}