Conception of Information Systems 2005

Exercise 2: JDBC & JSP

! Before doing this and the rest of exercises you have to read the practical guidelines and subscribe your group here.

In this exercise you will learn basic concepts of JSP and JDBC. The goal of the exercise is to learn how to work with the databases within the web context. In short, your task will be to create, populate and query a simple database, all this from JSP pages. Because this task involves quite some detail related to writing JSP code and working with the J2EE environment we have provided a demo that will help you to do the exercise more easily. In particular, you will be able to solve the homework problem by reusing and adapting the code from the demo application. In what follows we describe how to work with the demo (Part 1 below) and define the homework problem (Part 2). We are assuming that you have read and understood practical guidelines and thus know the meaning of "X" and "Y" below!

Part 1: The Demo Application

The demo application consists of the following three files: create.jsp.txt, select.html.txt and select-response.jsp.txt. We will first go through the steps needed to deploy the demo.

  1. Connect to your J2EE servers. Use the command  ssh -Y userY@lsir-cis-pcX.epfl.ch (do not replace -Y with 'your Y', it is a flag of the command).
  2. Make a new folder (name it demo) in your working directories and save the above three demo files in it. Do not forget to remove the .txt extension. Edit the files create.jsp and select-response.jsp and replace the occurrences of the string 5009 in them with 500Y (of course, take your Y).
  3. Start your J2EE server (just type in asadmin start-domain domain1 &)
  4. Start the deploytool (type in deploytool &). Click File -> Add Server. In the Server Port field type in 480Y (your Y, for example 4801 for user 01) and check Target Server option. Click OK.
  5. Deploy the mentioned JSP files:
    1. Click File->New->Web component, then check Create New Stand-Alone WAR Module, click Browse, then navigate to your demo directory created at step 2 and type in Demo in the File Name field. Click Create Module File.
    2. Enter /demo in the context root text field, click Edit Contents button. Navigate in the upper window to your demo directory and add the three files. Click OK. Click Next.
    3. Check JSP Page radio button. Click Next. In the JSP Filename drop-down list select /create.jsp and then click Next. Click Finish.
    4. Click Tools -> Deploy, enter username admin, password j2eeadminYY and click OK. You should get a message that the application has been successfully deployed. Click Close.

Now, the demo is accessible through the web. However, before you access it you must start the database server. To do this execute  the command /opt/j2ee0Y/pointbase/tools/serveroption/startserver.sh /port:500Y. You can now access the demo from your web browsers at the address http://lsir-cis-pcX.epfl.ch:800Y/demo/create.jsp. After accessing this page the database studentsDB will be created. It has one table (student) in which two rows have been inserted. Go now to http://lsir-cis-pcX.epfl.ch:800Y/demo/select.html and enter select * from student into the form. Click Submit and you will get a table with the rows inserted. You can always run the pointbase console by typing: /opt/j2ee01/pointbase/tools/serveroption/startconsole.sh &. The database name is StudentsDB, login is pbpublic and password is pbpublic.

Part 2: The Homeworks

Let us now turn our attention to the homework problem. The database you will create (let us give it the name bank) consists of four following tables.

account  (account_id,  type, description, balance);
customer  (customer_id,  name, address);
tx (tx_id, account_id, amount, description);
customer_account (customer_id, account_id);

Their primary keys are account_id, customer_id, tx_id and the pair (customer_id, account_id) respectively. The fields account_id in the tx and customer_account tables refer to account_id in the account table, while customer_id in the customer_account refers to customer_id in the customer table. The types of the fields are arbitrary, you can choose any types you wish. Please refer to the pointbase documentation (see  practical guidelines) for PointBase SQL specifics.

As far as the JSP part of the problem is concerned, we want to have a fully operational application to handle this database. This means:

  1. An index page to access all other pages. You can use this page index
  2. A page to create the database. This must be a JSP page because code to create the database should be executed.
  3. Two pages to insert some records into the database. We recommend writing an HTML page with a form to take the SQL command and a JSP page to handle the request.
  4. Two JSP pages to enter a new transaction. One of them should contain a form with four fields (for tx_id, account_id, amount and description) for specifying a new transaction. The field for account_id should be a drop-down list populated with the account id's already available in the account table so that the above foreign key constraint is not violated.. (This is actually the reason why this page must be JSP instead of HTML). The other page should be a JSP to handle the request.

In total you should produce six pages - two of them can be pure HTML pages (one of them is the index page) and the other four must be JSPs. To see this clearly, just visit the index page.

Your assistant for this exercise is Gleb Skobeltsyn