PostgreSQL with Glassfish

jakarta-ee

How to setup JDBC connection to a Postgres datasource in Glassfish

Prerequisites

  • PostgreSQL & phppgadmin
  • Java 8
  • Glassfish 4.1

First create a connection pool

  • Download a Postgres JDBC driver (I got the JDBC41 Postgresql Driver, Version 9.3-1102 driver)
  • Copy the downloaded jar to the [glassfish install folder]/glassfish/domains/[your domain]/lib folder
  • Restart GlassFish
  • Open the GlassFish admin pannel (e.g. http://localhost:4848)
  • Goto to Resources -> JDBC -> JDBC Connection Pool

  • Press the New... button
  • Give the pool a name
  • Resource Type to javax.sql.ConnectionPoolDataSource
  • Database Driver Vendor to Postgresql
  • Press Next
  • Fill properties
    • User
    • DatabaseName
    • ServerName (localhost??)
    • PortNumber (5432 for standard postgres)
  • Remove all other properties by selecting them and pressing Delete properties button.
  • Finish

Test the connection pool

  • Select the newly created pool
  • Press Ping button in General Tab to check the connection.
  • Fix possible errors in property values if ping is not successful

Create JDBC Resource in Glassfish

  • Open the GlassFish admin pannel (e.g. http://localhost:4848)
  • Goto to Resources -> JDBC -> JDBC Connection
  • Press New button.
  • Enter JNDI Name like jdbc/thedatabase-name
  • Choose the created connection pool pool
  • Save changes.

asadmin command-line

You can also do all of the above from the commandline:

Creating the ConnectionPool…

1
asadmin create-jdbc-connection-pool --datasourceclassname org.postgresql.ds.PGConnectionPoolDataSource --restype javax.sql.ConnectionPoolDataSource --property portNumber=5432:password=PASSWORD:user=USER:serverName=localhost:databaseName=DB_NAME_HERE CONNECTION_POOLNAME_HERE

Creating the DataSource:

1
asadmin create-jdbc-resource --connectionpoolid CONNECTION_POOLNAME_HERE jdbc/JNDI_NAME_HERE

Don’t forget to replace the UPPERCASE words and note the corresponding names!

No Questions Asked

You can make your scripts run smoothly by adding a passwordfile and –user to the command

Create a file called pwd.txt containing (some of) these options:

1
2
3
4
AS_ADMIN_PASSWORD=value
AS_ADMIN_ADMINPASSWORD=value
AS_ADMIN_USERPASSWORD=value
AS_ADMIN_MASTERPASSWORD=value

For me the first option was enough but I guess it would depend on your setup. Just try it out.

Now the commands would look like this:

1
2
3
asadmin --user USERNAME --passwordfile ./pwd.txt create-jdbc-connection-pool --datasourceclassname org.postgresql.ds.PGConnectionPoolDataSource --restype javax.sql.ConnectionPoolDataSource --property portNumber=5432:password=PASSWORD:user=USER:serverName=localhost:databaseName=DB_NAME_HERE CONNECTION_POOLNAME_HERE

asadmin --user USERNAME --passwordfile ./pwd.txt create-jdbc-resource --connectionpoolid CONNECTION_POOLNAME_HERE jdbc/JNDI_NAME_HERE

Concusion

Now we can refer to the database from persistence.xml in our application.