95-702 Organizational Communication & Distributed Object Technologies Carnegie Mellon UniversityLab 2 Due: Monday, February 14, 200595-702 Organizational Communication & Distributed Object Technologies Carnegie Mellon University Lab 2 Due: Monday, February 14, 2005Topics: Servlets, XML DOM, JNDI, JDBC, RDBMS In lab 1 you worked with the Simple API for XML (SAX) to read RSS news feeds. In this lab you will work withXML’s Document Object Model to read and process documents which contain schedules. Schedule documents arenot as prevalent on the web as are RSS documents. It is anticipated that the web of the future will contain a widevariety of data sources in XML. The intent of this lab is to help you prepare for that future. Your client application will read from static schedule documents (stored in local files and on the web) and fromdocuments dynamically generated from a database.Part I Web/Database Integration Exercise Two Questions at 10 Points EachUsing the PointBase RDBMS included with Sun's App server============================================Start the PointBase server==================D:\Sun\AppServer\pointbase\tools\serveroption>startserverServer started, listening on port 9092, display level: 0 ...>Place build.xml in a directory called MyTestDB==================================<?xml version="1.0" ?><!-- A minimal build.xml to populate a PointBase database. This is derived from the build.xml in the J2EE Tutorial.--><project name = "studentDB" default="create-db_common" basedir="."> <property file="build.properties"/> <path id="db.classpath"> <fileset dir="${db.root}/lib"> <include name="*.jar"/> </fileset> </path> <target name="create-db_common" depends="init" description="Create database tables and populate database." > <java classname="com.pointbase.tools.toolsCommander" fork="yes" > <jvmarg line="${db.jvmargs}" /> <arg line="${db.driver} ${db.url} ${sql.script} ${db.user} ${db.pwd}" /> <classpath refid="db.classpath" /> </java> </target> <target name="init"> <tstamp/> </target></project>Include the file build.properties in the same directory195-702 Organizational Communication & Distributed Object Technologies Carnegie Mellon University ======================================j2ee.home=D:/Sun/AppServersunone.home=${j2ee.home}domain.resources="domain.resources"domain.resources.port=8080db.root=${j2ee.home}/pointbasedb.driver=com.pointbase.jdbc.jdbcUniversalDriverdb.host=localhostdb.port=9092db.sid=sun-appserv-samplesdb.url=jdbc:pointbase:server://${db.host}:${db.port}/${db.sid}db.user=pbpublicdb.pwd=pbpublicurl.prop=DatabaseNameds.class=com.pointbase.jdbc.jdbcDataSourcedb.jvmargs=-ms16m -mx32m sql.script=student.sqlInclude a file student.sql in the same directory=================================DROP TABLE student;CREATE TABLE student(name VARCHAR(10), qpa VARCHAR(6));DELETE FROM student;INSERT INTO student VALUES('Sue', '4.0');INSERT INTO student VALUES('Billy','3.4');295-702 Organizational Communication & Distributed Object Technologies Carnegie Mellon University Execute the build.xml script with asant (Application Server Ant)==============================================D:\McCarthy\www\95-702\examples\MyTestDB>asantBuildfile: build.xmlinit:create-db_common: [java] ***************************************************************** [java] -driver com.pointbase.jdbc.jdbcUniversalDriver [java] -url jdbc:pointbase:server://localhost:9092/sun-appserv-samples [java] -script student.sql [java] -user pbpublic [java] -password pbpublic [java] -autocommit true [java] -prompt2 true [java] -spoolfile <none> [java] -silent false [java] ***************************************************************** [java] SQL> DROP TABLE student; [java] OK [java] SQL> CREATE TABLE student [java] (name VARCHAR(10), qpa VARCHAR(6)); [java] OK [java] SQL> DELETE FROM student; [java] OK [java] SQL> INSERT INTO student VALUES('Sue', '4.0'); [java] 1 row(s) affected [java] SQL> INSERT INTO student VALUES('Billy','3.4'); [java] 1 row(s) affectedBUILD SUCCESSFULTotal time: 2 secondsSee if the database was created in PointBase===============================Run the console withD:\Sun\AppServer\pointbase\tools\serveroption>startconsoleIn the URL field be sure to select jdbc:pointbase:server://localhost/sun-appserv-samplesUser name pbpublicPassword pbpublicSelect OK and you should see SCHEMAS and SECURITYSchemas->PBPUBLIC->Tables->Select StudentIn "Enter SQL Commands" enterselect * from student Click the Execute button (not the tab)(1) Paste a copy of the Pointbase GUI (showing the result of the query) here.Display the database contents on the web395-702 Organizational Communication & Distributed Object Technologies Carnegie Mellon University Create a DataSource in the Application Server=================================Start the app server (default server not sample server)Run the admin console.Expand JDBC and select the JDBC ResourceClick new.Enter jdbc/StudentDB as the JNDI nameChoose PointBasePool in the pool name drop down listClick OKWe have just associated a name with a connection pool. The name is a JNDI name. “JNDI” stands for the JavaNaming and Directory Interface. We have bound a people friendly name to a database resource that provides aconnection to our student database.495-702 Organizational Communication & Distributed Object Technologies Carnegie Mellon University Write a servlet that reads the database and writes HTML to a browser=================================================Compile the servlet called ReadStudentDB.java// ReadStudentDB.java.java import java.io.*;import javax.servlet.*;import javax.servlet.http.*;import java.sql.*;import javax.sql.*;import javax.naming.*;import java.util.*;public class ReadStudentDB extends HttpServlet { public void doGet(HttpServletRequest req, HttpServletResponse response) throws ServletException, IOException { Connection con = null; try { InitialContext ic = new InitialContext(); Context envCtx = (Context) ic.lookup("java:comp/env"); DataSource ds = (DataSource) envCtx.lookup("jdbc/StudentDB"); con = ds.getConnection(); response.setContentType("text/html"); PrintWriter out = response.getWriter(); String resultString = ""; String selectStatement = "select * " + "from student"; PreparedStatement prepStmt = con.prepareStatement(selectStatement);
View Full Document