VoLai

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

Here is my laptop's desk, running Window XP Professional:

On this laptop, I also run Window 2000 Professional and Kanotix Linux.

I would suggest Red Hat Linux or Fedora.However, to install them, you need to learn a bit about harddisk partition and dual system installation. Every good book about Linux has information about it.I would suggest Red Hat Linux 9.0 Bible or Fedora 2.0 Bible. If you buy those books, you get CDs to install Linux and Tools also.However, if you are afraid of messing up your harddisk, you should try with some live CD distros first. The live CD distros allow you to run Linux from CD without installation.If you have a FAT partition on harddisk, or you have some flash memory plug in to USB port, you can use it to save your configuration and home's config, and you can have your own settings such as color theme, wall paper, desktop organization ...I use Kanotix (a different style of Knoppix) with my laptop (although on my desktop I use Fedora). I like Kanotix better, because it uses the GRUB multi boot, and save me some time at the startup, and it has some better features. With config and home put on harddisk, some Eclipse, JBoss installations, my laptop become quite a powerfull J2EE develoment workstation running Linux. When I want to run Window, I just take the CD out and boot from harddisk.You can download the Kanotix iso file, and burn it to CD ROM and use it (assume that your computer can boot from CD ROM).

It is most likely because of your servlet engine configuration or your servlet configuration.I have seen this error before on a shared host, when I tried to invoke a JSP page. However, after some months, when I tried again, it worked perfectly.One solution for you is:Write another method in to your servlet like this:public void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException,ServletException{doPost(request, response);}After that, change the HTML file to<form name="Post your reminders here"method="GET"action="http://forums.xisto.com/no_longer_exists/ see if it works.If it works, please check your server configuration to see if it forbids certain POST from certain context.

Here is a very good link for beginner: http://forums.xisto.com/no_longer_exists/ I myself started to learn JSP/Servlet from this site, not from Sun. Try this link: http://forums.xisto.com/no_longer_exists/ After few hours reading this site, I started to write JSP/Servlet for Windchill/Intralink Gateway (if you want to know about Windchill and Intralink, see http://www.ptc.com/)or search the Web for it. In the year of 2002, Windchill got the Award of The World's second best Enterprise Software Architecture (After Matrix One). But I doubt if it ever get any high award any more, because many good senior developers have left PTC, including me ...he ...he ... About Sun JSP/Servlet tutorials, I think that they have samples to illustrate the technical points well, but their samples were poor designed, and violate certain principles of OOP and Design Patterns. One of the worst samples is the Mail Application using JSP/ Servlet. I have a much better one, using MVC design pattern model 2, Java Bean, Data Transfer Object pattern, and you can see the demo at: http://forums.xisto.com/no_longer_exists/

OpenCms is a CMS written in Java:
http://www.opencms.org/opencms/en/

Anyway, I must confess that I can not install both versions 5.x and 6.0.
I need a CMS system for my website, and I would prefer Java CMS, because I am a J2EE guy, and i can make use of Java CMS better.

Apache has one CMS called Lenya (you can see in http://lenya.apache.org/), but the functionalities are limited and not suit my needs.

Because I can not find a suitable CMS, I am writingn one for myself now, using Struts, JSP/Servlet and MySQL database. All the content pages will be XML, and I use XSL to reder it to the Web browser.

From my point of view, you should choose the editor according to the purpose that you want to use with Java.If you want to study Java, or write simple programs, these are good choice:On Linux: KDeveloper,emacs,JEditOn Window: Notepad, JEditIf you want develop simple Java for Web, Dreamwaver is OK.But if you want to develop complex software with multiple source files and configurations, you should choose some real IDE (Integrated Development Environment) which include editor, debug, deployment tool, plug-in management ...The best I consider is JBuilder from Borland because of the following reasons:- Multiple project management- Automatically completing code capability- Refactoring capability- UML diagram generation- Server integration (JBuilder can integrate with WebLogic, WebSphere, Sun One App Server, Borland Enterprise Server)- Team management (Can integrate with CVS, ClearCase)- EJB, Servlet, JSP, Web App deployment: JBuilder automatically generates necessary bean/servlet methods and deployment descriptors for you, packages them and deploy to the servers.- Highly flexible configuration.- Eclipse is a good IDE too, but you need some expertise to configure it. I am using Eclipse on Linux and like it. - WebSphere Application Developer Studio is based on Eclipse is a good one too.- WebLogic Workshop is good, and I like it better than WADS - Visual Cafe' is good too, but it can not be compared to JBuilder- IntelliJ is a favorite tool of many developers.- I think NetBean is OK, but the interface is not intuitive. Overall, I think if you have money, the best for you is JBuilder Enterprise Edition.If you want to use free, but professional IDE with a great extensiblity, you should use Eclipse.

Normally on a shared host, you can access only to the root context of your site (because the Web server and servlet engine are shared among all the users of the host).So you should try to put your JSP pages to the root context of your site, and see if it work. I don't have space on Xisto yet, so I will let you know if I can run JSP once I get some space here.