Java Database Connectivity (JDBC) - An Introduction to Network Programming with Java - page 222

Exercises

When attempting the exercises below, you may use whichever type of database is
convenient for you. If you are going to use an MS Access database, it will be
appropriate to implement your solutions only via the DriverManager approach.
If using any other type of database, however, it will probably be instructive to
attempt to produce solutions both via the DriverManager approach and via the
DataSource approach. (When implementing exercises 7.5 and 7.6 via the latter
approach, of course, you should ignore the JTable and GUI references.)

7 . 1 (i) Ensure that either your database system has an accessible JDBC driver
(using the URL supplied in Sect. 7.1 , if necessary) or it has an ODBC
driver (so that you can use the JDBC-ODBC bridge driver).
(ii) Create a database called Sales with a single table called Stock . Give it the
structure shown below and enter a few rows of data for use in the later
exercises. Make sure that there is at least one item for which the current
level is at or below the reorder level.

Field name Type

stockCode Integer

description Text/String

unitPrice Real/Float/Currency

currentLevel Integer

reorderLevel Integer

(iii) If you need to use the JDBC-ODBC bridge driver, then follow the procedure
outlined in Sect. 7.3 to create an ODBC DSN for your database.

7 . 2 (i) Write a program to display the stock code, unit price and current level for
all items in table Stock .
(ii) Modify the above code to display description, current level and reorder
level for all items whose current level is at or below reorder level.

7.3 Take a backup of your database. Then create a program (using SQL strings)
that will insert one record, increase by 10 % the unit price of each item of stock
and delete one record with specifi ed stock code. The program should display
stock codes and prices of all items both before and after the changes.

7.4 Restore your database to its original state by deleting it and then reinstating it
with your backup copy. Modify the preceding program so that, instead of using
SQL strings, it uses Java methods (as described in Sect. 7.10 ) to effect the same
changes. If you are using an MS Access database, you should fi nd that the
insertion and updates don't work (possibly with all prices being changed to
zero!), but the deletion should work fi ne.