Skip to content

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>configuration generic jdbc</name>
 
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.3.RELEASE</version>
    </parent>
 
    <dependencies>
        <!-- dépendances variables ********************************************** -->
        <!-- driver JDBC from SGBD -->
        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.1-901-1.jdbc4</version>
        </dependency>        
      <!-- dépendances constantes ********************************************** -->
        ...
    </dependencies>
...
</project>
  • lines 18–22: the PostgreSQL JDBC driver;

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


    // paramètres de connexion
    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 URL_DBPRODUITSCATEGORIES = "jdbc:postgresql:dbproduitscategories";
    public final static String USER_DBPRODUITSCATEGORIES = "postgres";
public final static String PASSWD_DBPRODUITSCATEGORIES = "postgres";

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


    // max number of parameters of 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:


    // product insertion
    @Bean
    public SimpleJdbcInsert simpleJdbcInsertProduit(DataSource dataSource) {
        return new SimpleJdbcInsert(dataSource)
                .withTableName(ConfigJdbc.TAB_PRODUITS)
                .usingGeneratedKeyColumns(ConfigJdbc.TAB_PRODUITS_ID)
                .usingColumns(ConfigJdbc.TAB_PRODUITS_NOM, ConfigJdbc.TAB_PRODUITS_PRIX, ConfigJdbc.TAB_PRODUITS_DESCRIPTION,
                        ConfigJdbc.TAB_PRODUITS_CATEGORIE_ID);
    }
 
    // insertion category
    @Bean
    public SimpleJdbcInsert simpleJdbcInsertCategorie(DataSource dataSource) {
        return new SimpleJdbcInsert(dataSource).withTableName(ConfigJdbc.TAB_CATEGORIES)
                .usingGeneratedKeyColumns(ConfigJdbc.TAB_CATEGORIES_ID)
                .usingColumns(ConfigJdbc.TAB_CATEGORIES_NOM);
}

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 provider JPA
    @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.

 

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.

https://stahe.github.io
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.