PostgreSQL database server configuration

So far we have learned how to set up a local environment to work with a Java EE example using Derby as the database server.

In the rest of the course we will work with a remote PostgreSQL database. It will be necessary to do some changes as described below:

• Running the asadmin start-database command is not required anymore.

• Open Eclipse.

• Similarly as we did with to see the Derby database in Eclipse, in the "Data Source Explorer" view, right click and "New...". To configure the new Driver, we will need to proceed as we did in the Database inspection. In the step 5 of that tutorial, a driver must be set, we can find the PostgreSQL driver in this link.

• For each group to configure the database, the properties are (replace XX for the number of your group):

  1. Database: db2019_gXX
  2. URL: jdbc:postgresql://data2.hib.no:5433/db2019_gXX
  3. User: g2019_XX
  4. Password: Provided by the instructor to each group

• The file persistence.xml need to be modified. Below there is an example of configuration:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
	xmlns="http://xmlns.jcp.org/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
	<persistence-unit name="ExamplePU" transaction-type="JTA">
	   	 <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
		<jta-data-source>java:app/db2019_gXX</jta-data-source>
	 	<class>name_of_entity_class</class>
		<properties>
      		<property name="javax.persistence.schema-generation.database.action" value="create"/>
		</properties>
	</persistence-unit>
</persistence>

• And the same with the glassfish-resources.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//GlassFish.org//DTD GlassFish Application Server 3.1 Resource Definitions//EN" "http://glassfish.org/dtds/glassfish-resources_1_5.dtd">
<resources>
	<jdbc-connection-pool
		allow-non-component-callers="false" associate-with-thread="false"
		connection-creation-retry-attempts="0"
		connection-creation-retry-interval-in-seconds="10"
		connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0"
		connection-validation-method="auto-commit"
		datasource-classname="org.postgresql.ds.PGSimpleDataSource"
		fail-all-connections="false" idle-timeout-in-seconds="300"
		is-connection-validation-required="false"
		is-isolation-level-guaranteed="true"
		lazy-connection-association="false" lazy-connection-enlistment="false"
		match-connections="false" max-connection-usage-count="0"
		max-pool-size="32" max-wait-time-in-millis="60000" name="post-gre-sql_db2019_gXXPool"
		non-transactional-connections="false" ping="false"
		pool-resize-quantity="2" pooling="true"
		res-type="javax.sql.DataSource" statement-cache-size="0"
		statement-leak-reclaim="false" statement-leak-timeout-in-seconds="0"
		statement-timeout-in-seconds="-1" steady-pool-size="8"
		validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
		<property name="serverName" value="data2.hib.no" />
		<property name="portNumber" value="5433" />
		<property name="databaseName" value="db2019_gXX" />
		<property name="User" value="g2019_XX" />
		<property name="Password" value="provided by the instructor" />
		<property name="URL"  value="jdbc:postgresql://data2.hib.no:5433/db2019_gXX" />
		<property name="driverClass" value="org.postgresql.Driver" />
	</jdbc-connection-pool>
    <jdbc-resource enabled="true" jndi-name="java:app/db2019_gXX" object-type="user" pool-name="post-gre-sql_db2019_gXXPool"/>
</resources>

• The dependency of PostgreSQL must be set in the pom.xml:

<dependency>
	<groupId>postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>9.1-901-1.jdbc4</version>
</dependency>