vsbabu.org

PostgreSQL on Mandrake 9.1 with Java

This is a followup article to yesterday’s experiment on quickly diving into PostgreSQL on Mandrake 9.1 (this is probably the only thing Mark Pilgrim hasn’t dived into yet on his sites ). I will try to explain how to get started with accessing data in our PostgreSQL from Java. Installing Java, necessary drivers and writing a small Java program are covered.

I. Helpful references before you start

If you don’t have the faintest idea what I’m talking about, you definitely need to take a look at:

1. Java tutorial.
2. PostgreSQL documentation.
3. Setting up Postgres in Mandrake 9.1.
4. JDBC tutorial if you want to understand the code.

II. Setup PostgreSQL

Yesterday, we didn’t enable TCP/IP connection in our default installation. We will need to enable TCP/IP sockets to allow JDBC to connect to our database.

1. Login as root using su - root.
2. Edit ~postgres/data/postgresql.conf and add a line below Connection Parameters like so:
   tcpip_socket = true
   Save the file.
3. Edit ~postgres/data/pg_hba.conf and add a line at the end like so:
   host all all 127.0.0.1 255.255.255.255 trust
   This means anyone in this machine can access any database. Needless to say, it is very insecure. Small comfort is that the database is only open to local machine. If you trust your machine, you should be fine.
4. Last step is to reload the configuration. Easiest way is to restart the service by /etc/rc.d/init.d/postgresql restart.

III. Install Java

You can download Java SDK and many additional tools from JPackage. They have these in RPM form and installation is a simple matter of downloading the package and running

rpm -Uvh installed_package.rpm

as root. I used java-1.4.2-sun.

Now, you need to install the JDBC driver for PostgreSQL. Get your Mandrake CD’s and run the following command as root:

urpmi postgresql-jdbc

To find out the drivers installed, you can run:

rpm -ql postgresql-jdbc

My output is given below. We will use pg73jdbc3.jar. Read the README file, it is pretty detailed.

/usr/share/doc/postgresql-jdbc-7.3.2
/usr/share/doc/postgresql-jdbc-7.3.2/README
/usr/share/pgsql/pg73jdbc1.jar
/usr/share/pgsql/pg73jdbc2.jar
/usr/share/pgsql/pg73jdbc2ee.jar
/usr/share/pgsql/pg73jdbc3.jar

IV. Try out a Java program

Fire up your favourite editor and create a file called connectPostgreSQL.java with the following code:

/**
 * Created on Apr 13, 2003
 * @author vsbabu
 * 
 * modified from
 * http://www.macdevcenter.com/mac/2002/06/07/examples/connectPostgreSQL.java
 * 
 */
import java.sql.*;

public class connectPostgreSQL {

    public static void main(java.lang.String[] args) {

        try {
            // Here we load the JDBC driver.
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Unable to load driver!");
            return;
        }

        try {

            /* Here I’m connecting to my database
             * using the standard JDBC URL for PostgreSQL
             * (jdbc:postgresql:) followed by the name of
             * our database, vsbabu. I’m also passing the
             * username "vsbabu" and the password "".
             */

            Connection pSQL =
                DriverManager.getConnection(
                    "jdbc:postgresql:vsbabu",
                    "vsbabu",
                    "");

            /* This is where we create a Statement object
             * and execute a SQL query using it. 
             */

            Statement query = pSQL.createStatement();

            ResultSet states =
                query.executeQuery(
                    "SELECT state_code, state_name FROM states;");

            /* Here we’ll loop through the ResultSet, 
             * printing out state code and name 
             */

            while (states.next()) {

                System.out.println(
                    states.getString("state_code")
                        + " = "
                        + states.getString("state_name"));

            }

            // Close all of our JDBC resources.
            states.close();
            query.close();
            pSQL.close();

        } catch (SQLException se) {
            // Give feedback for any SQL errors.
            System.out.println("SQL Exception: " + se.getMessage());
            se.printStackTrace(System.out);
        }

    }

}

Note that I’m using my login of vsbabu. Since we setup the server to trust local connections and local users (Step II above), we don’t need to give a password.

If you are wondering how a table called states came around, read the previous article.

Let us compile this program by running:

javac connectPostgreSQL.java

You shouldn’t see any errors.

All that is left to do is to run it and see how it works.

[vsbabu@toshiba pgsql]$ java connectPostgreSQL
Unable to load driver!

Ah! We installed JDBC drivers, but didn’t inform Java VM about the classpath.

[vsbabu@toshiba pgsql]$ java -classpath $CLASSPATH:/usr/share/pgsql/pg73jdbc3.jar connectPostgreSQL
VA = Virginia
MD = Maryland

V. Making use of Java

One immediate advantage of having Java connectivity is that you can use any number of Java database GUI managers that are available. This article on using PostgreSQL on Mac OS X mentioned ViennaSQL, one such client. Or you can try Squirrel-SQL which is a much more functional and that much more heavier client.

That’s all folks for now! Next in article in this series explains using ViennaSQL.

1. Spot on introduction.. just what I needed.. cheers!

   Posted by: gustchaser on July 8, 2003 03:42 AM

2. i installed postgresql7.3.2.in linux redhat9.0. i created a user and database also.i changed the postgresql.conf with tcpip_socket=true and saved the file.then i edited pg_hba.conf as u gave before.then i gave the restart command as /etc/rc.d/init.d/postgresql restart
   then i got this error
   Stopping postgresql service: [FAILED]
   rm: cannot remove `/var/run/postmaster.5432.pid': Permission denied
   rm: cannot remove `/var/lock/subsys/postgresql': Permission denied
   Initializing database: mkdir: cannot chdir to directory `/var/lib/pgsql': Permission denied
   chown: failed to get attributes of `/var/lib/pgsql/data': Permission denied
   chmod: failed to get attributes of `/var/lib/pgsql/data': Permission denied
   cp: accessing `/var/lib/pgsql/data/../initdb.i18n': Permission denied
   /etc/rc.d/init.d/postgresql: line 148: /var/lib/pgsql/data/../initdb.i18n: Permission denied
   standard in must be a tty
   [FAILED]
   Starting postgresql service: standard in must be a tty
   touch: creating `/var/lock/subsys/postgresql': Permission denied ]
   /etc/rc.d/init.d/postgresql: line 174: /var/run/postmaster.5432.pid: Permission denied
   can u please help me?
   plz rely as soon as possible.
   

   Posted by: mahija on November 27, 2003 06:51 AM