Skip to content

7. [TD]: Implementation of the [DAO] layer of the TD using the JDBC API

Keywords: relational databases, JDBC API, SQLException.

Let’s revisit the layered architecture of our application:

The data required for the election is stored in a MySQL database [dbelections]

7.1. Support

The [support / chap-07] folder [1] contains:

  • the Eclipse projects for this chapter [2];
  • the SQL script for creating the MySQL database [dbelections] [3];

7.2. The [ dbelections] database

Task: Create the MySQL database [dblelections] by following the procedure described in section 6.4.2.

The [dbelections] database is a MySQL database with two tables:

  

The [conf] table contains the election configuration information:

 
  • [id]: auto-incrementing primary key;
  • [version]: record version number—can be ignored here;
  • [sap]: number of seats to be filled;
  • [votingThreshold]: the threshold below which a list is eliminated;

Its contents are as follows:

 

The [listes] table contains the candidate lists for the election:

 
  • [id]: auto-incrementing primary key;
  • [version]: record version number—can be ignored here;
  • [name]: name of the list;
  • [votes]: votes for the list—known only after user input in the [presentation] layer;
  • [seats]: number of seats won—known only after calculation by the [business] layer;
  • [eliminated]: 1 if the list is eliminated, 0 otherwise—known only after the [business] layer is calculated;

The contents of the [listes] table are as follows:

 

The SQL script to generate the [dbelections] database is named [dbelections.sql] and is on the server. Its code is as follows:


-- phpMyAdmin SQL Dump
-- version 4.0.4
-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Generated on: Wed, March 11, 2015 at 12:20 PM
-- Server version: 5.6.12-log
-- PHP version: 5.4.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `dbelections`
--
CREATE DATABASE IF NOT EXISTS `dbelections` DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
USE `dbelections`;

-- --------------------------------------------------------

--
-- Structure of the `conf` table
--

CREATE TABLE IF NOT EXISTS `conf` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `version` int(11) NOT NULL DEFAULT '1',
  `sap` tinyint(4) NOT NULL,
  `votingThreshold` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB   DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=2 ;

--
-- Contents of the `conf` table
--

INSERT INTO `conf` (`id`, `version`, `sap`, `votingthreshold`) VALUES
(1, 1, 6, 0.05);

-- --------------------------------------------------------

--
-- Structure of the `listes` table
--

CREATE TABLE IF NOT EXISTS `listes` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `version` int(11) NOT NULL DEFAULT '1',
  `name` varchar(20) COLLATE utf8_swedish_ci NOT NULL,
  `votes` int(11) NOT NULL,
  `seats` int(11) NOT NULL,
  `eliminate` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB   DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=8 ;

--
-- Contents of the `listes` table
--

