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 work 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 [<exemples>/spring-database-config\postgresql\eclipse] folder.

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

11.1.2. Generating the databases

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

  • In [1], the script SQL will be loaded;
  • In [2], as with Oracle, the JPA layers use sequences to generate the primary keys. Here, the sequence [produits_sequence] generates the primary keys for the table [produits];

Now, execute 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 succeed.

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

  

Now, run the configurations:

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

Both must succeed.

11.2. Configuration of the JDBC layer

 

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

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

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


<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 JDBC driver from PostgreSQL;

The second change is in the [ConfigJdbc] class, which defines the database access 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 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 orders for 5,000 products, which will generate [PreparedStatement] with 5,000 parameters. MySQL also 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 column names in [ID] from the tables [CATEGORIES] and [PRODUITS] must be in lowercase for the project [spring-jdbc-04]. Otherwise, there is a crash on 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 JPA layer OpenJpa

 

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 JPA OpenJpa layer of the SGBD Oracle (see section 10.5). In fact, both SGBD use sequences to generate primary keys. There is only one change to make. It is in the definition of the [jpaVendorAdapter] bean of 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;
}
  1. Line 6: The JPA implementation is instructed to work with a PostgreSQL database. The JPA implementation will then adopt both the proprietary data types and the SQL that owns this SGBD.

Once these changes are made, the execution of the [spring-jpa-generic-JUnitTestDao-openjpa] configuration should succeed.

11.4. Configuration of the JPA Hibernate layer

 

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

The [postgresql-config-jpa-hibernate] project is similar to the [oracle-config-jpa-hibernate] project (Section 10.4) with the same modifications that were used to port [oracle-config-jpa-openjpa] 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: Run [Alt-F5] to regenerate all Maven projects.

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

With these modifications made, 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.