Database and Web Interaction: Designing Dynamic Web Pages

1. Project 2:Database and the Web Hyun J. Moon and Carlo Zaniolo University of California, Los Angeles CS143, Fall 2004

2. Project 2: Database and the Web • Learn how to design web pages that interact with a database • Due on Dec 7th (Tuesday) • Techniques: • JDBC: Java Database Connectivity API • SQL: RDBMS query language • Servlet: Java program running on a Web Server to generate dynamic content • HTML: standard web language • XML: eXtensible Markup Language: the next generation web language

3. Project 2: Database and the Web (cont’d) • Servers: • DB2 Database Server • Apache Tomcat Java Server (supports both Servlet and JSP) • Programming language: • Java • Editor: UNIX: emacs/xemacs on SEASNET; Windows: JBuilder, JDeveloper, TextPad, … • Compiler: javac, java from SUN JDK

4. Project 2: Database and the Web (cont’d) • Main Pages and Web Flow: online interaction with a music database • P1 • P2 • P3 • P4 • P5 • All HTML pages dynamically generated from Servlets (except P1)

5. Java Servlet Request Processing Client http://landfair.seas.ucla.edu:8080/Copycat.html Browser Tomcat App. Server HTTP Server HTML Internet JVM servlet/Copycat Copycat.class http://landfair.seas.ucla.edu:8080/servlet/Copycat=> ${HOME}/tomcat/webapps/ROOT/WEB-INF/classes/Copycat.class

6. Other Dynamic Web Environment • CGI: Common Gate Interface, can be written in any language • ASP (active server pages): by Microsoft, use visual basic, hard to reuse • JSP (Java Server Pages): normally used as web interface programming with Java Servlet as backend • Coldfusion: by Dreamweaver • php, python, etc.

7. CGIs vs Servlets • CGI programs • Separate process for each CGI program • Mod_perl and FastCGI improves performance of CGI but not to level of servlets • Have difficult time maintaining state across requests • Servlets • Run under single JVM (better performance) • Servlets loaded into memory with first call, and stay in memory • Have built-in state preservation methods • Java's inherent security • Proprietary source code can be retained by only giving up *.class files to the server

8. Communicate with Java Servlet with FORMS • FORMS are defined in HTML pages as: <formmethod=get action=servlet/Copycat> <inputtype=text name = some_text size = 40> <inputtype=submit value="Submit"> </form> • Actions: send request to a HTTP server • Two common methods: POST and GET

9. Forms: Method: GET and POST • GET: Data is sent to the server appended to the URL • can only send a small amount of information • The exchanged data are displayed in the URL as well http://landfair.seas.ucla.edu:8080/servlet/Copycat?some_text=test • The URL can be bookmarked • POST: Data is sent to the server in a separate message • with unlimited length of data, and the exchange is invisible to the user • Secure (e.g., for credit card numbers) • Can’t boomark

10. Forms: INPUT • Input tags are used to place many form elements • Input tags have several types • textbox • textarea • checkbox • radio • dropdown • filebox • The name and value of of the input is like a pair of (parameter-name, parameter-value) • No end tag

11. Forms: Buttons • Simple push-down controls • 3 flavors • <input type=submit>: cause all form data to be sent to the server • <input type=reset>: cause the form data to be set to the default • <input type=button>: used for JavaScript • Button images: can also work as a submit button • <input type=image src=coolbutton.gif name=coolbutton>

12. Forms: hidden • Send the information to the server and the user will not notice • Normally used to send control information to the server process <form action=servlet/showAlbum> <input type=hidden name=albumname value=choke> <input type=reset> <input type=submit> </form>

13. What a Servlet Does • Process requests from clients • doGet: process get request • doPost: process post request • Response to the clients • Return HTML contents to the client • Communicate through streams: • Servlets normally retrieve most of their parameters through an input stream, and send their responses using an output stream: ServletInputStream in = request.getInputStream (); ServletOutputStream out = response.getOutputStream ();

14. Structure of a Servlet:HelloWorld 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("<html>"); out.println("<body>"); out.println("<head>"); out.println("<title>Hello World!</title>"); out.println("</head>"); out.println("<body>"); out.println("<h1>Hello World!</h1>"); out.println("</body>"); out.println("</html>"); } } Client Side: <html> <body> <head> <title>Hello World!</title> </head> <body> <h1>Hello World!</h1> </body> </html> http://landfair.seas.ucla.edu:8080/servlet/HelloWorld

