[Fixed] PostgreSQL Datasource Connection pool with Jboss EAP..

September 30, 2015 | In: JBOSS, Tomcat

We need to have the Postgresql jdbc driver file registered. This can either be local to the application inside “WEB-INF/lib” or directly under the modules section of the Standalone configured. Here we show how to register the driver under the standalone module folder.

Download the jdbc driver for Postgresql https://jdbc.postgresql.org/download/postgresql-9.2-1004.jdbc4.jar .

You may check for the latest versions at https://jdbc.postgresql.org/download.html. I downloaded the version “https://jdbc.postgresql.org/download.html“. Now add this jar file to your installation at

Location

<$JBOSS_HOME>/modules/system/layers/base/org

create a directory named postgres and inside that directory create a folder named main. The following path should look like

Location

<$JBOSS_HOME>/modules/system/layers/base/org/postgres/main/

Upload the postgres driver jar file here.

Now create a file named module.xml in the same location and add the following contents.

Configuration

<?xml version=”1.0″ encoding=”UTF-8″?>
<module xmlns=”urn:jboss:module:1.0″ name=”org.postgresql”>
<resources>
<resource-root path=”postgresql-9.2-1004.jdbc4.jar”/>
</resources>
<dependencies>
<module name=”javax.api”/>
<module name=”javax.transaction.api”/>
</dependencies>
</module>

Now move on to add this driver under our standalone.xml. Open your standalone.xml at

Configuration

<$JBOSS_HOME>standalone/configuration/

check for the <datasources> and inside that you will have the <drivers></drivers>. Inside this drivers tag add our PostgreSQL driver tag.

Configuration

<driver name=”postgresql” module=”org.postgresql”>
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
</driver>

Now restart the JBoss service to add the driver. The console.log should show something like

log

^[[0m^[[0m16:37:22,645 INFO [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool — 25) JBAS010404: Deploying non-JDBC-compliant driver class org.postgresql.Driver (version 9.2)
^[[0m^[[0m16:37:22,667 INFO [org.jboss.ws.common.management] (MSC service thread 1-23) JBWS022052: Starting JBoss Web Services – Stack CXF Server 4.1.3.Final-redhat-3

If some error occurs, check that you have not copied any illegal characters during a copy/paste.

Now that you have the PostgresSQL JDBC driver up and running.

To create a Datasource Connection Pool you can use two methods. You may specify the Datasource configuration in your Standalone.xml or you can get it done within your application. Lets talk about the second method first(within the application).

You may need to add the configuration under your application WEB-INF/ folder. Create a file named¬† “connect2-ds.xml”¬† or <anyname>-ds.xml, add the following configuration. You may edit the jndi-name to edit your needs. Modify the connection URL with your database name, port and hostname. Modify the username and password too.

Configuration

<datasources>
<datasource jta=”false” jndi-name=”java:jboss/postgresDSE” pool-name=”postgresDSE” enabled=”true” use-java-context=”true” use-ccm=”false”>
<connection-url>jdbc:postgresql://localhost:5432/mytestdatabase</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<driver>postgresql</driver>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>20</max-pool-size>
</pool>
<security>
<user-name>mydbuser</user-name>
<password>stong@p@38s#</password>
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
<background-validation-millis>1</background-validation-millis>
</validation>
<statement>
<prepared-statement-cache-size>0</prepared-statement-cache-size>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</datasource>
</datasources>

Create the jboss-web.xml under WEB-INF/ with your normal contents like the following if you use virtual hosts.

Configuration

<jboss-web>
<context-root>/</context-root>
<virtual-host>myappname</virtual-host>
</jboss-web>

Now under your application create a jbosstest.jsp file to check your working.

The Code

<%@ page language=”java” %>
<!DOCTYPE HTML PUBLIC “-//w3c//dtd html 4.0 transitional//en”>
<html>
<head>
<title>JBoss Test</title>
</head>
<body bgcolor=”#FFFFFF”>

<%@ page import=”javax.naming.*” %>
<%@ page import=”java.sql.*” %>
<%@ page import=”javax.sql.*” %>

<%! private String connectionInfo = null; %>
<%! private Connection con = null; %>
<%! private Statement stmt = null; %>
<%! private ResultSet resultSet = null; %>
<%

try {

InitialContext ctxt = new InitialContext();
DataSource ds = (DataSource) ctxt.lookup(“java:jboss/postgresDSE”);

con = ds.getConnection();
DatabaseMetaData metaData = con.getMetaData();
connectionInfo = “Database Name: ” + metaData.getDatabaseProductName() + “<BR>” +
“Database Version: ” + metaData.getDatabaseProductVersion() + “<BR>” +
“Driver Name: ” + metaData.getDriverName() + “<BR>” +
“Driver Version: ” + metaData.getDriverVersion() + “<BR>” +
“URL: ” + metaData.getURL();
%>

<H1>Connected to Database</H1>
<P><%= connectionInfo%></P>

<%
}
catch (Exception except) {
out.print(“ERROR: ” + except);

Throwable cause = except.getCause();
while (cause != null) {
out.print(“<BR/>Nested Exception: ” + cause);
cause = cause.getCause();
}
}
finally {

try {

if (resultSet != null) {
resultSet.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
}
catch (SQLException except) {
}
}
%>

</body>
</html>

Modify the Datasource context lookup with the name that your created in the <anyname>-ds.xml

DataSource ds = (DataSource) ctxt.lookup(“java:jboss/postgresDSE”);

 

Now deploy the application and try calling the jbosstest.jsp via browser.

 

For the second method to create a datasource via the standalone.xml. The contents which was added in the <anyname>-ds.xml should be added to the standalone.xml under the <datasources></datasources> tag. Create the new datasource with the following

Configuration

<datasource jta=”false” jndi-name=”java:jboss/postgresDS” pool-name=”postgresDS” enabled=”true” use-java-context=”true” use-ccm=”false”>
<connection-url>jdbc:postgresql://localhost:5432/mytestdatabase</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<driver>postgresql</driver>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>20</max-pool-size>
</pool>
<security>
<user-name>mydbuser</user-name>
<password>stong@p@38s#</password>
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
<background-validation-millis>1</background-validation-millis>
</validation>
<statement>
<prepared-statement-cache-size>0</prepared-statement-cache-size>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</datasource>

Now this datasource can be accessed without defining it at the WEB-INF folder of your application. Our test application can be downloaded from http://presoon.com/blog/jbosstest.tar.gz

Thanks you all.

mm
Hi All… I have been working the IT Industry since last ten years… Specialized in Linux, Cloud Infrastructure and Solution Design.
On the hobby friend, I do research on IOT, Micro-controllers, Electronics etc.
Music is my passion..Connect me on..

1 Response to [Fixed] PostgreSQL Datasource Connection pool with Jboss EAP..

Avatar

Bok

October 11th, 2015 at 2:34 pm

I am working with the beeovld TCA as well. Thanks for taking the extra step to post that. I went through the same process to figure that out a couple of months back. It makes me wonder do you think it is supported by Oracle in any way to use the java api outside of the financials implementation?The next disappointment that you find is that the api only works with a OracleConnection instead of the generic Connection. It just makes things ugly.

Comment Form

*

Authors

mm
Presoon John

Hi All... I have been working the IT Industry since last ten years... Specialized in Linux, Cloud Infrastructure and Solution Design.
On the hobby friend, I do research on IOT, Micro-controllers, Electronics etc.
Music is my passion..Connect me on..

Access this post via Mobile

Scan the QR Code
QR Code Generator

Categories