Transcript DBMS (II)

Database Management
Systems (II)
Chapter 14. JDBC, Java
Server Pages, and MySQL
1
© 2005 by Dr. F. Lin
JDBC, JSP and MySQL
• Alternative to Microsoft solutions for
distributed database access
– Open-source software
• JDBC (not an acronym)
• Java Server Pages (JSP) using Apache/ Tomcat
• MySQL driver (an open-source DBMS developed
by M. Mathews)
– Running in open environments
• Linux, and also Unix
• Proprietary systems such as Windows
2
JDBC
• JDBC
– Allowing programmers to connect to many
databases with JDBC drivers
• For a listing of drivers, see:
http://java.sun.com/products/jdbc
– JDBC architecture and components (see
figure)
• Objects in applications: Connection, Statement,
ResultSet, ResultSetMetaData
• Driver manager
• Driver
• Database and DBMS
3
DBMS1
DBMS Driver1
Driver
Manager
DBMS Driver2
DB
DBMS2
DB
JDBC-ODBC Bridge
Connection
ODBC Driver
Statement
CallableStatement
ResultSet
PreparedStatement
DB
ResultSetMetaData
Applications
JDBC Architecture
4
DB
• JDBC driver types
– Type 1: JDBC-ODBC bridge drivers
– Type 2: connects to the native API of the
DBMS
– Type 3: translates JDBC into DBMSindependent network protocol
• Can be used for servlets and applets
– Type 4: translates JDBC into DBMS-specific
network protocol
• Can be used for servlets and applets
5
Driver
Type
Characteristics
1
JDBC-ODBC bridge drivers. Provides a Java API that
interfaces to an ODBC driver. Enables processing of
ODBC data sources from Java
2
A Java API that connects to the native library of a
DBMS product. The Java program and the DBMS must
reside on the same machine, or the DBMS program
must handle inter-machine communication, if not
3
A Java API that connects to a DBMS-independent
network protocol. Can be used for servlets and applets
4
A Java API that connects to a DBMS-dependent
network protocol. Can be used for servlets and applets
JDBC Driver Types
6
• Java programming language
– Java was designed to be
portable
• A Java program is not
compiled into machine
language, instead, into
machine-independent
bytecode (portability)
Bytecode is interpreted by the
Java virtual machine (JVM)
JVM: a bytecode interpreter
that is needed to run Java
programs
Different interpreters for
different machine
environments (Intel, Alpha,
7
etc.)
• Java performance
• Be interpreted, not as fast as
code that has been translated
into machine language
– This may or may not a problem,
depending on the application’s
workload.
8
• Java servlet
– A servlet is a Java program
invoked via HTTP on the
Web server
– The servlet uses the JVM
running on the server
• Java applet
– An applet is Java bytecode
that is designed to run on a
client
– Applet bytecode is sent to
the client machine using
HTTP. It is read and
interpreted by the Web
browser. Most, if not all,
Web browsers now run the
Java virtual machine
9
JVM
servlets
Applet
HTTP
Web
Browser
Web
Browser
Web
Browser
• Using JDBC
– No JDBC data source: a connection is built
in Java codes via JDBC driver
– Steps for using JDBC drivers
• Load the driver  Establish a connection to the
database  Create a statement  Process the
statement
– Loading the driver
• The directory containing the driver library must
be part of the CLASSPATH for the Java
compiler and the Java virtual machine
• The JDBC-ODBC Bridge driver is installed
automatically with the Java 2 SDK, Standard
Edition, as package sun.jdbc.odbc
10
• While there are several ways to load the drivers, the
following is one of the most reliable…
– Class.forName(“nameofdriver”).newInstance()
– Establishing a connection to the database
• Create an object that has a connection to the
database…
– Connection Conn =
DriverManager.getConnection(“connectionString”);
– Example:
DriverManager.getConnection(“jdbc:mysql:
//localhost/db1?user=abc&password=pwd”);
11
• Details regarding the JDBC connection string:
“connectionString” has three parts (each part is
delimited with a colon)
Part 1: jdbc:
Part 2: a keyword that identifies the DBMS,
e.g., mysql:
Part 3: a URL for the database, e.g.,
//localhost/db1?user=abc&password=pwd
– Creating a statement
• A statement is created by instantiating a new
Statement object
– Statement Stmt = Conn.createStatement();
12
– Processing the statement
• The driver’s API documentation will provide details
on what statements are available. However, the
core statements are standard
– Note: a driver’s API is a set of functions that
the program may call to receive services
• Examples (also see the codes):
ResultSet RS = Stmt.executeQuery(“select * from tab”);
int result = Stmt.executeUpdate(“update tab set name=‘abc’
where id = 1”);
– Prepared statement and callable statement
• Used to invoke compiled queries and stored
procedures in database
• Similar to command (but not supported by MySQL)
13
An example of JDBC-ODBC and Java
1.
Build a simple database with Microsoft Access
named db1.mdb, and create a table in db1.mdb:
named CUSTOMER, which has three fields:
CustomerID, Name, AreaCode, PhoneNumber,
Street, City, State, and Zip;
input some data into the table; save the database in
somewhere in the disk, say C:\.
2. Create a data source for db1.mdb: open the
“Control Panel” of Windows 2000/XP; From the
item “Administrative Tools”, find the icon for
“Data Sources (ODBC)”, and then click on the
icon, from the window “ODBC Data Source
Administrator” click on the tab “System DSN”,
14
An Example (con’t)
– Add a data source, select “Microsoft access
driver” and click OK, enter a name for the
data source, “test”; and click the tab
“select” and find and select the database
we just built, db1.mdb. Press “OK”. Now we
have created a data source in our Window
environment.
– Install Java 1.4 or higher on your machine
and edit the example in Figure 14-3;
15
Figure 14-3 (modified)
import java.io.*;
import java.sql.*;
Check for at least one parameter
public class GeneralTable {
public static void main(String[] args) {
if (args.length <1) {
Set the variable varTableName to
System.out.println(Insufficient data provided.”);
the input table name
return;
and convert that name to uppercase.
}
String varTableName= args[0];
varTableName = varTableName.toUpperCase();
System.out.println("Showing Table " + varTableName);
try {
JDBC-ODBC driver is loaded
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String connString="jdbc:odbc:test";
The Bridge is installed
automatically
with the Java 2 SDK,
Standard Edition,
as package sun.jdbc.odbc
The Bridge driver uses the odbc subprotocol.
URLs for this subprotocol are of the form:
jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]*
16
Figure
14-3
}
System.out.println("Trying connection with "+connString);
Connection conn = DriverManager.getConnection(connString);
Statement stmt = conn.createStatement();
String varSQL = "SELECT * FROM " + varTableName;
ResultSet rs = stmt.executeQuery(varSQL);
ResultSetMetaData rsMeta = rs.getMetaData();
String varColNames = "";
int varColCount = rsMeta.getColumnCount();
for (int col =1; col <= varColCount; col++) {
varColNames = varColNames + rsMeta.getColumnName(col) + " ";
}
System.out.println(varColNames);
while (rs.next()) {
for (int col=1; col<=varColCount; col++) {
System.out.print(rs.getString(col) + " ");
}
System.out.println();
}
rs.close(); conn.close();
}
catch (Exception e) { e.printStackTrace(); }
}
17
An example (con’t)
– Save it in C:\wan\GeneralTable.java
– Compile: C:\wan>javac GeneralTable.java
– Run it: C:\wan>java GeneralTable
customer
18
Questions
• Can JDBC drivers be imported into Java
Programs?
19
Figure 14-4
– Implement the logic for the View Ridge
CustomerInsert procedure in Chapter 7:
• Accept four parameters: a new customer’s
Name, AreaCode, LocalNumber, and the
Nationality of all artists in whom the
customer maintains an interest.
20
import java.io.*;
import java.sql.*;
public class CustomerInsert {
public static void main(String[] args) {
if (args.length<4) {
System.out.println("insufficient data provided.");
return;
}
String varName = args[0];
String varAreaCode = args[1];
String varLocalNumber= args[2];
String varNationality = args[3];
}
To be transformed into a java bean
insertData(varName, varAreaCode, varLocalNumber, varNationality);
public static void insertData(String varName, String varAreaCode,
String varLocalNumber, String varNationality) {
System.out.println("Adding row for " + varName);
21
try {
//load JDBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String connString="jdbc:odbc:test";
System.out.println("Trying connection with "+connString);
Connection conn = DriverManager.getConnection(connString);
Statement stmt = conn.createStatement();
// Now check for duplicated data
String varSQL = "SELECT Name ";
String varWhere = "FROM CUSTOMER WHERE Name= '";
varWhere = varWhere + varName + "' AND AreaCode = '";
varWhere = varWhere + varAreaCode + "' AND PhoneNumber = '";
varWhere = varWhere + varLocalNumber + "'";
varSQL = varSQL + varWhere;
ResultSet rs= stmt.executeQuery(varSQL);
while (rs.next()) {
// if get here, there is duplicate data
System.out.println("Data Duplicates an existing customer. No change
Made.");
rs.close(); stmt.close(); conn.close(); return;
}
22
// OK to insert new data
varSQL = "INSERT INTO CUSTOMER (Name, AreaCode,
PhoneNumber)";
varSQL = varSQL + " VALUES ('" + varName + "', '";
varSQL = varSQL + varAreaCode + "', '";
varSQL = varSQL + varLocalNumber + "')";
int result = stmt.executeUpdate(varSQL);
if (result == 0) {
System.out.println("Problem with Insert");
rs.close();
stmt.close();
conn.close();
return;
}
C:\wan>javac CustomerInsert.java
C:\wan>java CustomerInsert bush 8900 7113685 us
C:\wan>java GeneralTable customer
23
Needs in Developing Web
Applications
• Create simple interactive
Web pages
• separate content generation
from presentation
• take advantage of reusable,
portable objects, simplifying
the maintenance of Web
applications.
• Support complex web sites
that are fully integrated
with enterprise-level
applications
24
What is JSP?
• Java Server Pages (JSP) is a technology that lets you mix
regular, static HTML with dynamically-generated HTML.
JSP lets you create the two parts separately. Here's an
example:
<html>
<%java.util.Date date=new java.util.Date();%>
<head>
<meat http-equiv="Content-Type" content="text/html;
charset=gb2312">
</head>
<body>
<p><b> Today is: </b></p>
<%=date.getYear()+1900%>
.<%=date.getMonth()+1%>
.<%=date.getDate()%>
25
</body></html>
JSP (Java Server Pages)
JSP: providing a means of creating dynamic
Web pages using HTTP, XML, and Java
• JSP only allows for Java coding, ASP only allows for
JavaScript or VBScript coding
• JSP inherits the power and portability of
Java
– Since Java is powerful and platform-independent,
so is JSP
– JSP Specifications are located at:
http://java.sun.com/products/jsp
26
JSP
• JSP technology separates content generation
from presentation and takes advantage of
reusable tags and objects, simplifying the
maintenance of your web applications.
• JSP technology provides the scripting ability
you need to create simple interactive Web
pages, or it scales to support complex web
sites that are fully integrated with enterprise
class applications.
27
• JSP and Servlets
– JSP are transformed into standard Java
code (in particular, Java servelet), then
they are compiled into bytecode (just like a
regular Java program)
• Similar to ASP but fundamentally different in
underlying technology
• Machine independent
• JSP: subclasses of the HttpServlet class
• Consequently, due to inheritance, you may embed
Java code enclosed by <% … %> directly into JSP
28
What are Java Servlets?
• Servlets are Java technology's answer to CGI
programming. They are programs that run on a Web
server and build Web pages.
• Building Web pages on the fly is useful for a number
of reasons:
• The Web page is based on data submitted by the
user. For example the results pages from search
engines are generated this way, and programs that
process orders for e-commerce sites do this as well.
• The data changes frequently. For example, a
weather-report or news headlines page
• The Web page uses information from corporate
databases or other such sources. For example, you
would use this for making a Web page at an on-line
store that lists current prices and number of items in
stock.
29
What are the Advantages of JSP?
• JSP vs. Pure Servlets.
– JSP doesn't give you anything that you
couldn't in principle do with a servlet. But it is
more convenient to write (and to modify!)
regular HTML than to have a zillion println
statements that generate the HTML.
– By separating the look from the content you
can put different people on different tasks:
your Web page design experts can build the
HTML, leaving places for your servlet
programmers to insert the dynamic content.
30
JSP vs. JavaScript
• JavaScript can generate HTML dynamically on
the client. This is a useful capability, but only
handles situations where the dynamic
information is based on the client's
environment.
• With the exception of cookies, HTTP and
form submission data is not available to
JavaScript.
• And, since it runs on the client, JavaScript
can't access server-side resources like
databases, catalogs, pricing information, and
the like.
31
– Web Server Applications
• To run JSP, the Web server must implement:
– Java servlet 2.1+, and
– Java Server Pages 1.0+
• Apache web server: not support servlets
• Apache TomCat: implemented the above
specifications
– Apache Tomcat
• Apache Tomcat: a common Web server
application a servlet processor
– http://jakarta.apache.org
– May run in conjunction with Apache (in a
production environment) or standalone (in a
development environment)
32
• Setting up Tomcat
for JSP
– Different ports
for Apache and
Tomcat
– Directory
structure
33