Java and Web-based Database Applications

1
Java and Web-based Database Applications

• Li-Yan Yuan
• University of Alberta

2
Contents

• How to Interface web to databases?
• Java JDBC
• Java Applets
• Java Servlets
• XML and Databases
• 6 Lab exercises
• the development of a web-based database
  applications using JDBC and Servlets

3
How to Interface Database to Web

• Database accesses
  how to access a database server through
  network?
• Interactive access
• Oracle sqlplus
• Embedded SQL and standard Interface
• embedded C
• host language standard SQL statements placed
  within application programs
• Oracle ProC/C
• Open Database Connectivity (ODBC)
• Java Database Connectivity (JDBC)

4

• Client-side Application Program Development Tools
• Power builder
• Developer 2000
• Visual Basic
• MS Access
• ...

5

• Web-based database applications
• Three tier system architectures for web-based
  database applications
  

Web Server
Client
Database Servers
6
How to Interface Database to Web ?

• CGI programs
• Development tools and Application Servers
• Database Vendors software packages
• Java Applets with JDBC
• Java Servlets with JDBC

7
Common Gateway Interface (CGI)

• a standard approach to interfacing external
  programs to web servers and/or databases
• ( HTML documents are static )
• a CGI program will be executed in real time and
  thus can output dynamic information
• it is basically the equivalent of letting the
  world run a program in your system

8
Static HTML Files
URL
?
9
Dynamic Creation of HTML Files
1. Client makes web request
2. Server invokes CGI program
4. Newly generated document is sent back to
client
3. CGI generates HTML document
10

• CGI (continued)
• Perl, TCL, C/C, Visual basic, Unix shell
• call of CGI programs
• URL ltgt CGI program
• input of CGI programs
• query string, path_info, environment variables
  (command line options )
• output of CGI programs
• the CGI program will generate MIME (multipurpose
  Internet Mail Extension) encode documents and
  send it back to the client who requests it.
• HTML, text, video, audio, reference

11
HTML Content-type text/html ltHTMLgt ltHEADgt
ltTITLEgtoutput of HTML from CGI scriptlt/TITLEgt
lt/HEADgt ltBODYgt ltH1gtSample outputlt/H1gt What do
you think of ltSTRONGgtthis?lt/STRONGgt
lt/BODYgt lt/HTMLgt
Reference Location /dir1/dir2/myfile.html
12

• The most important functions of CGI program is to
  implement two methods of transmitting information
  from a browser to a server
• GET
• if your form has methodGET, the program will
  receive the encoded form input the environment
  variable QUERY_STRING
• POST
• If your form has METHOD"POST" in its FORM tag,
  your CGI program will receive the encoded form
  input on stdin. The server will NOT send you an
  EOF on the end of the data, instead you should
  use the environment variable CONTENT_LENGTH to
  determine how much data you should read from stdin

13

• CGI
• Security concerns
• a client is capable of running a program in your
  computer system!!
• Packaged software
• One database connection per request
• relatively slow
• development cost
• Pool of database connections maintained by the
  database server

14
Development Tools and Application Servers

• Visual Basic, Development 2000,Power Builder
• Application Servers
• pre-forked threads or processes to pool database
  connections
• flexible middle tier packages with many different
  functions
• integration of heterogeneous data sources
• often with Server-side Java

15
Database Vendors Offering

• Oracle WebServer
• connects the clients requests to the database
  server
• IBM WebSphere
• full fledged Web server
• dynamic web page generation
• Java Servlet

16
Java Applet

• Java applets are program modules that extend web
  browsers, and therefore can be used for web users
  to access the database servers.
• Java applets use the JDBC driver to connect to
  the database.

17
Java JDBC
internet
Database Servers
Database Clients
18
Web-Database and Java Applet
Java Applets reside in Web server
Web Server
Client
Database Servers
19
Web-Database and Java Applet
Web Server
Client
Client must download Applets from web server
first and then connect to the db server
Database Servers
20
Java Applet

• a real three tier system
• persistent database connection
• computation carried out in clients machine
• fast and efficient
• large files to be downloaded
• no dynamic HTML files
• security concerns
• Java enable web browser

