4. Introduction to Spring JDBC
In this chapter, we will examine the following architecture:
 |
This is the same architecture as before. We will introduce two changes:
- the database will have two tables linked by a foreign key relationship;
- the [DAO] layer will be implemented using the [Spring JDBC] library, which simplifies the management of API and JDBC;
4.1. Setting up the work environment
Using STS, import the [spring-jdbc-04] project located in the [<exemples>/spring-database-generic/spring-jdbc] folder
 |
In addition, we need to create a new database named MySQL using the [MyManager] client (see section 3.1):
 |
- In [3], the following examples work on a database named MySQL called [dbproduitscategories];
 |
- In [9], enter the root user’s password (this password is “root” in this document);
 |
 |
- In [18], the database [dbproduitscategories] was created empty. We create tables and populate it with a script SQL [19-20];
 |
- In [21], navigate to the [<exemples>/spring-database-config/mysql/databases] folder;
 |
- In [25], make sure you are positioned on the [dbproduitscategories] database and not the [dbproduits] database;
- In [29], the script SQL created five tables. The [ROLES, USERS, USERS_ROLES] tables will only be used when we address securing the web service built to expose the [dbproduitscategories] database on the web;
4.2. The [dbproduitscategories] database
The [dbproduitscategories] database is an extension of the [dbproduits] database discussed previously. Whereas in the [PRODUITS] table the product had a category identified by a number that had no particular meaning, here this number will be a foreign key in the [CATEGORIES] table.
The [PRODUITS] table is as follows:
 |
- [ID]: the auto-increment primary key of table [2];
- [NOM]: the unique product name of [4];
- [PRIX]: the product price;
- [DESCRIPTION]: the product description;
- [VERSIONING] is the product's version number. Its initial version is 1 [3]. Each time the product is modified, its version number will be incremented by the code that operates the table;
- [CATEGORIE_ID]: the foreign key in the [CATEGORIES] table to designate the category to which the product belongs;
 |
- in [1-3], the foreign key [CATEGORIE_ID] from the table [PRODUITS]. It targets the [ID] column of the [CATEGORIES] table [4-5];
- when a category is deleted, all products linked to it are also deleted [6]. This point is important to note because it is used in the construction of the [DAO] layer utilizing the [dbproduitscategories] database;
The [CATEGORIES] table of categories is as follows:
 |
- [ID]: auto-incrementing primary key;
- [VERSIONING]: version number of the category;
- [NOM]: unique name of the category;
4.3. The Eclipse Project
 |
The [spring-jdbc-04] project implements the following architecture:
 |
The [spring-jdbc-04] project is a Maven project configured by the following [pom.xml] file:
 |
<?xml version="1.0" encoding="UTF-8"?>
<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>spring-jdbc-generic-04</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-jdbc-generic-04</name>
<description>Demo project for Spring JdbcTemplate</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.2.3.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- configuration JDBC of SGBD -->
<dependency>
<groupId>dvp.spring.database</groupId>
<artifactId>generic-config-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
<!-- Spring JdbcTemplate -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.18.1</version>
</plugin>
</plugins>
</build>
</project>
- lines 28–32: the project relies on the [mysql-config-jdbc] project, which configures the JDBC layer;
- lines 34-37: the [spring-boot-starter-jdbc] artifact includes the Spring libraries JDBC;
In the end, the dependencies are as follows:
 |
4.4. Spring Configuration
 |
The [AppConfig] class that configures the Spring project is as follows:
package spring.jdbc.config;
import generic.jdbc.config.ConfigJdbc;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
@ComponentScan(basePackages = { "spring.jdbc.dao" })
@EnableTransactionManagement
@Import({ generic.jdbc.config.ConfigJdbc.class })
public class AppConfig {
// data source
@Bean
public DataSource dataSource() {
// data source TomcatJdbc
DataSource dataSource = new DataSource();
// configuration access JDBC
dataSource.setDriverClassName(ConfigJdbc.DRIVER_CLASSNAME);
dataSource.setUsername(ConfigJdbc.USER_DBPRODUITSCATEGORIES);
dataSource.setPassword(ConfigJdbc.PASSWD_DBPRODUITSCATEGORIES);
dataSource.setUrl(ConfigJdbc.URL_DBPRODUITSCATEGORIES);
// initially open connections
dataSource.setInitialSize(5);
// result
return dataSource;
}
// Transaction manager
@Bean
public PlatformTransactionManager transactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
// JdbcTemplate
@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
// product insertion
@Bean
public SimpleJdbcInsert simpleJdbcInsertProduit(DataSource dataSource) {
return new SimpleJdbcInsert(dataSource).withTableName(ConfigJdbc.TAB_PRODUITS).usingGeneratedKeyColumns(
ConfigJdbc.TAB_PRODUITS_ID);
}
// insertion category
@Bean
public SimpleJdbcInsert simpleJdbcInsertCategorie(DataSource dataSource) {
return new SimpleJdbcInsert(dataSource).withTableName(ConfigJdbc.TAB_CATEGORIES).usingGeneratedKeyColumns(
ConfigJdbc.TAB_CATEGORIES_ID);
}
}
- line 16: the class is a Spring configuration class;
- line 17: the [spring.jdbc.dao] package will be scanned for Spring components other than those present in the [AppConfig] class. The component implementing the [DAO] layer will be found there;
- line 18: we will not manage transactions ourselves but leave them to Spring JDBC. The only thing to do will be to annotate the methods that need to be executed within a transaction with the Spring [@Transactional] annotation. Line 18 ensures that this annotation is processed and not ignored. Transaction management is handled by one of the project dependencies, Spring JDBC, imported by the file [pom.xml];
- line 19: we import the beans already defined in the [generic.jdbc.config.ConfigJdbc] class from the [mysql-config-jdbc] project;
- lines 23–36: the data source [tomcat-jdbc] introduced in the example [spring-jdbc-02];
- lines 40–42: the transaction manager associated with the previously defined data source. The bean must be named [transactionManager], as this is the name used by the [@EnableTransactionManagement] annotation. The handler [DataSourceTransactionManager] is provided by the Spring library JDBC (line 12);
- lines 45–48: the [namedParameterJdbcTemplate] bean on which the implementation of the [DAO] layer will rely. This bean is provided by the Spring library JDBC (line 10). This bean is also linked to the data source defined previously (line 47);
- lines 51–55: the [simpleJdbcInsertProduit] bean (arbitrary name) will be used to insert a product into the [PRODUITS] table and retrieve the generated primary key. The various parameters used are as follows:
- [dataSource]: the [tomcat-jdbc] data source from lines 24–36;
- [ConfigJdbc.TAB_PRODUITS]: the table [PRODUITS];
- [ConfigJdbc.TAB_CATEGORIES_ID]: the primary key column of the [PRODUITS] table. Note that for PostgreSQL, the name of this column must be in lowercase;
- lines 58–62: the bean [simpleJdbcInsertCategorie] will be used to insert a category into the table [CATEGORIES] and retrieve the generated primary key;
4.5. Project Exceptions
 |
We have already seen the [UncheckedException, DaoException, ShortException] classes in the [spring-jdbc-03] project. We are adding a new one:
package spring.jdbc.infrastructure;
public class MyIllegalArgumentException extends UncheckedException {
private static final long serialVersionUID = 1L;
// manufacturers
public MyIllegalArgumentException() {
super();
}
public MyIllegalArgumentException(int code, Throwable e, String className) {
super(code, e, className);
}
}
- The class [MyIllegalArgumentException] derives from the class [UncheckedException] and is therefore an unchecked class. It will be used to signal a call with incorrect arguments to a method in the [DAO] layer. We did not name it [IllegalArgumentException] because this exception already exists in JDK, and this sometimes caused the compiler to generate an incorrect [import];
4.6. Project Entities
 |
The classes in the [spring.jdbc.entities] package are the representations of the rows in the tables of the [dbproduitscategories] database. For now, we will ignore the representations of the [USERS, ROLES, USERS_ROLE] tables.
All entities extend the parent class [AbstractCoreEntity]:
package spring.jdbc.entities;
public abstract class AbstractCoreEntity {
// properties
protected Long id;
protected Long version;
// manufacturers
public AbstractCoreEntity() {
}
public AbstractCoreEntity(Long id, Long version) {
this.id = id;
this.version = version;
}
public AbstractCoreEntity(AbstractCoreEntity entity) {
this.id = entity.id;
this.version = entity.version;
}
public void setAbstractCoreEntity(AbstractCoreEntity entity) {
this.id = entity.id;
this.version = entity.version;
}
// ------------------------------------------------------------
// redefine [equals] and [hashcode]
@Override
public int hashCode() {
return (id != null ? id.hashCode() : 0);
}
@Override
public boolean equals(Object entity) {
if (!(entity instanceof AbstractCoreEntity)) {
return false;
}
String class1 = this.getClass().getName();
String class2 = entity.getClass().getName();
if (!class2.equals(class1)) {
return false;
}
AbstractCoreEntity other = (AbstractCoreEntity) entity;
return id != null && other.id != null && id.equals(other.id);
}
// getters and setters
...
}
- line 5: the field [id] will be associated with the column [ID], the primary key of the tables;
- line 6: the field [version] will be associated with the column [VERSIONING] in the tables;
- lines 8–26: various constructors and methods for constructing or initializing a [AbstractCoreEntity] object;
- lines 35–47: the [equals] method states that two [AbstractCoreEntity] objects are equal if they have the same [id] field. It should be noted here that [AbstractCoreEntity] objects will be images of table rows where [id] is the primary key and where, therefore, there cannot be two rows with the same [id];
- Lines 30–33: a proposal for [hashCode];
The class [Produit] will be the representation of a row in the table [PRODUITS]:
package spring.jdbc.entities;
import com.fasterxml.jackson.annotation.JsonFilter;
@JsonFilter("jsonFilterProduit")
public class Produit extends AbstractCoreEntity {
// properties
private String nom;
private Long idCategorie;
private double prix;
private String description;
private Categorie categorie;
// manufacturers
public Produit() {
}
public Produit(Long id, Long version, String nom, Long idCategorie, double prix, String description,
Categorie categorie) {
super(id, version);
this.nom = nom;
this.idCategorie = idCategorie;
this.prix = prix;
this.description = description;
this.categorie = categorie;
}
// signature
public String toString() {
return String.format("[id=%s, version=%s, nom=%s, prix=10.2f, desc=%s, idCategorie=%s]", id, version, nom, prix,
description, idCategorie);
}
// getters and setters
...
}
- line 6: the [Produit] class extends the [AbstractCoreEntity] class;
- lines 8–12: the [id, version, nom, idCategorie, prix, description] fields are the images of the [ID, VERSIONING, NOM, CATEGORIE_ID, PRIX, DESCRIPTION] columns in the [PRODUITS] table;
- line 12: the object of type [Categorie] with primary key [idCategorie]. This field may or may not be populated, depending on the case. When it is filled in, we refer to the product version (long) [LongProduit]; otherwise, to the product version (short) [ShortProduit];
- Line 5: a filter jSON. Note that the project [mysql-config-jdbc] includes a library jSON. The filter is necessary because the [categorie] field may or may not be filled in. In this case, the jSON representation of the product differs. To handle these two cases, we will configure the [jsonFilterProduit] filter on line 5. A jSON filter allows us to dynamically specify the fields to exclude from the jSON representation. When it is determined that the [categorie] field has not been filled in, it will be excluded from the jSON representation of the product;
The [Categorie] class represents a row in the [CATEGORIES] table:
package spring.jdbc.entities;
import java.util.ArrayList;
import java.util.List;
import com.fasterxml.jackson.annotation.JsonFilter;
@JsonFilter("jsonFilterCategorie")
public class Categorie extends AbstractCoreEntity {
// properties
private String nom;
public List<Produit> produits;
// manufacturers
public Categorie() {
}
public Categorie(Long id, Long version, String nom, List<Produit> produits) {
super(id, version);
this.nom = nom;
this.produits = produits;
}
// signature
public String toString() {
return String.format("[id=%s, version=%s, nom=%s]", id, version, nom);
}
// methods
public void addProduit(Produit produit) {
// add a product
if (produits == null) {
produits = new ArrayList<Produit>();
}
if (produit != null) {
// we add the product
produits.add(produit);
// set your category
produit.setCategorie(this);
produit.setIdCategorie(this.id);
}
}
// getters and setters
...
}
- Line 9: The class [Categorie] extends the class [AbstractCoreEntity];
- line 12: the [id, version, nom] fields are the images of the [ID, VERSIONING, NOM] columns in the [CATEGORIES] table;
- line 13: the [produits] field represents the list of products in the category. This field is not always populated. When it is not, we refer to a short category version ([ShortCategorie]); otherwise, a long category version ([LongCategorie]);
- lines 32–44: the [addProduit] method allows you to add a product to the category (line 39) and to set the category’s characteristics (idCategorie and category) in the added product;
- line 8: a filter jSON. When the library jSON needs to serialize/deserialize an object [Categorie], it must be instructed on how to handle the filter named [jsonFilterCategorie];
4.7. The Idao<T> interface
 |
 |
