Miscellaneous Technology

Postgres is the most advanced Open Source database available in marketplace today.

If you are not building an extremely scalable enterprise application,

or if you have budgetary constraints, then you could consider using PostGres database.

Below, you will find the steps of writing a simple java program, that will connect to postgres database, using JDBC.

You can use this as a stepping stone to freebie build database based web application.

Step 1. First thing you do is to download the JDBC Driver for the postGres SQL Database.

This can be done from link http://jdbc.postgresql.org/download.html

Before you download, you need to know the version of your database.

Step 2. Save the downloaded jar file in any given location.

Step 3. Include this downloaded JDBC Driver location into the CLASSPATH.

This can either be done on command prompt, or use jDeveloper/Eclipse type of tool.

Below screenshot shows how this can be done when developing & testing in jDeveloper.

Step 4.

Edit the pg_hba.conf file in the machine where PostGres Database resides.

Put the IP Address/host from where database will be accessed.

You can also make wildcard entries to all access from all machines, if need be.

vi /san/data/xxdir/xxloc/pg_hba.conf

At the very bottom of this file, I added to two entries similar to below

host    dbnameHere           dsuat   155.999.136.56  255.255.255.255 trust

host    dbnameHere           dsuat   155.999.136.56  255.255.255.0 trust

Step 5

Bounce the postgres database.

This is required for changes to pg_hba.conf to take place.

Bouncing the database can be done by executing two commands, as shown below

pg_ctl stop -m fast -D /san/xxdir/xxloc/dblocationDirectory

postmaster -D /san/xxdir/xxloc/dblocationDirectory &

Step 6

Write a class and test out the results.

package xxspiralutilities;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class xxQueueSizeUtilities {

    public static String getQueueSize() {

        try {

            Class.forName("org.postgresql.Driver");

        } catch (ClassNotFoundException cnfe) {

            System.err.println("Couldn't find driver class:");

            System.out.println("Couldn't find driver class:");

            cnfe.printStackTrace();

        }

        System.out.println("Able to locate the jmake a connection.");

        Connection postGresConn = null;

        try {

            postGresConn =

                    DriverManager.getConnection("jdbc:postgresql://xxmachine.com:5433/nameOfDBHere",

                                                "userNameHere", "passwordHere");

        } catch (SQLException se) {

            System.out.println("Couldn't connect: print out a stack trace and exit.");

            se.printStackTrace();

            System.exit(1);

        }

        if (postGresConn != null)

            System.out.println("Successfully connected to Postgres Database");

        else

            System.out.println("We should never get here.");

        try {

            Statement stGetCount = postGresConn.createStatement();

            ResultSet rs =

                stGetCount.executeQuery("SELECT SUM(import_count -import_remaining) from  xx_queue_table where finish is null and import_end is null");

            //ResultSet rs = stGetCount.executeQuery("SELECT SUM(import_count -import_remaining) from  xx_queue_table") ;

            rs.next();

            return rs.getString(1);

        } catch (SQLException e) {

            System.out.println("Could not create statement in JDBC");

            e.printStackTrace();

        }

        return "SUCCESS";

    }

    public static void main(String[] args) {

        String textString = getQueueSize();

        System.out.println("Result is " + textString);

    }

}

Anil Passi

Add comment