• Environment:

This following are assumed throughout this guide:

• Red Hat Linux 7.2

Download the Demo sample, extract it and test it.

• Introduction:

The purpose of this sample is to demonstrate how to integrate the postgresql server with its JDBC driver with Tomcat (J2SE).

• Installation:

compat-libstdc++-6.2-2.9.0.16.i386.rpm
j2sdk-1_4_0-fcs-linux-i386.rpm
jakarta-tomcat-3.2.4.tar.gz
postgresql-7.1.3-2.i386.rpm
postgresql-libs-7.1.3-2.i386.rpm
postgresql-server-7.1.3-2.i386.rpm
postgresql-jdbc-7.1.3-2.i386.rpm

• Configuration:

/bin/vi /root/.bash_profile

JAVA_HOME="/usr/java/j2sdk1.4.0"
CLASSPATH=${CLASSPATH}:"/usr/share/pgsql/jdbc7.1-1.2.jar"
CLASSPATH=${CLASSPATH}:"/opt/jakarta-tomcat-3.2.4/lib/servlet.jar"
export JAVA_HOME CLASSPATH

chmod 755 /opt/jakarta-tomcat-3.2.4/bin/*

/opt/jakarta-tomcat-3.2.4/bin/shutdown.sh
/opt/jakarta-tomcat-3.2.4/bin/startup.sh
/usr/bin/lynx http://localhost:8080/

• DB Initialisation:

/bin/vi /var/lib/pgsql/data/postgresql.conf

tcpip_socket = true

/sbin/service postgresql restart

dropdb -U postgres dbtest
createdb -U postgres dbtest

/bin/vi initdb.pgsql

create sequence tests_id start 1;
create table tests
(
  id   int  not null primary key,
  name text not null,
  unique(name)
);
insert into tests (id, name) values (nextval('tests_id'), 'Hello World');

/bin/cat initdb.pgsql | /usr/bin/psql -U postgres dbtest

• Web Application - DBTest:

/bin/vi /opt/jakarta-tomcat-3.2.4/conf/server.xml

<ContextManager>
<!-- DBTester - Virtual Host -->
<Host name="localhost.localdomain">
<Context path="dbtester" docBase="webapps/dbtester" />
</Host>
</ContextManager>

/bin/mkdir -p /opt/jakarta-tomcat-3.2.4/webapps/dbtester/WEB-INF/classes
/bin/vi /opt/jakarta-tomcat-3.2.4/webapps/dbtester/WEB-INF/classes/Test.java

import java.lang.*;

public class Test extends java.lang.Object
{
  java.sql.Connection  connection;
  java.sql.Statement   statement;
  java.lang.String     type;
  java.lang.String     name;
  java.lang.String     username;
  java.lang.String     password;
  java.lang.String     driverName;
  java.sql.ResultSet   resultSet;

  public Test
  (
  )
  {
    this.type       = new java.lang.String("postgresql");
    this.name       = new java.lang.String("dbtest");
    this.username   = new java.lang.String("postgres");
    this.password   = new java.lang.String("");
    this.driverName = new java.lang.String("org.postgresql.Driver");

    return;
  }

  public void connect
  (
  )
  {
    try
    {
      java.lang.Class.forName(this.driverName);

      this.connection =
        java.sql.DriverManager.getConnection
        (
          "jdbc:" + this.type + ":" + this.name,
          this.username,
          this.password
        );
    }
    catch
    (
      java.lang.Exception exception
    )
    {
      java.lang.System.err.println(exception.toString());
    }

    return;
  }

  public void disconnect
  (
  )
  {
    try
    {
      this.connection.close();
    }
    catch
    (
      java.lang.Exception exception
    )
    {
      java.lang.System.err.println(exception.toString());
    }

    return;
  }

  public void doQuery
  (
    java.lang.String query
  )
  {
    this.connect();

    try
    {
      this.statement = this.connection.createStatement();
      this.resultSet = this.statement.executeQuery(query);
      this.resultSet.beforeFirst();
      this.resultSet.close();
    }
    catch
    (
      java.lang.Exception exception
    )
    {
      java.lang.System.err.println(exception.toString());
    }

    this.disconnect();

    return;
  }
}

cd /opt/jakarta-tomcat-3.2.4/webapps/dbtester/WEB-INF/classes
/opt/jakarta-tomcat-3.2.4/bin/shutdown.sh
/bin/rm *.class
/usr/java/j2sdk1.4.0/bin/javac *.java
/opt/jakarta-tomcat-3.2.4/bin/start.sh

• Test Demo:

cd /opt/jakarta-tomcat-3.2.4/webapps/dbtester
/bin/vi test.jsp

<%!
  Test test;
%>

<%
  test = new Test();
  test.doQuery("delete from tests;");
%>

/usr/bin/lynx http://127.0.0.1:8080/dbtester/test.jsp