Add the jdbc drivers for PostgreSQL into the CATALINA_HOME/lib
rpm -qli postgresql-jdbc
Name : postgresql-jdbc
Version : 42.2.12
Release : 1.rhel7
Architecture: noarch
Install Date: Sun 03 May 2020 02:56:27 PM EDT
Group : Unspecified
Size : 935111
License : BSD and ASL 2.0
Signature : DSA/SHA1, Wed 01 Apr 2020 04:26:27 PM EDT, Key ID 1f16d2e1442df0f8
Source RPM : postgresql-jdbc-42.2.12-1.rhel7.src.rpm
Build Date : Wed 01 Apr 2020 04:26:24 PM EDT
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://jdbc.postgresql.org/
Summary : JDBC driver for PostgreSQL
Description :
PostgreSQL is an advanced Object-Relational database management
system. The postgresql-jdbc package includes the .jar files needed for
Java programs to access a PostgreSQL database.
/usr/share/doc/postgresql-jdbc-42.2.12
/usr/share/doc/postgresql-jdbc-42.2.12/README.md
/usr/share/java/postgresql-jdbc.jar
/usr/share/java/postgresql-jdbc2.jar
/usr/share/java/postgresql-jdbc2ee.jar
/usr/share/java/postgresql-jdbc3.jar
/usr/share/licenses/postgresql-jdbc-42.2.12
/usr/share/licenses/postgresql-jdbc-42.2.12/LICENSE
/usr/share/maven-poms/JPP-postgresql-jdbc.pom
Copy the drivers:
cp /usr/share/java/postgresql-jdbc.jar /app/apache-tomcat-9.0.34/lib
cp /usr/share/java/postgresql-jdbc2.jar /app/apache-tomcat-9.0.34/lib
cp /usr/share/java/postgresql-jdbc2ee.jar /app/apache-tomcat-9.0.34/lib
cp /usr/share/java/postgresql-jdbc3.jar /app/apache-tomcat-9.0.34/lib
creating servlet
The structure for web application firstapp:
-bash-4.2$ ls -R /app/apache-tomcat-9.0.34/webapps/firstapp
/app/apache-tomcat-9.0.34/webapps/firstapp:
css index.html index.jsp src WEB-INF
/app/apache-tomcat-9.0.34/webapps/firstapp/css:
cba.css
/app/apache-tomcat-9.0.34/webapps/firstapp/src:
HelloServlet.java makefile pgTable.java
/app/apache-tomcat-9.0.34/webapps/firstapp/WEB-INF:
classes web.xml
/app/apache-tomcat-9.0.34/webapps/firstapp/WEB-INF/classes:
HelloServlet.class pgTable.class
This example uses makefile for building an applet. Alternative approach for more complex projects is using maven as described in Generating Tomcat servlets using maven and deploying them
Create a makefile:
vi makefile
JAVAC=javac
CATALINA_HOME=/app/apache-tomcat-9.0.34
WEB_APP=${CATALINA_HOME}/webapps/firstapp/WEB-INF/classes
default: pgTable
pgTable:
${JAVAC} -cp '.:${CATALINA_HOME}/lib/*' -d ${WEB_APP} pgTable.java
create a source file:
vi pgTable.java
import java.sql.*;
import java.util.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.lang.*;
public class pgTable extends HttpServlet
{
Connection conn = null;
public void init(ServletConfig config) throws ServletException
{
super.init(config);
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
String url = "jdbc:postgresql://192.168.2.23:5432/dataexplorerdb2";
Properties props = new Properties();
props.setProperty("user","dataexplorer2");
props.setProperty("password","2MuchTime");
PrintWriter out = response.getWriter();
out.println("<HTML>");
out.println("<HEAD>");
out.println("<TITLE>PostgresSQL testing</TITLE>");
out.println("<link rel=stylesheet type=text/css href=css/cba.css>");
out.println("</HEAD>");
out.println("<BODY>");
out.println("<H1>Product sales by the month</H1>");
out.println("<H1>Establishing connection ...</H1>");
try
{
Connection conn = DriverManager.getConnection(url, props);
out.println("<H1>Connected ..</H1>");
String strSQL="select * from product_sales_monthly";
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(strSQL);
out.println("<TABLE>");
out.println("<TR><TH>Product</TH><TH>Month</TH><TH>Day</TH><TH>Amount,CAD</TH></TR>");
while(rs.next())
{
out.println("<TR>");
out.println("<TD>"+rs.getString("product")+"</TD>");
out.println("<TD>"+rs.getString("month")+"</TD>");
out.println("<TD>"+rs.getString(3)+"</TD>");
out.println("<TD>"+rs.getDouble(4)+"</TD>");
out.println("</TR>");
}
out.println("</TABLE>");
conn.close();
out.println("</BODY>");
out.println("</HTML>");
} //end of Try
catch(SQLException e)
{
out.println(e.getMessage());
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
doGet(request,response);
}
};
Make the application, make sure permissions in WEB-INF/classes permit the writing to it:
make
Update the web.xml file:
vi /app/apache-tomcat-9.0.34/webapps/firstapp/WEB-INF/web.xml
<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0">
<servlet>
<servlet-name>pgTable</servlet-name>
<servlet-class>pgTable</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>pgTable</servlet-name>
<url-pattern>/report</url-pattern>
</servlet-mapping>
</web-app>
In the browser go to : http://r02edge.custom-built-apps.com:1962/firstapp/report
you should see the report web page if the driver is correct and permissions are set up properly
Please note, this is a testing application, the actual development will be done using Maven Tomcat plug-in. The resulting war files will be deployed using manager webapp of TomCat.
passing connection information dynamically
connection information should be stored in the configuration file connection.ini and loaded at run time
- At development time create a file connection.ini and put it into the src/main/resources directory
- the file should contain connection information as follows:
#connection information is kept here
url=jdbc:postgresql://192.168.2.30:5432/cbaworkflowdb
user=dataexplorer1
password=2MuchTime - create a utility class in src/main/java/com/cba/web
package com.cba.web;
import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.*;public class cbaUtil
{
public void populateVariables(Map<String,String> variables,String configFileName) throws IOException
{
ClassLoader classLoader = getClass().getClassLoader();
InputStream inputStream = classLoader.getResourceAsStream(configFileName);
try (BufferedReader br = new BufferedReader(new InputStreamReader(inputStream)))
{
String line;
while ((line = br.readLine()) != null)
{
if(line.startsWith("#"))
continue;
if(line.contains("="))
{
String[] pair =line.split("=");
variables.put(pair[0],pair[1]);
}
}
}
}};
- In the servlet doGet code:
Map<String,String> variables=new HashMap<>();
String configFileName="connection.ini";
cbaUtil u = new cbaUtil();
u.populateVariables(variables,configFileName);
String url = variables.get("url");
Properties props = new Properties();
props.setProperty("user",variables.get("user"));
props.setProperty("password",variables.get("password"));
try
{
Connection conn = DriverManager.getConnection(url, props);
}
catch(SQLException e)
{
out.println(e.getMessage());
}
5. After the war deployment the information in connection.ini can be changed to appropriate values. The connection.ini will be located in the classes directory of the web application: cbaWorkflow/WEB-INF/classes/connection.ini
The information will be updateable by tomcat user.
