Servlets and JDBC

1
Servlets and JDBC
2
Servlets
3
A form
4
The HTML source
lthtmlgt ltheadgt lttitlegtChapter
1lt/titlegt lt/headgt ltbodygtltfont size"4"gt
ltcentergtPlease enter your name and password
then press startltbrgt ltform
method"GET" action"http//localhost8080/servlet
/GetDemo" gt Name ltinput
name"uname" value"" type"text" size"20"gt
ltbrgt Password ltinput
name"userpw" value"" type"password" size10gt
ltinput value"Start" type"submit"
gt ltbrgt lt/formgt lt/centergt
lthrgt lt/bodygt lt/htmlgt
5
A servlet

• import java.io.
• import java.net.
• import javax.servlet.
• import javax.servlet.http.
• public class GetDemo extends HttpServlet
• protected void processRequest(HttpServletReques
  t request,
• HttpServletResponse response)
• throws ServletException, IOException
• response.setContentType("text/html")
• PrintWriter out response.getWriter()
• String username request.getParameter("un
  ame")
• String password request.getParameter("us
  erpw")

6

• out.println("ltHTMLgt")
• out.println("ltHEADgtltTITLEgtGetDemo
  Outputlt/TITLEgtlt/HEADgt")
• out.println("ltBODYgt")
• out.println("Hello " username
  "ltbrgt")
• out.println("Your password was "
  password "ltbrgt")
• out.println("lt/BODYgt")
• out.println("lt/HTMLgt")
• out.close()
• protected void doGet(HttpServletRequest
  request, HttpServletResponse response)
• throws ServletException, IOException
• processRequest(request, response)
• protected void doPost(HttpServletRequest
  request, HttpServletResponse response)
• throws ServletException, IOException
• processRequest(request, response)
• public String getServletInfo() return
  "Short description"

7
Servlet Container

• You need a servlet container for this. E.g.
  Apache Tomcat.
• Download the preconfigured Apache Tomcat from the
  course website.
• Unzip
• Will create two directories, "apache-tomcat-6.0.18
  " and "development"
• Starting/Stopping Tomcat
• If you are working on Windows, open file
• apache-tomcat-6.0.18\bin\startup.bat
• and modify line
• set JAVA_HOMEC\Program Files\Java\jdk1.5.0_09
• to reflect your local path of java jdk. (Do the
  same for the shutdown.bat)
• If you are working on the lab machines (Linux)
  execute first
• chmod x apache-tomcat-6.0.18/bin/.sh

8
Servlet Container

• Then start Apache Tomcat by double clicking on
• apache-tomcat-6.0.18\bin\startup.bat
• (In Windows)
• or execute
• apache-tomcat-6.0.18/bin/startup.sh
• (in Linux)
• To stop Tomcat execute the shutdown.bat in
  Windows, shutdown.sh in Linux.

9
Servlet Container

• Your HTML files should go under
• apache-tomcat-6.0.18/webapps/ROOT
• (In Linux)
• apache-tomcat-6.0.18\webapps\ROOT
• (In Windows)
• Then to open an HTML file in the browser, specify
  e.g.
• http//localhost8080/GetExample.html
• Your Java classes should go under
• apache-tomcat-6.0.18/webapps/ROOT/WEB-INF/classes
• (In Linux)
• apache-tomcat-6.0.18\webapps\ROOT\WEB-INF\classes
• (In Windows)
• Any jar file (such as classes12.jar of Oracle
  JDBC) should go under
• apache-tomcat-6.0.18/webapps/ROOT/WEB-INF/lib
• (In Linux)
• apache-tomcat-6.0.18\webapps\ROOT\WEB-INF
• (In Windows)

10
Servlet Container

• Copy/sftp the /opt/oracle/jar/classes12.jar to
  your development directory.
• To compile, cd to the 'development' directory and
  execute the following
• javac -d ../apache-tomcat-6.0.18/webapps/ROOT/WEB-
  INF/classes -classpath ../apache-tomcat-6.0.18/lib
  /servlet-api.jarclasses12.jar .java
• (In Linux)
• javac -d ..\apache-tomcat-6.0.18\webapps\ROOT\WEB-
  INF\classes -classpath ..\apache-tomcat-6.0.18\lib
  \servlet-api.jarclasses12.jar .java
• (In Windows)
• These commands will copy your .class files to the
  ..\apache-tomcat-6.0.18\webapps\ROOT\WEB-INF\class
  es directory.

11
Connectionmanager

• import java.sql.
• import java.util.
• public class ConnectionManager
• private static ConnectionManager instance
  null
• private Stack connections
• private ConnectionManager ()
• connections new Stack()
• try
• DriverManager.registerDriver (new
  oracle.jdbc.driver.OracleDriver())
• catch (Exception ex)
• System.out.println(ex)
• public static ConnectionManager getInstance()
  
• if (instance null) instance new
  ConnectionManager()
• return instance

12
Connectionmanager
public Connection getConnection()
Connection conn null if
(!connections.empty()) conn
(Connection) connections.pop() else
//No one left in the stack, create a new one
try conn
DriverManager.getConnection
("jdbcoraclethin_at_localhost1521TEACH",
"user", "passw") catch
(SQLException ex)
System.out.println("SQLException " ex)
return conn
public void returnConnection(Connection
conn) if (conn ! null)
connections.push(conn)
This is assuming the ssh tunel is used.
Otherwise, if running on the lab machines,
replace by ("jdbcoraclethin_at_oracle.csc.uvic.ca
1521TEACH", "user", "passw")
13
SSH Tunnel

• In order to be able to connect to ORACLE remotely
  from your machine at home execute (in your
  machine)
• ssh2 -L 1521oracle.csc.uvic.ca1521
  ltyourusernamegt_at_u-knoppix.csc.uvic.ca

Might be called just ssh in your machine.
Use some other lab machine.
14
Insert Servlet
import java.io. import java.net. import
javax.servlet. import javax.servlet.http. impo
rt java.sql. public class Insert extends
HttpServlet protected void
processRequest(HttpServletRequest request,
HttpServletResponse response) throws
ServletException, IOException
response.setContentType("text/htmlcharsetUTF-8")
PrintWriter out response.getWriter()
String title
request.getParameter("title") String
year request.getParameter("year")
String length request.getParameter("length")
String studioName request.getParameter("st
udio") String statementString
"INSERT INTO Movie(title, year, length,
studioName) " "VALUES( '"
title "'," year "," length ",'"
studioName "')"
15
Connection conn ConnectionManager.getIns
tance().getConnection() try
Statement stmt conn.createStatement()
stmt.executeUpdate(statementString)
stmt.close()
catch(SQLException e) out.println(e)
ConnectionManager.getInstance().returnConnection(c
onn) protected void doGet(HttpServletReq
uest request, HttpServletResponse response)
throws ServletException, IOException
processRequest(request, response)
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws
ServletException, IOException
processRequest(request, response)
public String getServletInfo() return "Short
description"
16
The HTML source
lthtmlgt ltheadgt lttitlegtInsertlt/titlegt
lt/headgt ltbodygtltfont size"4"gt
ltcentergtPlease enter your name and password then
press startltbrgt ltform method"GET"
action"http//localhost8080/servlet/Insert" gt
Title ltinput type"text"
name"title" value"" /gt ltbrgt
Year ltinput type"text" name"year" value"" /gt
ltbrgt Length ltinput type"text"
name"length" value"" /gt ltbrgt
Studio ltinput type"text" name"studio" value""
/gt ltbrgt ltinput type"submit"
value"Start" gt ltbrgt lt/formgt
lt/centergt lthrgt lt/bodygt lt/htmlgt

17
MovieServlet1

• import java.io.
• import java.net.
• import javax.servlet.
• import javax.servlet.http.
• import java.sql.
• public class MovieServlet1 extends HttpServlet
• protected void processRequest(HttpServletReque
  st request, HttpServletResponse response)
• throws ServletException, IOException
• response.setContentType("text/htmlcharset
  UTF-8")
• PrintWriter out response.getWriter()
• Connection conn ConnectionManager.getInstance
  ().getConnection()
• try Statement stmt conn.createStatemen
  t()
• ResultSet rset
  stmt.executeQuery(
• "SELECT title, year "
• "FROM Movie")

18

• out.println("lttablegt")
• while (rset.next())
• out.println("lttrgt")
• out.print (
• "lttdgt"rset.getString("title")"lt/tdgt"
• "lttdgtltA href\"http//localhost8080/servlet/Movi
  eServlet2?year" rset.getString("year")"\"gt"rs
  et.getString("year")"lt/Agtlt/tdgt")
• out.println("lt/trgt")
• out.println("lt/tablegt")
• stmt.close()
• catch(SQLException e) out.println(e)
• ConnectionManager.getInstance().returnConnecti
  on(conn)
• protected void doGet(HttpServletRequest
  request, HttpServletResponse response)
• throws ServletException, IOException
• processRequest(request, response)
• protected void doPost(HttpServletRequest
  request, HttpServletResponse response)
• throws ServletException, IOException

19
(No Transcript)
20
(No Transcript)
21
MovieServlet2

• import java.io.
• import java.net.
• import javax.servlet.
• import javax.servlet.http.
• import java.sql.
• public class MovieServlet2 extends HttpServlet
• protected void processRequest(HttpServletReque
  st request, HttpServletResponse response)
• throws ServletException, IOException
• response.setContentType("text/htmlcharset
  UTF-8")
• PrintWriter out response.getWriter()
• String year request.getParameter("year")
• Connection conn ConnectionManager.getIns
  tance().getConnection()
• try Statement stmt conn.createStatemen
  t()
• ResultSet rset
  stmt.executeQuery(
• "SELECT title, year "
• "FROM Movie "
• "WHERE year " year)

22

• out.println("lttablegt")
• while (rset.next())
• out.println("lttrgt")
• out.print (
• "lttdgt"rset.getString("title")"lt/tdgt"
• "lttdgt"rset.getString("year")"lt/tdgt")
• out.println("lt/trgt")
• out.println("lt/tablegt")
• stmt.close()
• catch(SQLException e) out.println(e)
  
• ConnectionManager.getInstance().returnConn
  ection(conn)
• protected void doGet(HttpServletRequest
  request, HttpServletResponse response)
• throws ServletException, IOException
• processRequest(request, response)
• protected void doPost(HttpServletRequest
  request, HttpServletResponse response)
• throws ServletException, IOException