95-702 Organizational Communications and Distributed Object Technology Homework 5 Due: Friday, August 7, 2009 This project has three parts: First, the student is provided with detailed instructions on building a relational database using Netbeans and Sun's Java DB. The student is also provided with code that acts as a wrapper around a single row of the database. The code makes use of JDBC to interact with the database. The student is required to run the code and see the impact on these data. Second, the student is provided with a new database schema and is required to create a small database with several tables and several constraints on database fields. Third, the student is asked to write a SOAP based web service that may be used by two web service clients to make updates to the data. One client is a JSP page running on Glassfish and the other client is a standalone console based application. If a constraint violation occurs, the web service is written to rollback the local transaction. This is done with the rollback and commit features of JDBC connections. The project may be demonstrated in class and naturally leads to discussions concerning WSDL, interoperability, locks, transaction handling, entity beans and distributed transactions. Part 0. A Little Java DB Tutorial ================================= Build a simple Derby database: 1. Open Netbeans 6.5. 2. Select Services Tab. 3. Expand Databases. 4. Right click Java DB. 5. Choose Create Database. 6. Enter Account, Username, Password. 7. Right click jdbc:derby://localhost... 8. Choose Connect. 9. Expand jdbc:derby://localhost... 10. Right click Table. 11. Choose Create Table. 12. Name the table AccountTable. 13. Enter AccountID (SMALLINT, size 3, unique key and index). 14. Enter CustomerName (VARCHAR size 40). 15. Amount (DOUBLE, size 10, constraint 0 <= Amount and Amount <= 15000). 16. Right Click AccountTable. 17. Click the Insert Record icon. 18. Enter tuples (100, Mary, 14000), (200, Sue, 14900), (20,Sam,300), (30,Ben,500). Write a Java class using JDBC that wraps a row. 1. Select File/New Project/Java/Java Application. 2. The project name is DatabaseDemoProject. 3. Right click project select Java/Java Class. 4. Enter package name databasedemoproject. 5. Name the class Account. 6. Enter Account.java as shown below. 7. Right click project select Java/Java Class. 8. The name is RecordNotFoundException and the package is databasedemoproject. 9. Enter RecordNotFoundException as shown below. 10. Right click DatabaseDemoProject node. 11. Choose Set Configurartion. 12. Choose Customize. 13. Choose Libraries. 14. Choose Add Jar/Folder. 15. Double click Netbeans/glassfish-v3-prelude/javadb/lib/derbyclient.jar. 16. Select OK. 17. Study Account.java. There is a change to the code that you need to make. 18. We are not using the main provided by Netbeans and it may be deleted. 19. Select and then run Account.java. 20. What happens when you run Account.java a second time? Why? Write a Java client that interacts with Account objects. 1. Right click the project node. 2. Select New/Java class/. 3. Name the class DBClient.java. 4. Name the package databasedemoproject. 5. Enter DBClient.java as shown below. 6. Make the same change that you needed to make above. 7. Right click DBClient.java and select Run 8. What did the code do? Can you run the program a second time? Why? Part 1. Simple database development using Netbeans and Java DB. =============================================================== Build a small database using JavaDB and Netbeans. The database will be called Trip and will consist of three relations with the following schemas: HOTEL ID : integer unique key name : string location: string URL : string rooms_avail: integer checked constraint rooms_avail <= 100 and rooms_aval >= 0 CAR ID : integer unique key name : string location: string URL : string cars_avail: integer checked constraint cars_avail <= 10 and cars_avail >= 0 PLANE ID : integer unique key name : string location: string URL : string seats_avail: integer checked constraint seats_avail <= 40 and seats_avail >= 0 Populate the database with the following data: CAR 1 Pittsburgh Rental Pittsburgh PA www.pghrental.com cars_avail = 10 2 LA Rental Los Angeles CA www.larental.com cars_avail = 10 HOTEL 1 Hilton Pittsburgh PA www.pghhilton.com rooms_avail = 100 PLANE 1 QANTAS Sydney Australia www.qantas.com seats_avail = 40 2 American Pittsburgh PA www.aal.com seats_avail = 32 Part 1 Submission: ================== Take a screen shot of each of the three tables as they appear in Netbeans. Part 2. Web Service development using Netbeans. =============================================== Write a web service in Java called BookTripWebService. The web service will run on Glassfish V2 and will make available the following method: public boolean bookTrip(int hotelID, int numRooms, int carID, int numCars, int planeID, int numSeats) { : : } Note that the bookTrip method does not throw an exception. It uses try catch blocks instead. A value of false is returned to the client if an exception is generated within bookTrip. If the booking is a legal one (that is, it does not violate any constraints set in the database) then the database is updated and the method returns true. The update consists of a reduction in the number of seats available, cars available, and rooms available. Otherwise, if the booking violates a database constraint, no change is made to the database and the method returns false. The bookTrip method will be contained in a file called BookTripWebService.java. This class will make good use of three additional classes: Car.java, Hotel.java, and Plane.java. Each of these will be modeled after Account.java that is shown below. Each of the Java classes that you write will have getters and setters as well as the methods create, read, update, and two deletes. Feel free to copy the code from Account.java. But in the end, your Java code will have meaningful names. Do not use those names from Account.java. This project uses JDBC and you will need to use derbyclient.jar. See above to see how to include this jar file. With this jar added to the project you will be able to use the JDBC statements: Class.forName("org.apache.derby.jdbc.ClientDriver"); Connection con = DriverManager.getConnection ("jdbc:derby://localhost:1527/Trip","mm6","sesame"); In order to implement local transactions, your web service method will make good use of JDBC connections, rollback and commit. Part 2. Submission: =================== When you test your web service using the testing capabilities provided by Netbeans, you will be prompted by your browser for the parameters that need to be passed to the bookTrip method. Take a screen shot of this browser view. Also, after the browser visits the web service, input and output SOAP documents are displayed. Take a screen shot of this browser view. In addition, submit to blackboard your web service project and all of its files. Be sure to document the web service itself. That is, BookTripWebServicee.java needs to be well documented. Part 3 A web application that interacts with the web service ============================================================ Write a web service client that allows a user to make calls on the bookTrip method. This web service client will be a JSP based web application. You will need to add an HTML front end to this web application. The browser user will be prompted for values and those values will be used to update the database. From within a JSP page, one can very simply access the parameters passed from an HTML form by using the built in JSP request object. If you prefer, you may use a servlet instead of a JSP page. JSP pages are converted to servlets anyway. Part 3 Submission: ================== Take a screen shot showing the data being entered into an HTML form. Take another one after the web application responds. In addition, submit to blackboard your web service project and all of its files. For this part, no documentation is required. Part 4. A console application that interacts with the web service ================================================================= Write a web service client that allows a user to make calls on the bookTrip method. This web service client will be a command line based application. The user will be prompted for values and those values will be used to update the database. Part 4 Submission: ================== Take a screen shot of the console when it's running. Submit a documented client to blackboard. Notes: ====== Notes from Part 0. ================== /* Account.java */ package databasedemoproject; // Professional Java Server Programming J2EE Edition (modified) // A simple Account object - wraps a row in the Account table. import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.*; import java.util.*; import java.io.*; public class Account { private String accountID; private String customerName; private double amount; public String toString() { return "ID " + accountID + "\t" + "Name " + customerName + "\t" + "Amount " + amount ; } public String getAccountID() { return accountID; } public String getCustomerName() { return customerName; } public void setCustomerName(String c) { customerName = c; } public double getAmount() { return amount; } public void setAmount( double amt) { amount = amt; } public void create(String actID, String customerName, double amt, Connection con) throws SQLException, Exception { accountID = actID; this.customerName = customerName; this.amount = amt; PreparedStatement statement = null; try { statement = con.prepareStatement("Insert into AccountTable (accountID," + "CustomerName, Amount)" + "Values ( ?,?,?)"); statement.setString(1,accountID); statement.setString(2,customerName); statement.setDouble(3,amt); statement.executeUpdate(); } catch(SQLException e) { System.out.println("Caught exception in create" + e); throw new Exception(e); } finally { if(statement != null) { statement.close(); } } } public void read(String accountID, Connection con) throws SQLException, RecordNotFoundException { PreparedStatement statement = null; try { statement = con.prepareStatement("Select customerName, amount FROM AccountTable" +" where accountID = ?"); statement.setString(1,accountID); ResultSet result = statement.executeQuery(); if(result.next()) { this.accountID = accountID; this.customerName = result.getString(1); this.amount = result.getDouble(2); } else { System.out.println("Could not read a record"); throw new RecordNotFoundException(); } } finally { if(statement != null) { statement.close(); } } } public void update(Connection con) throws SQLException { PreparedStatement statement = null; try { statement = con.prepareStatement("Update accountTable set customername = ?," + "amount = ? where accountID = ?"); statement.setString(1, customerName); statement.setDouble(2, amount); statement.setString(3,accountID); statement.executeUpdate(); } finally { if(statement != null) { statement.close(); } } } public void delete(Connection con) throws SQLException { PreparedStatement statement = null; try { statement = con.prepareStatement("Delete from AccountTable Where AccountID = ?"); statement.setString(1,accountID); int h = statement.executeUpdate(); System.out.println("Tried to delete " + accountID + " Changed " + h + " records"); } finally { if (statement != null) { statement.close(); } } } public void delete(String accountID, Connection con) throws SQLException { PreparedStatement statement = null; try { statement = con.prepareStatement("Delete from AccountTable Where AccountID = ?"); statement.setString(1,accountID); statement.executeUpdate(); } finally { if (statement != null) { statement.close(); } } } public static void main(String args[]) throws SQLException, RecordNotFoundException, ClassNotFoundException, Exception { Class.forName("org.apache.derby.jdbc.ClientDriver"); Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/Account","mm6","sesame"); // Test code. After running once the database has data. // That's why a second execution throws an exception. Account personA = new Account(); personA.create("1","Mike McCarthy",100.0,con); Account personB = new Account(); personB.create("2","Sue Smith",45.00,con); ResultSetMetaData rsm = null; String answer = ""; Statement s = con.createStatement(); ResultSet rs = s.executeQuery("select * from AccountTable"); rsm = rs.getMetaData(); try { while(rs.next()) { for(int col = 1; col <= rsm.getColumnCount(); col++) answer += rs.getString(col); } con.close(); } catch (SQLException sqle) { System.err.println("Exception caught in main:" + sqle); } System.out.println(answer); con.close(); } } ========================================================================= package databasedemoproject; /** * * @author mm6 */ public class RecordNotFoundException extends Exception{ public RecordNotFoundException() { } public String toString() { return "Could not find database record"; } } ======================================================================== package databasedemoproject; // This is example is from Java Server Programming J2EE Edition // by Wrox Press Ltd. import java.sql.Connection; import java.sql.DriverManager; public class DBClient { private static final String ACCOUNT1 = "123"; private static final String NAME1= "Cristina Couglin"; private static final double AMOUNT1 = 10000.0; private static final String ACCOUNT2 = "124"; private static final String NAME2= "Mary Klopot"; private static final double AMOUNT2 = 14000.0; private static final String ACCOUNT3 = "125"; private static final String NAME3= "Mike McCarthy"; private static final double AMOUNT3 = 100; private static final double TRANSFER_AMOUNT = 1.00; public static void main(String args[]) throws Exception { Class.forName("org.apache.derby.jdbc.ClientDriver"); Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/Account","mm6","sesame"); // set up three test accounts createAccounts(con); // move some money around from a to b to c to a transfer(ACCOUNT1, ACCOUNT2, TRANSFER_AMOUNT,con); transfer(ACCOUNT2,ACCOUNT3,TRANSFER_AMOUNT,con); transfer(ACCOUNT3,ACCOUNT1,TRANSFER_AMOUNT,con); } private static void createAccounts(Connection con) throws Exception { try { // Create three new accounts after removing the old // versions if any. Account account1 = new Account(); account1.delete(ACCOUNT1, con); account1.create(ACCOUNT1,NAME1,AMOUNT1,con); System.out.println(account1); Account account2 = new Account(); account2.delete(ACCOUNT2, con); account2.create(ACCOUNT2,NAME2,AMOUNT2,con); System.out.println(account2); Account account3 = new Account(); account3.delete(ACCOUNT3, con); account3.create(ACCOUNT3,NAME3,AMOUNT3,con); System.out.println(account3); System.out.println("Acoounts created"); } catch(Exception e) { System.out.println("Exception thrown"); e.printStackTrace(); throw new Exception(e); } } private static void transfer(String accountIDFrom, String accountIDTo, double amount, Connection con) { try { // transfer amount from a to b con.setAutoCommit(false); Account accountFrom = new Account(); accountFrom.read(accountIDFrom,con); Account accountTo = new Account(); accountTo.read(accountIDTo,con); accountFrom.setAmount(accountFrom.getAmount() - amount); accountTo.setAmount(accountTo.getAmount() + amount); accountFrom.update(con); accountTo.update(con); System.out.println("Funds Transfererred"); System.out.println("From account " + accountFrom); System.out.println("To account " + accountTo); con.commit(); } catch(Exception e) { try { System.out.println("Transaction aborted - Rolling back changes."); con.rollback(); } catch(Exception re) { System.out.println("Problem doing rollback. Exception " + re); } e.printStackTrace(); } } } Notes on Part 1. ================ Select Service/Expand Databses/Right click Java DB/Create Database. Select jdbc:derby://localhost:1527/Trip../Right click/Connect. Expand jdbc:derby://localhost:1527/Trip Right Click Tables and build HOTEL, CAR and PLANE Notes on Part 2. ================ An essential reading for this project is found here: http://www.netbeans.org/kb/60/websvc/jax-ws.html To test your web service, right click the project node and choose deploy. Expand the web service node. Your web service will be listed there. Right click your web service and choose Test Web Service. Notes on Part 3. ================ Notes for Part 3 and 4 were compiled from Arun Gupta's screen cast at: http://download.java.net/javaee5/screencasts/hello-simple-client-glassfish Create a new project called DBWSClientWebApp. This client is configured as a web application. On the test web service browser, right click WSDL File, choose Copy Link Location. Right click the client project and select new WEB SERVICE CLIENT. Select WSDL URL toggle and paste the URL. Click finish and client side artifacts are generated. A Web Service Reference is added to our project. Expand this all the way down to a method level description. Drag and drop the method into the generated JSP page. The result is Java code placed inside the JSP.This is is a web application client. Edit the JSP and deploy the project. Visit http://localhost:8080/DBWSClientWebApp. Notes on Part 4. ================ Create a new project called DBWSClientConsoleApp. This client is configured as a Java Application. Right click the project and select new web service client. Paste the WSDL URL as before. The package name is dbwebserviceconsoleappclient. Client artifacts are generated. Expand Web Service References. Drag and drop the web service method into the generated main method. Build and run the Java application.