14. Firebird 2.5.4

We will now address the port to Firebird 2.5.4 of what was done with Oracle.

14.1. Setting up the work environment

14.1.1. Eclipse environment

We will be working with the following Eclipse environment:

The Firebird projects listed above can be found in the [<exemples>/spring-database-config\firebird\eclipse] folder.

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

14.1.2. Generating the databases

As was done with Oracle, DB2, and SQL Server, we will need to install the Firebird driver JDBC in the local Maven repository.

The [install.bat] file contains the following code:


"%M2_HOME%\bin\mvn.bat" install:install-file -Dfile=jaybird-2.2.7.jar -Dpackaging=jar -DgroupId=org.firebirdsql.jdbc -DartifactId=jaybird -Dversion=2.2.7

where [%M2-HOME%] is the Maven installation directory (see section 23.2). After this installation, the Firebird driver JDBC can be referenced in [pom.xml] files using the following dependency:


        <dependency>
            <groupId>org.firebirdsql.jdbc</groupId>
            <artifactId>jaybird</artifactId>
            <version>2.2.7</version>
</dependency>

Throughout the rest of this guide, connections to Firebird databases are made using the credentials [sysdba / masterkey]. Start Firebird and its client [IBManager] (see section 23.10). Firebird databases are unique in that they are encapsulated in a single file.

In [1], the file will be found in [<exemples>\spring-database-config\firebird\databases\DBPRODUITS.GDB];

as with Oracle, primary keys are generated using sequences.

We proceed in the same way to load the database [dbproduitscategories], which will be found in [<exemples>\spring-database-config\firebird\databases\DBPRODUITSCATEGORIES.GDB]

Now, run the configurations:

[spring-jdbc-generic-01.IntroJdbc01];
[spring-jdbc-generic-01.IntroJdbc02];
[spring-jdbc-generic-03.JUnitTestDao1];
[spring-jdbc-generic-03.JUnitTestDao2] ;
[spring-jdbc-generic-04.JUnitTestDao] ;
[spring-jpa-generic-JUnitTestDao-openjpa] ;

They must all succeed.

14.2. Configuration of layer JDBC

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

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

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


<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>org.firebirdsql.jdbc</groupId>
            <artifactId>jaybird</artifactId>
            <version>2.2.7</version>
        </dependency>
        <!-- required for Firebird driver -->
        <dependency>
            <groupId>javax.resource</groupId>
            <artifactId>connector-api</artifactId>
            <version>1.5</version>
        </dependency>
        <dependency>
            <groupId>org.antlr</groupId>
            <artifactId>antlr-runtime</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- dépendances constantes ********************************************** -->
        ...
    </dependencies>
...
</project>

lines 18–22: the Firebird driver JDBC;
lines 24–33: the dependencies required for the Firebird driver JDBC;

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


    // connection parameters
    public final static String DRIVER_CLASSNAME = "org.firebirdsql.jdbc.FBDriver";
    public final static String URL_DBPRODUITS = "jdbc:firebirdsql:localhost/3050:<exemples>/SPRING-DATABASE-CONFIG/FIREBIRD/DATABASES/DBPRODUITS.GDB";
    public final static String USER_DBPRODUITS = "sysdba";
    public final static String PASSWD_DBPRODUITS = "masterkey";
    public final static String URL_DBPRODUITSCATEGORIES = "jdbc:firebirdsql:localhost/3050:<exemples>/SPRING-DATABASE-CONFIG/FIREBIRD/DATABASES/DBPRODUITSCATEGORIES.GDB";
    public final static String USER_DBPRODUITSCATEGORIES = "sysdba";
public final static String PASSWD_DBPRODUITSCATEGORIES = "masterkey";

Lines 3 and 6: You must enter the exact paths for both databases;

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 = 1000;

The [JUnitTestPushTheLimits] test generates SQL commands for 5,000 products, which will generate [PreparedStatement] with 5,000 parameters. MySQL supported this value. Firebird does not.

The fourth change concerns a reserved word in Firebird-config-jdbc: PASSWORD. The column [USERS.PASSWORD] has therefore been renamed [USERS.PASSWD]:


public final static String TAB_USERS_PASSWORD = "PASSWD";
public static final String SELECT_USER_BYLOGIN = "SELECT u.ID as u_ID, u.VERSIONING as u_VERSIONING, u.NAME as u_NAME,u.LOGIN as u_LOGIN,u.PASSWD as u_PASSWORD FROM USERS u WHERE u.LOGIN= :login";

14.3. Configuration of the JPA layer OpenJpa

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

The project is analogous to the [firebird-config-jpa-openjpa] configuration project for the JPA and OpenJpa layers 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.DEFAULT);
        openJpaVendorAdapter.setGenerateDdl(true);
        return openJpaVendorAdapter;
}

Line 6: We instruct the JPA implementation that it will be working with an unrecognized SGBD. The JPA implementation will then adopt the standard data types from SQL.

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

14.4. Configuration of the JPA Hibernate layer

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

The [firebird-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 [oracle-config-jpa-openjpa] to the [firebird-config-jpa-openjpa] project (Section 10.4).

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

14.5. Configuration of the JPA layer EclipseLink

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

The [firebird-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 [firebird-config-jpa-openjpa] project (section 10.4).

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