15. Fundamental Parts of a Servlet 1. import javax.servlet.*; and import javax.servlet.http.*; - packages of servlet classes that implement the Java Servlet API 2. public class HelloWorld extends HttpServlet { - extends the HTTPServlet class 3. init() -initializes servlet after loading into memory - place to perform operations done only once at start-up - reading a current properties - clearing log files, notifying other services that the servlet is running 4. service(), doGet(), doPost() - this is where work is done - each time the servlet is called a new thread of execution begins - input is passed to the Java code via either HTTP GET or POST commands 5. destroy() - executed when server engine calls servlet to terminate - used to flush I/O, disconnect from database

16. Get Parameters • Sample forms: <formmethod=post action=servlet/Copycat> <inputtype=text name = some_text size = 40> <inputtype=hidden name = songname value=“natural”> <inputtype=submit value="Submit"> </form> • Process it in a Servlet: • String method = request.getMethod (); // e.g. post • String some_text = request.getParameter (“some_text") ; • String songname = request.getParameter (“songname");

17. Sample Parameter Handling Copycat.html From the web public class Copycat extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println("<html>"); out.println("<head>"); String title = "Copycat Servlet"; out.println("<title>" + title + "</title>"); out.println("</head>"); out.println("<body bgcolor=white>"); out.println("<h1>" + title + "</h1>"); String studentSaid = request.getParameter("some_text"); out.println("The student said: " + studentSaid + "<br>"); out.println("</body>"); out.println("</html>"); } } Modify and add your JDBC stuff!

