Transcript Databases

DT228/3 Web 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
1
2
3
Name
John
Liz
Rory
Phone
879687
975645
321544
SELECT * from customers?
SELECT name, phone from customers
where customer_ID = 2 ?
Row
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 <SQL> tags to
access databases. We’ll use JSTL here.
• Full description of <SQL> tags in JSTL documentation
• Note: To use JSTL <sql> tags, need to include the
appropriate <taglib> directive into the JSP page
Accessing a database directly
from JSP page
•Use the <SQL:setDataSource> action from the JSTL
SQL library and specify the attributes it needs
•The <SQL:setDataSource> 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
<sql:setDataSource
To access the Db
var=“productsdb"
driver=“sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:products”
user = “cindy”
DSN
password = “DIT”
scope = “session”
datasource = ..
/>
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
<SQL:setDataSource> tag
<sql:setDataSource
var
="shopDb"
scope
= "session"
driver = "sun.jdbc.odbc.JdbcOdbcDriver"
url
= "jdbc:odbc:Driver={Microsoft Access
Driver (*.mdb)};DBQ=c:\\Assignment\Shopping.mdb"
/>
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
query
result
Example
<sql:query var = “productResult”
dataSource = “${productsdb}”
DSN as defined in
the setDAtasource
SELECT * FROM Products
WHERE ProductID = ?
<sql:param value = “${param.productNAME}”>/
<sql: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…)
•
<sql:query var = “productResult”
dataSource = “${productsdb}”
SELECT * FROM Products
WHERE ProductType = ‘grocery’
<sql:query>
Note: DOESN’T use “ “. !
Querying a database:
<sql:query>
<sql:query> tag has 6 attributes
•
•
dataSource
•
sql
•
maxRows
•
•
startRows
•
•
var
•
scope
•
(p 151 of specification)
Name of datasource. Optional.
Needed if setDataSource tag used
SQL statement. Mandatory. unless
specified as the body.
Optional. Maximum # of rows to show
in result. Default is all rows
Optional.
•
Mandatory. The name of the variable
to store the result.
•
Optional. The scope for the query
results - page (default), request,
session or application.
Set this carefully if you want to carry
query results through to another page
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…
<sql:query var = “productResult” scope =
“request”
dataSource = “${productsdb}”>
SELECT * FROM Products
WHERE ProductID = ?
<sql:param value = “${param.productNAME}”>
</sql: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.