21
Java Servlet Approach

• Java Servlets are modules that extend
  request/response oriented servers such as Java
  enabled web servers
• Servlets are not tied to a specific client-server
  protocol but they are most commonly used with
  HTTP and the word "Servlet" is often used in the
  meaning of "HTTP Servlet".

22
Web-Database and Java Servlets
Java Servlets extend Web server
Web Server
Dynamic HTML files
Client
Database Servers
23
Servlets

• Servlets are developed to process POST and GET
  methods, as well as to access the database server
• Servlets can be embedded in many web servers
• There is no particular requirement for clients
  web browsers

24
Servlets Vs CGI

• A Servlet does not run in a separate process.
  This removes the overhead of creating a new
  process for each request
• A Servlet stays in memory between requests. A CGI
  program (and probably also an extensive runtime
  system or interpreter) needs to be loaded and
  started for each CGI request.
• There is only a single instance which answers all
  requests concurrently. This saves memory and
  allows a Servlet to easily manage persistent
  data.

25
Java JDBC

• What Is JDBC?
• JDBC stands for Java Database Connectivity.
• It provides a standard API for accessing
  virtually any data sources, from relational
  databases to spreadsheets and flat files.
• What Does JDBC Do?
• establish a connection with a database
• send SQL statements
• process the results.

26
JavaSoft Framework

• the JDBC driver manager,
• the JDBC driver test suite, and
• the JDBC-ODBC bridge.

27
JDBC Driver Types

• JDBC-ODBC bridge plus ODBC driver
• Javas JDBC driver
• Native-API partly-Java driver
• Oracles OCI driver
• JDBC-Net pure Java driver
• Native-protocol pure Java driver
• Oracles thin driver

28
JDBC Connection

• A connection object represents a connection to a
  database
• To establish a connection, one needs to specify
• JDBC driver
• JDBC URL
• user name
• password
• A connection can be used to
• send SQL statements
• set transactions isolation level

29
String drivername "oracle.jdbc.driver.OracleDriv
er" String dbstring"jdbcoraclethin_at_luscar.cs.
ualberta.ca1521csdb"
/ To connect to the specified database /
private Connection getConnected( String
drivername,
String dbstring,
String
username,
String password )
throws Exception Class drvClass
Class.forName(drivername)
DriverManager.registerDriver((Driver)
drvClass.newInstance()) return(
DriverManager.getConnection(dbstring,username,pass
word))
30

• JDBC Driver String
• specify the driver name
• a JDBC driver shall be registered with JavaSoft
• JDBC URL ( database connection string)
• jdbcsubprotocl_at_url_of_machineportdb_name
• Driver Manager
• Keep tracking of available JDBC drivers
• A Java API can load any number of JDBC drivers,
  and it will automatically choose a suitable
  driver for the given database connection string.
• Establish connections

31
Statement

• A Statement object is used to send SQL statements
  to a database
• Statement
• PreparedStatement
• CallableStatement
• The statement interface provides basic methods
  for executing SQL commands and retrieving results