18. JDBC: Dynamic Queries • Prepared statement allows queries to be compiled and executed multiple times with different arguments: PreparedStatement pStmt = conn.prepareStatement( “select albumname from album where year= ? and musicianName= ?”); pStmt.setInt(1, 1990); pStmt.setString(2, “BJork"); pStmt.executeQuery();

19. Java Servlet at SEAS • Port Assignment • Each student is assigned two ports for running Java Servlets on SEAS • Please make sure that you only use the ports assigned to you • Two ports • Connector Port: internal communication • Server Port: access from the web: e.g. (replace 8080 with your own server port) HTML: http://landfair.seas.ucla.edu:8080/ Servlet: http://landfair.seas.ucla.edu:8080/servlet/

20. Tomcat • Tomcat is a free, open open-source implementation of Java Servlet and Java Server Pages technologies • Tomcat can be used as a small stand stand-alone server for testing servlets and JSP pages, or can be integrated into the Apache Web server • Tomcat is free! Download at: http://jakarta.apache.org • Besides Tomcat, there are other systems that support servlet: • Allaire jrun, IBM Websphere, Bea WebLogic, Borland AppServer, Oracle App Server

21. Setup Tomcat on SEASNET • login to your seas project account • setup libraries and paths • source /u/cs/class/cs143v/cs143vta/bin/all.env: • Put in your .cshrc file (or .login): • source /u/cs/class/cs143v/cs143vta/bin/all.env • Setup Tomcat directories • ${HOME}/tomcat: home directory of tomcat • ${HOME}/logs directory stores the log files generated by the server • ${HOME}/tomcat/webapps/ROOT: your HTML file • ${HOME}/tomcat/webapps/ROOT/WEB-INF/classes: your Servlet classes • ${HOME}/conf: server.xml, tomcat-users.xml, web.xml, and jk2.properties

22. Configure Tomcat Ports • Modify ${HOME}/tomcat/conf/server.xml file: • Connector port: • Server port="8005" shutdown="SHUTDOWN" debug="0"> => • Server port="11033" shutdown="SHUTDOWN" debug="0"> • Server Port: • <Connector className="org.apache.coyote.tomcat4.CoyoteConnector" port="8080“ => • <Connector className="org.apache.coyote.tomcat4.CoyoteConnector" port=“11034“

23. Startup and Shutdown Tomcat • Startup tomcat: • startup.sh  • Stop tomcat: • Ctr+C on your tomcat running screen • If you run tomcat on background, run • shutdown.sh  • Open another window for your coding • Always shut down your tomcat before you log out

24. Test Servlet • After Tomcat is up, you can test Servlet: • cp /w/class.01/cs/cs143v/cs143vta/project/Copycat.html to: • ${HOME}/tomcat/webapps/ROOT • ${HOME} is your home directory (pwd after you first login) • cp /w/class.01/cs/cs143v/cs143vta/project/Copycat.java to: • ${HOME}/tomcat/webapps/ROOT/WEB-INF/classes/ • Compile your Java file: • javac Copycat.java • If httpServlet error happened, you must have not source all.env • Open your URL: • http://<machinename>:<port>/<filename>.htmle.g.: http://landfair.seas.ucla.edu:11256/Copycat.html

25. Debug Your Code • Servlet not easy to debug! • Return status code: • sendError method in class HttpResponse • Log errors written into servlet log file • public void servletContext.log(string msg) • public void servletContext.log(Exception e, String msg)

26. Debug Your Code – Common Status Codes Constant:Code:Meaning • SC_OK: 200: client request successful • SC_NO_CONTENT:203: the request succeed, but without new response to return • SC_NOT_UNAUTHORIZED:401: unauthorized • SC_NOT_FOUND: 404: not found on the server • SC_INTERNAL_SERVER_ERROR: 500: the servlet is wrong

27. What to Submit • SQL Script file: db.sql • SQL script updated from Project I to include additional tables if needed • Project pages: P1-P5 • Servlet generated HTML file: p1html.html, p2html.html, etc. • Use “View Source” from your web browser to copy and paste the files • readme.txt file: Class Name: CS143, LEC.1 Name(Last, First): Student SID: Project login account: Database you use(STUDBL, STUDBW, or STUDBN): Servlet PORT numbers: Connector Port:____ Server Port:____

28. Extra Credit: XML (10 percent) • For page P1, add a checkbox "Display as XML". • If that checkbox is checked, a page of XML document (instead of HTML) is displayed ( through IE only). • The document you generated must satisfy a DTD and will be validated using the following XML Validator. You can define your own DTD. • Sample XML document: <albums> <album> <albumname>Choke</albumname> <musicianname></musicianname> … </album> … </albums>

29. What’s XML • XML: EXtensible Markup Language • A W3C standard widely accepted Similarity between HTML: both are markup languages Difference from HTML: • It’s extensible: a framework to define mark-up languages • Separate syntax from semantics. • XML +XSLT = HTML

30. A Sample XML Document starting tag <orders> <order id="1" itemcount="3"> <customer id="100"> <lastname>Skonnard</lastname> <firstname>Aaron</firstname <phone>(888)888-8888</phone </customer> <item id="5000" type="software"> <manuf>Microsoft</manuf> <pname>Microsoft Money 99</pname> <price>39.99</price> <quant>1</quant> <total>39.99</total> </item> </order> </orders> ending tag

31. HTML Editors • Microsoft Frontpage/Word • Don’t use it! Tons of extra tags make your servlet a nightmare! • Macromedia Homesite (I use this one) • free 30-day trial • Macromedia Dreamweaver • free 30-day trial • Altova XMLSpy (for both XML and HTML) • can be used for testing extra credit question • free 30-day trial • UNIX: emacs/xemacs

32. Documentations • Java Servlet APIs • DB2 Documentation • SQL Reference • Java APIs • HTML Beginners Guide • Dave’s HTML Guide • HTML 4.0 Reference

33. Project SEASNET Account • 3 machines to login • landfair.seas.ucla.edu • lindbrook.seas.ucla.edu • westholme.seas.ucla.edu • To decide which machine you need to login: LAST_2_DIGITS_OF_YOUR_STUDENT_ID mode 3

34. Two Options for JDBC Driver • Option I. use .app. driver instead of .net. driver, e.g.: • Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); and in your JDBC URL, use • jdbc:db2:studbw, (if you login to westholme), or • jdbc:db2:studbn, (if you login to lindbrook), or • jdbc:db2:studbl, (if you login to landfair). • i.e., the hostname is omitted. • Option II: or use .net. driver: • Class.forName("COM.ibm.db2.jdbc.net.DB2Driver"); • but use the JDBC URL as: • jdbc:db2://landfair.seas.ucla.edu/studbw, or jdbc:db2://landfair.seas.ucla.edu/studbn, or jdbc:db2://landfair.seas.ucla.edu/studbl. • i.e., the hostname is always landfair.seas.ucla.edu no matter which machine you login. The database name still corresponds to the machine you login.

35. Project 2: 3-Tier Architecture JDBC Database Server Servlets HTTP/Application Specific Protocol Network Client Client Client Application/HTTP Server (Tomcat)

36. Project 1: 2-Tier Architecture Database Server Client Client Client JDBC Network

37. Good Luck! • Start early! • Great experience for your career!