INSERT INTO `lists` (`id`, `version`, `name`, `votes`, `seats`, `eliminated`) VALUES
(1, 21, 'A', 10, 1, 0),
(2, 22, 'B', 20, 2, 0),
(3, 21, 'C', 30, 3, 0),
(4, 13, 'D', 40, 3, 0),
(5, 17, 'E', 50, 6, 0),
(6, 18, 'F', 60, 1, 0),
(7, 19, 'G', 70, 2, 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

7.3. The Eclipse Project

The Eclipse project for the [DAO] layer will be as follows:

  
  • the [elections.dao.entities] package contains the objects manipulated by the [DAO] layer;
  • the [elections.dao.service] package contains the implementation of the [DAO] layer;
  • the [elections.dao.config] package contains the configuration for the [DAO] layer
  • the [elections.dao.junit] package contains a JUnit test class for the project;
  • the [elections.dao.console] package contains an executable test class;

7.4. Maven Project Configuration

 

The [pom.xml] file that configures the Maven project is as follows:


<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>istia.st.elections</groupId>
    <artifactId>elections-dao-jdbc-01</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.7.RELEASE</version>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- Spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
        </dependency>
        <!-- Tomcat JDBC -->
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jdbc</artifactId>
        </dependency>
        <!-- JSON library -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>
        <!-- Spring Boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot Logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>
    </dependencies>

    <!-- plugins -->
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>config.AppConfig</mainClass>
                        </manifest>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
            </plugin>
            <!-- for installing the project artifact in the local Maven repository -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.18.1</version>
            </plugin>
        </plugins>
    </build>
</project>

This file is similar to the one described in Section 6.5.1. The following changes have been made:

  • lines 8–12: a parent Maven project has been defined. The [spring-boot-starter-parent] project (line 10) defines a large number of dependencies along with their versions. When using one of them (lines 19–57), there is no need to specify the version, as it is defined in the parent Maven project;
  • lines 40–51: dependencies required for the project’s test class. These dependencies have the [<scope>test</scope>] attribute, meaning they are only required for classes in the [src/test/java] folder. These dependencies will not be included in the final project archive;
  • lines 53–56: the [spring-boot-starter-logging] library will be used by Spring to log to the console;
  • lines 14–17: Maven configuration properties:
    • line 15: specifies that the source files are encoded in UTF-8;
    • line 16: specifies that the compiler version must be 1.8;

7.5. Entities in the [DAO] layer

  
  • [ElectionsConfig] is the object model associated with a row in the [CONF] table;
  • [VoterList] is the object model associated with a row in the [LISTS] table;
  • [AbstactEntity] is the parent class of the two preceding classes. It encapsulates the [id, version] fields common to both classes;
  • [ElectionsException] is an exception class;

7.5.1. The [ElectionsException] class

  

The [ElectionsException] class was described in Section 4.3. Here is its code again:


package ...;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

// exception class for the Elections application
// the exception is unchecked

public class ElectionsException extends RuntimeException implements Serializable {

    // serial ID
    private static final long serialVersionUID = 1L;

    // local fields
    private int code;
    private List<String> errors;

    // constructors
    public ElectionsException() {
        super();
    }

    public ElectionsException(int code, Throwable e) {
        // parent
        super(e);
        // local
        this.code = code;
        this.errors = getErrorsForException(e);
    }

    public ElectionsException(int code, String message, Throwable e) {
        // parent
        super(message, e);
        // local
        this.code = code;
        this.errors = getErrorsForException(e);
    }

    public ElectionsException(int code, String message) {
        // parent
        super(message);
        // local
        this.code = code;
        List<String> errors = new ArrayList<>();
        errors.add(message);
        this.errors = errors;
    }

    public ElectionsException(int code, List<String> errors) {
        // super
        super();
        // local
        this.code = code;
        this.errors = errors;
    }

    // list of error messages for an exception
    private List<String> getErrorsForException(Throwable th) {
        // retrieve the list of error messages for the exception
        Throwable cause = th;
        List<String> errors = new ArrayList<>();
        while (cause != null) {
            // retrieve the message only if it is not null and not empty
            String message = cause.getMessage();
            if (message != null) {
                message = message.trim();
                if (message.length() != 0) {
                    errors.add(message);
                }
            }
            // next cause
            cause = cause.getCause();
        }
        return errors;
    }

    // getters and setters
...
}

An [ElectionsException] object is characterized by two pieces of information:

  • line 16: an error code;
  • line 17: a list of error messages associated with the stack trace of the exceptions that occurred;
  • the class has 5 constructors (lines 20, 24, 32, 40, 50);
  • lines 59–76: the [getErrorsForException] method retrieves the error messages from the exception stack;

7.5.2. The [AbstractEntity] class

  

The [AbstractEntity] class is as follows:


package elections.dao.entities;

import java.io.Serializable;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public abstract class AbstractEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    // fields
    protected Long id;
    protected Long version;

    // constructors
    public AbstractEntity() {

    }

    public AbstractEntity(Long id, Long version) {
        this.id = id;
        this.version = version;
    }

    // signature
    public String toString() {
        try {
            return new ObjectMapper().writeValueAsString(this);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    // getters and setters
...
}

It stores the [ID, NAME] fields of the rows in the [CONF] and [LISTS] tables (lines 8–9).

  • line 8: the class has the [Abstract] attribute indicating that it cannot be instantiated. It can only be derived;
  • lines 26–33: JSON signature of the object;
  • line 28: the JSON string of [this] is returned. If, at runtime, [this] represents an object derived from [AbstractEntity], the JSON string of the derived object is obtained. Derived classes will thus not need to define a [toString] method. The one from the parent class is sufficient;

7.5.3. The [ElectionsConfig] class

  

The [ElectionsConfig] class is as follows:


package elections.dao.entities;


public class ElectionsConfig extends AbstractEntity {

    private static final long serialVersionUID = 1L;
    // fields
    private int numberOfSeatsToBeFilled;
    private double electoralThreshold;

    // constructors
    public ElectionsConfig() {
    }

    public ElectionsConfig(Long id, Long version, int numberOfSeatsToBeFilled, double electoralThreshold) {
        // parent
        super(id, version);
        // Local fields
        this.seatsToBeFilled = seatsToBeFilled;
        this.votingThreshold = votingThreshold;
    }

    // getters and setters
...
}
  • line 4: the class extends the [AbstractEntity] class;
  • lines 8-9: store the information from the [SAP, SEUILELECTORAL] columns of the [CONF] table;

7.5.4. The [VoterList] class

  

The [VoterList] class is as follows:


package elections.dao.entities;

public class VoterList extends AbstractEntity {

    // fields
    private String name;
    private int votes;
    private int seats;
    private boolean eliminated;

    // constructors
    public VoterList() {
    }

    public VoterList(String name, int votes, int seats, boolean eliminated) {
        // parent
        super();
        // local fields
        initName(name);
        initVotes(votes);
        initSeats(seats);
        this.eliminate = eliminate;
    }

    public ElectoralList(Long id, Long version, String name, int votes, int seats, boolean eliminated) {
        // parent
        super(id, version);
        // local fields
        initName(name);
        initVotes(votes);
        initSeats(seats);
        this.eliminate = eliminate;
    }

    // private methods
    private void initName(String name) {
        this.name = name.trim();
        if ("".equals(name)) {
            throw new ElectionsException(10, "The name cannot be empty");
        }
    }

    private void initVotes(int votes) {
        this.votes = votes;
        if (votes < 0) {
            throw new ElectionsException(11, "The number of votes cannot be <0");
        }
    }

    private void initSeats(int seats) {
        this.seats = seats;
        if (seats < 0) {
            throw new ElectionsException(12, "The number of seats cannot be <0");
        }
    }

    // getters and setters

    public String getName() {
        return name;
    }

    public void setName(String name) {
        initName(name);
    }

    public int getVoice() {
        return votes;
    }

    public void setVoice(int voice) {
        initVoice(voice);
    }

    public int getSeats() {
        return seats;
    }

    public void setSeats(int seats) {
        initSeats(seats);
    }

    public boolean isEliminated() {
        return eliminated;
    }

    public void setEliminated(boolean eliminated) {
        this.deleted = deleted;
    }

}
  • line 3: the class extends the [AbstractEntity] class;
  • lines 6–9: the class stores the columns [NAME, VOTES, SEATS, ELIMINATED] from the [LISTS] table;

7.6. Spring configuration of the [DAO] layer

 

The [AppConfig] class is a Spring configuration class that configures database access as follows:


package elections.dao.config;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.cache.CacheManager;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.cache.concurrent.ConcurrentMapCacheManager;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

@ComponentScan(basePackages = { "elections.dao.service" })
@EnableCaching
public class AppConfig {

    // constants
    public final static String URL = "jdbc:mysql://localhost:3306/dbelections";
    public final static String USER = "root";
    public final static String PASSWD = "";
    public final static String DRIVER_CLASSNAME = "com.mysql.jdbc.Driver";
    public final static String SELECT_LISTS = "SELECT ID, VERSION, NAME, VOTES, SEATS, ELIMINATED FROM LISTS";
    public final static String SELECT_CONF = "SELECT ID, VERSION, SAP, ELECTORAL_THRESHOLD, SAP FROM CONF";
    public final static String UPDATE_LISTES = "UPDATE LISTES SET VOTES=?, SEATS=?, REMOVED=? WHERE ID=?";

    @Bean
    public DataSource dataSource() {
        // TomcatJdbc data source
        DataSource dataSource = new DataSource();
        // JDBC access configuration
        dataSource.setDriverClassName(DRIVER_CLASSNAME);
        dataSource.setUsername(USER);
        dataSource.setPassword(PASSWD);
        dataSource.setUrl(URL);
        // an initially open connection
        dataSource.setInitialSize(1);
        // result
        return dataSource;
    }

    @Bean
    public CacheManager cacheManager() {
        return new ConcurrentMapCacheManager("electionsConfig");
    }
}
  • lines 25–38: Access to the database will be via a data source [tomcat-jdbc]. This type of data source was used and explained in section 6.5;
  • lines 17–23: a set of static constants accessible by all classes in the project;
  • line 13: the [@Configuration] annotation makes the [AppConfig] class a Spring configuration class;
  • line 11: the [@ComponentScan] annotation specifies the packages where Spring objects can be found. Here, we will define a Spring object in the [dao] package. The [@ComponentScan] annotation makes the class a configuration class, saving us from having to add the [@Configuration] annotation;
  • Line 12 enables cache management. The principle is as follows:
    • we apply the [@Cacheable('cache_name')] annotation to a method M. The 'cache_name' is the name used on line 41;
    • when method M is called for the first time, its results are returned and also stored in the cache named by the annotation;
    • when method M is called subsequently with the same parameters as the first time, it is not executed, and Spring simply returns the cached values;

7.7. Log configuration

The logging libraries are defined by the following dependency in [pom.xml]:


        <!-- Spring Boot Logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
</dependency>

This dependency includes the following libraries:

  

The [logback] library handles logging. It is configured by two files:

  • [logback.xml] for the main code branch;
  • [logback-test.xml] for the test branch of the code. If this file is missing, the previous file is used instead;

These two files must be located in the project’s [Classpath]. For this reason, they are placed in the folder:

  • [src/main/resources] for the main code branch;
  • [src/test/resources] for the test branch of the code;

The contents of the files are the same here:


<configuration> 

  <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender"> 
    <!-- Encoders are assigned the type
         ch.qos.logback.classic.encoder.PatternLayoutEncoder -->
    <encoder>
      <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
    </encoder>
  </appender>

  <!-- log level control -->
  <root level="info"> <!-- info, debug, warn -->
    <appender-ref ref="STDOUT" />
  </root>
</configuration>

Everything happens on line 12, where we set the desired log level:

  • [debug]: the most detailed level;
  • [off]: no logs;
  • [info]: the normal logging level;
  • [warn]: same as [info] plus warning messages. These messages indicate a potential error;

Switch to [debug] mode as soon as errors appear during execution.

7.8. Implementation of the [DAO] layer

  

The [IDao] interface of the [DAO] layer is as follows:


package istia.st.elections.webapi.client.dao;

import istia.st.elections.webapi.client.entities.ElectionsConfig;
import istia.st.elections.webapi.client.entities.VoterList;

public interface IDao {
    // election configuration
    public ElectionsConfig getElectionsConfig();

    // competing lists
    public VoterList[] getVoterLists();

    // saving election results
    public void setElectionLists(ElectionList[] electionLists);

}

The skeleton of the [ElectionsDaoJdbc] class implementing the [dao] layer with a database will be as follows:


package elections.dao.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Component;

import elections.dao.entities.ElectionsConfig;
import elections.dao.entities.ElectionsException;
import elections.dao.entities.VoterList;

@Component
@SuppressWarnings("unused")
public class ElectionDaoJdbc implements IElectionsDao {

    @Autowired
    private DataSource dataSource;

    @Cacheable("electionsConfig")
    // Get election configuration
    public ElectionsConfig getElectionsConfig() {
        throw new RuntimeException("[getElectionsConfig] not yet implemented");
    }

    // Get the voter lists
    public VoterList[] getVoterLists() {
        throw new RuntimeException("[getVoterLists] not yet implemented");
    }

    // modifying the lists [votes, seats, eliminated]
    public void setVoterLists(VoterList[] voterLists) {
        throw new RuntimeException("[setVoterLists] not yet implemented");
    }

    // -------------------- private methods

    // handling the finally block
    private ElectionsException doFinally(int code, ResultSet rs, PreparedStatement ps, Connection connection) {
        // close ResultSet
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e1) {

            }
        }
        // Close [PreparedStatement]
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e2) {

            }
        }
        // close the connection
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e3) {
                // throw an [ElectionsException]
                return new ElectionsException(code, e3);
            }
        }
        // no exception
        return null;
    }

    // catch handling
    private ElectionsException doCatchException(int code1, int code2, Connection connection, Throwable th) {
        // generate an [ElectionsException]
        ElectionsException ex1 = new ElectionsException(code1, th);
        // roll back transaction
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException e2) {
        }
        // return the exception
        return ex1;
    }
}
  • line 24: the [@Cacheable] annotation is a Spring annotation that instructs the system to cache the results of the [getElectionsConfig] method. This is possible here because the contents of the [CONF] table never change. It would not be possible to apply this annotation to the [getListesElectorales] method because the contents of the [LISTES] table change over time;
  • the [doCatchException] and [doFinally] methods return an [ElectionsException] type. The [doFinally] method returns a null pointer if the resources were released without errors;

