Apache / Tomcat – PostgreSQL – Sample Database Query

 

Background

A few weeks ago I covered a little Java/JDBC-JSTL Application.  The app read data out of a Microsoft SQL Server DB.  The application sat on a Apache Tomcat which is an Opensource Web Server and Servlet Container.

Here is similar version that reads data from a PostgreSQL Database.

There are many similarities between the original Application and this one and I will forsake cut/paste tendencies and omit much here.

Additionally, in truth this was a much quicker implementation.

The URL for the MS SQL Server implementation  is https://danieladeniji.wordpress.com/2014/04/24/technical-apache-tomcat-microsoft-sql-server-sample-database-query/ .

 

PostgreSQL JDBC Driver

Download Location

As of May 17th, 2014, the latest PostgreSQL is version 9.3.  It is available @ http://jdbc.postgresql.org/download.html

 

 

Versioning

There are 3 versions of the JDBC Available.

JVM Version JDBC Version Driver Version
v 1.7, v 1.8 JDBC41 Postgresql Driver, Version 9.3-1101
v 1.6 JDBC4 Postgresql Driver, Version 9.3-1101
 v 1.5 JDBC3 Postgresql Driver, Version 9.3-1101

 

Depending on our JVM, we will download one of the above.

What is your JVM?

Version:

java -version

Output:

java --version

 

 

Download

As we JVM 1.8, we will down the JDBC41.jar

The exact URL is http://jdbc.postgresql.org/download/postgresql-9.3-1101.jdbc41.jar

 

Sample:

wget "http://jdbc.postgresql.org/download/postgresql-9.3-1101.jdbc41.jar" -O "/tmp/postgresql-9.3-1101.jdbc41.jar"

 

Output:

DownloadPostgresSQL

 

Where is $CATALINA_HOME/lib

We know we need to place the jar file in $CATALINA_HOME/lib… What and Where is that?

We installed Apache Tomcat version 6 using yum, and our install location is /usr/share/tomcat6.

Underneath /usr/share/tomcat there is a lib folder; which is actually a symbolic link to /usr/share/java/tomcat6.

 

Confirm $CATALINA_HOME/lib Folder Contents

Let us see if there are other jdbc jar files in that location…

Sample:

