SER322 Database Management Systems Lab: JDBC programming and XML

This lab asks you to write simple JDBC programs, and execute an XPath query.

What you will need:

A MySQL server installation

A MySQL JDBC driver

Create a database instance named jdbclab in MySQL

Load the JDBCLabInit.sql into the jdbclab space in MySQL

Activity 1: Construct a JDBC program according to the following specifications:

Write a method named query1 that uses JDBC to execute a SELECT query to List all Emp IDs, Names, together with their Dept names

Use a regular JDBC Statement object for this.

Pretty-print the results to the console (One line per row in readable aligned columns)

This would be invoked on the command line as

java ser322.JdbcLab query1

Write a method named query2 that uses JDBC to execute a SELECT query to List a Dept Name together with the Names of Customers who have purchased a Product made by that Department and the amount the Customer spent on the purchase.

Use a PreparedStatement with a parameter for the DeptNo and filter the query results to only return rows tied to that Dept.

Pretty-print the results to the console (One line per row in readable aligned columns)

This would be invoked on the command line as

java ser322.JdbcLab query2

Write a method named dml1 that uses JDBC to add a new Customer to the database

Use a PreparedStatement with parameters for the 4 values to INSERT

Be sure to commit your results appropriately!

If successful, indicate by printing out SUCCESS

This would be invoked on the command line as

java ser322.JdbcLab dml1

Note: you can run query2 to check on dml1, as the results should change!

Activity 2: XML-ize the database and execute XPath queries

Using the same database instance, do the following:

Modify your JDBC program from Activity 1 to export the entire database to an XML file

This would be invoked on the command line as

java ser322.JdbcLab export

Construct a separate Java program to display the results of an XPath expression on an exported XML file that returns all Product descriptions of Products from a given Dept

This would be invoked on the command line as

java ser322.JdbcLab2

Extra Credit:

For extra credit,

Create an XML schema for your database export, name it JdbcLab2.xsd

Write a standalone program to import an XML file that conforms to the schema to the database.

This would be invoked on the command line as

java ser322.JdbcLabEC

Make sure of the following for this lab:

You never leak database resources!

Fully handle all error situations. By fully we mean give directed feedback as to what is incorrect about the users query, or what the error executing the query is.

Your programs should use the package ser322. Note Activity 2, #2 changes the program name slightly.

Your code should be readable and documented, and exhibit coding practices appropriate for an upper-division programming course in Software Engineering.

The command-line examples above do not show the use of the -classpath (-cp) option to java. We will add this as per our grading environment.

Submission Instructions:

You should submit a zipfile with the following contents:

A readme.txt that explains how to build the source code. Given these are small standalone programs, a simple javac command is fine.

All source code in a subdirectory src followed by package (e.g. src/ser322).

You do not have to include the JDBC driver or your SQL database dump.

Do not include hardcoded paths in any scripts or code.

CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK'); INSERT INTO DEPT VALUES (20,'HOME','DALLAS'); INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO'); INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER, SAL FLOAT, COMM FLOAT, DEPTNO INTEGER NOT NULL, FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), FOREIGN KEY (MGR) REFERENCES EMP(EMPNO), PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, 5000,NULL,10); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,2450,NULL,10); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,1250,1400,30); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,1600,300,30); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,1500,0,30); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,950,NULL,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,1250,500,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,3000,NULL,20); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,800,NULL,20); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,3000,NULL,20); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,1100,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,1300,NULL,10); CREATE TABLE PRODUCT ( PRODID INTEGER, PRICE FLOAT, MADE_BY INTEGER NOT NULL, DESCRIP VARCHAR(30), PRIMARY KEY (PRODID), FOREIGN KEY (MADE_BY) REFERENCES DEPT (DEPTNO)); INSERT INTO PRODUCT VALUES ('60', 39.99, 10, 'ACE TENNIS RACKET I'); INSERT INTO PRODUCT VALUES ('61', 49.99, 10, 'ACE TENNIS RACKET II'); INSERT INTO PRODUCT VALUES ('70', 9.99, 30, 'TENT STAKES'); INSERT INTO PRODUCT VALUES ('71', 24.49, 20, 'BATH TOWELS'); INSERT INTO PRODUCT VALUES ('90', 19.99, 40, 'TENNIS SHIRT'); INSERT INTO PRODUCT VALUES ('80', 70.19, 30, 'HIKING BOOTS'); INSERT INTO PRODUCT VALUES ('63', 8.99, 20, 'WELCOME MAT'); INSERT INTO PRODUCT VALUES ('30', 199.37, 10, 'CALLAWAY IRONS'); INSERT INTO PRODUCT VALUES ('76', 14.99, 30, 'SB ENERGY BAR-6 PACK'); INSERT INTO PRODUCT VALUES ('81', 12.99, 30, 'SB VITA SNACK-6 PACK'); CREATE TABLE CUSTOMER ( CUSTID INTEGER NOT NULL, PID INTEGER NOT NULL, NAME VARCHAR(45), QUANTITY INTEGER, FOREIGN KEY (PID) REFERENCES PRODUCT (PRODID), PRIMARY KEY (CUSTID)); INSERT INTO CUSTOMER VALUES (96711, 60, 'Joe Sith', 3); INSERT INTO CUSTOMER VALUES (94061, 61, 'Luke Moonwalker', 1); INSERT INTO CUSTOMER VALUES (95133, 70, 'Darth Marth', 7); INSERT INTO CUSTOMER VALUES (97544, 71, 'Yoda Yoga', 1);