Task: Write the [ElectionDaoJdbc] class, drawing inspiration from the [IntroJdbc02] class studied in Section 6.5.2.

7.9. The [Main] test class

  

The [Main] class is as follows:


package elections.dao.console;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import elections.dao.config.AppConfig;
import elections.dao.entities.ElectionsConfig;
import elections.dao.entities.ElectionsException;
import elections.dao.entities.VoterList;
import elections.dao.service.IElectionsDao;

public class Main {

    // data source
    private static IElectionsDao dao;

    public static void main(String[] args) {
        // retrieve the reference to the [DAO] layer after instantiating the Spring context
        try (AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(AppConfig.class)) {
            // retrieve the data source
            dao = ctx.getBean(IElectionsDao.class);
        } catch (Exception e) {
            System.out.println("The following errors occurred while initializing the Spring context -------");
            for (String error : getErrorsForThrowable(e)) {
                System.out.println(error);
            }
            // end
            return;
        }
        // Read the database
        ElectionsConfig electionsConfig = null;
        VoterList[] lists;
        try {
            // contents of the two tables
            electionsConfig = dao.getElectionsConfig();
            voterLists = dao.getVoterLists();
        } catch (ElectionsException e) {
            System.out.println("The following errors occurred while reading the tables ----------");
            for (String error : e.getErrors()) {
                System.out.println(error);
            }
            // end
            return;
        }
        // everything went well - display
        System.out.println(String.format("Number of seats to be filled: %d", electionsConfig.getNbSiegesAPourvoir()));
        System.out.println(String.format("Electoral threshold: %5.2f", electionsConfig.getSeuilElectoral()));
        System.out.println("Candidate lists----------------");
        for (ElectionList list : lists) {
            System.out.println(list);
        }
    }