The [IDao] interface of the [DAO] layer has the following signature:
package spring.jdbc.dao;
import java.util.List;
import spring.jdbc.entities.AbstractCoreEntity;
public interface IDao<T extends AbstractCoreEntity> {
// list of all T entities
public List<T> getAllShortEntities();
public List<T> getAllLongEntities();
// particular entities - version short
public List<T> getShortEntitiesById(Iterable<Long> ids);
public List<T> getShortEntitiesById(Long... ids);
public List<T> getShortEntitiesByName(Iterable<String> names);
public List<T> getShortEntitiesByName(String... names);
// particular entities - version long
public List<T> getLongEntitiesById(Iterable<Long> ids);
public List<T> getLongEntitiesById(Long... ids);
public List<T> getLongEntitiesByName(Iterable<String> names);
public List<T> getLongEntitiesByName(String... names);
// update of several entities
public List<T> saveEntities(Iterable<T> entities);
public List<T> saveEntities(@SuppressWarnings("unchecked") T... entities);
// delete all entities
public void deleteAllEntities();
// deletion of multiple entities
public void deleteEntitiesById(Iterable<Long> ids);
public void deleteEntitiesById(Long... ids);
public void deleteEntitiesByName(Iterable<String> names);
public void deleteEntitiesByName(String... names);
public void deleteEntitiesByEntity(Iterable<T> entities);
public void deleteEntitiesByEntity(@SuppressWarnings("unchecked") T... entities);
}
- Line 7: Here we have an interface [IDao] parameterized by a type T with a condition: this type must extend the class [AbstractCoreEntity] or implement the interface [AbstractCoreEntity]. The keyword [extends] is used for both cases. Here, T will be instantiated either by the type [Produit] or by the type [Categorie]. In fact, it quickly becomes apparent that we are performing the same types of operations (insertion, modification, deletion, selection) on the types [Produit] and [Categorie]. It therefore makes sense to group these methods into a generic interface;
- depending on the case, the terms [LongEntity] and [ShortEntity] refer to different situations:
- when T is of type [Produit]:
- [ShortEntity] is the product without its [Categorie categorie] field filled in;
- [LongEntity] is the product with its [Categorie categorie] field filled in;
- when T is of type [Categorie]:
- [ShortEntity] is the category without its [List<Produit> produits] field filled in;
- [LongEntity] is the product with its [List<Produit> produits] field filled in;
- when T is of type [Produit]:
We therefore have an interface with 19 methods. Most of the methods are duplicates. Let’s take the example of the [getShortEntitiesById] method:
public List<T> getShortEntitiesById(Iterable<Long> ids);
public List<T> getShortEntitiesById(Long... ids);
- Lines 1 and 3: The parameter is the list of primary keys of the entities for which we want the short version. This list is presented in two different forms:
- line 1: a list implementing the [Iterable<Long>] interface. The type [List<Long>] implements this interface, but there are many others. If we had used [List<Long> ids], that would have been sufficient for our examples, but it would have forced the user of our examples to perform conversions if their parameter was not of the exact expected type;
- Line 3: Unfortunately, the type Long[] does not implement the [Iterable<Long>] interface. In this case, we will use version from line 3. The formal parameter [Long... ids] (3 points) can accept the value of either an array or a sequence of ids: getShortEntitiesById(id1, id2, ...);
This same interface IDao<T> will be implemented by the following architecture:
 |
where a [JPA] (Java Persistence Api) will be inserted between the [DAO] layer and the JDBC driver of the SGBD. This will allow us to have a common test layer for both architectures. In both cases, the [DAO] layer will have two interfaces:
- IDao<Product> to access the [PRODUITS] table;
- IDao<Category> to access the [CATEGORIES] table;
4.8. Implementation of the IDao<T> interface
|
- The interface IDao<Product> is implemented by the class [DaoProduit];
- The interface IDao<Category> is implemented by the class [DaoCategorie];
The classes [DaoProduit] and [DaoCategorie] both extend the following abstract class [AbstractDao] :
package spring.jdbc.dao;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.transaction.annotation.Transactional;
import spring.jdbc.entities.AbstractCoreEntity;
import spring.jdbc.infrastructure.MyIllegalArgumentException;
import com.google.common.collect.Lists;
public abstract class AbstractDao<T extends AbstractCoreEntity> implements IDao<T> {
// injections
@Autowired
@Qualifier("maxPreparedStatementParameters")
protected int maxPreparedStatementParameters;
// local
protected String simpleClassName = getClass().getSimpleName();
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesById(Iterable<Long> ids) {
// argument validity
List<T> entities = checkNullOrEmptyArgument(true, ids);
if (entities != null) {
return entities;
}
// obtaining by tranches
entities = new ArrayList<T>();
int taille = maxPreparedStatementParameters;
List<Long> listIds = Lists.newArrayList(ids);
int nbIds = listIds.size();
for (int i = 0; i < nbIds; i += taille) {
int limit = Math.min(nbIds, i + taille);
entities.addAll(getShortEntitiesById(listIds.subList(i, limit)));
}
// result
return entities;
}
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesById(Long... ids) {
// argument validity
List<T> entities = checkNullOrEmptyArgument(true, ids);
if (entities != null) {
return entities;
}
// result
return getShortEntitiesById((Iterable<Long>) Lists.newArrayList(ids));
}
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesByName(Iterable<String> names) {
...
}
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesByName(String... names) {
...
}
@Override
@Transactional(readOnly = true)
public List<T> getLongEntitiesById(Iterable<Long> ids) {
...
}
@Override
@Transactional(readOnly = true)
public List<T> getLongEntitiesById(Long... ids) {
...
}
@Override
@Transactional(readOnly = true)
public List<T> getLongEntitiesByName(Iterable<String> names) {
...
}
@Override
@Transactional(readOnly = true)
public List<T> getLongEntitiesByName(String... names) {
...
}
@Override
@Transactional
public List<T> saveEntities(Iterable<T> entities) {
...
}
@Override
@Transactional
public List<T> saveEntities(@SuppressWarnings("unchecked") T... entities) {
...
}
@Override
public void deleteEntitiesById(Iterable<Long> ids) {
...
}
@Override
public void deleteEntitiesById(Long... ids) {
...
}
@Override
public void deleteEntitiesByName(Iterable<String> names) {
...
}
@Override
public void deleteEntitiesByName(String... names) {
...
}
@Override
public void deleteEntitiesByEntity(Iterable<T> entities) {
...
}
@Override
public void deleteEntitiesByEntity(@SuppressWarnings("unchecked") T... entities) {
...
}
protected void deleteEntitiesByEntity(List<T> entities) {
...
}
@Override
@Transactional(readOnly = true)
public abstract List<T> getAllShortEntities();
@Override
@Transactional(readOnly = true)
public abstract List<T> getAllLongEntities();
@Override
public abstract void deleteAllEntities();
// méthodes privées ----------------------------------------------
private <T2> List<T> checkNullOrEmptyArgument(boolean checkEmpty, Iterable<T2> elements) {
...
}
@SuppressWarnings("unchecked")
private <T2> List<T> checkNullOrEmptyArgument(boolean checkEmpty, T2... elements) {
...
}
// méthodes protégées ----------------------------------------------
abstract protected List<T> getShortEntitiesById(List<Long> ids);
abstract protected List<T> getShortEntitiesByName(List<String> names);
abstract protected List<T> getLongEntitiesById(List<Long> ids);
abstract protected List<T> getLongEntitiesByName(List<String> names);
abstract protected List<T> saveEntities(List<T> entities);
abstract protected void deleteEntitiesById(List<Long> ids);
abstract protected void deleteEntitiesByName(List<String> names);
}
- Line 15: The class [AbstractDao] is abstract (keyword `abstract`). As such, it cannot be instantiated. It can only be derived from. This class has several roles:
- to define the nature of the transaction in which each method is executed;
- to handle as many common tasks as possible for the two implementations of the [IDao<Produit>] and [IDao<Categorie>] interfaces. This primarily involves validating the arguments. Null arguments and empty lists are not accepted;
- Unify the types of the parameters `T... params` and `Iterable<T> params` into a single type: `List<T> params`;
- delegate the work to the child classes as soon as it becomes specific to one of the two interfaces;
Thanks to the standardization of the parameters of the various methods performed by the [AbstractDao] class, the child classes [DaoProduit] and [DaoCategorie] will only have 10 methods to implement instead of 19:
// methods implemented by child classes ----------------------------------------------
abstract protected List<T> getShortEntitiesById(List<Long> ids);
abstract protected List<T> getShortEntitiesByName(List<String> names);
abstract protected List<T> getLongEntitiesById(List<Long> ids);
abstract protected List<T> getLongEntitiesByName(List<String> names);
abstract protected List<T> saveEntities(List<T> entities);
abstract protected void deleteEntitiesById(List<Long> ids);
abstract protected void deleteEntitiesByName(List<String> names);
@Override
@Transactional(readOnly = true)
public abstract List<T> getAllShortEntities();
@Override
@Transactional(readOnly = true)
public abstract List<T> getAllLongEntities();
@Override
public abstract void deleteAllEntities();
Let's look at some methods of the [AbstractDao] class.
Method [getShortEntitiesById]
This method retrieves the version list of entities for which the primary keys are provided.
// injections
@Autowired
@Qualifier("maxPreparedStatementParameters")
protected int maxPreparedStatementParameters;
// local
protected String simpleClassName = getClass().getSimpleName();
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesById(Iterable<Long> ids) {
...
}
- lines 2-4: we inject the [maxPreparedStatementParameters] bean defined in the [ConfigJdbc] configuration file, which configures the JDBC layer of a specific SGBD:
// max number of parameters of a [PreparedStatement]
public final static int MAX_PREPAREDSTATEMENT_PARAMETERS = 10000;
@Bean(name = "maxPreparedStatementParameters")
public int maxPreparedStatementParameters() {
return MAX_PREPAREDSTATEMENT_PARAMETERS;
}
- Lines 1–7: define the [maxPreparedStatementParameters] bean, which sets the maximum number of parameters that can be passed to a [PreparedStatement] type. This requirement did not arise with the SGBD and MySQL, which accepted 10,000 parameters for a [PreparedStatement] type. During testing with the SGBD and SQL servers, an exception was thrown indicating that the maximum number of parameters for a [PreparedStatement] type was 2,100. Therefore, this number has become a configuration parameter for the various SGBD instances. It must therefore be included in the [sgbd-config-jdbc] configuration project for each SGBD;
Let’s return to the code for the [getShortEntitiesById] method:
// injections
@Autowired
@Qualifier("maxPreparedStatementParameters")
protected int maxPreparedStatementParameters;
// local
protected String simpleClassName = getClass().getSimpleName();
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesById(Iterable<Long> ids) {
...
}
- line 7: the class name. Used as a parameter for one of the constructors of the [DaoException] exception class;
- line 10: the [@Transactional(readOnly = true)] annotation indicates that the method must be executed within a read-only transaction. One might question the usefulness of such a transaction, since the method only performs reads and therefore, in the event of a failure, there is nothing to roll back. The author of the [Spring Data] library recommends this and explains why. I followed his advice;
The body of the method is as follows:
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesById(Iterable<Long> ids) {
// argument validity
List<T> entities = checkNullOrEmptyArgument(true, ids);
if (entities != null) {
return entities;
}
...
}
- line 5: the validity of the parameter [ids] is checked by the following method:
private <T2> List<T> checkNullOrEmptyArgument(boolean checkEmpty, Iterable<T2> elements) {
// elements null ?
if (elements == null) {
throw new MyIllegalArgumentException(222, new NullPointerException("L'argument ne peut être null"), simpleClassName);
}
// empty elements?
if (!elements.iterator().hasNext()) {
if (checkEmpty) {
throw new MyIllegalArgumentException(223, new RuntimeException("l'argument ne peut être une liste vide"),
simpleClassName);
} else {
return new ArrayList<T>();
}
}
// default result
return null;
}
- line 1: the method [checkNullOrEmptyArgument] is a generic method parameterized by the type <T2>. T2 is the type of the elements passed as the second parameter of the method. This can be [Long, String, AbstractCoreEntity];
- line 1: the [checkNullOrEmptyArgument] method accepts two parameters:
- [Iterable<T2> elements]: the parameter to be tested;
- [checkEmpty]: set to true if we need to check that the previous parameter is a non-empty list;
- lines 4–6: we verify that the parameter [elements] is not null. If this is not the case, an exception of type [MyIllegalArgumentException] is thrown;
- lines 8-15: if the list is empty and we were supposed to check that it was non-empty, we throw an exception of type [MyIllegalArgumentException];
- line 13: if the list is empty and we were not supposed to check that it was non-empty, then we return an empty list of elements of type T. The [Iterable<T2>] interface has a method [iterator()] that allows iterating over the elements of the list implementing the interface. Two methods of this iterator are useful:
- [itérateur].hasNext(): returns true if the list still has an element to process, false otherwise;
- [iterateur].next(): returns the current element of the list and advances the iterator by one element;
- Finally,
- if the argument [T2... elements] is null or empty, an exception of type [MyIllegalArgumentException] is thrown;
- if the argument [T2... elements] is an empty list and that was valid, then an empty list of elements of type T is returned;
A similar method exists when the argument to be tested is of type [T2... elements]:
@SuppressWarnings("unchecked")
private <T2> List<T> checkNullOrEmptyArgument(boolean checkEmpty, T2... elements) {
...
}
Let’s return to the code for the [getShortEntitiesById] method:
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesById(Iterable<Long> ids) {
// argument validity
List<T> entities = checkNullOrEmptyArgument(true, ids);
// obtaining by tranches
entities = new ArrayList<T>();
int taille = maxPreparedStatementParameters;
List<Long> listIds = Lists.newArrayList(ids);
int nbIds = listIds.size();
for (int i = 0; i < nbIds; i += taille) {
int limit = Math.min(nbIds, i + taille);
entities.addAll(getShortEntitiesById(listIds.subList(i, limit)));
}
// result
return entities;
}
- line 7: if we get here, it means the argument [Iterable<Long> ids] is valid;
- lines 7–14: we will see later that the method [getShortEntitiesById] will be implemented by a type [PreparedStatement], which will take as parameters the list of primary keys to search for. For example:
public final static String SELECT_SHORTCATEGORIE_BYID = "SELECT c.ID as c_ID, c.VERSIONING as c_VERSIONING, c.NOM as c_NOM FROM CATEGORIES c WHERE c.ID in (:ids)";
:ids is a parameter whose actual value will be of type List<Long>. Each element of this list will be the subject of a parameter ? of type [PreparedStatement]. However, we have stated that this type accepts a maximum number of parameters, a number set by the [maxPreparedStatementParameters] field of the class;
- line 7: the list of T entities that will be returned by the [getShortEntitiesById] method. This list will be constructed in chunks of [maxPreparedStatementParameters] elements;
- line 9: using the [Iterable<Long> ids] argument, a [List<Long> listIds] type is created. The [Lists] class is a class from the Google Guava library that provides numerous static methods for manipulating collections of objects. The Google Guava library was imported (pom.xml) by the Maven project [mysql-config-jdbc]:
<!-- Google Guava -->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>16.0.1</version>
</dependency>
- line 10: the number of T entities to search for in the database;
- lines 11–13: they are searched for in groups of [taille = maxPreparedStatementParameters] elements;
- line 12: a calculation to prevent going beyond the end of the [listIds] list;
- line 13: the T entities are obtained via the [getShortEntitiesById(listIds.subList(i, limit))] call. This method is defined in the class as:
abstract protected List<T> getShortEntitiesById(List<Long> ids);
It is therefore the child class that will retrieve the T entities from the database:
- [DaoProduit] if T is of type [Produit];
- [DaoCategorie] if T is of type [Categorie];
The benefit of this work by the parent class is twofold:
- the signature of the [getShortEntitiesById] method in the child class is unique: its argument is of type [List<Long> ids];
- the child class does not have to deal with the issue of the [maxPreparedStatementParameters] parameters of a [PreparedStatement]. Its parent class has handled this for it;
- line 13: the entities returned by the child class are added to the list of entities that will be returned by the parent class (line 16);
Now, let’s look at the implementation of the other method, [getShortEntitiesById], in the class:
@Override
@Transactional(readOnly = true)
public List<T> getShortEntitiesById(Long... ids) {
// argument validity
List<T> entities = checkNullOrEmptyArgument(true, ids);
// result
return getShortEntitiesById((Iterable<Long>) Lists.newArrayList(ids));
}
- line 3: the nature of the argument has changed: Long... ids ;
- line 5: the validity of this argument is tested;
- line 7: the method [getShortEntitiesById] that we just described is called. Here again, we use the class [Lists] from the library [Google Guava]. Note that we must perform an explicit cast to the [Iterable<Long>] type to help the compiler choose the correct method, since the [getShortEntitiesById] method has three signatures in the class:
- List<T> getShortEntitiesById(Long... ids);
- List<T> getShortEntitiesById(Iterable<Long> ids);
- List<T> getShortEntitiesById(List<Long> ids), which is abstract and implemented by the child class;
We will not comment further on the abstract class [AbstractDao], the parent class of the classes [DaoProduit] and [DaoCategorie]. We will simply note that it is sometimes useful to factor out behaviors common to several classes into a parent class, whether abstract or not. After this work, the child classes only have the following methods left to implement:
// methods implemented by child classes ----------------------------------------------
abstract protected List<T> getShortEntitiesById(List<Long> ids);
abstract protected List<T> getShortEntitiesByName(List<String> names);
abstract protected List<T> getLongEntitiesById(List<Long> ids);
abstract protected List<T> getLongEntitiesByName(List<String> names);
abstract protected List<T> saveEntities(List<T> entities);
abstract protected void deleteEntitiesById(List<Long> ids);
abstract protected void deleteEntitiesByName(List<String> names);
@Override
@Transactional(readOnly = true)
public abstract List<T> getAllShortEntities();
@Override
@Transactional(readOnly = true)
public abstract List<T> getAllLongEntities();
@Override
public abstract void deleteAllEntities();
The code in Section 4.8 shows the different transaction types used for each method. Note the following points:
- methods that read the database are annotated with [@Transactional(readOnly = true)];
- methods that modify the database are annotated with [@Transactional];
- The [delete] methods are not annotated and therefore do not run within a transaction. The idea is that if a deletion fails, the user likely does not want to roll back all the previous successful deletions;
4.9. The [DaoCategorie] class
 |
