Lab 6: JDBC Connections

Objective: In this lab, you will install MySQL and configure and deploy a Web application that connects to a database in MySQL.

1. Start by installing MySQL using the following command. When prompted for a root password, simply use password:

   sudo apt-get install mysql-server

2. Once it is installed, run the mysql client using the following command:

   mysql --user=root -p

   The password is password
3. At the mysql> prompt, enter the following command, which grants a user named tomcat all privileges on MySQL:

   GRANT ALL PRIVILEGES ON *.* TO tomcat@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;

4. Exit the mysql prompt by entering quit;
5. Change directories to the Lab_06 directory.
6. Run the following command, which executes the SQL statements in best.txt on your MySQL instance:

   mysql --user root -p < best.txt

7. Verify the command worked by logging back into the mysql tool and running a SELECT on the inventory table:

   $ mysql --user root -p
                       
   mysql> use best;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Database changed
   mysql> select * from inventory;
   +--------+--------------------------------+------------+---------+----------+
   | number | description                    | category   | price   | quantity |
   +--------+--------------------------------+------------+---------+----------+
   |    431 | Sony VAIO PCV-MSX30            | computers  | 2499.99 |        4 |
   |    236 | Compaq Presario CP-1100        | computers  |    1299 |       29 |
   |    421 | IBM ThinkPad 3200X             | computers  |    1750 |        4 |
   |    422 | IBM ThinkPad 3200XS            | computers  |    1890 |        0 |
   |    949 | U2 - Greatest Hits             | music      |   14.95 |      200 |
   |    766 | The Beatles - White Album      | music      |    19.5 |       43 |
   |    804 | Elvis Presley - The Golden Yea | music      |     9.9 |      152 |
   |    892 | Tom Petty - Wildflowers        | music      |   12.99 |        8 |
   |   6587 | GE Cordless phone              | telephones |      45 |       28 |
   |   6588 | GE Cordless phone w/ headset   | telephones |      59 |       15 |
   |   6590 | Sony HL-50 desktop             | telephones |   79.95 |       21 |
   |   6591 | Sony HL-50S 90MHz desktop      | telephones |   89.95 |       30 |
   +--------+--------------------------------+------------+---------+----------+
   12 rows in set (0.00 sec)
   
   mysql>

8. Quit the mysql client.
9. If your Tomcat instance is running, stop it now.
10. Copy the Lab_06/mysql-connector-java-5.x.x-bin.jar file into the lib folder of your Tomcat instance.
11. Copy the Lab_06/best/ folder into your Tomcat's webapps folder.
12. Within webapps/best/WEB-INF/web.xml, add a <resource-ref> entry that satisifes the following criteria:
    • The <res-ref-name> is jdbc/bestDB
    • The <res-type> is javax.sql.DataSource
    • The <res-auth> is Container
13. Within webapps/best/META-INF, define a <Resource> for the jdbc/bestDB resource reference in web.xml. Set the url to "jdbc:mysql://localhost:3306/best", and the rest of the settings can be determined by reviewing the example from the slides in this chapter.
14. Startup Tomcat again.
15. Go to the index.jsp page of the app at http://your_ip:8080/best/. You should see the following page:
    
16. Click on the Products link. If your data source is configured properly, you should see the items from the inventory table listed on the page:
    
17. Add some items to the shopping cart and play around with the app. Congratulations! You just configured a Tomcat application to connect to a MySQL database.