Skip to content

1. Introduction

The PDF of this document is available |HERE||.

The examples in this document are available |HERE|.

1.1. Contents

In this document, we propose to study different database deployment configurations. Consider the following layered architecture:

The execution flow goes from left to right:

  • one of the classes in the [ui] (User Interface) layer is executed first. It instantiates the [business] and [DAO] layers. If the [ui] layer is a graphical user interface, it then waits for user actions. A user action can trigger the execution of methods across all layers of the architecture down to the database. The result of these executions is returned to the user in one form or another;

The roles of the different layers could be as follows:

  • The [JDBC] (Java Database Connectivity) layer is a universal database access interface. It always presents the same interface to the [DAO] layer. If you switch DBMSs, you simply need to change the JDBC driver. The [DAO] layer remains unchanged if you have taken care to follow a certain set of rules. However, it is difficult to ensure 100% portability between DBMSs because they often contain a significant amount of proprietary SQL that is hard to ignore, as it often provides performance gains. As soon as proprietary SQL is used, portability between DBMSs is no longer possible. Furthermore, DBMSs often have different policies for automatically generating primary keys, as well as reserved keywords that vary from one system to another. In this document, we have nevertheless succeeded in porting the studied JDBC architecture to six different DBMSs by accepting that a configuration project is required for each of them;
  • the [DAO] layer exposes an interface for accessing data from the specific database being used (to be distinguished from the JDBC interface, which exposes methods valid for any DBMS);
  • the [business] layer implements the application’s management rules or business rules.
    • Its input data consists of data from the database via the [DAO] layer and/or user input transmitted to it by the [UI] layer;
    • It produces data that it can save to the database via the [DAO] layer and/or return to the [UI] layer that queried it, for display to the user;
  • The [UI] layer is the layer that executes the user’s actions and returns the results of those actions to the user;

Above, the [DAO] layer sends SQL queries to the [JDBC] layer for execution in the DBMS. In recent years (since 2006), this architecture has evolved as follows:

It is now the JPA (Java Persistence API) layer that sends SQL queries to the JDBC layer and receives the results. The [JPA] layer presents operations to the [DAO] layer for persisting, modifying, deleting, and retrieving objects. The [DAO] layer no longer issues SQL commands. This approach is more portable because JPA implementations handle differences between DBMSs, but it is slower than JDBC technology. We will conduct performance tests to demonstrate this. JPA technology formalizes the work done by the Hibernate framework [http://hibernate.org/] years ago.

We will examine two [DAO] layers using one of the following two architectures:

We will require the [DAO1] and [DAO2] layers to implement the same [IDAO] interface. Thus, the [JUnitTestsDao] test will be the same for both configurations and will allow us to compare performance. The [DAO1] layer will be implemented with Spring JDBC and the [DAO2] layer with Spring JPA;

Once this is done, we will expose the [IDAO] interface on the web as follows:

  • In [1], the [IDAO] layer is exposed on the web through a web layer [2] implemented by Spring MVC. It is indeed the [IDAO] interface that is exposed, and we will build two versions of the web service depending on whether this interface is implemented using a [DAO-JDBC] or [DAO-JPA-JDBC] architecture;
  • In [B], a remote client uses the URLs exposed by the web service, which provide access to the methods of the [IDAO-server] layer. We will ensure that the [DAO-Client] layer [3] implements the [IDAO-server] interface [1]. This will allow us to use the same [JUnitTestsDao] test that has already been used twice [4];
  • In [3], the [DAO-Client] layer will be implemented using Spring RestTemplate;

Once this is done, we will secure access to the web service:

  • in [5], the client’s HTTP request passes through an authentication layer implemented with Spring Security;

Once this is done, we will evolve the previous architecture into the following:

  • In [3], the client application is itself a web application. It will feature a form [5] allowing users to query the URLs of the secure web service. HTTP access to the secure web service will be handled via a [DAO-client-js] layer implemented in JavaScript. This architecture utilizes what are known as cross-domain requests:
    • the web service [2] provides URLs of the form [http://machine1:port1/];
    • the client web application [3] is downloaded from a URL [http://machine2:port2/]. If [http://machine2:port2/] is not identical to [http://machine1:port1/] (same machine, same port), then the client browser will block HTTP calls from the [DAO-client-js] layer. To resolve this issue, the web service must allow cross-domain requests. We will see how;

The projects presented have been tested with the following six DBMSs:

  • MySQL 5 Community Edition;
  • SQL Server 2014 Express;
  • PostgreSQL 9.4;
  • Oracle Express 11g Release 2;
  • IBM DB2 Express-C 10.5;
  • Firebird 2.5.4;

For each of these DBMSs, we developed four different [DAO] layers:

  • a layer implemented with Spring JDBC;
  • a layer implemented with Spring JPA and the Hibernate JPA provider;
  • a layer implemented with Spring JPA and the EclipseLink JPA provider;
  • a layer implemented with Spring JPA and the OpenJPA JPA provider;

Thus, a set of twenty-four different configurations is presented here. We made a significant effort to factorize the code:

  • most of the code is written only once. It is based on two Maven configuration projects:
    • one configures the JDBC layer;
    • the other configures the JPA layer;

The Maven configuration project for the JDBC layer [1] of a specific DBMS consists of two steps:

  • importing the JDBC driver archive;
  • defining the access credentials for the database in use and the various SQL statements that the [DAO1] layer will send to the JDBC driver. Although SQL is standardized, we encountered portability issues primarily due to the presence in queries of table/column names that turned out to be prohibited keywords in certain DBMSs (the ROLES table for DB2, the PASSWORD column for Firebird). Furthermore, although a column name is normally case-insensitive (upper/lowercase), we encountered an issue with PostgreSQL regarding the ID column of the primary key in the tables. It required that it be named "id" in lowercase. These are typical examples of unexpected portability issues;

The three Maven projects for configuring the JPA layer [2] of a specific DBMS also consist of two steps:

  • importing the JPA implementation archive;
  • configure the JPA implementation used for the specific connected DBMS. In fact, it is the JPA layer that issues SQL commands to the JDBC layer. To be effective, it must know the DBMS in order to send it SQL commands that it will recognize. These commands may use the proprietary SQL of that DBMS as well as its specific features (data types, sequences, triggers, procedures, automatic generation of primary keys, etc.);

This results in twenty-four Maven configuration projects (4 configurations × 6 DBMS) upon which all other database operation projects will be based. In the diagrams above, since the [DAO1] and [DAO2] layers provide the same interface, the 24 configurations of the two architectures above will be tested using a single test class [JUnitTestsDao]. Once these architectures have been verified, there are no further difficulties:

  • the Maven project for publishing the database on the web is based on these two architectures. There are therefore also 24 possible configurations here;
  • the Maven project for securing access to the web service builds on the previous project and also has 24 possible configurations;
  • finally, the Maven project enabling cross-domain requests to the secure web service builds on the previous project and also has 24 possible configurations;

The study is conducted using the MySQL5 DBMS and the Hibernate JPA implementation. We then port the code to the Eclipselink and OpenJPA JPA implementations. Finally, we port it to other databases (PostgreSQL, Oracle, SQL Server, DB2, Firebird).

This course is intended for beginners. Most of the concepts used are explained. No prior knowledge of database programming or web programming is required. However, a solid understanding of the SQL language is necessary, as the SQL queries used are not explained.

To understand the examples, you need a basic knowledge of the Java language, which can be found in any introductory course on the language. The first two chapters of the document [Introduction to the Java Language] will suffice. It is an older document (1998, revised in 2002), but the fundamentals are there. For a comprehensive course, one can read Jean-Marie Doudoux’s extensive book [http://www.jmdoudoux.fr/java].

This document is by no means exhaustive. It is intended solely to provide a methodology and code that can be reused in similar contexts. The document has been written so that it can be read without a computer at hand. Therefore, many screenshots are included.

Although it does not cover all the capabilities of the Java language or all its areas of application, this document can be used as a learning resource for the language. By following this document—even if not in its entirety—a beginner reader will reach an “advanced Java” level in both the use of the language and the Spring framework. They can then continue their Java training with the following books:

  • [Spring MVC and Thymeleaf by Example] [https://stahe.github.io/en-springmvc-thymeleaf-janv-2015/], which continues the exploration of the Spring ecosystem by introducing its "MVC web programming" branch;
  • [AngularJS / Spring MVC Tutorial] [https://stahe.github.io/en-spring-angular1.x-juillet-2014/], which presents a client/server web architecture, where the client is implemented using the [AngularJS] framework and the server using [Spring MVC];
  • [Introduction to Java EE], which moves away from the Spring ecosystem to a web architecture based on JSF (Java Server Faces) and EJB (Enterprise JavaBeans);
  • [Introduction to Android Tablet Programming] [https://stahe.github.io/en-android-aout-2016/] which describes a client/server architecture, where the client is an Android tablet and the server is a web service implemented by Spring MVC;

1.2. Sources

This document has two main sources:

  • [ ref1]: [Spring MVC and Thymeleaf by Example] at the URL [https://stahe.github.io/en-springmvc-thymeleaf-janv-2015/]. This document revisits the work done and presented in [ref1] using a different database. Simply put, it removes it from the context of web programming with Spring MVC. I decided to create a separate document because I found that the code and methodology used in [ref1] to expose a database on the web were reusable;
  • [ ref2]: [Java Persistence in Practice] at the URL [https://stahe.github.io/en-jpa-juin-2007/];

To learn more about Spring, you can use the following references:

Readers with insufficient SQL knowledge can learn the basics from the book [Introduction to SQL with the Firebird DBMS] at the URL [http://tahe.developpez.com/divers/sql-firebird/].

1.3. Tools Used

The following examples have been tested in the following environment:

  • Windows 8.1 Pro 64-bit machine;
  • JDK 1.8 (section 23.1);
  • Spring Tool Suite 3.6.3 IDE (section 1);
  • Chrome browser (other browsers were not used);
  • Chrome extension [Advanced Rest Client] (section 1);
  • MySQL 5.6 Community Edition DBMS (Section 23.4);
  • SQL Server 2014 Express (Section 23.9);
  • PostgreSQL 9.4 (section 23.7);
  • Oracle Express 11g Release 2 DBMS (paragraph 23.6);
  • IBM DB2 Express-C 10.5 DBMS (section 23.8);
  • Firebird 2.5.4 DBMS (Section 23.10);
  • the EMS Manager clients for these six DBMSs (Section 23.5);

Note regarding JDK 1.8: One method in the case study uses a method from the [java.lang] package in Java 8.

Most of the examples are Maven projects that can be opened in Eclipse, IntelliJ IDEA, or NetBeans. In the following, the screenshots are from the Spring Tool Suite IDE, a variant of Eclipse.

1.4. The examples

The examples are available |HERE| as a downloadable ZIP file.

  • In [1], the example folders;
  • in [2], the [spring-core] folder contains the Spring learning projects;
  • in [3], the [spring-database-config] folder contains the JDBC and JPA configuration projects for the six databases;
  • in [4], the Oracle DBMS configuration. It contains three folders:
    • [databases] contains the SQL scripts for generating the two databases used in the document;
    • [jdbc-driver] contains the Oracle JDBC driver as well as a script to install it in the local Maven repository;
    • [eclipse] contains [5] the four Oracle configuration projects:
      • [oracle-config-jdbc] configures the JDBC layer for accessing the DBMS;
      • [oracle-config-jpa-hibernate] configures the JPA layer for accessing the DBMS using the Hibernate JPA provider;
      • [oracle-config-jpa-eclipselink] configures the JPA layer for database access using the Eclipselink JPA provider;
      • [oracle-config-jpa-openjpa] configures the JPA layer for database access using the OpenJPA JPA provider;
  • in [6], the [eclipse config / launch configurations] folder contains the launch configurations that the reader can import into Eclipse and then adapt to their own environment;
  • in [7], the [spring-database-generic] folder contains all the DB access code common to the six DBMSs and the three JPA providers;
  • In [8], [spring-jdbc] contains four projects that demonstrate the JDBC API as well as Spring JDBC;
  • In [9], [spring-jpa / spring-jpa-generic] is the project that uses a JPA layer to access a database. The [generic-create-db*] projects are JPA projects used to create the databases utilized by the JPA layer;

  • In [10], the [spring-webjson] folder contains the projects that expose the database on the web;

    • [spring-webjson-server-jdbc-generic] is the web service that exposes the database accessed via Spring JDBC;
    • [spring-webjson-server-jpa-generic] is the web service that exposes the database accessed via Spring JPA;
    • [spring-webjson-client-generic] is the single client that allows access to the two previous web services;

  • in [11], the [spring-security] folder contains the projects that expose the database on the web with secure access;

    • [spring-security-server-jdbc-generic] is the secure web service that exposes the database accessed via Spring JDBC;
    • [spring-security-server-jpa-generic] is the secure web service that exposes the database accessed via Spring JPA;
    • [spring-security-client-generic] is the single client that allows access to the two previous secure web services;

  • in [12], the [spring-cors] folder contains the projects that expose the database on the web with secure access allowing cross-domain access, such as that originating from a browser’s JavaScript code;

    • [spring-cors-server-jdbc-generic] is the secure web service that allows cross-domain access and exposes the database accessed via Spring JDBC;
    • [spring-cors-server-jpa-generic] is the secure web service that allows cross-domain access and exposes the database accessed via Spring JPA;
    • [spring-cors-client-generic] is a web application that queries the two previous web services;
https://tahe.developpez.com
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.