Using PostgreSQL in Java without JDBC

JDBC is the Java starndard API for accessing relational databases. There is any good reason for not using it, directly or through a higher-level API/framework (Hibernate, JPA, etc.)? Usually not, but if you feel you need, probably it's a good reason.

My own reason is related to pgpool-II and its parallel mode. As far as I know (please write to me if I'm wrong), pgpool's parallel mode doesn't support the PostgreSQL extended query sub-protocol. Unfortunately the PostgreSQL JDBC Driver can use only that protocol. Even setting the JDBC Driver for using the V2 protocol doesn't solve the problem because other issues in the communication with pgpool.

The only solution I found is not using JDBC, but accessing PostgreSQL through the libpq C library. Of course I need to do it in Java, so I wrapped the libpq library in Java classes, using the SWIG tool.

You'll find the code of my experiments in this Github repository.

Let's go through the full process.

1. Install SWIG, downloading from here, or using the correct package of your OS. I use Debian, so I just needed to install the swig package:
   

   apt-get install swig
   

2. Prepare a SWIG interface file, describing the C functions you want to wrap. If you want to wrap a whole library, this task could be very simple, as you can simply include the C header files:
   pqswig.i:

   %module pq
   %{
   #include "/usr/include/postgresql/libpq-fe.h"
   %}
   
   %include "/usr/include/postgresql/libpq-fe.h"
   

3. Use the SWIG command for generating the Java wrapper classes and the C SWIG wrapper functions:

   swig -java \
       -package com.benfante.experiments.libpqwrapper.swig \
       -outdir \
       ../../../generated/java/com/benfante/experiments/libpgwrapper/swig \
       pqswig.i

   In the same directory you'll find the pqswig_wrapper.c file. In the indicated path (../../../generated/java/com/benfante/experiments/libpgwrapper/swig, change it following your needs) you'll find the Java wrapper classes.
4. Compile the pqswig_wrapper.c file:
   

   gcc -c pqswig_wrap.c -I /usr/lib/jvm/java-6-sun/include -I /usr/lib/jvm/java-6-sun/include/linux

5. Build the dynamic library:

   ld -G pqswig_wrap.o -o libpqswig.so -lpq

6. Copy (or link) the libpqswig.o file into a directory of the java.library.path. For example, copy it into the /usr/lib directory:

   cp libpqswig.so /usr/lib/

   That's all. Of course the previous steps are for a Linux OS. If you are using a different OS, read the SWIG documentation and FAQs (in particular the SharedLibraries section).

Now you can execute code like the following:

package com.benfante.experiments.libpgwrapper;

import com.benfante.experiments.libpqwrapper.swig.ConnStatusType;
import com.benfante.experiments.libpqwrapper.swig.ExecStatusType;
import com.benfante.experiments.libpqwrapper.swig.SWIGTYPE_p_pg_conn;
import com.benfante.experiments.libpqwrapper.swig.SWIGTYPE_p_pg_result;
import com.benfante.experiments.libpqwrapper.swig.pq;

public class App {

    public static void main(String[] args) {
        System.out.println(System.getProperty("java.library.path"));
        System.loadLibrary("pqswig");
        System.out.println("PQ Library Version: " + pq.PQlibVersion());
        SWIGTYPE_p_pg_conn connection = connectToPostgres("localhost", "5432", "test", "test", "test");

        SWIGTYPE_p_pg_result res = selectAllPerson(connection);

        printTuples(res);

        pq.PQclear(res);

        pq.PQfinish(connection);
    }

    private static SWIGTYPE_p_pg_conn connectToPostgres(String host, String port, String db, String username, String password) {
        SWIGTYPE_p_pg_conn connection = pq.PQconnectdb("postgresql://" + username + ":" + password + "@" + host + ":" + port + "/" + db);
        if (pq.PQstatus(connection) != ConnStatusType.CONNECTION_OK) {
            System.out.println("Connection to database failed: " + pq.PQerrorMessage(connection));
            exitNicely(connection);
        }
        return connection;
    }

    private static SWIGTYPE_p_pg_result selectAllPerson(SWIGTYPE_p_pg_conn connection) {
        SWIGTYPE_p_pg_result res = pq.PQexec(connection, "SELECT * FROM person");
        if (pq.PQresultStatus(res) != ExecStatusType.PGRES_TUPLES_OK) {
            System.out.println("SELECT failed: " + pq.PQerrorMessage(connection));
            pq.PQclear(res);
            exitNicely(connection);
        }
        return res;
    }

    private static void exitNicely(SWIGTYPE_p_pg_conn connection) {
        pq.PQfinish(connection);
        System.exit(0);
    }

    private static void printTuples(SWIGTYPE_p_pg_result res) {
        int nTuples = pq.PQntuples(res);
        int nFields = pq.PQnfields(res);
        for (int i = 0; i < nFields; i++) {
            System.out.printf("%-15s|", pq.PQfname(res, i));
        }
        System.out.println("");
        for (int i = 0; i < nTuples; i++) {
            for (int j = 0; j < nFields; j++) {
                System.out.printf("%-15s|", pq.PQgetvalue(res, i, j));
            }
            System.out.println("");
        }
    }
}