    // private methods ------------------
    private static List<String> getErrorsForThrowable(Throwable th) {
        // retrieve the list of error messages from the exception
        Throwable cause = th;
        List<String> errors = new ArrayList<>();
        while (cause != null) {
            // retrieve the message only if it is not null and not empty
            String message = cause.getMessage();
            if (message != null) {
                message = message.trim();
                if (message.length() != 0) {
                    errors.add(message);
                }
            }
            // next cause
            cause = cause.getCause();
        }
        return errors;
    }

}
  • lines 21–31: retrieving a reference to the [DAO] layer;
  • line 21: we use a syntax called try_with_resources. Its syntax is as follows: 

try (T resource = expression) {
            // use of resource
...
}
  • (continued)
    • The type T in line 1 must implement the [java.lang.AutoCloseable] interface;
    • Upon exiting the block of lines 1–4, the resource of type [java.lang.AutoCloseable] is automatically released, regardless of whether an exception occurred. Those familiar with the C# language will recognize this as a syntactic and functional counterpart to the using clause (T resource = expression);
  • Lines 40–49: Use of the [DAO] layer to retrieve the contents of the [CONF] and [LISTES] tables;
  • lines 41-47: the [electionsconfig] cache is tested. This cache has been defined in two places:
    • in the [ElectionsDaoJdbc] class: 

  @Cacheable("electionsConfig")
  public ElectionsConfig getElectionsConfig() {
  • (continued)
    • in the [AppConfig] configuration class: 

@EnableCaching
public class AppConfig {
...
  @Bean
  public CacheManager cacheManager() {
    return new ConcurrentMapCacheManager("electionsConfig");
  }
}
  • line 59: closing the Spring context;
  • lines 62–67: display of the retrieved information;

The results obtained with an implemented [DAO] layer are as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
...
start of query 1: 11:09:29:752
end of query 1 and start of query 2: 11:09:30:132
end of request 2: 11:09:30:133
...
Number of seats to be filled: 6
Electoral threshold: 0.05
Candidate lists----------------
{"id":1,"version":9,"name":"A","votes":32000,"seats":2,"eliminated":false}
{"id":2,"version":13,"name":"B","votes":25000,"seats":2,"eliminated":false}
{"id":3,"version":14,"name":"C","votes":16000,"seats":1,"eliminated":false}
{"id":4,"version":13,"name":"D","votes":12000,"seats":1,"eliminated":false}
{"id":5,"version":14,"name":"E","votes":8000,"seats":0,"eliminated":false}
{"id":6,"version":13,"name":"F","votes":4500,"seats":0,"eliminated":true}
{"id":7,"version":13,"name":"G","votes":2500,"seats":0,"eliminated":true}
  • Lines 2–4: show the impact of the cache:
    • Request 1 takes 80 milliseconds;
    • Query 2 takes 1 millisecond;

When the database is offline, the following results are obtained:

1
2
3
4
5
The following errors occurred while reading the tables ----------
Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
Connection refused: connect

7.10. JUnit tests for the [ ElectionsDaoJdbc] class

  

The [Test01] class is the following [JUnit] test class:


package elections.dao.junit;

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 elections.dao.config.AppConfig;
import elections.dao.entities.ElectionsConfig;
import elections.dao.entities.VoterList;
import elections.dao.service.IElectionsDao;


@SpringApplicationConfiguration(classes = AppConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class Test01 {

    // [DAO] layer
    @Autowired
    private IElectionsDao electionsDao;

    @Before
    public void init() {
        // clear the [LISTS] table
        // competing lists
        ElectoralLists[] lists = electionsDao.getElectoralLists();
        // Set votes and seats to 0 and set "eliminated" to false
        int votes = 0;
        int seats = 0;
        boolean eliminated = false;
        for (VoterList list : lists) {
            list.setVotes(votes);
            list.setSeats(seats);
            list.setEliminated(eliminated);
        }
        // We make this data persistent using the [DAO] layer
        electionsDao.setVoterLists(lists);
    }

    @Test
    public void testElections01() {
        ...
    }
}
  • line 17: the [RunWith] annotation, which is a [JUnit] annotation (line 6), ensures integration with Spring via the [SpringJUnit4ClassRunner] class;
  • line 16: the [SpringApplicationConfiguration] annotation is a [Spring] annotation (line 8) that allows you to specify the configuration classes for the JUnit test. Here, we specify the [AppConfig] class used to configure the project. We then have access to all the Spring objects defined by this configuration class. This is how we can inject, on lines 21–22, a reference to the [DAO] layer that will be tested;
  • line 25: The [Before] annotation indicates a method that must be executed before each test;
  • Lines 26–41: The [init] method sets the votes and seats in the [LISTES] table to zero and the [elimine] boolean to [false];

The only test method is as follows:


@Test
    public void testElections01() {
        System.out.println("testElections01-------------------------------------");
        // retrieve the election configuration
        ElectionsConfig electionsConfig = electionsDao.getElectionsConfig();
        int numSeatsToBeFilled = electionsConfig.getNumSeatsToBeFilled();
        double electoralThreshold = electionsConfig.getSeuilElectoral();
        Assert.assertEquals(6, nbSiegesAPourvoir);
        Assert.assertEquals(0.05, electoralThreshold, 1E-6);

        // competing lists
        ElectionLists[] lists = electionsDao.getElectionLists();
        // display read values
        System.out.println("Number of seats to be filled: " + nbSiegesAPourvoir);
        System.out.println("Electoral threshold: " + electoralThreshold);
        System.out.println("Lists in the race ---------------------");
        for (int i = 0; i < lists.length; i++) {
            System.out.println(lists[i]);
        }

        // assign votes and seats to the lists
        int votes = 0;
        int seats = 0;
        boolean eliminated = false;
        for (VoterList list : lists) {
            list.setVotes(votes);
            list.setSeats(seats);
            list.setEliminated(eliminated);
            votes += 10;
            seats += 1;
            eliminated = !eliminated;
        }

        // We make this data persistent using the [DAO] layer
        electionsDao.setVoterLists(lists);

        // we read the data again
        VoterList[] voterLists2 = electionsDao.getVoterLists();
        // we verify the retrieved data
        Assert.assertEquals(7, electoralLists2.length);
        votes = 0;
        seats = 0;
        eliminated = false;
        for (VoterList list : voterLists2) {
            Assert.assertEquals(votes, list.getVotes());
            Assert.assertEquals(seats, list.getSeats());
            Assert.assertEquals(eliminated, list.isEliminated());
            votes += 10;
            seats += 1;
            eliminate = !eliminate;
        }
        System.out.println("Competing lists ---------------------");
        for (int i = 0; i < lists.length; i++) {
            System.out.println(lists[i]);
        }
    }
  • lines 5-9: we ensure that we can retrieve the contents of the [CONF] table;
  • lines 11–19: we display the contents of the [LISTES] table. There are no tests here, only a visual check;
  • lines 21–35: we modify the [LISTES] table in the database by assigning values to the lists’ fields [voices, seats, eliminated];
  • lines 37–51: we read the [LISTES] table again and verify that the result matches what was entered;
  • lines 52–55: visual verification;

The console results obtained with an implemented [DAO] layer are as follows:


March 11, 2015 4:50:00 PM org.springframework.test.context.support.DefaultTestContextBootstrapper getDefaultTestExecutionListenerClassNames
INFO: Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener]
March 11, 2015 4:50:00 PM org.springframework.test.context.support.DefaultTestContextBootstrapper instantiateListeners
INFO: Could not instantiate TestExecutionListener [org.springframework.test.context.transaction.TransactionalTestExecutionListener]. Specify custom listener classes or make the default listener classes (and their required dependencies) available. Offending class: [org/springframework/transaction/interceptor/TransactionAttributeSource]
March 11, 2015 4:50:00 PM org.springframework.test.context.support.DefaultTestContextBootstrapper instantiateListeners
INFO: Could not instantiate TestExecutionListener [org.springframework.test.context.web.ServletTestExecutionListener]. Specify custom listener classes or make the default listener classes (and their required dependencies) available. Offending class: [javax/servlet/ServletContext]
March 11, 2015 4:50:00 PM org.springframework.test.context.support.DefaultTestContextBootstrapper instantiateListeners
INFO: Could not instantiate TestExecutionListener [org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener]. Specify custom listener classes or make the default listener classes (and their required dependencies) available. Offending class: [org.springframework.transaction.interceptor.TransactionAttribute]
March 11, 2015 4:50:00 PM org.springframework.test.context.support.DefaultTestContextBootstrapper getTestExecutionListeners
INFO: Using TestExecutionListeners: [org.springframework.test.context.support.DependencyInjectionTestExecutionListener@483bf400, org.springframework.test.context.support.DirtiesContextTestExecutionListener@21a06946]

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.2.2.RELEASE)