ls /usr/share/tomcat6/lib/*jdbc*.jar

 

Output:

lib--jdbc

 

Copy JDBC Jar files into $CATALINA_HOME/lib

We need to copy postgresql-*jdbc.jar unto the lib folder.

Syntax:

sudo cp <jar-files> <tomcat>/lib

Sample:

sudo cp /tmp/postgresql-9.3-1101.jdbc41.jar /usr/share/tomcat6/lib

 

Confirm JDBC file copied

Syntax:

ls <tomcat>/lib/*jdbc*

Sample:

ls /usr/share/tomcat6/lib/post*jdbc*.jar

 

Output:

tomcat-lib-files

 

JSTL

Download Location

Download javax.servlet.jsp.jstl-1.2.1.jar from http://search.maven.org/#browse|-1002239620

Code – WAR File

File Contents

Here are the regular files that we will be including in our Jar file:

  • WEB-INF/web.xml
  • WEB-INF/lib
  • WEB-INF/classes
  • META-INF/context.xml
  • html files
  • image files
  • jsp files
  • 3rd Party Jar files – jstl files

 

Here are the files that we will not be including:

  • Notice that you do not to indicate the manifest file, as that file is auto created

 

WAR File – Folder Structure

 

Folder Folder/Sub-file What? Sample
WEB-INF Web Information
web.xml Web Configuration File
 lib Jar files 3rd party and application jar files (jstl*.jar)
 classes Compiled java files
META-INF Meta Information about the Jar file
 context.xml

 

 

Source Code Location

On our system, we will place our source files in /home/dadeniji/development/apache.org/tomcat/NBADraft

 

Create empty file structure

Create empty file structure


if [[!-e WEB-INF ]]; then
   sudo mkdir -p WEB-INF


if [[!-e WEB-INF/lib ]]; then
   sudo mkdir -p WEB-INF/lib

if [[!-e WEB-INF/classes ]]; then
   sudo mkdir -p WEB-INF/classes


if [[!-e META-INF ]]; then
   sudo mkdir -p META-INF



 

 

WEB-INF/web.xml

Create WEB-INF/web.xml

Here is the contents of our web xml file:


<?xml version="1.0" encoding="ISO-8859-1"?>

<web-app
   version="2.5"
   xmlns="http://java.sun.com/xml/ns/javaee" 
   xsi="http://www.w3.org/2001/XMLSchema-instance" 
   schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

    <resource-ref>
        <description>DB  Connection</description>
        <res-ref-name>DBSport</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    	<res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>

</web-app>

 

There are a couple of areas to note:

  • Our app version is targeting Servlet/JSP API version 2.5
  • For our DB, we are defining a resource self-named DBSport and its type is javax.sql.DataSource

 

 

META-INF/context.xml

Create META-INF/context.xml

<?xml version="1.0" encoding="UTF-8"?>

<Context>

 <Resource
  name="DBSport"
  auth="Container"
  type="javax.sql.DataSource"
  username="nbadraft"
  password="sleeper"
  driverClassName="org.postgresql.Driver"
  url="jdbc:postgresql://DBPostgreSQL:5432/DBSport"
  maxActive="1000"
  maxIdle="30"
  maxWait="10000"
  factory="org.apache.commons.dbcp.BasicDataSourceFactory" 
  logValidationErrors="true" 
 >
 </Resource>


</Context> 
 

There are a couple of areas to note:

  • We are further defining our database resource, DBsport
  • Its type is javax.sql.DataSource
  • The username & password that is predefined on the DB are noted
  • The Driver Class Name for MS SQL Server JDBC 4.0 is indicated as org.postgresql.Driver
  • The Url is “jdbc:postgresql://DBPostgreSQL:5432/DBSport
  • For the Connection factor we are hard-coding org.apache.commons.dbcp.BasicDataSourceFactory
  • And, logging validation errors via set setting logValidationErrors to true

 

Copy jstl jar files into WEB-INF lib

Here are the steps to copy the jstl files under WEB-INF/lib:

 


if [[ !-e WEB-INF/lib ]]; then
   sudo mkdir -p WEB-INF/lib

sudo cp --update /tmp/jstl/javax.servlet.jsp.jstl-1.2.1.jar  WEB-INF/lib

sudo cp --update /tmp/jstl/javax.servlet.jsp.jstl-api-1.2.1.jar  WEB-INF/lib

Source Code :- jspSimple.jsp

Here is a sample source code that connects to the hard coded sql server and relies on JDBC APIs to query and return the recordset from a table.

 

<html>
<head></head>
<body>
	<%@ page import="java.sql.*" %>
	<%
             String CLASSNAME_DB = "org.postgresql.Driver"; 

	     String connectionUrl = "jdbc:postgresql://DBPostgreSQL:5432/DBSport?user=dbuser&password=dbpassword";

             String strSQLStatement =
                    "select * from \"public\".\"NBADraft\"  limit 100  ";
                
       Connection con = null;
       Statement statement = null;
       ResultSet resultset = null;
 
       Class.forName(CLASSNAME_DB); 

       con = DriverManager.getConnection(connectionUrl); 

       statement = con.createStatement() ; 

       resultset = statement.executeQuery(strSQLStatement);

 %>
 
  <table> 
  <tr>
  <th><b>Athlete</b></th>
  <th><b>Year </b></th>
  <td><b>School</b> </th>
  <td><b>Position</b> </th>
  <td><b>Age</b> </th>
  <td><b>Age</b> </th>
  <td><b>Weight (lbs)</b> </th>



  </tr>

<% 
   while (resultset.next())
   { 
%>
 
<tr> 

    <td><%= resultset.getString("Athlete") %></td> 
    <td><%= resultset.getString("draftYear") %></td> 
    <td><%= resultset.getString("school") %></td>
    <td><%= resultset.getString("position") %></td>
    <td><%= resultset.getString("age") %></td>
    <td><%= resultset.getString("vitalsHeight") %></td>
    <td><%= resultset.getString("vitalsWeight") %></td>




</tr>

<% 
} 
%> 

</table>
</body> 
</html> 

 

Source Code :- explicit.jsp

The code below relies on the Context (part of javax.naming.*) and DataSource (part of javax.sql*).

Obviously, javax.naming.Context allows us to hide the server name and authentication credentials in xml files outside of the Source Code.

 

<%@   page   contentType="text/html;charset=UTF-8" %>   
<%@   page   import="java.sql.*" %> 
<%@   page   import="javax.sql.*" %> 
<%@   page   import="javax.naming.*" %> 
<HTML>
<HEAD>
<TITLE>JSP example</TITLE>
</HEAD>
<BODY>
  <h1>Hello,test JNDI !  </h1>
  <%
    Context ctx = new InitialContext();  

    Context envctx =  (Context) ctx.lookup("java:comp/env"); 
    DataSource ds =  (DataSource) envctx.lookup("DBSport");  

    //DataSource ds = (DataSource) ctx.lookup("DBSport");

    Connection  conn=ds.getConnection();   
    Statement  st=conn.createStatement();
    String    sql="select * from \"NBADraft\"    ";  
    ResultSet    rs=st.executeQuery(sql); 
    while(rs.next())   {
  %>   
       Athlete:<%=rs.getString(1) %>  
       DraftYear:<%=rs.getString(2) %> 
       School:<%=rs.getString(3) %>
       Position:<%=rs.getString(4) %>
       Age:<%=rs.getString(5) %>
       Height:<%=rs.getString(6) %>
       Weight:<%=rs.getString(7) %>
       
 
       <br>
  <%
   }
  %>

   
  <%
   rs.close(); 
   st.close();  
   conn.close();   
  %> 
</BODY>
</HTML> 

Source Code :- queryDB.jsp

The code below uses JSTL tags.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>


<sql:query var="rs"
        dataSource="DBSport"

>
   select * from "NBADraft"
</sql:query>



  <head>
    <title>DB Test</title>
  </head>

  <body>

       <h2>Results</h2>


     <table border="1" align="center" valign="center">
	<th>Athlete</th>
	<th>Year</th>
	<th>School</th>
	<th>Position</th>
        <th>Age</th>
        <th>Height</th>
        <th>Weight</th>


       <c:forEach var="rs" items="${rs.rows}">

	<tr>
	    <td><c:out value="${rs.Athlete}"/>&nbsp;</td>
	    <td><c:out value="${rs.draftYear}"/>&nbsp;</td>
	    <td><c:out value="${rs.school}"/>&nbsp;</td>
	    <td><c:out value="${rs.position}"/>&nbsp;</td>
	    <td><c:out value="${rs.age}"/>&nbsp;</td>
	    <td><c:out value="${rs.vitalsHeight}"/>&nbsp;</td>
	    <td><c:out value="${rs.vitalsWeight}"/>&nbsp;</td>
		
         </tr>

     </c:forEach>

    </table>
  

   </body>

</html>

Create Jar file

Here are the steps to create a jar file

Syntax:

jar cvf <war-file> *.jsp *.html images WEB-INF META-INF
 

 

Sample:

jar cvf NBADraft.war *.jsp *.html images WEB-INF META-INF
 

 

View Jar file Contents

View Jar file contents

Syntax:

jar tf <jar-file>
 

Sample:


jar tf NBADraft.war

Output:

jar list

 

 

Deploy – Manual

Manually deploy jar file by copying the war file into your webapps folder

There is a very noteworthy coverage of manually deploying WAR file available @

http://www.manydesigns.com/en/portofino/portofino3/3_1_x/installation-guide/deploying-on-tomcat/#TOC-Manually-deploying-and-configuring

#un-deploy war
if [[ -f "/usr/share/tomcat6/webapps/NBADraft.war" ]]; then

echo "Removing file /usr/share/tomcat6/webapps/NBADraft.war ."

  sudo rm "/usr/share/tomcat6/webapps/NBADraft.war"

   echo "Removed file /usr/share/tomcat6/webapps/NBADraft.war"

fi

#deploy war
echo "Deploying file /usr/share/tomcat6/webapps/NBADraft.war.."

 sudo cp NBADraft.war /usr/share/tomcat6/webapps/NBADraft.war

echo "Deployed file /usr/share/tomcat6/webapps/NBADraft.war"

Deploy – Verification – CATALINA_HOME/conf/Catalina/localhost/<APP-NAME>.XML

Review CATALINA_HOME/conf/Catalina/localhost and look for a configuration file (XML) bearing your Application Name.

In our case, our folder’s name is /usr/share/tomcat/conf/Catalina/localhost

conf--Catalina--localhost--folder

 

And, the file we are looking for is NBADraft.xml

File Contents:

catalina--localhost--NBADraft

 

 

Diagnostic Steps

Use lsof

Use lsof to determine where tomcat jdbc jar files are being loaded from:

Sample:

sudo lsof | grep -i "tomcat" | grep -i "jdbc"

 

Output:

grep--tomcat--jdbc

 

Run-Time Error Messages

javax.servlet.servletException : java.lang.ClassNotFoundException: org.postgresql.Driver

The exact error message is:


javax.servlet.ServletException: java.lang.ClassNotFoundException: org.postgresql.Driver
	org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:862)
	org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
	org.apache.jsp.jspSimple_jsp._jspService(jspSimple_jsp.java:125)
	org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

 

Diagnostic:

Check loaded jdbc jar files
sudo lsof | grep -i "jdbc"

 

Output:

lsof--jdbc

Explanation:

  • Noticed that tomcat has the Microsoft’s sqljdbc4 loaded, but our postgresQL jdbc driver is not loaded
  • Remembered that I have yet to restart tomcat since I copied the PostgreSQL file to our tomcat folder
  • Let us go restart tomcat ( sudo service tomcat6 restart ) and recheck lsof

 

Here is the output:

Output:

tomcat-restart--postgresql

Explanation:

  • Along with sqljdbc4.jar, we know have /usr/share/java/tomcat6/postgresql-9.3-101.jdbc41.jr

 

javax.servlet.servletException : org.postgresql.util.PSQLException: FATAL : no_pg_hba.conf entry for host <hostname>, user <ApplicationName>, database “<database-name;user=<username>;password=<password>”,SSL of

The exact error message is:

javax.servlet.ServletException: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "10.0.4.27", user "tomcat", database "DBSport;user=nbadraft;password=s1eeper", SSL of

 

Changed Connection URL to

Syntax:

jdbc:postgresql://hostname:portNo/DBS?user=username&password=pwd

Sample:

jdbc:postgresql://PostgreSQL:5432/DBSport?user=nbadraft&password=hello

 

Source Control

Github

https://github.com/DanielAdeniji/JavaJSPJSTLPostgreSQL

 

References

References – JSTL – Blog

References – JSTL – Tutorial

 

References – JSTL – Jar Files Repository

References – PostgreSQL

 

References – Linux – vi

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s