Chapter 13: Configuring and Deploying JDBC Resources with PostgreSQL

Overview

Relational database management systems (RDBMSs) are a cornerstone fixture of almost all enterprise systems. To handle database connections, most modern Java application servers utilize the J2EE Connector Architecture 1.5 (JCA) to create a pool of connection objects that is managed by the application server. In addition to JDBC connections, a JCA resource adapter can also manage JMS connection factories. This will be the subject of Chapter 16.

Relational databases are everywhere, and there are many from which to choose. This chapter discusses PostgreSQL. Started as a follow-on project to the Ingres project, the POSTGRES (post-Ingres) project began in 1986 at the University of California, Berkeley and has enjoyed a long and healthy life in the Open Source community. Today, PostgreSQL is a BSD-licensed, Open Source, relational database developed by the community.

Now backed in various forms by such companies as Pervasive Software, EnterpriseDB, and Greenplum, PostgreSQL is an enterprise-ready relational database with an impressive set of features. It provides such functionality as cursors, full outer joins, functions, indexes, inheritance, multi-version concurrency, point-in-time recovery, spatial data types, transactionality (both distributed and local), triggers, user-defined types, views, and much, much more.

While this chapter does not outline all the necessary details to get PostgreSQL configured, there are many documents available that cover this topic in depth, starting with the official documentation at the PostgreSQL Web site (http://www.postgresql.org/). This chapter assumes that PostgreSQL is set up on the localhost. Replacing the term localhost with the location of your PostgreSQL instance should work just fine. The version of PostgreSQL being used for this chapter is:

 bsnyder=# select version();                                       version -----------------------------------------------------------------------------------  PostgreSQL 8.1.3 on i686-apple-darwin8.6.1, compiled by GCC i686-apple-darwin8- gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5332) (1 row) bsnyder=#

Note that the preceding command can only be run once the PostgreSQL server is up and running. See the section “Starting PostgreSQL” later in this chapter for a discussion on the start command.

Notice that this version of PostgreSQL (8.1.3) was compiled for Mac OS X (see the powerpc-apple- darwin8.6.1 reference in the previous code). Note that there is no requirement to use Mac OS X for this chapter.