Transcript Document

Web Application Deployment
& JDBC
CSC 667, Spring 2004
Dr. Ilmi Yoon
Web Application
• With the release of the Java Servlet
Specification 2.2
• Web Application is a collection of servlets,
html pages, classes, and other resources that
can be bundled and run on multiple containers
from multiple vendors
• Each web application has one and only one
ServletContext
• http://www.onjava.com/pub/a/onjava/2001/0
3/15/tomcat.html
• http://www.onjava.com/pub/a/onjava/2001/0
4/19/tomcat.html
Deployment
• Deployment descriptor (web.xml)
• Web applications can be changed
without stopping the server
• With a standardized deployment
comes standardized tools
• Check
http://unicorn.sfsu.edu/~csc667/0424/667_files/frame.htm for tips for Ant,
TogetherSoft, Tomcat install &
deployment
Table 1. The Web Application Directory Structure
Directory
Contains
/onjava
This is the root directory of the web application.
All JSP and XHTML files are stored here.
/onjava/WEB-INF
This directory contains all resources related to
the application that are not in the document
root of the application. This is where your web
application deployment descriptor is located.
Note that the WEB-INF directory is not part of
the
document.
files contained
this
Thispublic
directory
is whereNo
servlet
and utilityinclasses
directory
can be served directly to a client.
are located.
/onjava/WEB-INF/classes
/onjava/WEB-INF/lib
This directory contains Java Archive files that
the web application depends upon. For example,
this is where you would place a JAR file that
contained a JDBC driver.
Installation & Setup
• Update CLASSPATH
– Identify the Classes (jsp.jar, jspengine.jar,
servlet.jar, jasper.jar) to the java Compiler
– Unix
•
•
•
•
•
CLASSPATH=${TOMCAT_HOME}/webserver.jar
CLASSPATH=${CLASSPATH}:${TOMCAT_HOME}/webserver.jar
CLASSPATH=${CLASSPATH}:${TOMCAT_HOME}/lib/servlet/jar
CLASSPATH=${CLASSPATH}:${TOMCAT_HOME}/lib/jsper.jar
CLASSPATH=${CLASSPATH}:${TOMCAT_HOME}/examples/WEBINF/classes
– Windows
• set
CLASSPATH=.;dir\servlet.jar;dir\jspengine.jar
Installation & Setup
• Compile and Install your servlets
– Tomcat
• install_dir/webpages/WEB-INF/classes
• install_dir/classes
• install_dir/lib (non frequently changing
classes)
• install_dir/webapps/ROOT/WEBINF/classes (3.1)
• Invoking the servlets
– http://host:port/servlet/Packagename.servetN
ame use /servlet/ regardless the actual
directory name
– Register servlet
Calling Servlets From a Browser
• The URL for a servlet has the following general
form, where servlet-name corresponds to the name
you have given your servlet:
http://machine-name:port/servlet/servlet-name
• Servlet URLs can contain queries, such as for HTTP
GET requests. For example, if the servlet's name is
bookdetails; the URL for the servlet to GET and
display all the information about the bookstore's
featured book is:
http://localhost:8080/servlet/bookdetails?bookId=
203
Web.xml – under WEB_INF
<web-app> <display-name>The OnJava
App</display-name> <sessiontimeout>30</session-timeout> <servlet>
<servlet-name>TestServlet</servlet-name>
<servletclass>com.onjava.TestServlet</servlet-class>
<load-on-startup>1</load-on-startup> <initparam> <param-name>name</param-name>
<param-value>value</param-value> </initparam> </servlet> </web-app>
Packing the Web Application
• Web ARchive file (WAR)
• Command : jar cvf onjava.war .
• Now you can deploy your web
application by simply distributing this
file
Table 5. The Tomcat Directory Structure
/bin
/conf
This directory contains the main configuration files for Tomcat. The two most
important are the server.xml and the global web.xml.
/server
This directory contains the Tomcat Java Archive files.
/lib
This directory contains Java Archive files that Tomcat is dependent upon.
/logs
This directory contains Tomcat's log files.
/src
This directory contains the source code used by the Tomcat server. Once Tomcat is
released, it will probably contain interfaces and abstract classes only.
/webapps
All web applications are deployed in this directory; it contains the WAR file.
/work
This is the directory in which Tomcat will place all servlets that are generated from
JSPs. If you want to see exactly how a particular JSP is interpreted, look in this
directory.
Steps Involved in Deploying a
Web Application to Tomcat
1.
Copy your WAR file to the
TOMCAT_HOME/webapps directory.
2. Add a new Context entry to the
TOMCAT_HOME/conf/server.xml file,
setting the values for the path and
docBase to the name of your web
application.
3. <Context path="/onjava" docBase="onjava"
debug="0" reloadable="true" />
If you look at the TOMCAT_HOME/webapps directory, you will see a
new directory matching the name of your WAR file
JDBC
• Database
– Collection of data
• DBMS
– Database management system
– Storing and organizing data
• SQL
– Relational database
– Structured Query Language
• JDBC
– Java Database Connectivity
– JDBC driver
Relational-Database Model
• Relational database
–
–
–
–
Table
Record
Field, column
Primary key
• Unique data
• SQL statement
– Query
– Record sets
Manipulating Databases with
JDBC
• Connect to a database
• Query the database
• Display the results of the query
Connecting to and Querying a
JDBC Data Source
• DisplayAuthors
– Retrieves the entire authors table
– Displays the data in a JTextArea
Create Connection at Init()
public class SQLGatewayServlet extends HttpServlet{
private Connection connection;
public void init() throws ServletException{
try{
Class.forName("org.gjt.mm.mysql.Driver");
String dbURL = "jdbc:mysql://localhost/murach";
String username = "root";
String password = "";
connection = DriverManager.getConnection(
dbURL, username, password);
}
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException{
String sqlStatement = request.getParameter("sqlStatement");
String message = "";
try{
}
Statement statement = connection.createStatement();
sqlStatement = sqlStatement.trim();
String sqlType = sqlStatement.substring(0, 6);
if (sqlType.equalsIgnoreCase("select")){
ResultSet resultSet = statement.executeQuery(sqlStatement);
// create a string that contains a HTML-formatted result set
message = SQLUtil.getHtmlRows(resultSet);
} else
{
int i = statement.executeUpdate(sqlStatement);
if (i == 0) // this is a DDL statement
message = "The statement executed successfully.";
else
// this is an INSERT, UPDATE, or DELETE statement
message = "The statement executed successfully.<br>"
+ i + " row(s) affected.";
}
statement.close();
From JDBC Example at course web page
public void init() throws ServletException{
connectionPool = MurachPool.getInstance();
}
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException{
Connection connection = connectionPool.getConnection();
String firstName = request.getParameter("firstName");
String lastName = request.getParameter("lastName");
String emailAddress =
request.getParameter("emailAddress");
User user = new User(firstName, lastName, emailAddress);
HttpSession session = request.getSession();
session.setAttribute("user", user);
String message = "";
Processing Multiple ResultSets
or Update Counts
• Execute the SQL statements
• Identify the result type
– ResultSets
– Update counts
• Obtain result
– getResultSet
– getUpdateCount
JDBC 2.0 Optional Package
javax.sql
• Package javax.sql
– Included with Java 2 Enterprise Edition
• Interfaces in package javax.sql
– DataSource
– ConnectionPoolDataSource
– PooledConnection
– RowSet
Connection Pooling
• Database connection
– Overhead in both time and resources
• Connection pools
– Maintain may database connections
– Shared between the application clients
Relational DB and SQL
statements
This section is self-study section
Relational-Database Model
Row /Rec ord
Number Name
Department
Salary
Location
23603
Jones
413
1100
New Jersey
24568
Kerwin
413
2000
New Jersey
34589
Larson
642
1800
Los Ang ele s
35761
M yers
611
1400
Orla nd o
47132
Neum ann
413
9000
New Jersey
78321
Step he ns
611
8500
Orla nd o
Prima ry key
Column/ Fie ld
Relational-database structure of an Employee table.
Relational Database Overview:
The books Database
• Sample books database
– Four tables
• Authors, publishers, authorISBN and
titles
– Relationships among the tables
Relational Database Overview: The
books Database
Field
authorID
Desc rip tion
Author’s ID number in the database. In the books database, this integer
field is defined as an autoincremented field. For each new record inserted
in this table, the database automatically increments the authorID value
to ensure that each record has a unique authorID. This field represents
the table’s primary key.
firstName Author’s first name (a string).
lastName
Author’s last name (a string).
Fig. 8.3 authors ta b le fro m books.
authorID
1
firstName
Harvey
lastName
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Sean
Santry
Fig. 8.4 Da ta fro m the authors ta b le o f books.
Relational Database Overview:
The books Database (Cont.)
Fie ld
publisherID
De sc rip tio n
The publisher’s ID number in the database. This autoincremented
integer is the table’s primary-key field.
publisherName The name of the publisher (a string).
Fig. 8.5 publishers ta b le fro m books.
publisherID
1
publisherName
Prentice Hall
2
Prentice Hall PTG
Fig. 8.6 Da ta fro m the publishers ta b le o f books.
Relational Database Overview:
The books Database (Cont.)
Fie ld
authorID
De sc rip tio n
The author’s ID number, which allows the database to associate
each book with a specific author. The integer ID number in this
field must also appear in the authors table.
isbn
The ISBN number for a book (a string).
Fig. 8.7 authorISBN ta b le fro m books.
Relational Database Overview:
The books Database
authorID
1
isbn
0130895725
authorID
2
1
0132261197
2
1
0130895717
2
1
0135289106
2
1
0139163050
2
1
013028419x
2
1
0130161438
2
1
0130856118
2
1
0130125075
2
1
0138993947
2
1
0130852473
2
1
0130829277
2
1
0134569555
2
1
0130829293
3
1
0130284173
3
1
0130284181
3
1
0130895601
3
2
0130895725
3
2
0132261197
3
2
0130895717
3
2
0135289106
4
Fig. 8.8 Da ta fro m the authorISBN ta b le o f books.
isbn
0139163050
013028419x
0130161438
0130856118
0130125075
0138993947
0130852473
0130829277
0134569555
0130829293
0130284173
0130284181
0130895601
013028419x
0130161438
0130856118
0134569555
0130829293
0130284173
0130284181
0130895601
Relational Database Overview:
The books Database (Cont.)
Fie ld
isbn
De sc rip tio n
ISBN number of the book (a string).
title
editionNumber
Title of the book (a string).
Edition number of the book (an integer).
copyright
publisherID
Copyright year of the book (a string).
Publisher’s ID number (an integer). This value must correspond to an
ID number in the publishers table.
imageFile
Name of the file containing the book’s cover image (a string).
price
Suggested retail price of the book (a real number). [Note: The prices
shown in this book are for example purposes only.]
Fig. 8.9 titles ta b le fro m books.
Relational Database Overview:
The books Database (Cont.)
authors
authorID
firstName
lastName
1

authorISBN
authorID
isbn
publishers
publisherID
publisherName
1


1
Fig. 8.11 Table relationships in books.
titles
isbn
title
editionNumber
copyright
publisherID
imageFile
price
Structured Query Language
(SQL)
• SQL overview
• SQL keywords
SQL ke yw o rd
SELECT
De sc rip tio n
Select (retrieve) fields from one or more tables.
FROM
Tables from which to get fields. Required in every SELECT.
WHERE
Criteria for selection that determine the rows to be retrieved.
GROUP BY
Criteria for grouping records.
ORDER BY
Criteria for ordering records.
INSERT INTO
Insert data into a specified table.
UPDATE
Update data in a specified table.
DELETE FROM
Delete data from a specified table.
Fig. 8.12
SQL q ue ry ke yw o rd s.
Basic SELECT Query
• Simplest format of a SELECT query
– SELECT * FROM tableName
•SELECT * FROM authors
• Select specific fields from a table
– SELECT authorID, lastName FROM
authors
authorID
1
2
3
4
Fig. 8.13
lastName
Deitel
Deitel
Nieto
Santry
authorID a nd lastName fro m the authors ta b le .
WHERE Clause
• specify the selection criteria
– SELECT fieldName1, fieldName2, … FROM
tableName WHERE criteria
• SELECT title, editionNumber, copyright
FROM titles
WHERE copyright > 1999
• WHERE clause condition operators
– <, >, <=, >=, =, <>
– LIKE
• wildcard characters % and _
WHERE Clause (Cont.)
•SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘D%’
authorID
1
2
Fig. 8.15
firstName
Harvey
lastName
Deitel
Paul
Deitel
Autho rs w ho se la st na m e sta rts w ith D fro m the authors ta b le .
WHERE Clause (Cont.)
•SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘_i%’
authorID
3
firstName
Tem
lastName
Nieto
Fig. 8.16
The o nly a utho r fro m the authors ta b le w ho se la st na m e
c o nta ins i a s the se c o nd le tte r.
ORDER BY Clause
• Optional ORDER BY clause
– SELECT fieldName1, fieldName2, … FROM
tableName ORDER BY field ASC
– SELECT fieldName1, fieldName2, … FROM
tableName ORDER BY field DESC
• ORDER BY multiple fields
– ORDER BY field1 sortingOrder, field2
sortingOrder, …
• Combine the WHERE and ORDER BY clauses
ORDER BY Clause (Cont.)
•SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName ASC
authorID
2
1
3
4
Fig. 8.17
firstName
Paul
lastName
Deitel
Harvey
Deitel
Tem
Nieto
Sean
Santry
Autho rs fro m ta b le authors in a sc e nd ing o rd e r b y lastName.
ORDER BY Clause (Cont.)
•SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName DESC
authorID
4
3
2
1
Fig. 8.18
firstName
Sean
lastName
Santry
Tem
Nieto
Paul
Deitel
Harvey
Deitel
Autho rs fro m ta b le authors in d e sc e nd ing o rd e r b y lastName.
ORDER BY Clause (Cont.)
•SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName, firstName
authorID
1
firstName
Harvey
lastName
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Sean
Santry
Fig. 8.19
Autho rs fro m ta b le authors in a sc e nd ing o rd e r b y lastName
a nd b y firstName.
ORDER BY Clause (Cont.)
•SELECT isbn, title, editionNumber,
copyright, price
FROM titles WHERE title LIKE ‘%How to Program’
ORDER BY title ASC
isbn
0130895601
0132261197
0130895725
0135289106
0130895717
0130161438
title
edition- copyNumber
right
Advanced Java 2 Platform How to Program 1
2002
price
69.95
C How to Program
2
1994
49.95
C How to Program
3
2001
69.95
C++ How to Program
2
1998
49.95
C++ How to Program
3
2001
69.95
Internet and World Wide Web How to
1
2000
69.95
Program
0130284181 Perl How to Program
1
2001
69.95
0134569555 Visual Basic 6 How to Program
1
1999
69.95
0130284173 XML How to Program
1
2001
69.95
013028419x e-Business and e-Commerce How to
1
2001
69.95
Program
Fig. 8.20
Bo o ks fro m ta b le titles w ho se title e nd s w ith How to Program
in a sc e nd ing o rd e r b y title.
Merging Data from Multiple
Tables: Joining
• Join the tables
– Merge data from multiple tables into a single
view
– SELECT fieldName1, fieldName2, …
FROM table1, table2
WHERE table1.fieldName = table2.fieldName
– SELECT firstName, lastName, isbn
FROM authors, authorISBN
WHERE authors.authorID =
authorISBN.authorID
ORDER BY lastName, firstName
Merging Data from Multiple
Tables: Joining (Cont.)
firstName
Harvey
lastName
Deitel
isbn
0130895601
firstName
Harvey
lastName
Deitel
isbn
0130284173
Harvey
Harvey
Deitel
Deitel
0130284181
0134569555
Harvey
Paul
Deitel
Deitel
0130829293
0130852473
Harvey
Deitel
0130829277
Paul
Deitel
0138993947
Harvey
Deitel
0130852473
Paul
Deitel
0130125075
Harvey
Deitel
0138993947
Paul
Deitel
0130856118
Harvey
Deitel
0130125075
Paul
Deitel
0130161438
Harvey
Deitel
0130856118
Paul
Deitel
013028419x
Harvey
Deitel
0130161438
Paul
Deitel
0139163050
Harvey
Deitel
013028419x
Paul
Deitel
0135289106
Harvey
Deitel
0139163050
Paul
Deitel
0130895717
Harvey
Deitel
0135289106
Paul
Deitel
0132261197
Harvey
Deitel
0130895717
Paul
Deitel
0130895725
Harvey
Deitel
0132261197
Tem
Nieto
0130284181
Harvey
Deitel
0130895725
Tem
Nieto
0130284173
Paul
Deitel
0130895601
Tem
Nieto
0130829293
Paul
Deitel
0130284181
Tem
Nieto
0134569555
Paul
Deitel
0130284173
Tem
Nieto
0130856118
Paul
Deitel
0130829293
Tem
Nieto
0130161438
Paul
Deitel
0134569555
Tem
Nieto
013028419x
Paul
Deitel
0130829277
Sean
Santry
0130895601
Fig. 8.21
A utho rs a nd the ISBN num b e rs fo r the b o o ks the y ha ve w ritte n in
a sc e nd ing o rd e r b y lastName a nd firstName.
INSERT INTO Statement
• Insert a new record into a table
– INSERT INTO tableName ( fieldName1,
… , fieldNameN )
VALUES ( value1, … , valueN )
•INSERT INTO authors ( firstName,
lastName )
VALUES ( ‘Sue’, ‘Smith’ )
authorID
1
2
3
4
5
Fig. 8.22
firstName
Harvey
lastName
Deitel
Paul
Deitel
Tem
Nieto
Sean
Santry
Sue
Smith
Ta b le Authors a fte r a n INSERT INTO o p e ra tio n to a d d a re c o rd .
UPDATE Statement
• Modify data in a table
– UPDATE tableName
SET fieldName1 = value1, … , fieldNameN =
valueN
WHERE criteria
• UPDATE authors
SET lastName = ‘Jones’
WHERE lastName = ‘Smith’ AND firstName = ‘Sue’
authorID
1
2
3
4
5
Fig. 8.23
firstName
Harvey
lastName
Deitel
Paul
Deitel
Tem
Nieto
Sean
Santry
Sue
Jones
Ta b le authors a fte r a n UPDATE o p e ra tio n to c ha ng e a re c o rd .
DELETE FROM Statement
• Remove data from a table
– DELETE FROM tableName WHERE
criteria
•DELETE FROM authors
WHERE lastName = ‘Jones’ AND
firstName = ‘Sue’
authorID
1
2
3
4
Fig. 8.24
firstName
Harvey
lastName
Deitel
Paul
Deitel
Tem
Nieto
Sean
Santry
Ta b le authors a fte r a DELETE o p e ra tio n to re m o ve a re c o rd .