Implementation of Connection Pooling for PostgreSQL in Tomcat6.0

  1. Copy the below Resource tag in between open tag <Context> and end tag          </Context> of context.xml available in META-INF directory of your Web Application

For Example

D:\SampleWeb\web\META-INF

<Context>

                                                                                                                         

<Resource name="jdbc/psql"

    auth="Container"

    type="javax.sql.DataSource"

    maxActive="100"

    maxIdle="30"

    maxWait="20000"

    removeAbandoned="true"

    removeAbandonedTimeout="120"

    username="postgres"

    password="postgres"

    driverClassName="org.postgresql.Driver"

    url="jdbc:postgresql://127.0.0.1:5432/testdb"/>

</Context>

2. Copy the same above Resource tag in between open tag <Context> and end tag        </Context> of context.xml available in Tomcat’s conf directory.

For example

           C:\Program Files\Apache Software Foundation\Tomcat 6.0\conf\

<Context>

<Resource name=" jdbc/psql"

    auth="Container"

    type="javax.sql.DataSource"

    maxActive="100"

    maxIdle="30"

    maxWait="20000"

    removeAbandoned="true"

    removeAbandonedTimeout="120"

    username="postgres"

    password="postgres"

    driverClassName="org.postgresql.Driver"

    url="jdbc:postgresql://10.1.14.38:5432/testnpermit"/>

</Context>

3. You can have any number of Resource tags as you wish but names must be different.

    

4. Copy the postgresql-8.4-701.jdbc3.jar in Tomcat’s lib directory

  C:\Program Files\Apache Software Foundation\Tomcat 6.0\lib

5. Create a Java Class ConnectionPooling

import java.sql.Connection;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

import nic.java.util.Debug;

public class ConnectionPooling {

    private static DataSource ds;

    public static Connection getDBConnection() throws NamingException, Exception {

        Connection con = null;

        try {

            if (ds != null) {

                con = ds.getConnection();

            } else {

                String dsString = "java:/comp/env/ jdbc/psql";

              ds = (DataSource) new InitialContext().lookup(dsString);

                con = ds.getConnection();

            }

        } catch (Exception e) {

            e.printStackTrace();

                  }

        return con;

    }

}

Postgres Query to find difference between months

select ( date '2010-01-01', interval '4 months') overlaps 

 ( date '2010-04-30', date '2010-04-30') as flag

This Query  returns true if difference between months is 4 or less than 4 months 

in postgresql and returns false if difference is morethan 4 months

Reading Postgres Database Connection from properties file using ResourceBundle class

DB.java

import java.sql.*;
import java.util.*;

public class DB
{
static ResourceBundle rb = ResourceBundle.getBundle("myapp", Locale.getDefault());

public static Connection getConnection ()
{
Connection conn = null;

try {
Class.forName (rb.getString("dbDriver"));
conn = DriverManager.getConnection(rb.getString("dbURL"), rb.getString("Username"), rb.getString("Password"));
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}


public static void closeConnection (Connection conn)
{

try {
if (conn !=null)
{
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
--------------------------------------------------------------------------------

CallDB.java

import java.sql.*;
import java.util.*;


public class CallDB
{
static ResourceBundle rb = ResourceBundle.getBundle("sql", Locale.getDefault());

public static void loadAndShowResultSet()
{
Connection conn = null;
Statement stmt = null;

try {
conn = DB.getConnection ();
stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery(rb.getString("sql.query"));
while (rset.next())
{
System.out.println (rset.getString(1));
}

}
catch (Exception e) {
e.printStackTrace();
}
finally
{
try {
stmt.close();
DB.closeConnection (conn);
}
catch (Exception e) {
e.printStackTrace();
}
}
}

public static void main(String args[])
{
try {
CallDB callDB= new CallDB();
callDB.loadAndShowResultSet();
}
catch (Exception e) {
e.printStackTrace();
}
}
}

------------------------------------------------------
myapp.properties

dbDriver=org.postgresql.Driver
dbURL=jdbc:postgresql://localhost:5432/dbname
Username=postgres
Password=postgres

--------------------------------------------------------

sql.properties

sql.query=select first_name from username;

JSF Related topics: JCaptcha in JSF, Integrating Richfaces with JSF,Getting client and server sessionId in JSF and more.....