11. PostgreSQL 9.4

We will now address the port to PostgreSQL 9.4 of what was done with Oracle.

11.1. Setting up the working environment

11.1.1. Eclipse environment

We will be working with the following Eclipse environment:

The PostgreSQL projects listed above can be found in the folder [<examples>/spring-database-config\postgresql\eclipse].

Note: Press [Alt-F5] to regenerate all Maven projects.

11.1.2. Generating the databases

Throughout the rest of this guide, connections to PostgreSQL databases are made using the credentials [postgres / postgres]. Start PostgreSQL and its client [PgManager] (see section 23.7).

In [1], load the SQL script [<examples>\spring-database-config\postgresql\databases\dbproduits.sql];

In [2], as with Oracle, the JPA layers use sequences to generate primary keys. Here, the sequence [products_sequence] generates the primary keys for the [products] table;

Now, run the configurations:

[spring-jdbc-generic-01.IntroJdbc01];
[spring-jdbc-generic-01.IntroJdbc02];
[spring-jdbc-generic-03.JUnitTestDao1];
[spring-jdbc-generic-03.JUnitTestDao2];

They should all pass.

Now let’s generate the [dbproduitscategories] database. Repeat the procedure used to create [dbproduits] for [dbproduitscategories]. The SQL script to load is located at [<examples>\spring-database-config\postgresql\databases\ dbproduitscategories.sql] ;

Now, run the tests:

[spring-jdbc-generic-04.JUnitTestDao];
[spring-jpa-generic-JUnitTestDao-openjpa];

Both should pass.

11.2. Configuring the JDBC Layer

The [postgresql-config-jdbc] project configures the [JDBC] layer of the following test architecture:

The project is analogous to the [mysql-config-jdbc] configuration project for the JDBC layer of the MySQL DBMS (see Section 3.3). We present only the changes:

The [pom.xml] file imports the PostgreSQL JDBC driver:


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>dvp.spring.database</groupId>
    <artifactId>generic-config-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>generic jdbc configuration</name>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.3.RELEASE</version>
    </parent>

    <dependencies>
        <!-- variable dependencies ********************************************** -->
        <!-- JDBC driver for the DBMS -->
        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.1-901-1.jdbc4</version>
        </dependency>        
      <!-- constant dependencies ********************************************** -->
        ...
    </dependencies>
...
</project>

lines 18–22: the PostgreSQL JDBC driver;

The second change is in the [ConfigJdbc] class, which defines the database credentials:


    // connection parameters
    public final static String DRIVER_CLASSNAME = "org.postgresql.Driver";
    public final static String URL_DBPRODUITS = "jdbc:postgresql:dbproduits";
    public final static String USER_DBPRODUITS = "postgres";
    public final static String PASSWD_DBPRODUITS = "postgres";
    public final static String PRODUCT_CATEGORY_DB_URL = "jdbc:postgresql:productcategories";
    public final static String USER_PRODUCT_CATEGORIES_DB = "postgres";
public final static String PASSWD_DBPRODUCTSCATEGORIES = "postgres";

The third modification that can be made is to the maximum number of parameters that a [PreparedStatement] can support:


    // maximum number of parameters for a [PreparedStatement]
    public final static int MAX_PREPAREDSTATEMENT_PARAMETERS = 10000;

The [JUnitTestPushTheLimits] test generates SQL statements for 5,000 products, which will generate [PreparedStatement] objects with 5,000 parameters. MySQL supported this value. PostgreSQL did as well.

The fourth change is more surprising:


public final static String TAB_PRODUITS_ID = "id";
public final static String TAB_CATEGORIES_ID = "id";

The names of the [ID] columns in the [CATEGORIES] and [PRODUCTS] tables must be in lowercase for the [spring-jdbc-04] project. Otherwise, there is a crash in statements using the following two beans from this project:


    // Add product
    @Bean
    public SimpleJdbcInsert productInsert(DataSource dataSource) {
        return new SimpleJdbcInsert(dataSource)
                .withTableName(ConfigJdbc.PRODUCTS_TABLE)
                .usingGeneratedKeyColumns(ConfigJdbc.PRODUCTS_ID)
                .usingColumns(ConfigJdbc.PRODUCTS_NAME, ConfigJdbc.PRODUCTS_PRICE, ConfigJdbc.PRODUCTS_DESCRIPTION,
                        ConfigJdbc.PRODUCTS_CATEGORY_ID);
    }

    // insert category
    @Bean
    public SimpleJdbcInsert simpleJdbcInsertCategory(DataSource dataSource) {
        return new SimpleJdbcInsert(dataSource).withTableName(ConfigJdbc.TAB_CATEGORIES)
                .usingGeneratedKeyColumns(ConfigJdbc.TAB_CATEGORIES_ID)
                .usingColumns(ConfigJdbc.TAB_CATEGORIES_NAME);
}

11.3. Configuration of the OpenJPA JPA layer

The [postgresql-config-jpa-openjpa] project configures the [JPA] layer of the test architecture:

The project is analogous to the [oracle-config-jpa-openjpa] configuration project for the OpenJpa JPA layer of the Oracle DBMS (see Section 10.5). In fact, both DBMSs use sequences to generate primary keys. There is only one change to make. It is in the definition of the [jpaVendorAdapter] bean in the [ConfigJpa] class:


    // the JPA provider
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        OpenJpaVendorAdapter openJpaVendorAdapter = new OpenJpaVendorAdapter();
        openJpaVendorAdapter.setShowSql(false);
        openJpaVendorAdapter.setDatabase(Database.POSTGRESQL);
        openJpaVendorAdapter.setGenerateDdl(true);
        return openJpaVendorAdapter;
}

Line 6: We tell the JPA implementation that it will be working with a PostgreSQL database. The JPA implementation will then adopt the proprietary data types and SQL of this DBMS.

With these changes made, running the [spring-jpa-generic-JUnitTestDao-openjpa] configuration should succeed.

11.4. Configuring the Hibernate JPA layer

Note: Press [Alt-F5] to regenerate all Maven projects.

The [postgresql-config-jpa-hibernate] project is analogous to the [oracle-config-jpa-hibernate] project (Section 10.4) with the same modifications that were used to port the [oracle-config-jpa-openjpa] project to the [postgresql-config-jpa-openjpa] project (Section 11.3).

With these modifications in place, the execution of the [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink] configuration should succeed.

11.5. Configuring the EclipseLink JPA layer

Note: Press [Alt-F5] to regenerate all Maven projects.

The [postgresql-config-jpa-eclipselink] project is analogous to the [oracle-config-jpa-eclipselink] (Section 10.3) with the same modifications that were used to port the [oracle-config-jpa-openjpa] project to the [postgresql-config-jpa-openjpa] project (Section 11.3).

With these modifications in place, the execution of the [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink] configuration should succeed.