[2015-03-11 16:50:01.272] - 11696 INFOS [main] --- org.eclipse.jdt.internal.junit.runner.RemoteTestRunner: Starting RemoteTestRunner on Gportpers3 with PID 11696 (started by ST in D:\data\istia-1415\eclipse\intro-jdbc\elections-jdbc-01)
[2015-03-11 16:50:01.317] - 11696 INFOS [main] --- org.springframework.context.annotation.AnnotationConfigApplicationContext: Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@74ad1f1f: startup date [Wed Mar 11 16:50:01 CET 2015]; root of context hierarchy
Mar 11, 2015 4:50:01 PM org.eclipse.jdt.internal.junit.runner.RemoteTestRunner logStarted
INFO: Started RemoteTestRunner in 0.775 seconds (JVM running for 1.433)
testElections01-------------------------------------
Number of seats to be filled: 6
Electoral threshold: 0.05
Competing lists ---------------------
{"id":1,"version":21,"name":"A","votes":0,"seats":0,"eliminated":false}
{"id":2,"version":22,"name":"B","votes":0,"seats":0,"eliminated":false}
{"id":3,"version":21,"name":"C","votes":0,"seats":0,"eliminated":false}
{"id":4,"version":13,"name":"D","votes":0,"seats":0,"eliminated":false}
{"id":5,"version":17,"name":"E","votes":0,"seats":0,"eliminated":false}
{"id":6,"version":18,"name":"F","votes":0,"seats":0,"eliminated":false}
{"id":7,"version":19,"name":"G","votes":0,"seats":0,"eliminated":false}
Lists in the race ---------------------
{"id":1,"version":21,"name":"A","votes":0,"seats":0,"eliminated":false}
{"id":2,"version":22,"name":"B","votes":10,"seats":1,"eliminated":true}
{"id":3,"version":21,"name":"C","votes":20,"seats":2,"eliminated":false}
{"id":4,"version":13,"name":"D","votes":30,"seats":3,"eliminated":true}
{"id":5,"version":17,"name":"E","votes":40,"seats":4,"eliminated":false}
{"id":6,"version":18,"name":"F","votes":50,"seats":5,"eliminated":true}
{"id":7,"version":19,"name":"G","votes":60,"seats":6,"eliminated":false}
  • lines 1-23: Spring Test logs;
  • lines 25-26: the contents of the [CONF] table;
  • lines 27-34: the initial contents of the [LISTES] table;
  • lines 35-42: the contents of the [LISTES] table after assigning values to the [voix, sieges, elimine] fields;