|
The [DaoCategorie] class implements the [IDao<Categorie>] interface, which providesaccess to data in the [CATEGORIES] table of the MySQL database. Its skeleton is as follows:
package spring.jdbc.dao;
import generic.jdbc.config.ConfigJdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Component;
import spring.jdbc.entities.Categorie;
import spring.jdbc.entities.Produit;
import spring.jdbc.infrastructure.DaoException;
import com.google.common.collect.Lists;
@Component
public class DaoCategorie extends AbstractDao<Categorie> {
// constants
// injections
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
private SimpleJdbcInsert simpleJdbcInsertCategorie;
@Autowired
private IDao<Produit> daoProduit;
@Override
public List<Categorie> getAllShortEntities() {
...
}
@Override
public List<Categorie> getAllLongEntities() {
...
}
@Override
public void deleteAllEntities() {
...
}
@Override
protected List<Categorie> getShortEntitiesById(List<Long> ids) {
...
}
@Override
protected List<Categorie> getShortEntitiesByName(List<String> names) {
...
}
@Override
protected List<Categorie> getLongEntitiesById(List<Long> ids) {
...
}
@Override
protected List<Categorie> getLongEntitiesByName(List<String> names) {
...
}
@Override
protected List<Categorie> saveEntities(List<Categorie> entities) {
...
}
@Override
protected void deleteEntitiesById(List<Long> ids) {
...
}
@Override
protected void deleteEntitiesByName(List<String> names) {
...
}
...
}
// --------------------- mappers
class ShortCategorieMapper implements RowMapper<Categorie> {
....
}
class LongCategorieMapper implements RowMapper<Categorie> {
....
}
- line 28: the class [DaoCategorie] is a Spring component and, as such, can be injected into other Spring components;
- line 29: the class [DaoCategorie] extends the abstract class [AbstractDao<Categorie>], making it an implementation of the interface [IDao<Categorie>];
- lines 34–37: injection of beans defined in the [AppConfig] class described in section 4.4;
- lines 38–39: injection of a reference to the class [DaoProduit], which implements the interface [IDao<Produit>] that manages access to data in the table [PRODUITS];
- lines 41–89: implementation of the [IDao<Categorie>] interface;
- lines 95–101: two internal classes implementing the [RowMapper<T>] interface;
Let’s examine the methods one by one.
4.9.1. The [getAllShortEntities] method
The [getAllShortEntities] method converts all categories from the [CATEGORIES] table into their short version form:
@Override
public List<Categorie> getAllShortEntities() {
try {
return namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_ALLSHORTCATEGORIES, new ShortCategorieMapper());
} catch (Exception e) {
throw new DaoException(202, e, simpleClassName);
}
}
All methods rely on the [namedParameterJdbcTemplate] object defined in the Spring configuration file and provided by the Spring library JDBC. It has numerous methods. The one used above is as follows:
- [sql] is the SQL command to be executed;
- [rowMapper] is an instance of the following [RowMapper<T>] interface:
The idea is as follows:
- the method [namedParameterJdbcTemplate].query(String sql, RowMapper<T> rowMapper) executes the SQL command of type [Select]. It handles any exceptions, as well as opening and closing the connection to SGBD. The only thing it cannot do isencapsulate the elements of the [ResultSet] objects it obtains into a [Categorie] type because it does not know the relationship between the fields of the [Categorie] type and the columns of the [Resultset]. We will see later that this relationship is created using the JPA technology, which will automate the encapsulation of elements from a [ResultSet] into instances of type T. For now, the second parameter of the [query] method is an instance of the [RowMapper<T>] interface capable of performing this encapsulation;
Let’s return to the code:
@Override
public List<Categorie> getAllShortEntities() {
try {
return namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_ALLSHORTCATEGORIES, new ShortCategorieMapper());
} catch (Exception e) {
throw new DaoException(202, e, simpleClassName);
}
}
The order SQL [ConfigJdbc.SELECT_ALLSHORTCATEGORIES] is as follows:
public final static String SELECT_ALLSHORTCATEGORIES = "SELECT c.ID as c_ID, c.VERSIONING as c_VERSIONING, c.NOM as c_NOM FROM CATEGORIES c";
The query retrieves the [ID, VERSIONING, NOM] columns from the elements in the [CATEGORIES] table. We will consistently use the following syntax:
SELECT t1.COL1 as t1_COL1, t1.COL2 as t1_COL2 FROM TABLE1 t1, TABLE2 t2 WHERE ...
What is important is the naming of the columns obtained by SELECT with the [as nom_colonne] attribute. This is the only way to ensure portability between SGBD, as these all have a proprietary way of naming the columns obtained by a SELECT in which columns from different tables have the same name (ID, NOM, or VERSIONING, for example, in our case). We then resolve this ambiguity by specifying the names these columns should have ourselves.
The internal class [ShortCategorieMapper] is as follows:
class ShortCategorieMapper implements RowMapper<Categorie> {
@Override
public Categorie mapRow(ResultSet rs, int rowNum) throws SQLException {
return new Categorie(rs.getLong("c_ID"), rs.getLong("c_VERSIONING"), rs.getString("c_NOM"), null);
}
}
- line 1: the class [ShortCategorieMapper] implements the interface [RowMapper<Categorie>] and, as such, must implement the method [mapRow] from lines 4-5, whose role is to encapsulate a line of the [ResultSet rs] produced by the [SELECT] command into a [Categorie] type;
- line 5: this encapsulation is performed. Note that the name used by the [rs.getType(nom)] methods is the name used in the [as nom] attributes of the columns in SELECT;
We have thus obtained the list of categories in their short form without handling exceptions or connections. This is the benefit of the Spring library, which handles everything that can be abstracted in the management of table elements and leaves the developer to handle what cannot be.
4.9.2. The [getAllLongEntities] method
The [getAllLongEntities] method returns all categories from the [CATEGORIES] table in their version long form:
@Override
public List<Categorie> getAllLongEntities() {
try {
return filterCategories(namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_ALLLONGCATEGORIES,
new LongCategorieMapper()));
} catch (Exception e) {
throw new DaoException(223, e, simpleClassName);
}
}
The order SQL [ConfigJdbc.SELECT_ALLLONGCATEGORIES] is as follows:
public final static String SELECT_ALLLONGCATEGORIES = "SELECT p.ID as p_ID, p.VERSIONING as p_VERSION, p.NOM as p_NOM, p.PRIX as p_PRIX, p.DESCRIPTION as p_DESCRIPTION, p.CATEGORIE_ID AS p_CATEGORIE_ID, c.ID as c_ID, c.NOM as c_NOM, c.VERSIONING as c_VERSION FROM PRODUITS p RIGHT JOIN CATEGORIES c ON p.CATEGORIE_ID=c.ID";
The goal is to retrieve the categories along with their products. This is achieved by joining the table [CATEGORIES] with the table [PRODUITS] via the foreign key [CATEGORIE_ID] thatlinks the [PRODUITS] table to the [CATEGORIES] table. The [FROM PRODUITS p RIGHT JOIN CATEGORIES c ON p.CATEGORIE_ID=c.ID] syntax also allows you to retrieve categories that have no associated products. In this case, the SELECT query returns a category and a product with all its columns to NULL.
The [LongCategorieMapper] class is as follows:
class LongCategorieMapper implements RowMapper<Categorie> {
@Override
public Categorie mapRow(ResultSet rs, int rowNum) throws SQLException {
Categorie categorie = new Categorie(rs.getLong("c_ID"), rs.getLong("c_VERSION"), rs.getString("c_NOM"), null);
List<Produit> produits = new ArrayList<Produit>();
long idProduit = rs.getLong("p_ID");
// case of the category without products
if (!rs.wasNull()) {
produits.add(new Produit(idProduit, rs.getLong("p_VERSION"), rs.getString("p_NOM"), rs.getLong("p_CATEGORIE_ID"),
rs.getDouble("p_PRIX"), rs.getString("p_DESCRIPTION"), categorie));
}
categorie.setProduits(produits);
return categorie;
}
}
- line 4: the [mapRow] method must return a [Categorie] object with its [produits] field populated, based on a row from the [ResultSet] derived from the previous SELECT order;
Ultimately, the operation:
[namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_ALLLONGCATEGORIES,new LongCategorieMapper())]
will return a list of the type:
where each category [ci] will have a field [produits] that is a list of products containing a single item [produitsij]. However, we need the following list:
where each category [ci] will have a field [produits] that will be the list of products [produiti1, produiti2, ...]. This is achieved by passing the list of categories obtained to a private method [filterCategories]:
@Override
public List<Categorie> getAllLongEntities() {
try {
return filterCategories(namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_ALLLONGCATEGORIES,
new LongCategorieMapper()));
} catch (Exception e) {
throw new DaoException(223, e, simpleClassName);
}
}
The [filterCategories] method is as follows:
private List<Categorie> filterCategories(List<Categorie> categories) {
if (categories.size() == 0) {
return categories;
}
// categories to be returned
List<Categorie> cats = new ArrayList<Categorie>();
// browse the list of categories obtained
for (Categorie categorie : categories) {
boolean trouve = false;
for (Categorie cat : cats) {
if (categorie.equals(cat)) {
cat.addProduit(categorie.getProduits().get(0));
trouve = true;
break;
}
}
// found?
if (!trouve) {
cats.add(categorie);
}
}
// result
return cats;
}
- line 1: [List<Categorie> categories] is the list of categories to filter (or group);
- line 6: the list of categories to return to the caller;
- lines 8–21: each category in the list to be filtered is processed;
- lines 10–16: we check whether the current category [categorie] is already present in the list of categories [cats] to be constructed (note that two categories are considered equal if they have the same primary key, see section 4.6);
- lines 11–14: if this is already the case, then the product encapsulated in [categorie] is added to the list of products for [cat];
- lines 18–20: if the current category [categorie] is not already present in the list of categories [cats] to be constructed, then it is added to it along with its product list, which contains a single item;
Let’s consider the case where the SQL Select query returns categories with no associated products. Which entity does the [LongCategorieMapper] class represent?
class LongCategorieMapper implements RowMapper<Categorie> {
@Override
public Categorie mapRow(ResultSet rs, int rowNum) throws SQLException {
Categorie categorie = new Categorie(rs.getLong("c_ID"), rs.getLong("c_VERSION"), rs.getString("c_NOM"), null);
List<Produit> produits = new ArrayList<Produit>();
long idProduit = rs.getLong("p_ID");
// case of the category without products
if (!rs.wasNull()) {
produits.add(new Produit(idProduit, rs.getLong("p_VERSION"), rs.getString("p_NOM"), rs.getLong("p_CATEGORIE_ID"),
rs.getDouble("p_PRIX"), rs.getString("p_DESCRIPTION"), categorie));
}
categorie.setProduits(produits);
return categorie;
}
}
In the event that the SQL Select query returned a category with no products, the product columns returned with the category all contain the value SQL NULL. This case is handled in lines 7–9:
- line 7: retrieve the product’s primary key as an integer long;
- line 9: we check if the value read was SQL NULL (rs.wasNull). If not, the product is added to the list in line 6; otherwise, nothing is added and the product list remains empty.
Note that in all cases, a category is returned with a [produits] field that is not null.
4.9.3. The [getShortEntitiesById] method
The [getShortEntitiesById] method is analogous to the [getAllShortEntities] method, except that it returns only those entities whose primary keys are specified in a list:
@Override
protected List<Categorie> getShortEntitiesById(List<Long> ids) {
try {
return namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_SHORTCATEGORIE_BYID,
Collections.singletonMap("ids", ids), new ShortCategorieMapper());
} catch (Exception e) {
throw new DaoException(203, e, simpleClassName);
}
}
- Line 4: The signature of the [query] method used is as follows:
The first parameter is a configured SQL [Select] command. The second is a dictionary associating each parameter with a value. The third is the instance of the class that encapsulates a line of the [ResultSet] result of the [Select] into an object of type T;
- Line 4: The configured SQL [Select] command is as follows:
public final static String SELECT_SHORTCATEGORIE_BYID = "SELECT c.ID as c_ID, c.VERSIONING as c_VERSIONING, c.NOM as c_NOM FROM CATEGORIES c WHERE c.ID in (:ids)";
This query retrieves from the [CATEGORIES] table the categories whose primary keys are in the list: ids.
- Line 5: The second parameter of the [query] method is here a dictionary associating the key 'ids' (first parameter) to the list [ids] passed in line 1 as a parameter to the method [getShortEntitiesById]. The class [Collections] belongs to the library [Google Guava], which we have already discussed. [Collections.singleMap] returns a dictionary of a single element;
- line 5: the class responsible for encapsulating a row from [ResultSet]—the result of [Select]—into an object of type [Categorie] is the class [ShortCategorieMapper], which we have already examined;
This is typically where the [maxPreparedStatementParameters] bean comes into play. In fact, the [:ids] parameter of the SQL order, which represents a list of primary keys, can contain anywhere from 1 to several thousand parameters. There is a limit to this number that depends on each SGBD. For MySQL, we were able to pass 10,000 parameters without error and did not test beyond that. For SQL Server, the official limit is 2,100. For Firebird, 1,000 was too many. We reduced it to 100. Generally speaking, we have not tested the maximum limit for this number across the various SGBD instances.
4.9.4. The [getLongEntitiesById] method
The [getLongEntitiesById] method is similar to the [getShortEntitiesById] method, except that it returns the long versions of the categories:
@Override
protected List<Categorie> getLongEntitiesById(List<Long> ids) {
try {
return filterCategories(namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_LONGCATEGORIE_BYID,
Collections.singletonMap("ids", ids), new LongCategorieMapper()));
} catch (Exception e) {
throw new DaoException(205, e, simpleClassName);
}
}
Line 4, the query SQL [ConfigJdbc.SELECT_LONGCATEGORIE_BYID] is as follows:
public final static String SELECT_LONGCATEGORIE_BYID = "SELECT p.ID as p_ID, p.VERSIONING as p_VERSION, p.NOM as p_NOM, p.PRIX as p_PRIX, p.DESCRIPTION as p_DESCRIPTION, p.CATEGORIE_ID AS p_CATEGORIE_ID, c.ID as c_ID, c.NOM as c_NOM, c.VERSIONING as c_VERSION FROM PRODUITS p RIGHT JOIN CATEGORIES c ON c.ID=p.CATEGORIE_ID WHERE c.ID in (:ids)";
4.9.5. The [getShortEntitiesByName] method
The [getShortEntitiesByName] method is similar to the [getShortEntitiesById] method, except that categories are searched by their names rather than by their primary keys:
@Override
protected List<Categorie> getShortEntitiesByName(List<String> names) {
try {
return namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_SHORTCATEGORIE_BYNAME,
Collections.singletonMap("noms", names), new ShortCategorieMapper());
} catch (Exception e) {
throw new DaoException(204, e, simpleClassName);
}
}
Line 4, the SQL [ConfigJdbc.SELECT_SHORTCATEGORIE_BYNAME] command is as follows:
public final static String SELECT_SHORTCATEGORIE_BYNAME = "SELECT c.ID as c_ID, c.VERSIONING as c_VERSIONING, c.NOM as c_NOM FROM CATEGORIES c WHERE c.NOM in (:noms)";
4.9.6. The [getLongEntitiesByName] method
The [getLongEntitiesByName] method is similar to the [getShortEntitiesByName] method, except that categories are searched for in their long versions:
@Override
protected List<Categorie> getLongEntitiesByName(List<String> names) {
try {
return filterCategories(namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_LONGCATEGORIE_BYNAME,
Collections.singletonMap("noms", names), new LongCategorieMapper()));
} catch (Exception e) {
throw new DaoException(215, e, simpleClassName);
}
}
Line 4, the SQL [ConfigJdbc.SELECT_LONGCATEGORIE_BYNAME] order is as follows:
public final static String SELECT_LONGCATEGORIE_BYNAME = "SELECT p.ID as p_ID, p.VERSIONING as p_VERSION, p.NOM as p_NOM, p.PRIX as p_PRIX, p.DESCRIPTION as p_DESCRIPTION, p.CATEGORIE_ID AS p_CATEGORIE_ID, c.ID as c_ID, c.NOM as c_NOM, c.VERSIONING as c_VERSION FROM PRODUITS p RIGHT JOIN CATEGORIES c ON c.ID=p.CATEGORIE_ID WHERE c.NOM in(:noms)";
4.9.7. The [deleteAllEntities] method
The [deleteAllEntities] method deletes all categories from the [CATEGORIES] table:
@Override
public void deleteAllEntities() {
try {
// we eliminate all categories and, by cascade, all products
namedParameterJdbcTemplate.update(ConfigJdbc.DELETE_ALLCATEGORIES, (Map<String, Object>) null);
} catch (Exception e) {
throw new DaoException(208, e, simpleClassName);
}
}
- Line 4: The [namedParameterJdbcTemplate.update] method used has the following signature:
The first parameter is a SQL update command (INSERT, UPDATE, DELETE). The second parameter is the dictionary associating values with the various parameters of the SQL order. The method returns the number of rows updated by the SQL order.
- Line 4: The SQL [ConfigJdbc.DELETE_ALLCATEGORIES] command is as follows:
public final static String DELETE_ALLCATEGORIES = "DELETE FROM CATEGORIES";
This is therefore not a parameterized statement. This is why the second parameter of the [update] method has the value null.
4.9.8. The [deleteAllEntitiesById] method
The [deleteAllEntitiesById] method deletes the categories from the [CATEGORIES] table for which the primary keys are passed:
@Override
protected void deleteEntitiesById(List<Long> ids) {
try {
namedParameterJdbcTemplate.update(ConfigJdbc.DELETE_CATEGORIESBYID, Collections.singletonMap("ids", ids));
} catch (Exception e) {
throw new DaoException(209, e, simpleClassName);
}
}
Line 4, the SQL [ConfigJdbc.DELETE_CATEGORIESBYID] order is as follows:
public final static String DELETE_CATEGORIESBYID = "DELETE FROM CATEGORIES WHERE ID in (:ids)";
4.9.9. The [deleteAllEntitiesByName] method
The [deleteAllEntitiesByName] method deletes the categories from the [CATEGORIES] table whose names are passed:
@Override
protected void deleteEntitiesByName(List<String> names) {
try {
namedParameterJdbcTemplate.update(ConfigJdbc.DELETE_CATEGORIESBYNAME, Collections.singletonMap("noms", names));
} catch (Exception e) {
throw new DaoException(225, e, simpleClassName);
}
}
Line 4, the order SQL [ConfigJdbc.DELETE_CATEGORIESBYNAME] is as follows:
public final static String DELETE_CATEGORIESBYNAME = "DELETE FROM CATEGORIES WHERE NOM in (:noms)";
4.9.10. The [saveEntities] method
4.9.10.1. The code
The signature of this method is as follows:
@Override
protected List<Categorie> saveEntities(List<Categorie> entities) {
The method receives a list of categories as a parameter. It performs the following operations on them:
- if the category has a null primary key, an operation SQL INSERT is performed; otherwise, an operation SQL UPDATE is performed;
- this operation is repeated for each product in the category;
The method returns the list of persisted or updated categories. The returned list is an exact representation of the categories and products present in the tables, version numbers aside: these are not actually modified in the updated entities, even though they have been incremented in the database.
This is by far the most complex method. Its code is as follows:
@Override
protected List<Categorie> saveEntities(List<Categorie> entities) {
try {
// --------------------------------------------- categories
List<Categorie> insertCategories = new ArrayList<Categorie>();
List<Categorie> updateCategories = new ArrayList<Categorie>();
// scan categories
for (Categorie categorie : entities) {
// insert or update ?
if (categorie.getId() == null) {
insertCategories.add(categorie);
} else {
updateCategories.add(categorie);
}
}
// category insertions
if (insertCategories.size() > 0) {
insertCategories(insertCategories);
}
// updates categories
if (updateCategories.size() > 0) {
updateCategories(updateCategories);
}
// --------------------------------------------- produits
// update category products
List<Produit> allProduits = new ArrayList<Produit>();
for (Categorie categorie : entities) {
List<Produit> produits = categorie.getProduits();
Long idCategorie = categorie.getId();
if (produits != null) {
// we add it to the list of all products
allProduits.addAll(produits);
// we scan products one by one to link them to their category
for (Produit produit : produits) {
// link the product to its category
produit.setIdCategorie(idCategorie);
produit.setCategorie(categorie);
}
}
}
// insert / product update
daoProduit.saveEntities(allProduits);
// result
return entities;
} catch (DaoException e) {
throw e;
} catch (Exception e) {
throw new DaoException(207, e, simpleClassName);
}
}
- lines 5–23: insert or update categories;
- lines 26–43: insert or update products;
- lines 35-39: this code links each product to its category. In the previous phase of inserting categories, they were assigned a primary key that must be entered into the product’s [idCategorie] field (line 37). Additionally, lines 37–38 allow for correcting situations where the caller has not correctly linked each product to its category. To ensure this relationship is correct, the method [Categorie].add(Product p) must be used; however, nothing prevents a user from adding a product directly to the category’s product list without using this method, at the risk of having the [idCategorie, categorie] fields of product p incorrectly populated;
- Line 43: We delegate the task of persisting / updating the products to the instance of the [IDao<Produit>] interface. Recall that this instance was injected into the [DaoCategorie] class:
@Autowired
private IDao<Produit> daoProduit;
4.9.10.2. Inserting categories
Categories are inserted into the [CATEGORIES] table using the following private method [insertCategories]:
private List<Categorie> insertCategories(List<Categorie> categories) {
Map<Long, Categorie> mapCategories=new HashMap<Long,Categorie>();
try {
// categories to add
for (Categorie categorie : categories) {
Number newId = simpleJdbcInsertCategorie.executeAndReturnKey(getMapForCategorie(categorie));
// we store the primary key
mapCategories.put(newId.longValue(), categorie);
}
} catch (Exception e) {
throw new DaoException(201, e, simpleClassName);
}
// everything is OK - primary keys are assigned to persistent categories
for(Long id : mapCategories.keySet()){
Categorie categorie=mapCategories.get(id);
categorie.setId(id);
}
// result
return categories;
}
- Line 6: We use the [simpleJdbcInsertCategorie] bean injected into the class by the following lines:
@Autowired
private SimpleJdbcInsert simpleJdbcInsertCategorie;
This bean is defined in the [AppConfig] class of the project as follows:
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
@Bean
public SimpleJdbcInsert simpleJdbcInsertCategorie(DataSource dataSource) {
return new SimpleJdbcInsert(dataSource).withTableName(ConfigJdbc.TAB_CATEGORIES)
.usingGeneratedKeyColumns(ConfigJdbc.TAB_CATEGORIES_ID)
.usingColumns(ConfigJdbc.TAB_CATEGORIES_NOM);
}
- Line 5: The class [SimpleJdbcInsert] is a class in the Spring library JDBC (line 1):
- the parameter of the [SimpleJdbcInsert] constructor is the data source on which the operation is performed;
- the [withTableName] clause specifies the table into which an element is to be inserted, in this case table [CATEGORIES];
- the clause [usingGeneratedKeyColumns] specifies the column of the auto-generated primary key, in this case column [ID];
- The clause [usingColumns] restricts the insertion to certain columns. Here, we exclude the column [ID], which is auto-generated by SGBD, and the column [VERSIONING], which has a default value of 1;
Let’s return to the code for the [insertCategories] method:
private List<Categorie> insertCategories(List<Categorie> categories) {
Map<Long, Categorie> mapCategories=new HashMap<Long,Categorie>();
try {
// categories to add
for (Categorie categorie : categories) {
Number newId = simpleJdbcInsertCategorie.executeAndReturnKey(getMapForCategorie(categorie));
// we store the primary key
mapCategories.put(newId.longValue(), categorie);
}
} catch (Exception e) {
throw new DaoException(201, e, simpleClassName);
}
// everything is OK - primary keys are assigned to persistent categories
for(Long id : mapCategories.keySet()){
Categorie categorie=mapCategories.get(id);
categorie.setId(id);
}
// result
return categories;
}
- Line 6: The [simpleJdbcInsertCategorie.executeAndReturnKey] method is used:
The method expects a dictionary as a parameter that maps table columns to the values to be inserted into them. It returns the primary key as a [Number] type. The [Number.longValue()] method returns the primary key as a [Long] type.
The [getMapForCategorie] method is the following private method:
private Map<String, ?> getMapForCategorie(Categorie categorie) {
Map<String, Object> map = new HashMap<String, Object>();
map.put(ConfigJdbc.TAB_CATEGORIES_NOM, categorie.getNom());
return map;
}
The keys of the dictionary are the names of the columns to be populated ([NOM]), and the values of the dictionary are the values to be inserted into these columns.
- Line 8 [insertCategories]: The retrieved primary key is stored in a dictionary. We will wait until we are sure that all entities have been inserted before assigning their primary keys to them. Indeed, in the event of an exception, all insertions will be rolled back, and we want the entities [categories] from line 1 to remain unchanged as well;
- lines 14–17: now that we are sure everything went well, we assign the generated primary keys to the categories;
- line 19: we return the list of categories with their primary keys;
4.9.10.3. Updating the categories
The categories are updated using the following private method [updateCategories]:
private void updateCategories(List<Categorie> categories) {
try {
for (Categorie categorie : categories) {
// basic category update
int nbLignes = namedParameterJdbcTemplate.update(ConfigJdbc.UPDATE_CATEGORIES,
new BeanPropertySqlParameterSource(categorie));
// did we succeed?
Long idCategorie = null;
if (nbLignes == 0) {
// we didn't succeed - we're trying to find out why
// search for the basic category
idCategorie = categorie.getId();
List<Categorie> categoriesInBd = getShortEntitiesById(idCategorie);
if (categoriesInBd.size() == 0) {
// category does not exist
throw new RuntimeException(String.format("Erreur de mise à jour. La catégorie de clé [%s] n'existe pas",
idCategorie));
} else {
// the version was no good
throw new RuntimeException(String.format(
"Erreur de mise à jour. La catégorie de clé [%s] n'a pas la bonne version", idCategorie));
}
}
}
} catch (DaoException e) {
throw e;
} catch (Exception e) {
throw new DaoException(206, e, simpleClassName);
}
}
Updating a C1 category in the database with a C2 category in memory is permitted only if categories C1 and C2 have the same version. This version number is used to prevent simultaneous updates of the entity by two different users: two users, U1 and U2, read entity E with a version number equal to V1. U1 modifies E and commits this change to the database: the version number then changes to V1+1. U2 modifies E in turn and saves this change to the database: they will receive an exception because their version (V1) differs from the one in the database (V1+1).
- Lines 2–29: The `try` block has two `catch` blocks:
- the first, on line 25, is there to allow any [DaoException]-type exception thrown by the code on line 13 to pass through;
- the second, on line 27, is there to handle other exception types;
- line 3: we scan all categories to be updated;
- line 4: we update the current category using the [namedParameterJdbcTemplate.update] method:
- let’s analyze the statement:
int nbLignes = namedParameterJdbcTemplate.update(ConfigJdbc.UPDATE_CATEGORIES, new BeanPropertySqlParameterSource(categorie));
The SQL [ConfigJdbc.UPDATE_CATEGORIES] command is as follows:
public final static String UPDATE_CATEGORIES = "UPDATE CATEGORIES SET VERSIONING=VERSIONING+1, NOM=:nom WHERE ID=:id AND VERSIONING=:version";
The command has three parameters (:id, :version, :name) whose values are in the fields of the same name in the modified [categorie] object. We use this feature by passing [new BeanPropertySqlParameterSource(categorie)] as the second parameter, which indicates "the parameter values are in the fields of the same names in this Java bean";
The result returned by this operation, when it runs normally, is the number of modified rows, i.e., 0 or 1.
Let’s return to the code we’re examining:
private void updateCategories(List<Categorie> categories) {
try {
for (Categorie categorie : categories) {
// basic category update
int nbLignes = namedParameterJdbcTemplate.update(ConfigJdbc.UPDATE_CATEGORIES,
new BeanPropertySqlParameterSource(categorie));
// did we succeed?
Long idCategorie = null;
if (nbLignes == 0) {
// we didn't succeed - we're trying to find out why
// search for the basic category
idCategorie = categorie.getId();
List<Categorie> categoriesInBd = getShortEntitiesById(idCategorie);
if (categoriesInBd.size() == 0) {
// category does not exist
throw new RuntimeException(String.format("Erreur de mise à jour. La catégorie de clé [%s] n'existe pas",
idCategorie));
} else {
// the version was no good
throw new RuntimeException(String.format(
"Erreur de mise à jour. La catégorie de clé [%s] n'a pas la bonne version", idCategorie));
}
}
}
} catch (DaoException e) {
throw e;
} catch (Exception e) {
throw new DaoException(206, e, simpleClassName);
}
}
- line 9: we check if the update was successful;
- Line 10: The modification failed. Since the [WHERE] clause involves the [ID] and [VERSIONING] columns, we identify the column that caused the [WHERE] to fail;
- Lines 12–18: We verify that the category key [id] is in the database. If it is not, we run a [RuntimeException] with an appropriate error message;
- lines 19–22: handle the case where version was invalid;
4.10. The [DaoProduit] class
 |
|
The [DaoProduit] class implements the [IDao<Produit>] interface, which providesaccess to data in the [PRODUITS] table of the MySQL [dbproduitscategories] database. Its skeleton is as follows:
package spring.jdbc.dao;
import generic.jdbc.config.ConfigJdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Component;
import spring.jdbc.entities.Categorie;
import spring.jdbc.entities.Produit;
import spring.jdbc.infrastructure.DaoException;
import com.google.common.collect.Lists;
@Component
public class DaoProduit extends AbstractDao<Produit> {
// injections
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
private SimpleJdbcInsert simpleJdbcInsertProduit;
@Override
public List<Produit> getAllShortEntities() {
...
}
@Override
public List<Produit> getAllLongEntities() {
....
}
@Override
public void deleteAllEntities() {
...
}
@Override
protected List<Produit> getShortEntitiesById(List<Long> ids) {
...
}
@Override
protected List<Produit> getShortEntitiesByName(List<String> names) {
....
}
@Override
protected List<Produit> getLongEntitiesById(List<Long> ids) {
...
}
@Override
protected List<Produit> getLongEntitiesByName(List<String> names) {
try {
return namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_LONGPRODUIT_BYNAME,
Collections.singletonMap("noms", names), new LongProduitMapper());
} catch (Exception e) {
throw new DaoException(112, e, simpleClassName);
}
}
@Override
protected List<Produit> saveEntities(List<Produit> entities) {
...
}
@Override
protected void deleteEntitiesById(List<Long> ids) {
....
}
@Override
protected void deleteEntitiesByName(List<String> names) {
...
}
}
// --------------------- mappers
class ShortProduitMapper implements RowMapper<Produit> {
...
}
class LongProduitMapper implements RowMapper<Produit> {
...
}
The code is very similar to that of the [DaoCategorie] class. We will examine only a few methods.
4.10.1. The [getShortEntitiesById] method
The [getShortEntitiesById] method generates a short version of the products for which the primary keys are passed:
@Override
protected List<Produit> getShortEntitiesById(List<Long> ids) {
try {
return namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_SHORTPRODUIT_BYID,
Collections.singletonMap("ids", ids), new ShortProduitMapper());
} catch (Exception e) {
throw new DaoException(109, e, simpleClassName);
}
}
- line 4: the SQL Select [ConfigJdbc.SELECT_SHORTPRODUIT_BYID] statement is as follows:
public final static String SELECT_SHORTPRODUIT_BYID = "SELECT p.ID as p_ID, p.VERSIONING as p_VERSIONING, p.NOM as p_NOM, p.CATEGORIE_ID as p_CATEGORIE_ID, p.PRIX as p_PRIX, p.DESCRIPTION as p_DESCRIPTION FROM PRODUITS p WHERE p.ID in (:ids)";
- Line 4: The [ShortProduitMapper] class responsible for encapsulating [ResultSet] in a list of products is as follows:
class ShortProduitMapper implements RowMapper<Produit> {
@Override
public Produit mapRow(ResultSet rs, int rowNum) throws SQLException {
return new Produit(rs.getLong("p_ID"), rs.getLong("p_VERSIONING"), rs.getString("p_NOM"),
rs.getLong("p_CATEGORIE_ID"), rs.getDouble("p_PRIX"), rs.getString("p_DESCRIPTION"), null);
}
}
4.10.2. The [getLongEntitiesByName] method
The [getShortEntitiesById] method returns the version format for the products whose names are passed:
@Override
protected List<Produit> getLongEntitiesByName(List<String> names) {
try {
return namedParameterJdbcTemplate.query(ConfigJdbc.SELECT_LONGPRODUIT_BYNAME,
Collections.singletonMap("noms", names), new LongProduitMapper());
} catch (Exception e) {
throw new DaoException(112, e, simpleClassName);
}
}
- line 4: the SQL Select [ConfigJdbc.SELECT_LONGPRODUIT_BYNAME] statement is as follows:
public final static String SELECT_LONGPRODUIT_BYID = "SELECT p.ID as p_ID, p.VERSIONING as p_VERSION, p.NOM as p_NOM, p.PRIX as p_PRIX, p.DESCRIPTION as p_DESCRIPTION, p.CATEGORIE_ID AS p_CATEGORIE_ID, c.ID as c_ID, c.NOM as c_NOM, c.VERSIONING as c_VERSION FROM PRODUITS p, CATEGORIES c WHERE p.ID in (:ids) AND p.CATEGORIE_ID=c.ID";
- Line 4: The class [LongProduitMapper], responsible for encapsulating the elements of [ResultSet] into products, version long, is as follows:
class LongProduitMapper implements RowMapper<Produit> {
@Override
public Produit mapRow(ResultSet rs, int rowNum) throws SQLException {
return new Produit(rs.getLong("p_ID"), rs.getLong("p_VERSION"), rs.getString("p_NOM"),
rs.getLong("p_CATEGORIE_ID"), rs.getDouble("p_PRIX"), rs.getString("p_DESCRIPTION"), new Categorie(rs.getLong("c_ID"), rs.getLong("c_VERSION"), rs.getString("c_NOM"), null));
}
}
4.10.3. The [saveEntities] method
The [saveEntities] method is used interchangeably to insert new products (id==null) or update existing products (id!=null):
@Override
protected List<Produit> saveEntities(List<Produit> entities) {
try {
// insert products
List<Produit> insertProduits = new ArrayList<Produit>();
// products to be updated
List<Produit> updateproduits = new ArrayList<Produit>();
// scan the list of entities received
for (Produit produit : entities) {
Long id = produit.getId();
if (id == null) {
insertProduits.add(produit);
} else {
updateproduits.add(produit);
}
}
// additions
insertProduits(insertProduits);
// changes
updateProduits(updateproduits);
// result
return entities;
} catch (DaoException e) {
throw e;
} catch (Exception e) {
throw new DaoException(103, e, simpleClassName);
}
}
Line 18: The products to be inserted are inserted using the following private method [insertProduits]:
private List<Produit> insertProduits(List<Produit> produits) {
Map<Long, Produit> mapProduits = new HashMap<Long, Produit>();
try {
// products to add
for (Produit produit : produits) {
Number newId = simpleJdbcInsertProduit.executeAndReturnKey(getMapForProduit(produit));
// note the primary key
mapProduits.put(newId.longValue(), produit);
}
} catch (Exception e) {
throw new DaoException(201, e, simpleClassName);
}
// everything is OK - primary keys are assigned to persistent products
for (Long id : mapProduits.keySet()) {
Produit produit = mapProduits.get(id);
produit.setId(id);
}
// result
return produits;
}
private Map<String, ?> getMapForProduit(Produit produit) {
Map<String, Object> map = new HashMap<String, Object>();
map.put(ConfigJdbc.TAB_PRODUITS_NOM, produit.getNom());
map.put(ConfigJdbc.TAB_PRODUITS_CATEGORIE_ID, produit.getIdCategorie());
map.put(ConfigJdbc.TAB_PRODUITS_PRIX, produit.getPrix());
map.put(ConfigJdbc.TAB_PRODUITS_DESCRIPTION, produit.getDescription());
return map;
}
This method is analogous to the [insertCategories] method discussed in Section 4.9.10.3.
- line 4: the [simpleJdbcInsertProduit] bean, which was injected into the class, is used:
@Autowired
private SimpleJdbcInsert simpleJdbcInsertProduit;
This bean was defined in the [AppConfig] class, which configures the project:
@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);
}
- lines 3-6: the [simpleJdbcInsertProduit] bean
- is linked to the [dbproduitscategories] database data source (line 3), and to the [ConfigJdbc.TAB_PRODUITS] table in that source (line 4);
- the primary key for this table is generated in the [ConfigJdbc.TAB_PRODUITS_ID] column (line 5);
- values are only assigned to the columns in [ConfigJdbc.TAB_PRODUITS_NOM, ConfigJdbc.TAB_PRODUITS_PRIX, ConfigJdbc.TAB_PRODUITS_DESCRIPTION, ConfigJdbc.TAB_PRODUITS_CATEGORIE_ID] (row 6);
The method [updateProduits], which updates the products (line 20 of [saveEntities]), is as follows:
private void updateProduits(List<Produit> updateProduits) {
try {
// we scan products
for (Produit produit : updateProduits) {
// basic product update
int nbLignes = namedParameterJdbcTemplate.update(ConfigJdbc.UPDATE_PRODUITS,
new BeanPropertySqlParameterSource(produit));
// did we succeed?
Long idProduit = null;
if (nbLignes == 0) {
// we didn't succeed - we're trying to find out why
// we search for the basic product
idProduit = produit.getId();
List<Produit> produitsInBd = getShortEntitiesById(idProduit);
if (produitsInBd.size() == 0) {
// the product does not exist
throw new RuntimeException(String.format("Erreur de mise à jour. Le produit de clé [%s] n'existe pas",
idProduit));
} else {
// the version was no good
throw new RuntimeException(String.format(
"Erreur de mise à jour. Le produit de clé [%s] n'a pas la bonne version", idProduit));
}
}
}
} catch (DaoException e) {
throw e;
} catch (Exception e) {
throw new DaoException(106, e, simpleClassName);
}
}
It is similar to the one that updates categories (see section 4.9.10.3). Line 23, the SQL [ConfigJdbc.UPDATE_PRODUITS] command executed to update products is as follows:
public final static String UPDATE_PRODUITS = "UPDATE PRODUITS SET VERSIONING=VERSIONING+1, NOM=:nom, PRIX=:prix, CATEGORIE_ID=:idCategorie, DESCRIPTION=:description WHERE ID=:id AND VERSIONING=:version";
The parameter names in [:id,:version,:nom,:prix,:idCategorie,:description] are also the field names in the [Produit] class, which allows the statement in lines 6–7 to be used to update the current product.
4.11. The test layer
 |
 |
