how I set up a PostgreSQL JDBC Driver on JBoss 7

Posted by andilog Feb 8, 2012

Download the Postgres Driver which you need as .jar-file (http://jdbc.postgresql.org/download.html)
Go to JBoss directory and find modules\org folder. Create two new folders, so you get modules\org\postgresql\main and insert the .jar file there.

Create in the same folder a file named module.xml with the content (set in <resource-root path="" > the name of your .jar-file):

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="org.postgresql">
 <resources>
 <resource-root path="postgresql-Y.X-Z.jdbcV.jar"/>
 </resources>
 <dependencies>
 <module name="javax.api"/>
 <module name="javax.transaction.api"/>
 </dependencies>
</module>

Edit your standalone.xml and add the following code between the tags <datasources><drivers>

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

Now start the server and check wether he is loading the driver. The console should show something like: INFO [org.jboss.as.connector.subsystems.datasources] JBAS010404: Deploying non-JDBC-compliant driver class org.postgresql.Driver (version 8.4). If it nothing showing about your Postgres Driver, check the spelling and the coding (e.g. the quotation marks can be in different code and dont work, can happen if you just copy and paste). Fix this before going on.

Go to your standalone.xml and set between <datasources> following code (set your connection-url, usename and password, jndi-name can also be java:/postgresDS, you can do this step through admin console of the server as well):

<datasource jndi-name="java:jboss/postgresDS" pool-name="postgresDS" enabled="true" jta="false" use-java-context="true" use-ccm="false">
<connection-url>
 jdbc:postgresql://192.z.y.x:5432/yourdatabase
 </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>
urusername
 </user-name>
 <password>
urpasswort
 </password>
 </security>
 <validation>
 <validate-on-match>
 false
 </validate-on-match>
 <background-validation>
 false
 </background-validation>
 <background-validation-millis>
 0
 </background-validation-millis>
 </validation>
 <statement>
 <prepared-statement-cache-size>
 0
 </prepared-statement-cache-size>
 <share-prepared-statements>
 false
 </share-prepared-statements>
 </statement>
 </datasource>

Start you database server

Test the jdbc connection with following jsp-file (set the name of your table and the column names):

<%@page
 import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*"%>
<%
 DataSource ds = null;
 Connection con = null;
 Statement stmt = null;
 InitialContext ic;
  
 try {
 ic = new InitialContext();
 ds = (DataSource) ic.lookup("java:/postgresDS");
 con = ds.getConnection();
 stmt = con.createStatement();
 ResultSet rs = stmt.executeQuery("select * from tabname");

 while (rs.next()) {
 out.println("<br> " + rs.getString("onecolumn") + " | "
 + rs.getString("othercolumn"));
 }
 rs.close();
 stmt.close();
 } catch (Exception e) {
 out.println("Exception thrown :/");
 e.printStackTrace();
 } finally {
 if (con != null) {
 con.close();
 }
 }
    %>