J2ee Technologies: Integrate Servlet, Jndi , Jdbc J2EE Web App using Servlet

[VoLai 0]

J2EE Technologies: Integrate Servlet, JNDI , JDBC and RDBMS

Abstract: This example shows you how to use a Servlet to lookup a JDBC connection via JNDI, then connect to a database, and display content of a database table.

Tools: SunOne App Server, PointBase database
Download J2SE, J2EE SDKs from java.sun.com
Suppose that you install SunOne App Server in D:\Sun

I. Prepare the database:
Start the PointBase database server:

==================
D:\Sun\AppServer\pointbase\tools\serveroption>startserver
Server started, listening on port 9092, display level: 0 ...
>

Create an XML file to build with asant, to create database and table:
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 directory
======================================

j2ee.home=D:/Sun/AppServer
sunone.home=${j2ee.home}
domain.resources="domain.resources"
domain.resources.port=8080
db.root=${j2ee.home}/pointbase
db.driver=com.pointbase.jdbc.jdbcUniversalDriver
db.host=localhost
db.port=9092
db.sid=sun-appserv-samples
db.url=jdbc:pointbase:server://${db.host}:${db.port}/${db.sid}
db.user=pbpublic
db.pwd=pbpublic
url.prop=DatabaseName
ds.class=com.pointbase.jdbc.jdbcDataSource
db.jvmargs=-ms16m -mx32m
sql.script=student.sql

Include a file student.sql in the same directory
=================================

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');

Execute the build.xml script with asant (Application Server Ant)
==============================================

D:\Java\MyTestDB>asant

Buildfile: build.xml
init:
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> 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) affected

BUILD SUCCESSFUL
Total time: 2 seconds

See if the database was created in PointBase
===============================
Run the console with

D:\Sun\AppServer\pointbase\tools\serveroption>startconsole

In the URL field be sure to select jdbc:pointbase:server://localhost/sun-appserv-samples
User name pbpublic
Password  pbpublic
Select OK and you should see SCHEMAS and SECURITY
Schemas->PBPUBLIC->Tables->Select Student
In "Enter SQL Commands" enter
select * from student
Click the Execute button (not the tab)

II. Display database on the Web
1) 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 Resource
Click new.
Enter jdbc/PBPUBLIC as the JNDI name
Choose PointBasePool in the pool name drop down list
Click OK

We have just associated a name with a connection pool. The name is a JNDI name. �JNDI� stands for the Java Naming and Directory Interface. We have bound a people friendly name to a database resource that provides a connection to our student database.

2) 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);
            ResultSet rs = prepStmt.executeQuery();

            resultString += "<html><body>";       

            while (rs.next()) { 
                resultString += rs.getString(1)+"<p>";
                resultString += rs.getString(2)+"<p>";
            }
            resultString += "</body></html>"; 
                   
            prepStmt.close();


            out.println(resultString);
       
            }
            catch (SQLException ex) {
              System.out.println("SQL EX " + ex.getMessage());
            }
            catch(Exception ex) {
              System.out.println(" A Wierd Exception " + ex);
            }

            finally {

              try {
                    if(con != null) con.close();
              }
              catch(SQLException e){
                    System.out.println("Problem closing");
              }
          } 
                   
      }

    }

Compile the servlet with the command javac ReadStudentDB.java.

Go to the directory D:\Sun\AppServer\bin, run the command:

D:\Sun\AppServer\bin>deploytool

Deploytool ->File->New Web Component
Browse to the directory where the servlet resides and enter that directory path in the WAR location text box. This path will end with MyDBReader.war
The war file MyDBReader (without the .war) will be automatically placed in the WAR name text box.
Set the context root to /GetStudents (this will be the name that appears after localhost:8080/ on the browser)
Edit Contents and place the compiled servlet in the WAR
Complete the wizard, select the servlet from the tree to the left, select the alias tab and give it the alias /CoolServlet. Don�t forget to hit the return key. (This will be the name that appears after localhost:8080/GetStudents/ on the browser.)

Select the WAR name on the tree to the left.
Select the Resource Ref�s tab
Click Add
Type jdbc/StudentDB in the coded name field (don�t forget the return key). The �coded name� must be the same with the name in the servlet�s code:
DataSource ds = (DataSource) envCtx.lookup("jdbc/StudentDB");

Select localhost:4848 in the server list (log in if you must)
Select MyDBReader in the web war list
Select the Resource Ref�s tab
Select the Resource Reference Name, jdbc/StudentDB, that you just entered
In the Sun-specifi settings frame, select jdbc/StudentDB from the Drop Down List for the JNDI name
In the User Name text field enter pbpublic (this is the database user name)
In the Password Field enter pbpublic (this is the database password)
Deploy the web application



III.Testing

Make sure the database is running
Make sure the Application Server is running
Make sure MyDBReader.war is deployed
Use a browser to visit localhost:8080/GetStudents/CoolServlet