Chapter 14 JDBC, Java Server Pages, and MySQL

clangpotatoSoftware and s/w Development

Oct 28, 2013 (3 years and 7 months ago)

102 views

Fundamentals, Design,

and Implementation, 9/e

Chapter 14

JDBC, Java Server Pages, and MySQL

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
2


Copyright © 2004

JDBC


JDBC

is an alternative to ODBC and ADO
that provides database access to programs
written in Java

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
3


Copyright © 2004

Java Servlet and Applet


An
applet

is a compiled, machine
-
independent,
Java bytecode program that is transmitted to a
browser via HTTP and is invoked using the HTTP
protocol


A
servlet

is a Java program that is invoked on the
server to respond to HTTP requests


Type 3 and Type 4 drivers can be used for both
applets and servlets


Type 2 drivers can be used only in servlets

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
4


Copyright © 2004

JDBC Components

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
5


Copyright © 2004

Using JDBC

1. Load the driver


The driver class libraries need to be in the
CLASSPATH for the Java compiler and for the
Java virtual machine


Class.forName(string).newInstance();

2. Establish a connection to the database


Connection conn =
DriverManager.getConnection(string);


A connection string includes the literal jdbc:,
followed by the name of the driver and a URL to
the database

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
6


Copyright © 2004

Using JDBC (cont.)

3. Create a statement


Statement stmt = conn.createStatement();

4. Execute the statement


ResultSet rs = stmt.executeQuery(querystring);


Int result = stmt.executeUpdate(updatestring);


ResultSetMetaData rsMeta = rs.getMetaData();



Both compiled queries and stored procedures can
be processed via JDBC using PreparedStatement
and CallableStatement objects

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
7


Copyright © 2004

Java Server Pages


Java Server Pages

(
JSP
) provide a means
to create dynamic Web pages using HTML,
XML, and Java


JSP pages provide the capabilities of a full
object
-
oriented language to the page
developer


Neither VBScript nor JavaScript can be used in
a JSP


JSPs are compiled into machine
-
independent
bytecode

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
8


Copyright © 2004

JSP Compilation Process

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
9


Copyright © 2004

JSP Pages and Servlets


JSPs are compiled as subclasses of the
HTTPServlet class


Consequently, small snippets of code can be placed in a
JSP, as well as complete Java programs


To use JSPs, the Web server must implement the
Java Servlet 2.1+ and Java Server Pages 1.0+
specifications


Apache Tomcat
, an open source product from the
Jakarta Project, implements these specifications


Tomcat can work in conjunction with Apache or can
operate as a standalone Web server for testing purposes

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
10


Copyright © 2004

Using JSP processor


When using JSP processor, e.g. Tomcat, the
JDBC drivers
,
JSP pages
, and any
Java beans

must be located in specified directories


When a JSP is requested, Tomcat ensures that
the most recent page is used


If an uncompiled newer version is available, Tomcat will
automatically cause it to be parsed and compiled


There is a maximum of one JSP in memory at a
time, and JSP requests are executed as a thread
of the servlet processor and not as a separate
process


The Java code in a JSP can load invoke a
compiled Java bean

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
11


Copyright © 2004

MySQL


MySQL

is an open source DBMS that runs
on UNIX, Linux, and Windows


MySQL is easy to use, relatively
inexpensive (no license fee), and can
provide fast query processing


Limitations


MySQL does not support views, stored
procedures, or triggers


Referential integrity can be defined, but it is not
enforced by MySQL

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
12


Copyright © 2004

MySQL (cont.)


MySQL maintains a data dictionary in a
database named
mysql


The user and db tables can be queried to
determine user permissions


To access MySQL from JDBC, the user
account must be granted access to the
database, either from any location or from
a TCP/IP address that represents the local
computer

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
13


Copyright © 2004

Concurrency Control


MySQL provides limited support for
concurrent processing


There is no support for transactions,

i.e., no COMMIT or ROLL BACK statements or
transaction isolation


MySQL locks at the table level


This may cause throughput problem


Shared read locks are obtained when
processing SELECT statements


Exclusive locks are obtained when processing
INSERT, UPDATE, or DELETE

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
14


Copyright © 2004

Concurrency Control (cont.)


Users can surround transaction logic with
LOCK TABLES and UNLOCK TABLES
commands


Deadlock is prevented by allowing, at most,
one lock statement to be open at a time


Dirty reads are possible if some
applications roll back their own work and
do not surround their activity with table
locks

Database Processing: Fundamentals, Design, and Implementation, 9/e

by David M. Kroenke


Chapter 14/
15


Copyright © 2004

Backup and Recovery


MySQL provides limited backup and recovery
facilities


There is a backup utility that augments the
operating system copy utilities


MySQL maintains a log of commands processed
but no before and after images, nor data values
from bulk updates or deletions


To restore a database, an older version of the
database is copied back, and the commands in
the log are reapplied

Fundamentals, Design,

and Implementation, 9/e

Chapter 14

JDBC, Java Server Pages, and MySQL