The test layer consists of three test classes:
- [JUnitTestCheckArguments]: the tests in this class call the various methods of the [DAO] layer with invalid arguments and verify that they respond correctly;
- [JUnitTestDao]: The tests in this class call the various methods of the [DAO] layer and verify that they behave as expected;
- [JUnitTestPushTheLimits] is not intended to test the [DAO] layer but to measure its performance;
This test layer plays a major role in this document. It is, in fact, common to all implementations of the [IDao<T>] interface. There are six per SGBD (1 JDBC implementation, 3 JPA implementations, 1 Spring implementation MVC, 1 secure Spring implementation MVC), so 36 for the six SGBD implementations tested. The test layer allows us to verify that all implementations behave the same way.
4.11.1. The [JUnitTestCheckArguments] test
The [JUnitTestCheckArguments] test class has 48 methods that test the behavior of the [DAO] layer methods when called with incorrect arguments. Its skeleton is as follows:
package spring.jdbc.tests;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import spring.jdbc.config.AppConfig;
import spring.jdbc.dao.IDao;
import spring.jdbc.entities.Categorie;
import spring.jdbc.entities.Produit;
import spring.jdbc.infrastructure.MyIllegalArgumentException;
import com.google.common.collect.Lists;
@SpringApplicationConfiguration(classes = AppConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class JUnitTestCheckArguments {
// layer [DAO]
@Autowired
private IDao<Produit> daoProduit;
@Autowired
private IDao<Categorie> daoCategorie;
// local data
private Iterable<String> names1 = null;
private Iterable<String> names2 = Lists.newArrayList(new String[0]);
private String[] names3 = null;
private String[] names4 = new String[0];
private Iterable<Long> ids1 = null;
private Iterable<Long> ids2 = Lists.newArrayList(new Long[0]);
private Long[] ids3 = null;
private Long[] ids4 = new Long[0];
private Iterable<Categorie> categories1 = null;
private Iterable<Categorie> categories2 = Lists.newArrayList(new Categorie[0]);
private Categorie[] categories3 = null;
private Categorie[] categories4 = new Categorie[0];
private Iterable<Produit> produits1 = null;
private Iterable<Produit> produits2 = Lists.newArrayList(new Produit[0]);
private Produit[] produits3 = null;
private Produit[] produits4 = new Produit[0];
...
}
- line 19: the JUnit test will be performed in integration with the Spring framework;
- line 18: before the tests, the beans defined in the [AppConfig] class of the project will be instantiated;
- lines 23–26: injection of an instance of each of the two interfaces in the [DAO] layer;
- lines 29–44: incorrect call parameters for the methods of the [DAO] layer;
- line 29: a null pointer of type [Iterable<String>] as a list of names;
- line 30: an empty list of type [Iterable<String>] as a list of names;
- line 29: a null pointer of type String[] as a name array;
- line 30: an empty array of type String[] as a list of names;
- ...
With the field [names1], we perform the following test, for example:
@Test(expected = MyIllegalArgumentException.class)
public void getShortProduitsByName1() {
daoProduit.getShortEntitiesByName(names1);
}
- Line 1: We specify that the [getShortProduitsByName1] test must throw the [MyIllegalArgumentException] exception
With the [names2] field, we perform the following test, for example:
@Test(expected = MyIllegalArgumentException.class)
public void getLongCategoriesByName2() {
daoCategorie.getLongEntitiesByName(names2);
}
With the field [names3], we perform the following test, for example:
@Test(expected = MyIllegalArgumentException.class)
public void getLongCategoriesByName3() {
daoCategorie.getLongEntitiesByName(names3);
}
With the field [names4], we perform the following test, for example:
@Test(expected = MyIllegalArgumentException.class)
public void getShortProduitsByName4() {
daoProduit.getShortEntitiesByName(names4);
}
We thus run 48 tests to cover all possible cases. We execute the test suite named [spring-jdbc-generic-04-JUnitTestCheckArguments] [1]. The result obtained is as follows [2]:
 |
4.11.2. The [JUnitTestDao] test
The [JUnitTestDao] test calls the methods of the [DAO] layer with valid arguments and verifies that the methods do what is expected of them. There are a total of 74 tests that verify the operations of inserting, selecting, updating, and deleting entities, categories, or products. In total, there are over 1,000 lines of code. We will examine only a few of these methods.
4.11.2.1. The Test Skeleton
The [JUnitTestDao] class has the following skeleton:
package spring.jdbc.tests;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import spring.jdbc.config.AppConfig;
import spring.jdbc.dao.IDao;
import spring.jdbc.entities.Categorie;
import spring.jdbc.entities.Produit;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Lists;
@SpringApplicationConfiguration(classes = AppConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class JUnitTestDao {
// spring context
@Autowired
private ApplicationContext context;
// layer [DAO]
@Autowired
private IDao<Produit> daoProduit;
@Autowired
private IDao<Categorie> daoCategorie;
// constants
private final int NB_PRODUITS = 5;
private final int NB_CATEGORIES = 2;
// local
// local
private Map<Long, Categorie> mapCategories = new HashMap<Long, Categorie>();
private Map<Long, Produit> mapProduits = new HashMap<Long, Produit>();
@Before
public void clean() {
// the base is cleaned before each test
log("Vidage de la base de données", 1);
// we empty table [CATEGORIES] and cascade table [PRODUITS]
daoCategorie.deleteAllEntities();
// emptying dictionaries
for (Long id : mapCategories.keySet()) {
mapCategories.remove(id);
}
for (Long id : mapProduits.keySet()) {
mapProduits.remove(id);
}
}
...
}
- lines 27-28: as with the [JUnitTestCheckArguments] test, this is a test integrated with Spring and configured by the [AppConfig] class in the project;
- lines 32-33: injection of the Spring context, which provides access to all its beans;
- lines 35-36: injection of the instance of the [IDao<Produit>] interface tested by the class;
- lines 37-38: injection of the instance of the [IDao<Categorie>] interface tested by the class;
- lines 41-42: when a test requires database data, a database of [NB_CATEGORIES] categories will be generated, each containing [NB_PRODUITS] products. This will result in [NB_CATEGORIES] categories in the [CATEGORIES] table and [NB_CATEGORIES] * [NB_PRODUITS] products in the [PRODUITS] table;
- lines 46–47: two dictionaries where we will store the products and categories;
- lines 49–62: the method [clean] runs before each test (line 49). On line 54, the table [CATEGORIES] is cleared. It is important to note here that the table [PRODUITS] has a primary key [CATEGORIE_ID] on the column ID of the table [CATEGORIES], and that this is defined as follows;
 |
- (continued)
- in [1-3], the foreign key [CATEGORIE_ID] from the table [PRODUITS]. It targets the [ID] column of the [CATEGORIES] table [4-5];
- when a category is deleted, all products linked to it are also deleted [6]. This point is important to note because it is used in the construction of the [DAO] layer utilizing the [dbproduitscategories] database;
Therefore, when the contents of table [CATEGORIES] are deleted, the contents of table [PRODUITS] will also be deleted.
- Lines 56–58: We clear the category dictionary;
- lines 59–61: we do the same with the product dictionary;
Note that before each test, the database contains empty tables and the memory contains empty dictionaries.
4.11.2.2. The [verifyClean] method
The method [verifyClean] verifies that after the method [clean], the tables are empty:
@Test
public void verifyClean() {
log("verifyClean", 1);
List<Categorie> categories = daoCategorie.getAllShortEntities();
Assert.assertEquals(0, categories.size());
List<Produit> produits = daoProduit.getAllShortEntities();
Assert.assertEquals(0, produits.size());
}
4.11.2.3. The [fillDataBase] method
This method verifies that the database has been properly populated with test data:
@Test
public void fillDataBase() throws BeansException, JsonProcessingException {
// base filling and dictionaries
registerCategories(fill(NB_CATEGORIES, NB_PRODUITS));
// display
Object[] data = showDataBase();
List<Categorie> categories = (List<Categorie>) data[0];
List<Produit> produits = (List<Produit>) data[1];
// a few checks
Assert.assertEquals(NB_CATEGORIES, categories.size());
Assert.assertEquals(NB_PRODUITS * NB_CATEGORIES, produits.size());
for (Categorie categorie : categories) {
checkShortCategorie(categorie);
}
for (Produit produit : produits) {
checkShortProduit(produit);
}
// dictionaries must be out of print
Assert.assertEquals(0, mapCategories.size());
Assert.assertEquals(0, mapProduits.size());
}
This test uses several private methods:
- [fill] line 4, which populates the database with test data;
- [registerCategories] line 4, which populates the dictionaries with the data returned by the [fill] method. These two dictionaries represent the persisted entities;
- [showDataBase] line 6, which reads the two tables [CATEGORIES] and [PRODUITS] and returns what it has read;
- [checkShortCategorie] line 13 checks the category read by [showDataBase]. It verifies that the short code version for this category matches what was recorded in the category dictionary;
- [checkShortProduit] line 16 does the same for products;
- When an entity is found in a dictionary, it is removed from the dictionary. Lines 19–20 verify that both dictionaries are empty. If both of these assertions are true, it means that:
- all values read by [showDataBase] were indeed found in the dictionaries;
- the dictionaries contain no entities other than those that were read;
The private method [fill] is as follows:
private List<Categorie> fill(int nbCategories, int nbProduits) {
// fill the tables
List<Categorie> categories = new ArrayList<Categorie>();
for (int i = 0; i < nbCategories; i++) {
Categorie categorie = new Categorie(null, null, String.format("categorie[%d]", i), null);
for (int j = 0; j < nbProduits; j++) {
Produit produit = new Produit(null, null, String.format("produit[%d,%d]", i, j), null,
100 * (1 + (double) (i * 10 + j) / 100), String.format("desc[%d,%d]", i, j), null);
categorie.addProduit(produit);
}
categories.add(categorie);
}
// category is added - by cascading the products will also be
// inserted
categories = daoCategorie.saveEntities(categories);
// result
return categories;
}
- lines 3–12: we build a list of [nbCategories] categories, each containing [nbProduits] products;
- line 15: this list of categories is persisted. We saw that the [daoCategorie.saveEntities] method also persists the products associated with the categories when they have any;
- line 17: the persisted list of categories is returned. The persisted entities (categories and products) now have a primary key in their [id] field;
The private method [registerCategories] will add these entities to both dictionaries:
private void registerCategories(List<Categorie> categories) {
// dictionaries
for (Categorie categorie : categories) {
mapCategories.put(categorie.getId(), categorie);
for (Produit produit : categorie.getProduits()) {
mapProduits.put(produit.getId(), produit);
}
}
}
Each dictionary uses the primary key of the entities as its access key.
Once this is done, the previously populated database will be read and displayed by the following private method [showDataBase]:
private Object[] showDataBase() throws BeansException, JsonProcessingException {
// list of categories
log("Liste des catégories", 2);
List<Categorie> categories = daoCategorie.getAllShortEntities();
affiche(categories, context.getBean("jsonMapperShortCategorie", ObjectMapper.class));
// product list
log("Liste des produits", 2);
List<Produit> produits = daoProduit.getAllShortEntities();
affiche(produits, context.getBean("jsonMapperShortProduit", ObjectMapper.class));
// result
return new Object[] { categories, produits };
}
- lines 4 and 8: we retrieve the short versions of the categories and products;
- line 11: returns an array containing the two lists of retrieved entities;
- lines 5 and 9: the lists of entities are displayed using the following private method [affiche]:
// display a list of elements of type T
private <T> void affiche(List<T> elements, ObjectMapper mapper) throws JsonProcessingException {
for (T element : elements) {
affiche(element, mapper);
}
}
// display of a T-type element
private <T> void affiche(T element, ObjectMapper mapper) throws JsonProcessingException {
System.out.println(mapper.writeValueAsString(element));
}
Entities are displayed by a jSON mapper (line 10). This mapper is the second parameter of the [affiche] method, line 2. The Spring context defines four jSON mappers in the [ConfigJdbc] file of the [mysql-config-jdbc] Maven dependency:
// filters jSON -------------------------------------
@Bean
public ObjectMapper jsonMapper() {
return new ObjectMapper();
}
@Bean
@Scope(value = ConfigurableBeanFactory.SCOPE_PROTOTYPE)
ObjectMapper jsonMapperShortCategorie() {
ObjectMapper jsonMapper = jsonMapper();
jsonMapper.setFilters(new SimpleFilterProvider().addFilter("jsonFilterCategorie",
SimpleBeanPropertyFilter.serializeAllExcept("produits")));
return jsonMapper;
}
@Bean
@Scope(value = ConfigurableBeanFactory.SCOPE_PROTOTYPE)
ObjectMapper jsonMapperLongCategorie() {
ObjectMapper jsonMapper = jsonMapper();
jsonMapper.setFilters(new SimpleFilterProvider().addFilter("jsonFilterCategorie",
SimpleBeanPropertyFilter.serializeAllExcept()).addFilter("jsonFilterProduit",
SimpleBeanPropertyFilter.serializeAllExcept("categorie")));
return jsonMapper;
}
@Bean
@Scope(value = ConfigurableBeanFactory.SCOPE_PROTOTYPE)
ObjectMapper jsonMapperShortProduit() {
ObjectMapper jsonMapper = jsonMapper();
jsonMapper.setFilters(new SimpleFilterProvider().addFilter("jsonFilterProduit",
SimpleBeanPropertyFilter.serializeAllExcept("categorie")));
return jsonMapper;
}
@Bean
@Scope(value = ConfigurableBeanFactory.SCOPE_PROTOTYPE)
ObjectMapper jsonMapperLongProduit() {
ObjectMapper jsonMapper = jsonMapper();
jsonMapper.setFilters(new SimpleFilterProvider().addFilter("jsonFilterProduit",
SimpleBeanPropertyFilter.serializeAllExcept()).addFilter("jsonFilterCategorie",
SimpleBeanPropertyFilter.serializeAllExcept("produits")));
return jsonMapper;
}
- these jSON mappers (lines 7–9, 16–18, 26–28, 35–37) have an attribute
[@Scope(value = ConfigurableBeanFactory.SCOPE_PROTOTYPE)]
which makes them beans that are instantiated with every request made to the Spring context. This is new. All the Spring beans we’ve seen so far were singletons: only a single instance was created, and that instance was returned every time a reference to it was requested from the Spring context. Why the change? In fact, the four [jsonMapperShortCategorie, jsonMapperLongCategorie, jsonMapperShortProduit , jsonMapperLongProduit] beans configure the single jSON mapper (which is indeed a singleton) defined in lines 2–5. This mapper must be reconfigured on every call to one of the four preceding beans, rather than just once during context initialization. If we had decided to have four different jSON mappers, one for each of the four beans, then these could have been singletons. That was entirely possible. We would then have written lines 10, 19, 29, 38:
ObjectMapper jsonMapper = new ObjectMapper();
- The four json mappers are used to configure the jSON filters for the [Produit] and [Categorie] entities. We have in fact written (see sections 4.6 and 4.6) the following:
@JsonFilter("jsonFilterCategorie")
public class Categorie extends AbstractCoreEntity {
and
@JsonFilter("jsonFilterProduit")
public class Produit extends AbstractCoreEntity {
The representation jSON of the entity [Categorie] is controlled by the filter jSON [jsonFilterCategorie], and that of theentity [produit] by the filter jSON [jsonFilterProduit]. The four jSON mappers in the Spring context configure these two filters as follows:
- the [jsonMapperShortCategorie] mapper configures the jSON and [jsonFilterCategorie] filters for a short version version of the category: the [produits] field will not be included in the jSON representation of the category;
- the mapper [jsonMapperLongCategorie] configures the filter jSON [jsonFilterCategorie] for a long version of the category: the field [produits] will be included in the representation jSON of the category;
- the mapper [jsonMapperShortProduit] configures the filter jSON [jsonFilterProduit] for a short version of the product: the field [categorie] will not be included in the product representation jSON;
- The [jsonMapperLongProduit] mapper configures the jSON filter [jsonFilterProduit] for a long product version version: the field [categorie] will be included in the product representation jSON;
We are done with the private method [showDataBase]. Let’s return to the test code [fillDataBase]:
@Test
public void fillDataBase() throws BeansException, JsonProcessingException {
// base filling and dictionaries
registerCategories(fill(NB_CATEGORIES, NB_PRODUITS));
// display
Object[] data = showDataBase();
List<Categorie> categories = (List<Categorie>) data[0];
List<Produit> produits = (List<Produit>) data[1];
// a few checks
Assert.assertEquals(NB_CATEGORIES, categories.size());
Assert.assertEquals(NB_PRODUITS * NB_CATEGORIES, produits.size());
for (Categorie categorie : categories) {
checkShortCategorie(categorie);
}
for (Produit produit : produits) {
checkShortProduit(produit);
}
// dictionaries must be out of print
Assert.assertEquals(0, mapCategories.size());
Assert.assertEquals(0, mapProduits.size());
}
- lines 6-8: we retrieve the short versions of the products and categories read from the database;
- lines 10-11: initial checks;
- lines 12-14: each category returned by the [showDataBase] method is checked by the following private [checkShortCategorie] method:
private void checkShortCategorie(Categorie actual) {
Long id = actual.getId();
Categorie expected = mapCategories.get(actual.getId());
mapCategories.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
// the [produits] field cannot be tested portably with jPA implementations
}
- line 1: [Categorie actual] is the category read from the database and must be identical to the category in the dictionary, [mapCategories];
- line 2: we retrieve the primary key of the category read;
- line 3: We retrieve the category stored with this primary key in the category dictionary;
- line 4: the key is removed from the dictionary to ensure that no other read category uses the same key;
- line 5: verify that the two categories have the same name;
The short version of the products returned by the [showDataBase] method is verified by the following private [checkShortProduit] method:
private void checkShortProduit(Produit actual) {
Long id = actual.getId();
Produit expected = mapProduits.get(id);
mapProduits.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
Assert.assertEquals(expected.getDescription(), actual.getDescription());
Assert.assertEquals(expected.getPrix(), actual.getPrix(), 1e-6);
Assert.assertEquals(actual.getIdCategorie(), expected.getIdCategorie());
// the [categorie] field cannot be tested portably with jPA implementations
}
- line 1: [Produit actual] is the short product name read from the database;
- lines 2-3: retrieve the product with the same primary key from the dictionary of persisted products;
- line 4: we delete the entry found in the dictionary;
- lines 5-8: we verify that the two products have the same field values;
4.11.2.4. The [getLongCategoriesByName3] method
This test is as follows:
@Test
public void getLongCategoriesByName3() {
// base filling
List<Categorie> categories = fill(NB_CATEGORIES, NB_PRODUITS);
// test
log("getLongCategoriesByName3", 1);
List<Categorie> categories2 = daoCategorie.getLongEntitiesByName("categorie[0]", "categorie[1]");
Assert.assertEquals(2, categories2.size());
registerCategories(Lists.newArrayList(categories.get(0), categories.get(1)));
for (Categorie categorie : categories) {
checkLongCategorie(categorie);
}
Assert.assertEquals(0, mapCategories.size());
}
- line 4: populate the database and retrieve the list of persisted categories and products;
- line 7: we test the [daoCategorie.getLongEntitiesByName(Iterable<String> names)] method of the [DAO] layer. We request a list of two products identified by their full names;
- line 8: we verify that the list returned by [daoCategorie.getLongEntitiesByName(Iterable<String> names)] indeed has two elements;
- line 9: the two elements persisted on line 4 are added to the category dictionary;
- lines 10–12: verify that the two elements read are indeed the ones that were persisted;
- line 13: we verify that the category dictionary is empty, which means both that all the read categories were found in the dictionary and that the dictionary does not contain any values that were not read;
Line 11: the [checkLongCategorie] method checks the long version of a category:
private void checkLongCategorie(Categorie actual) {
Long id = actual.getId();
Categorie expected = mapCategories.get(actual.getId());
mapCategories.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
Assert.assertNotNull(actual.getProduits());
}
- Line 6 verifies that the [produits] field of the category is not null. Indeed, reading a category in long format always returns it with a non-null [produits] field. If the category has no products, then the [produits] field is an empty but existing list;
4.11.2.5. The [updateDataBase1] method
@Test
public void updateDataBase1() {
// filling
fill(NB_CATEGORIES, NB_PRODUITS);
// test
log("Mise à jour du prix des produits de [categorie1]", 1);
Categorie categorie1 = daoCategorie.getLongEntitiesByName("categorie[1]").get(0);
List<Produit> produits = categorie1.getProduits();
Map<Produit, Long> versions = new HashMap<Produit, Long>();
for (Produit produit : produits) {
produit.setPrix(1.1 * produit.getPrix());
versions.put(produit, produit.getVersion());
}
daoProduit.saveEntities(produits);
// proofreading
List<Produit> produitsInBd = daoCategorie.getLongEntitiesByName("categorie[1]").get(0)
.getProduits();
Assert.assertEquals(produits.size(), produitsInBd.size());
// checks
for (Produit produit2 : produitsInBd) {
Produit produit = findProduitByName(produit2.getNom(), produits);
Assert.assertEquals(produit2.getPrix(), produit.getPrix(), 1e-6);
Assert.assertEquals(produit2.getVersion().longValue(), versions.get(produit) + 1);
}
}
private Produit findProduitByName(String nom, List<Produit> produits) {
for (Produit produit : produits) {
if (produit.getNom().equals(nom)) {
return produit;
}
}
return null;
}
The [updateDataBase1] method increases the price of products in the category named categorie[1] by 10% and checks two things:
- that the base price has indeed changed;
- that the version of the updated product has been incremented by 1;
The code does the following:
- line 4: populates the database;
- line 7: retrieves the category named 'categorie[1]' from the database;
- lines 8–13: increases the price of all products by 10% (line 11). Additionally, creates a dictionary associating a product with its version (lines 9 and 12);
- line 14: the [daoProduit.saveEntities] method is called. It will update the products;
- line 16: the products in the category named 'categorie[1]' are retrieved from the database;
- lines 20–24: for all products in this category, we verify that the price has been modified (line 22) and that version has been incremented by 1 (line 23);
4.11.2.6. The [deleteProduitsByProduit1] method
The [deleteProduitsByProduit1] method deletes products from the [PRODUITS] table:
@Test
public void deleteProduitsByProduit1() {
// filling
fill(NB_CATEGORIES, NB_PRODUITS);
// delete
daoProduit.deleteEntitiesByEntity(daoProduit.getShortEntitiesByName("produit[0,0]", "produit[1,1]"));
// check
List<Produit> produits = daoProduit.getShortEntitiesByName("produit[0,0]", "produit[1,1]");
Assert.assertEquals(0, produits.size());
}
- line 6: we delete two products;
- lines 8-9: we verify that they are no longer in the database;
4.11.2.7. The [getLongProduitsById3] method
@Test
public void getLongProduitsById3() {
// filling
List<Categorie> categories = fill(NB_CATEGORIES, NB_PRODUITS);
// test
log("getLongProduitsById3", 1);
List<Produit> produits = daoProduit.getLongEntitiesByName("produit[0,3]", "produit[1,4]");
Assert.assertEquals(2, produits.size());
registerProduits(Lists.newArrayList(categories.get(0).getProduits().get(3), categories.get(1).getProduits().get(4)));
produits = daoProduit.getLongEntitiesById(produits.get(0).getId(), produits.get(1).getId());
for (Produit produit : produits) {
checkLongProduit(produit);
}
Assert.assertEquals(0, mapProduits.size());
}
- line 4: populate the database and retrieve the list of persisted categories;
- line 7: retrieve the version record from the database, which contains two products identified by their names;
- line 9: the products [produit[0,3], produit[1,4]] present in the list of categories from line 4 are added to the product dictionary;
- line 10: these same two products are searched for in the database using their primary keys;
- lines 11–14: we verify that the data read is identical to the data stored in the dictionary;
The private method [checkLongProduit] is as follows:
private void checkLongProduit(Produit actual) {
Long id = actual.getId();
Produit expected = mapProduits.get(id);
mapProduits.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
Assert.assertEquals(expected.getDescription(), actual.getDescription());
Assert.assertEquals(expected.getPrix(), actual.getPrix(), 1e-6);
Assert.assertNotNull(actual.getCategorie());
}
4.11.2.8. Conclusion
We’ll stop here. There are 74 tests so far, and we could add more since I’ve probably forgotten some test cases. Even though they aren’t exhaustive, these tests have helped detect numerous errors—mostly edge cases that weren’t anticipated when the [DAO] layer was initially written. A comprehensive testing phase is essential for any project.
To run the test, you can use the imported execution configuration named [spring-jdbc-generic-04.JUnitTestDao].
 |  |
4.11.3. The [JUnitTestPushTheLimits] test
The [JUnitTestPushTheLimits] test is a performance test. We take advantage of the fact that the JUnit tests display their execution times to measure the performance of the [DAO] layer. These results will then be compared to those of the JPA implementations of the [DAO] layer.
4.11.3.1. Skeleton
The skeleton of the [JUnitTestPushTheLimits] class is as follows:
package spring.jdbc.tests;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import spring.jdbc.config.AppConfig;
import spring.jdbc.dao.IDao;
import spring.jdbc.entities.Categorie;
import spring.jdbc.entities.Produit;
@SpringApplicationConfiguration(classes = AppConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class JUnitTestPushTheLimits {
// layer [DAO]
@Autowired
private IDao<Produit> daoProduit;
@Autowired
private IDao<Categorie> daoCategorie;
// constants
private final int NB_CATEGORIES = 2500;
private final int NB_PRODUITS = 2;
// local
private Map<Long, Categorie> hCategories;
private Map<Long, Produit> hProduits;
@Before
public void clean() {
// empty the [CATEGORIES] table
daoCategorie.deleteAllEntities();
// dictionaries
hCategories = new HashMap<Long, Categorie>();
hProduits = new HashMap<Long, Produit>();
}
private List<Categorie> fill(int nbCategories, int nbProduits) {
// fill the tables
List<Categorie> categories = new ArrayList<Categorie>();
for (int i = 0; i < nbCategories; i++) {
Categorie categorie = new Categorie(null, 0L, String.format("categorie[%d]", i), null);
for (int j = 0; j < nbProduits; j++) {
Produit produit = new Produit(null, 0L, String.format("produit[%d,%d]", i, j), 0L,
100 * (1 + (double) (i * 10 + j) / 100), String.format("desc[%d,%d]", i, j), null);
categorie.addProduit(produit);
}
categories.add(categorie);
}
// add the category - the products will be cascaded in as well
categories = daoCategorie.saveEntities(categories);
// dictionaries
for (Categorie categorie : categories) {
hCategories.put(categorie.getId(), categorie);
for (Produit produit : categorie.getProduits()) {
hProduits.put(produit.getId(), produit);
}
}
// result
return categories;
}
....
// -------------------- private methods
private void checkLongProduit(Produit actual) {
Long id = actual.getId();
Produit expected = hProduits.get(id);
hProduits.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
Assert.assertEquals(expected.getDescription(), actual.getDescription());
Assert.assertEquals(expected.getPrix(), actual.getPrix(), 1e-6);
Assert.assertEquals(expected.getIdCategorie(), actual.getIdCategorie());
Assert.assertNotNull(actual.getCategorie());
}
private void checkShortProduit(Produit actual) {
Long id = actual.getId();
Produit expected = hProduits.get(id);
hProduits.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
Assert.assertEquals(expected.getDescription(), actual.getDescription());
Assert.assertEquals(expected.getPrix(), actual.getPrix(), 1e-6);
Assert.assertEquals(expected.getIdCategorie(), actual.getIdCategorie());
boolean erreur = false;
try {
actual.getCategorie().getNom();
} catch (Exception e) {
erreur = true;
}
Assert.assertTrue(erreur);
}
private void checkShortCategorie(Categorie actual) {
Long id = actual.getId();
Categorie expected = hCategories.get(actual.getId());
hCategories.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
boolean erreur = false;
try {
actual.getProduits().size();
} catch (Exception e) {
erreur = true;
}
Assert.assertTrue(erreur);
}
private void checkLongCategorie(Categorie actual) {
Long id = actual.getId();
Categorie expected = hCategories.get(actual.getId());
hCategories.remove(id);
Assert.assertEquals(expected.getNom(), actual.getNom());
Assert.assertNotNull(actual.getProduits());
}
}
Here we see the skeleton of the [JUnitTestDao] class. We have already encountered all of these methods. The test works with a database of 2,500 categories, each containing 2 products (lines 32–33). The [CATEGORIES] table will therefore have 2,500 rows, and the [PRODUITS] table will have 5,000 rows. We could have included more rows, but the test already takes nearly a minute to run. We therefore chose values that are tolerable for the user waiting for the test to finish.
There are 18 tests in total. They are run using the execution configuration [1]. The execution times are shown in [2]:
 |
4.11.3.2. doNothing [0,114]
The method [doNothing] does nothing. It is used to measure the duration of the method [clean], which is executed before each test and clears the database. Above, we can see that the duration of this operation is negligible compared to the others.
@Test
public void doNothing() {
// clean
}
4.11.3.3. perf01 [4,179]
The [perf01] test is used to measure the database populate time:
@Test
public void perf01() {
// insert
fill(NB_CATEGORIES, NB_PRODUITS);
}
4.11.3.4. perf02 [7,624]
The [perf02] method:
- fills the database;
- then modifies the name of all categories and the price of all products.
@Test
public void perf02() {
// update
List<Categorie> categories = fill(NB_CATEGORIES, NB_PRODUITS);
for (Categorie categorie : categories) {
categorie.setNom(categorie.getNom() + "*");
for (Produit produit : categorie.getProduits()) {
produit.setPrix(produit.getPrix() * 1.1);
}
}
// update
daoCategorie.saveEntities(categories);
}
4.11.3.5. perf03[3,911]
The [perf03] method:
- fills the database
- then deletes all categories one by one. The products are also deleted due to the cascade relationship between the [CATEGORIES] table and the [PRODUITS] table.
It may be surprising here that this operation takes less time ([3,911 s]) than the operation [perf01] [4,179 s], which does less.
@Test
public void perf03() {
// delete categories and cascade products
daoCategorie.deleteEntitiesByEntity(fill(NB_CATEGORIES, NB_PRODUITS));
}
If we look at the code for the [daoCategorie.deleteEntitiesByEntity] method, we see that a [PreparedStatement] with 2,500 parameters (the number of categories) will be executed. This is where the [maxPreparedStatementParameters] bean comes into play; it will split the SQL request into several [PreparedStatement] requests, each with a number of parameters that can be handled by the specific SGBD being used.
4.11.3.6. perf04[2,426]
The [perf04] method:
- fills the database;
- then requests the full version for all categories;
@Test
public void perf04() {
// select
List<Categorie> categories = fill(NB_CATEGORIES, NB_PRODUITS);
List<Long> ids = new ArrayList<Long>();
for (Categorie categorie : categories) {
ids.add(categorie.getId());
}
daoCategorie.getLongEntitiesById(ids);
}
4.11.3.7. perf05 [3,507]
The [perf05] method:
- fills the database;
- then deletes the 5,000 products using their primary keys (so we potentially have a [PreparedStatement] with 5,000 parameters);
- checks that the product table is then empty;
@Test
public void perf05() {
// delete products
List<Categorie> categories = fill(NB_CATEGORIES, NB_PRODUITS);
List<Long> ids = new ArrayList<Long>();
for (Categorie categorie : categories) {
for (Produit p : categorie.getProduits()) {
ids.add(p.getId());
}
}
daoProduit.deleteEntitiesById(ids);
// check
List<Produit> produits = daoProduit.getAllShortEntities();
Assert.assertEquals(0, produits.size());
}
4.11.3.8. Results
We will not continue to present the various tests. We will simply indicate what they do and their duration. These durations are only meaningful when compared to one another. Their values depend on the test environment used (hardware and software configuration). However, when obtained in the same environment, they can be compared.
Total test duration: 59.995 seconds
role | ||
populates the database with 2,500 categories and 5,000 products | ||
Fills and then modifies the database | ||
fills the database then deletes all categories and their products | ||
fills the database and requests the long version for all categories | ||
populates the database and deletes the 5,000 products one by one using their primary keys | ||
fills the database and deletes the 5,000 products one by one using their names | ||
fills the database and deletes the 5,000 products one by one using their SKUs | ||
populates the database and retrieves the short version for all products via their names | ||
populates the database and retrieves the long version for all products by name | ||
populates the database and retrieves the short version for all products via their primary keys | ||
populates the database and retrieves the long version for all products via their primary keys | ||
Fills the database and then deletes all categories (and thus the associated products) one by one via their names | ||
fills the database and then deletes all categories (and thus the associated products) one by one using their SKUs | ||
populates the database and requests the short version for all categories via their names | ||
Fills the database and requests the long version for all categories by name | ||
populates the database and retrieves the short version for all categories via their primary keys | ||
populates the database and retrieves the long version for all categories via their primary keys |
These results are sometimes surprising:
- it was faster to obtain the long version of the products (perf09) than their short version (perf08), even though the long version involves a join between two tables;
- the duration of the first fill (perf01) significantly exceeds that of all subsequent fills;
- requesting the short version version of the products via their names (perf08) is faster than requesting it via the primary keys (perf10). This seems quite logical. But for the long versions, the opposite is true (perf09, perf11);
We will therefore not dwell on these results. However, they will be useful for comparing this [Spring JDBC] solution to the solutions:
- [Spring JDBC] the five other SGBD;
- [Spring JPA] which will follow;