Transcript Chapter 14 - Emunix Emich

Chapter 14
JDBC, Java Server Pages, and MySQL
Fundamentals, Design,
and Implementation, 9/e
JDBC
 JDBC is an alternative to ODBC and ADO
that provides database access to programs
written in Java
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/2
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/3
JDBC Components
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/4
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/5
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/6
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/7
JSP Compilation Process
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/8
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/9
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/10
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/11
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/12
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/13
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/14
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
Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e
by David M. Kroenke
Chapter 14/15
Chapter 14
JDBC, Java Server Pages, and MySQL
Fundamentals, Design,
and Implementation, 9/e