DT211/3 Internet Application
Development
Databases
Database
• Almost all web application on the net access a database
e.g. shopping sites, message boards, search engines
• Relational databases (uses tables) are the most
common type used
• Small application may use just one table, larger
applications may have have hundreds of tables
• In JSP, database access can be done using scriplets or
using JSTL
• JSTL supplies the SQL library to enable database access
Relational DBs
Data is stored in tables. Rows and columns in tables can be
related to rows and columns in other tables in the dB
Each table usually has a primary key
Structured Query Language (SQL) is used to query the database
Common SQL Statements: SELECT, INSERT, UPDATE, DELETE
Column
customer ID Name Phone
Row
1 John 879687
2 Liz 975645
3 Rory 321544
SELECT * from customers?
SELECT name, phone from customers
where customer_ID = 2 ?
Relational DBs
INSERT
INSERT INTO CUSTOMERS (customer_ID, name, phone)
VALUES (5, “JOHN”, “875895”)
UPDATE
UPDATE CUSTOMERS SET NAME = “Robert” WHERE CUSTOMER_ID = 1
DELETE
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2
Some Database concepts
• To access a database from a web application, need a
path or connection to the database
• To support multiple connections to the same database
- have connection pooling. Works like a group of
connections that are used by clients as needed and
“put back” in the pool as needed.
• Each database engine (e.g. Oracle, SQLServer, Access)
needs its own database driver (The driver is just a
piece of software that translate SQL calls between the
web application to the database).
• Web applications have to “load” the appropriate
driver in order to connect to the database
Some Database concepts
• Java provides the JDBC API (Java Database Connectivity
API) to enable a uniform interface to different
database engines
• JDBC enables java code that accesses a databases to be
portable from one database to another (provided the
correct driver is used).
• Many databases have an ODBC (Open Database
Connectivity) interface (e.g. access)
• Sun provides a JDBC-ODBC
driver for development purposes
Some Database concepts
•JDBC-ODBC driver enables java (and JSP) applications to
connect to ODBC databases, using the JDBC API
•Note: In production environment, should use a production
quality driver from the database vendor
Databases and web application
•Variety of databases available for use by web applications
•typically will use relational database with support for
Structured Query Language
•Examples of common databases used:
SQL server, MySQL, Oracle, Access
Datasource names (DSNs)
• To use a database in a web application need to say:
- What the database is called
- Where is resides
- What driver is required by the database
• ODBC databases allow a Data source name to be used as a quick
way to specify the above within the applicaton
• e.g. , rather than saying “I want to connect to an Access2000
database named Exams.mdb in d:\samples\database directory in
every JSP page that uses the particular database, will rather
create a DSN named Exams
• Note: not all databases will support DSNs.
Datasource names (DSNs)
• To SET UP a data source name in Windows:
• Go to Windows control panel / administrative tools /
system DSN (NOT user DSN)
• Add a new database source, selecting appropriate
driver
• Note: Will use microsoft access for development
purposes – it‟s an ODBC database and supports DSNs
Accessing a database from JSP
• Need to identify and connect to the database to be used
with the JSP page:
1) Global datasource: Can specifying a default datasource
in a Tomcat configuration file for the application called the
web.xml file. The datasource will automatically be made
available to the JSP if done this way – Good approach for
larger applications.
OR
2) Direct from JSP: by specifying the database details
directly within the JSP page. Use instead of (1) all the time
OR just to override the default data source specified in (1)
Accessing a database directly
from JSP page
Will cover 1) on next course. Good for larger applications.
Will use 2) for development purposes. (Useful for smaller
applications - but does not support connection pooling)
Using option 2):
• Can use java code (via scriptlets) OR JSTL tags to
access databases. We‟ll use JSTL here.
• Full description of tags in JSTL documentation
• Note: To use JSTL tags, need to include the
appropriate directive into the JSP page
Accessing a database directly
from JSP page
•Use the action from the JSTL
SQL library and specify the attributes it needs
•The action creates a database
connection in the background
•Note: Can use the datasource name in the action, if
available – as shown on next page. Otherwise, have to
specify direct database and driver locations.
Accessing a database from JSP
with a Data source name
setDataSource tag has seven attributes:
Will use this name in any other SQL statements
Can specify username/password - optional
Accessing a database from JSP
(without a DSN)
• Note: IF issue in setting up DSN (e.g. permissions problem in
Labs)..
Have to specify the database more specifically within the
tag
Instead of the DSN, have to enter in exact URL required for that database.. Including
full path to the database
Querying a database
• To query a database… just use the sql: query tag
Variable
• to hold
• Example query
/
Holds the value of to be
transferred into the ?
placeholder.
• Will return all rows from Products table with a product ID name
same as that supplied in the product name parameter
Querying a Database
• Querying, where parameter value is already known
(i.e. not passed in…)
•
Note: DOESN‟T use “ “. !
Querying a database:
tag has 6 attributes (p 151 of specification)
• dataSource • Name of datasource. Optional.
Needed if setDataSource tag used
• SQL statement. Mandatory. unless
• sql
specified as the body.
• maxRows • Optional. Maximum # of rows to show
in result. Default is all rows
• startRows • Optional.
• Mandatory. The name of the variable
• var to store the result.
• scope • Optional. The scope for the query
Set this carefully if you want to carry results - page (default), request,
query results through to another page session or application.
Scope of a query…
A JSP page itemsearch.jsp queries a database for a list of
items that can be added to a shopping cart
The results of the query search will be displayed on
another JSP page called results.jsp
The scope of the query that is executed in itemsearch.jsp
needs to be „request‟. Otherwise, query
results will have „disappeared‟ when itemsearch.jsp has
passed control to results.jsp (because default scope is
„page‟ for queries).
Scope of a query…
/
Specify the scope of the query results if the results
need to be available to the request, session or
application. The default scope is page.
Querying a database: Partial
info
• Search engines, on-line catalogues often need to allow
user to search a database using partial information
• e.g. First name begins with “A” , surname contains
“mc”
• In SQL, use the LIKE keyword and wildcard characters
(%, _)
• SQL Examples of partial searches
Search * From Customers WHERE FirstName LIKE “Jon%”
Search * from Customers WHERE LastName LIKE “Sm_th”
Querying a database: Partial
info
• To implement in JSTL, need to just incorporate the
LIKE keyword and the wildcard characters
• Example:
/
/
• Will return all rows from Employee table where first
name begins with firstName parameter and last name
contains value of lastName parameter
Processing result of a query
• When a query is run, need to be able to process the
results e.g. to display rows back as a HTML table
• In the example, saw that query result is put into a
variable
, , etc
Processing result of a query:
example
First name /
Last name /
Assign
any variable
Rows from nameResult name to access
Property “rows” the columns
is used
Query: Code sample – outputs all
rows on a table to a html page
Query: Code sample – outputs all
rows on a table
SELECT UserName, Address1 FROM Customers
Sorry, there are no customers on the
Here is the list of customers
User name
Address
Query: Code sample – outputs all
rows on a table
Updating a database
• To update database information, can use INSERT,
UPDATE or DELETE
• Use the is used for any SQL statement
that doesn‟t return rows -- it is used for INSERT,
UPDATE, DELETE
• specification on page 148 of jstl
specificaiton document
• has three attributes: sql (= sql
statement), var (for result) and scope
• Uses ? and to assign parameter values
in same way as
Updating a database
Reminder --- SQL statements are of the form:…
INSERT
INSERT INTO CUSTOMERS (customer_ID, name, phone)
VALUES (5, “JOHN”, “875895”)
UPDATE
UPDATE CUSTOMERS SET NAME = “Robert” WHERE CUSTOMER_ID = 1
DELETE
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2
INSERT
• To INSERT a new record on a table called Customers
• Example:
Table name
INSERT INTO Customers Column names
(UserName, Password, FirstName, LastName,DateofBirth)
VALUES (?, ?, ?, ?, ?)
Column values
INSERT
• Place holders (?) for column values are filled up by
request parameters (I.e. very unlikely to be
hardcoded!)
• Request parameters are matched against the ?s in the
order they appear
• Add in the datasource name into the statement
Data conversion
•Note: setting a column that contains date or time –
need to use a special tag action called
because of JDBC API quirk
(requires specific JDBC data and time types).
•Also, if database table contains columns that are
numeric (e.g. INT, REAL etc), may need to use the
action to convert a string
request parameter
UPDATE
• To update database row(s), simply use the UPDATE
statement in the SQL statement
• example: to update password, firstname and lastname on
the row(s) in the Customer table where userName matches
that held in the username request parameter.
UPDATE Customers
SET Password = ?,
FirstName = ?
LastNAme = ?
WHERE UserName = ?
DELETE
• To update database row(s), simply use the UPDATE
statement in the SQL statement
• example: To delete all rows from the Customer table
where the username matches that held in the
userName request parameter.
DELETE FROM Customers
WHERE UserName = ?,
Example of a simple search application that allows a
user to search for an employee using a form
(search.html).
The search is processed by find.jsp, and
presented back by list.jsp**
** Separates presentation from business
logic/request processing
Sample code: find.jsp
%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>
SELECT * FROM Employees
WHERE FirstName LIKE ? Results of query
AND LastName LIKE ? must be available during the
AND Department LIKE ? full request (in order to
ORDER BY LastName send result on to another page)
Forward controL
to list.jsp to
display results
Sample code – search.html
Search in Employee Database
Please enter information about the employee you're looking for.
You can use partial information in all fields.
First Name:
Calls find.jsp
Last Name:
… ETC ETC….. rest of form
List.jsp displays the rows found. If not row found,
displays an error
Sample code – list.jsp
Search Result The name of the
query
Sorry, no employees were found.
The following employees were found:
Last Name
First Name
Department
Sample code – list.jsp
The name used by the developer
to access the contents of each row.
The used as row.LastName… etc
Common errors
• Not specifying the datasource in your SQL command
(get “database null” error in Apache when running)
• Using wrong number of parameters in SQL action
• When Specifying the dataSource name in a SQL
statement, need to put it as an expression in ${ } ...
otherwise, it will take the exact name in the “ “ and
use it. Get an error, no suitable driver if you do this
wrong.
• Scope: Make sure the scope on the SQL statement is
correct, so that the connection or SQL statement
results carry through to the required pages.
Tags used
•
•
•
Database access using JSLT
• Usually used for simple applications
• Can use java beans or servlets for database access for
more complex applications
Info on JSTL
Sun tutorial. Chapter 14 is on JSTL at:
http://java.sun.com/j2ee/1.4/docs/tutorial/doc/index.ht
ml
Contains examples
Also,
Sun‟s JSTL 1.0 tag specification on distrib. Good for
definition of each tag. Poor on examples