Glassfish and PostgreSQL

Pramod Gopinath's Weblog

What were the versions of the database and jdbc driver used ?
The version of the postgres database used was 8.0.1 and the jdbc driver was postgresql-8.2dev-500.jdbc3.jar.

What are the jdbc parameters for connecting to a postgresql database ?
If you are planning to connect to the postgres database running on another machine ensure that the database allows for TCP/IP connections to it. Refer the chapter titled "Client Authentication" in the Postgresql manual to enable clients to connect to your postgresql database.
The jdbc connection parameters for Postgres are as follows :

driverName	"org.postgresql.Driver"
ConnectionUrl	"jdbc:postgresql://<ip-address/machineName>:<port>/<dbName>"
userName	"<userNameAllowedForDB>"
password	"<password>"

How do you configure Glassfish to connect to a Postgresql database ?
Ensure that the jdbc connection pool is properly configured in Glassfish. The ideal way would be to use the admin gui (http://localhost:4848) screen to achieve this. But unless the fix for  issue is made you would need to do this steps using the appropriate asadmin create-jdbc-connection-pool command e.g

asadmin create-jdbc-connection-pool --user admin --passwordfile passwords.txt --host localhost --port 4848  --datasourceclassname org.postgresql.ds.PGSimpleDataSource --restype javax.sql.DataSource --property portNumber=5432:password=postgres:user=postgres:serverName=localhost:databaseName=testDB PostgresPool

After the jdbc connection pool has been added to domain.xml, restart the server. You can the add a jdbc resource by using the admin gui.


What are some of known Issues/Workarounds/Recommendations ?
This is the first cut at providing support for Postgresql database with glassfish. Please file issues if and when you find them. Here are some issues that we are aware of at this point of time and suggested workarounds. This is not a exhaustive list. We have added stuff that were seen during my tests using Postgresql database.

    1. Postgresql stores the table and column names in lower case, unless you have quoted the names. This is important when dealing with native queries and SqlResultSetMapping annotation. Ensure that you have specified the correct names to get the results back from the database.

    2. At this point of time we do not support the following postgresql specific datatypes :
        Monetary Types, Geometric Types, Network Address Types, Arrays, Composite Types, Object Identifier Types and Pseudo Types.

    3. Postgresql has a datatype called "SERIAL" for which the database creates an implicit sequence. The name of the sequence is <tableName>_<serialColumnName>_seq.
        Lets now relate it to EJB3 and Glassfish.
        If the user has defined the following in their Employee entity class
        @Id
        @GeneratedValue=(strategy=SEQUENCE)
        @Column(name="CUST_ID")
        public Long getId() { return id; }
       
and used java2db feature, the table created in postgresql database would have the CUST_ID defined as SERIAL.
        In this first implementation support for Postgresql database in Glassfish, the recommendation is to define the sequence name too.
        So change your code sample listed above to now read

        @Id
        @GeneratedValue(strategy=SEQUENCE, generator="EMPLOYEE_SEQUENCE_GENERATOR")
        @SequenceGenerator(name="EMPLOYEE_SEQUENCE_GENERATOR", sequenceName="EMPLOYEE_CUST_ID_SEQ")
        @Column(name="CUST_ID")
        public Long getId() { return id; }
        
Sequence name that would be used (w/o the schema name). This information would be used by glassfish to get the id value after an insert statement. Until we make changes to the code base please use this workaround. Posted by pramodg ( Feb 21 2006, 02:53:18 PM PST ) Permalink Comments [10]