ITP 499 Designing B2B E-Commerce
Project X
JSP/XML Dynamic Content Generation
Objective:
The objective of this lab is to understand how to do Dynamic Web Content Generation using Java Server Page, JDBC (Java Database Connectivity), XSL, and XML formatted Database.
General Procedure:
- Create Tables in your database.
- Create a form that accepts inputs, such as query string, jdbc driver path, and etc.
- Setup database connection using jdbc
- Send query to database.
- Obtain query result using ResultSet.
- Output result using implicit JSP “out” object. Implicit means you do not need to declare “out” object before you use it.
Some background knowledge about JSP
JSP (Java Server Page) enables you to embed java code inside HTML file or in our case XML file. The JSP engine translates your JSP file into a servlet automatically for you, so you do not need to write your own.
Basic JSP Syntax
<% Your java code %>
<%! Declarations %>
<%-- Comments --%>
JSP Objects and their methods
out – You use this to send output to your client (web browser)
out.println(“ whatever you want to output to client“);
request (HttpServletResponse) – You use this to get parameters from your HTML form.
request.getParameter(“textfield name”);
response (HttpServletRequest) – You use this to set the content type of your result page, for example, if the result page is xml page, you do the following:
response.setContentType("text/xml");
SQL Objects
Connection – Database connection object
DriverManager – Class responsible for selecting database drivers and makes new connection.
Statement- You use this class to execute SQL statements
ResultSet – You use this class to store the result of your query
JSP Setup
Step 1.
Download the latest JDK and JSWDK from either Javasoft or just get them from your instructor.
Step 2.
Install JDK and extract JSWDK to your local drive.
Step 3.
Add the following to your autoexec.bat:
SET PATH=c:\jdk1.3\bin;
SET CLASSPATH = c:\jdk1.3\lib c:\jswdk-1.0.1\lib\jspengine.jar;c:\jswdk-1.0.1\lib\servlet.jar; c:\jdk1.3\lib\tools.jar
Step 4.
Go to c:\jswdk-1.0.1 and edit startserver.bat
Locate “set JAVA_HOME=”, add “C:\jdk1.3” after the equal sign. (note. Do not include quotation mark!!!)
Locate “set CLASSPATH=%CLASSPATH%;%cp%;”, append “C:\jdk1.3\lib\tools.jar” to it.
Step5
Reboot System
Step 6
After you have restarted your system, go to c:\jswdk-1.0.1 and double click on startserver.bat.
A MS-Dos window will pop up showing the following:
JSWDK WebServer Version 1.0.1
Loaded configuration from: file:C:\jswdk-1.0.1\webserver.xml
endpoint created: localhost/127.0.0.1:8080
If you don’t see “exactly” the same message, you might have problems with your setup
Step 7
After you started the server, open up IE5. For the url, type
If you see the following, you are ready to start coding JSP!
JSP Expressions
- Current time: Tue Nov 07 22:04:26 PST 2000
- Your hostname: 127.0.0.1
- Your session ID: To1010mC9438243272241057At
- The testParam form parameter: null
Step 8
Double click on stopserver when you are done.
MS Access
We will be using MS Access’s Northwind Database, since jdbc driver for MS SQL server is unavailable.
*** Before you can test your jsp file, you must install MS Access and configure ODBC correctly, otherwise your jsp file won’t work! ***
*** Do not submit query to database while you are editing your Table, you will get errors ***
*** If you insert new stuff into your database, do not try to insert the same thing again, because you can’t update your database with duplicates***
Project X
All your jsp files should be placed in “c:\jswdk-1.0.1\webpages”, you can then access the page by typing as url, after you started the server.
You will need a form for entering query to the database, the form will be provided by your instructor.
The actual JSP file won’t be available, you have to create it, and name it “XQuery.jsp”.
General Procedure
- Create a form that accept inputs
- Create JSP file to connect to the database and display result using XSL.
- Create a style sheet for output
Code Walkthrough
<%!
////// Variable Declarations /////////////////
String dbName; // name of your database
String startRootTag; // this var stores start root tag for your xml file
String endRootTag; // this var stores end root tag for your xml file
String startTableTag;
String endTableTag;
String tableName="";
////////////////////
String queryString;
String username;
String password;
String dbDriver;
String QueryFieldName=null; // we need to store the query field name for later use
String command=null; // we need to know what command we are dealing with
String dbURL;
ResultSet rs;
%>
In this code fragment, we declare variables that we will be using in our JSP file, for instance, we’ll need some string variables to store inputs from the HTML for such as database name, query string, and etc.
------
<%-- Database Connection Function Declaration--%>
<%!
public static Connection getConnection(String dbDriver, String dbURL,
String username, String password )
throws SQLException, IOException, Exception
{
Class.forName(dbDriver); // load jdbc/odbc bridge driver
// DriverManager returns a connection object that we can use to
// send query to database
Connection con = DriverManager.getConnection(dbURL,
username, password);
return con;
}
%>
This code fragment is the “core” fragment of our JSP, because it deals with database connection. We start by loading the driver with this call “Class.forName(dbDriver);”.
The next step is to obtain a Connection object from DriverManager by passing in driver name, database url, user name, and password to this function :DriverManager.getConnection(dbURL,
username, password);
(Note. You do not need password and user name if you are using MS Access).
------
<%-- Main Java Code--%>
<%
// Get inputs from user's HTML form
username = request.getParameter("user_name");
password = request.getParameter("password");
// For the driver, we'll use JDBC-ODBC Bridge
dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
dbURL= "jdbc:odbc:"+dbName;
// this line calls the "getConnection" method we declared above
Connection con = getConnection(dbDriver, dbURL, username, password);
// this line creates a statement object for sending query
Statement stmt = con.createStatement();
if (queryString != null)
{
StringTokenizer st = new StringTokenizer(queryString);
command=st.nextToken();
command=command.toUpperCase();
if (command.equals("SELECT"))
{
QueryFieldName=st.nextToken();
// if we are dealing with SELECT statement
// we need to use executeQuery
st.nextToken();
tableName = st.nextToken();
rs = stmt.executeQuery(queryString);
}
else
{
// if we are updating database, ie. inserting
// new stuff, we need to use executeUpdate
stmt.executeUpdate(queryString);
out.println("Insertion Successful");
}
}
// set the content type to xml
response.setContentType("text/xml");
// if the query result is found
if(rs!=null)
{
// while you still have results in your resultset object
// store the results into a string one at time and outputs
// them to the browser
while (rs.next())
{
String s = rs.getString(QueryFieldName);
out.println(s);
}
}
%>
The last piece of code is the main part of the code. We start by getting information from the html with this call:request.getParameter() and store the results in variables. The next step is to get database connection by calling getConnection(dbDriver, dbURL, username, password), which we declared earlier. Now we need a way to send query string to query our database, so we need to create a statement object by calling: Statement stmt = con.createStatement(). Con is the connection object we obtained by earlier by calling getConnection function. Next, we need to process the query string because we can different query statements in our query strings such as SELECT, INSERT, UPDATE, and etc. We process the query string by breaking up string into tokens, and look for specific string. If we see a SELECT statement we need to call:
rs = stmt.executeQuery(queryString). Note that rs is a ResultSet object that stores the result of the query. Finally, we can use the following to output the result of our query:
while (rs.next())
{
String s = rs.getString(QueryFieldName);
out.println(s);
}
rs.next() will give you the next result from your result, so the while loop will continue until you have no more results in your ResultSet. You can get the result by passing in the field name of the table by calling : rs.getString(QueryFieldName).