32
Creating and Closing Statements
Connection con DriverManager.getConnection(url,"
sunny","") Statement stmt con.createStatement()

Stmt.close( )
33
Executing Statements
ResultSet rs stmt.executeQuery("SEL
ECT a, b, c FROM my_table")
Int result stmt.executeUpdate(INSERT
INTO your_table values(a,b,c)
34
ResultSet

• A ResultSet is an object that contains all the
  information of the result table returned by
  executeQuery
• number of columns
• column names, column types
• rows
• Rows and cursor
• a cursor pointing the current row
• move forward or backward

35
ResultSet

• getMetaData()
• to get all the meta-data of the table
• getXXX(column name), getXXX(3)
• to retrieve column values for the current row
• to convert the underline data to the specified
  Java type
• can be used to retrieve BLOB, CLOBC, LONG RAW,
  etc, using Java Streams

getByte, getShort, getInt, getLong, getFloat,
getDouble, getBigDecimal, getBoolean, getString,
getBytes, getDate, getTime, getTimestamp,
getAsciiStream, getUnicodesStream, getBinaryStream
, getObject
36
// execute the query and obtain the result set
ResultSet rset stmt.executeQuery(query) //
get the meta data of the result set
ResultSetMetaData rsetMetaData
set.getMetaData() int no_columns
rsetMetaData.getColumnCount() // print the head
line System.out.println() for ( int i 1 i lt
no_columns i ) String label
rsetMetaData.getColumnLabel(i)
System.out.print(label) for ( int j 0
j lt no_columns 10 i )
System.out.print("-") System.out.println()
37
// display all the result tuples while
(rset.next()) for ( int i 1 i lt
no_columns i ) String s
rset.getString(i)
System.out.print(s) for (int j 0 j lt 10
- s.length() j ) System.out.print(" ")
System.out.println() // close
the result set, the statement, and the connection
rset.close() stmt.close( )
38
PreparedStatement

• The PreparedStatement interface inherits from
  Statement and differs from it in two way
• it contain an SQL statement that has already been
  compiled.
• It may have one or more IN parameters (
• ? -- whose value will be supplied by appropriate
  methodss)

39
java.io.File file new java.io.File("/tmp/data")
int fileLength file.length()
java.io.InputStream fin new java.io.FileInputSt
ream(file) java.sql.PreparedStatement pstmt
con.prepareStatement( "UPDATE Table5
SET stuff ? WHERE index 4")
pstmt.setBinaryStream (1, fin, fileLength)
pstmt.executeUpdate()
40
CallableStatement

• A CallableStatement object provides a way to call
  stored procedures in a standard way for all
  DBMSs.
• A CallableStatement can return one ResultSet or
  multiple ResultSet objets. Multiple ResultSet
  objects are handled using operations inherited
  from Statement.

41
Mapping SQL and Java Types

• SQL types, Java types, and JDBC types
• JDBC Class types ( java.sql.Types)
• CHAR, VARCHAR,
• BIGINT, SMALLINT, INTEGER,
• DECIMAL, DOUBLE, FLOAT, NULL, NUMERIC, REAL,
  CHAR,VARCHAR,
• BIT, BINARY
• BLOB, CLOB, ONGVARBINARY,LONGVARCHAR
• DATE, TIME, TIMESTAMP,

42
Using Transactions

• Auto-commit Transaction Mode (default)
• each individual SQL statement is treated as a
  transaction and will be automatically committed
  after it is completed.
• Disabling Auto-commit Mode
• conn.setAutoCommit(false)
• Committing a Transaction

43
Con.setAutoCommit(false) PreparedStatement
updateSales con.prepareStatement( "UPDATE
COFFEES SET SALES ? WHERE COF_NAME LIKE
?") updateSales.setInt(1, 50) updateSales.setStr
ing(2, Edmonton) updateSales.setInt(1,
70) updateSales.setString(2, Toronto) con.com
mit() con.setAutoCommit(true)
44
Two Sample Programs

• Student.java
• InsertLobsOracle.java

45
Lab Exercise 1
A simple database consists of the following two
tables user_account(user_name, password,
user_type ) customer_info(
user_name,last_name,first_name,title,email,phone
) that can be used for an on-line company to
track their customers and to control accesses to
their on-line services. You are asked to design
a simple Java application program t
register a new customer, retrieve
customer information with a given user_name, and
modify an existing customer info. You
may create and populate the database using your
favorite approaches such as Oracle Sql-Plus with
an .sql file. But the application program must
use JDBC to access the database. You don't have
to implement a fancy GUI interface. A simple
text interface will do the job.
46
Lab Exercise 2
We know how to access to any database in the
netland using simple Java programs now and it is
time to consider something non-trivial. Consider
the following table photos( photo_id
integer,title varchar(20), place
varchar(20),image long raw ) and a few pictures
in a given directory. Implement a Java program
that insert all the pictures into the table. The
program shall search the directory to find a file
name and generate a valid photo_id, which
usually is the smallest integer that has not be
used in the table. The user will then be
prompted to provide the title and place for the
file. The program terminates when all the images
in the directory have been inserted into the
table. You have to use JDBC's PrepatedStatement
to insert data items of blob, clob, and long raw
types.
47
Java Servlets

• What is Servlet?
• Servlets are modules that extend
  request/response-oriented servers, such as
  Java-enabled web servers, similar to the way
  Applets extend web browsers.
• Servlets can be used to generate dynamic HTML
  files and documents of many other formats, and
  thus can be used to develop web-based database
  applications.
• Servlets has no graphical user interface.
• Many web servers DO support Java Servlets.

48
Architecture of Servlets Package

• Servlet Interface

Servlet
Generic Servlet
HTTPServlet
MyServlet
A Servlet is an instance of of MyServlet -- a
class that implements HTTPServlet
49
Servlet Life cycle

• Birth
• A server application loads a Servlet class and
  create an instance of it by calling the default
  no-argument constructor, which gives the birth to
  a Servlet.
• Initialization
• init( getServletConfig())
• Service
• service( request, response)
• multi treads
• Retirement
• destroy( )

50
(No Transcript)
51
public class SimpleServlet extends HttpServlet
/ Handle the HTTP GET method by building a
simple web page. / public void doGet
(HttpServletRequest request, HttpServletResponse
response) throws ServletException,
IOException PrintWriter
out String title "Simple
Servlet Output" // set
content type and other response header fields
first response.setContentTyp
e("text/html") / /then
write the data of the response
out response.getWriter()
out.println("ltHTMLgtltHEADgtltTITLEgt")
out.println(title)
out.println("lt/TITLEgtlt/HEADgtltBODYgt")
out.println("ltH1gt" title "lt/H1gt")
out.println("ltPgtThis is output
from SimpleServlet.")
out.println("lt/BODYgtlt/HTMLgt")
out.close()
52
Interacting with Clients

• Client requests are handled by service method
• service supports standard HTTP client request by
  dispatching each request to a method
• doGet, doPost, doPut, doTrace
• Each dispatching method has two object arguments
• Request encapsulate the input data from the
  client request
• getParameter, getXXX
• Response generate response to the client
• getWriter()
• getOutputStream()
• Treading

53
Source Code for HelloWorld Example import
java.io. import javax.servlet. import
javax.servlet.http. public class HelloWorld
extends HttpServlet public void
doGet(HttpServletRequest request,
HttpServletResponse response) throws
IOException, ServletException
response.setContentType("text/html")
PrintWriter out response.getWriter()
out.println("lthtmlgt") out.println("ltbodygt
") out.println("ltheadgt")
out.println("lttitlegtHello World!lt/titlegt")
out.println("lt/headgt")
out.println("ltbodygt") out.println("lth1gtHe
llo World!lt/h1gt") out.println("lt/bodygt")
out.println("lt/htmlgt")
Sample from Java at http//luscar.cs.ualberta.ca
8080/
54
Source Code for Request Info Example import
java.io. import javax.servlet. import
javax.servlet.http. public class RequestInfo
extends HttpServlet public void doGet(
HttpServletRequest request,
HttpServletResponse response) throws
IOException, ServletException
response.setContentType("text/html")
PrintWriter out response.getWriter()
out.println("lthtmlgt") out.println("ltbodygt
") out.println("ltheadgt")
out.println("lttitlegtRequest Information
Examplelt/titlegt") out.println("lt/headgt")
out.println("ltbodygt")
out.println("lth3gtRequest Information
Examplelt/h3gt") out.println("Method "
request.getMethod()) out.println("Request
URI " request.getRequestURI())
out.println("Protocol " request.getProtocol())
out.println("PathInfo "
request.getPathInfo())
out.println("Remote Address "
request.getRemoteAddr())
out.println("lt/bodygt")
out.println("lt/htmlgt")
55
/ We are going to perform the same
operations for POST requests as for GET
methods, so this method just sends the request to
the doGet method. / public void doPost(
HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException
doGet(request, response)
56
Servlet Communication

• A Servlet may need to access other resources,
  such as other Servlets and HTML pages at the same
  or different servers
• network resources use HTTP request
• local resources
• setAttribute
• getAttribute
• remvoeAttribute