Additionally, the JUnit test passes:

 

7.11. Creation of the [with-dependencies] archive for the [dao] layer

The final project has the following architecture:

Let’s review the project’s Maven configuration:


<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>istia.st.elections</groupId>
    <artifactId>elections-dao-jdbc-01</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.7.RELEASE</version>
    </parent>

    <dependencies>
        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- Spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
        </dependency>
        <!-- Tomcat Jdbc -->
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jdbc</artifactId>
        </dependency>
        <!-- JSON library -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>
        <!-- Spring Boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot Logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>
    </dependencies>

    <!-- plugins -->
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>config.AppConfig</mainClass>
                        </manifest>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
            </plugin>
            <!-- for installing the project artifact in the local Maven repository -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.18.1</version>
            </plugin>
        </plugins>
    </build>
</project>
  

We will generate a single JAR file that will contain the classes from all of the above JARs plus those from the [DAO] layer project. This is done by making a change to the [pom.xml] file:


<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>istia.st.elections</groupId>
    <artifactId>elections-jdbc-01</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.2.RELEASE</version>
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        ...
    </dependencies>

    <!-- plugins -->
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>console.Main</mainClass>
                        </manifest>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>
  • lines 25–37: configure a Maven plugin to generate the JAR;
  • line 15: specifies the Java version to use for compilation;

