18.5 Setting Up the music Table

The JDBC examples in this book use the Employees table from the Microsoft Access Northwind database (see Section 17.2) and the custom music table, shown in Table 18.1.

Table 18.1. Music Table

[View full width]

 ID COMPOSER         CONCERTO  AVAILABLE      PRICE --- ---------------- ---------------------  ---------- ----------   1 Mozart           No. 21 in C# minor  7      24.99   2 Beethoven        No. 3 in C minor  28      10.99   3 Beethoven        No. 5 Eb major  33      10.99   4 Rachmaninov      No. 2 in C minor  9      18.99   5 Mozart           No. 24 in C minor  11      21.99   6 Beethoven        No. 4 in G  33      12.99   7 Liszt            No. 1 in Eb major  48      10.99 

The music table summarizes the price and availability of concerto recordings for various classical composers. To create the music table in your database, you can run either of the two programs CreateMusicTable.java or create_music_table.sql , as explained in the following subsections.

Using CreateMusicTable.java to Create the music Table

The Java program CreateMusicTable.java , for creating the music table, is shown in Listing 18.4. Since CreateMusicTable is in the coreservlets package, the file must reside in a subdirectory called coreservlets . Before compiling the file, set the CLASSPATH to include the directory containing the coreservlets directory (see Section 2.7, "Set Up Your Development Environment") and compile the program by running javac CreateMusicTable.java from within the coreservlets subdirectory. However, to create the music table, you must refer to the full package name when executing CreateMusicTable , as shown in the following command,

 Prompt>  java coreservlets.CreateMusicTable   host dbName   username password vendor  

where host is the hostname of the database server, dbName is the name of the database in which to load the table, username and password are those of the user configured to access the database, and vendor is a keyword identifying the vendor driver ( MSACCESS , MYSQL , ORACLE ). Thus, if running MySQL on the local host with a database name of csajsp , you might enter the command

 Prompt>  java coreservlets.CreateMusicTable localhost   CSAJSP brown larry MYSQL  

where brown is the username and larry is the password to access the database.

This program uses two classes from Chapter 17 : DriverUtilities in Listing 17.5 and ConnectionInfoBean in Listing 17.9. DriverUtilities loads the driver information and creates a URL to the database. ConnectionInfoBean stores connection information to a database and can create a database connection. Currently, DriverUtilities supports Microsoft Access, MySQL, and Oracle databases. If using a different database vendor, you must modify DriverUtilities and add your specific vendor information. See Section 17.3 (Simplifying Database Access with JDBC Utilities) for details.

Listing 18.4 CreateMusicTable.java

 package coreservlets; import java.sql.*; import coreservlets.beans.*; /** Create a simple table named "music" in the  *  database specified on the command line. The driver  *  for the database is loaded from the utility class  *  DriverUtilities.  */ public class CreateMusicTable {   public static void main(String[] args) {     if (args.length < 5) {       printUsage();       return;     }     String vendor = args[4];     // Change to DriverUtilities2.loadDrivers() to     // load the drivers from an XML file.     DriverUtilities.loadDrivers();     if (!DriverUtilities.isValidVendor(vendor)) {       printUsage();       return;     }     String driver = DriverUtilities.getDriver(vendor);     String host = args[0];     String dbName = args[1];     String url =       DriverUtilities.makeURL(host, dbName, vendor);     String username = args[2];     String password = args[3];     String format =       "(id INTEGER, composer VARCHAR(16), " +       " concerto VARCHAR(24), available INTEGER, " +       " price FLOAT)";     String[] rows = {       "(1, 'Mozart',      'No. 21 in C# minor',  7, 24.99)",       "(2, 'Beethoven',   'No. 3 in C minor',   28, 10.99)",       "(3, 'Beethoven',   'No. 5 Eb major',     33, 10.99)",       "(4, 'Rachmaninov', 'No. 2 in C minor',    9, 18.99)",       "(5, 'Mozart',      'No. 24 in C minor',  11, 21.99)",       "(6, 'Beethoven',   'No. 4 in G',         33, 12.99)",       "(7, 'Liszt',       'No. 1 in Eb major',  48, 10.99)" };     Connection connection =       ConnectionInfoBean.getConnection(driver, url,                                        username, password);     createTable(connection, "music", format, rows);     try {       connection.close();     } catch(SQLException sqle) {       System.err.println("Problem closing connection: " + sqle);     }   }   /** Build a table with the specified format and rows. */   private static void createTable(Connection connection,                                   String tableName,                                   String tableFormat,                                   String[] tableRows) {     try {       Statement statement = connection.createStatement();       // Drop previous table if it exists, but don't get       // error if not. Thus, the separate try/catch here.       try {         statement.execute("DROP TABLE " + tableName);       } catch(SQLException sqle) {}       String createCommand =         "CREATE TABLE " + tableName + " " + tableFormat;       statement.execute(createCommand);       String insertPrefix =         "INSERT INTO " + tableName + " VALUES";       for(int i=0; i<tableRows.length; i++) {         statement.execute(insertPrefix + tableRows[i]);       }     } catch(SQLException sqle) {       System.err.println("Error creating table: " + sqle);     }   }   private static void printUsage() {      System.out.println("Usage: CreateMusicTable host dbName " +                         "username password vendor.");   } } 

Using create_music_table.sql to Create the music Table

The SQL script, create_music_table.sql , for creating the music table is shown in Listing 18.5. If the database vendor provides a utility to run SQL commands, you can run this script to create the music table.

For a MySQL database, you can run the MySQL monitor and execute the SQL script, as shown.

 mysql>  SOURCE create_music_table.sql  

For details on starting MySQL monitor, see Section 18.2. If the script is not located in the same directory in which you started MySQL monitor, you must specify the full path to the script.

For an Oracle database, you can run SQL*Plus and execute the SQL script by using either of the following two commands.

 SQL>  START create_music_table.sql  

or

 SQL>  @create_music_table.sql  

For details on starting SQL*Plus, see Section 18.3. Again, if the script is not located in the same directory in which you started SQL*Plus, you must specify the full path to the script.

Listing 18.5 create_music_table.sql

 /* SQL script to create music table.  *  * From MySQL monitor run:  *   mysql> SOURCE create_music_table.sql  *  * From Oracle9i SQL*Plus run:  *   SQL> START create_music_table.sql  *  * In both cases, you may need to specify the full  * path to the SQL script.  */ DROP TABLE music; CREATE TABLE music (   id INTEGER,   composer VARCHAR(16),   concerto VARCHAR(24),   available INTEGER,   price FLOAT); INSERT INTO music   VALUES (1, 'Mozart', 'No. 21 in C# minor', 7, 24.99); INSERT INTO music   VALUES (2, 'Beethoven', 'No. 3 in C minor', 28, 10.99); INSERT INTO music   VALUES (3, 'Beethoven', 'No. 5 Eb major', 33, 10.99); INSERT INTO music   VALUES (4, 'Rachmaninov', 'No. 2 in C minor', 9, 18.99); INSERT INTO music   VALUES (5, 'Mozart', 'No. 24 in C minor', 11, 21.99); INSERT INTO music   VALUES (6, 'Beethoven', 'No. 4 in G', 33, 12.99); INSERT INTO music   VALUES (7, 'Liszt', 'No. 1 in Eb major', 48, 10.99); COMMIT;