Once this change is made, you can generate the JAR as follows [1-10]:

  • In [5], select the project folder using the button [6];
  • in [7], give the build configuration a name;
  • in [8], enter the list of Maven tasks to execute:
    • [clean]: clears the project’s [target] folder where the generated JAR will be placed;
    • [compile]: compiles the project;
    • [assembly:single]: generates a single JAR file containing all the classes of the project and its dependencies;
  • in [9-10], verify that you have a JDK (Java Development Kit) and not a JRE (Java Runtime Environment). The difference is that the JDK includes a compiler, whereas the JRE does not. If you do not have a JDK, you must add one in [10] using [11]. To do this, follow the procedure described in section 3.1;
  • In [17], run the build configuration;
  • in [13], the generated archive;

You can open this archive with an unzipper:

 

7.12. Testing the [DAO] layer archive

Let’s create a standard Eclipse project (not Maven) [1]:

Let's copy and paste the [dao.console] package from the [elections-dao-jdbc-01] project into the [elections-dao-jdbc-02] project [2]. Several errors appear because the [Main] class references classes that are not in its [Classpath]. We will modify the [Classpath].

First, we create [2-8] a [lib] folder in the new project:

In [9], we place the archive created in the previous step into the [lib] folder, then modify the project’s Build Path:

  • in [18], we imported the archive of the [DAO] layer created previously;
  • in [19], the project no longer shows any errors;

We can then run the [Main] class. We get the same results as before.

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