3. Introduction to the JDBC API
3.1. Setting up the working environment
We will be working with a MySQL5 database.
You must have:
- a JDK (Java Development Kit) installed (Section 23.1);
- installed the Maven dependency manager (section 23.2);
- installed the Spring Tool Suite (STS) IDE (section 23.3);
- installed the MySQL5 DBMS (section 23.4) and its EMS MyManager client (section 23.5);
- downloaded the source code from [http://tahe.developpez.com/java/spring-database];
We assume hereinafter that the MySQL5 administrator is root with the password root. Start the MySQL5 DBMS and its client [MyManager]. Using [MyManager], we create the database [dbproduits] [1-34]:
 |
- in [3], the database must be named [dbproduits];
 |
- in [8-9], root with the root password (which is not shown in the screenshot above);
 |
- in [14a], the password is root again (which the screenshot does not show);
- in [15], the database [dbproduits] has been created;
 |
- in [20], pay attention to the selected database. It must be the [dbproduits] database;
 |
- in [22], the folder is <examples>/spring-database-config/mysql/databases, where <examples> is the folder containing the downloaded examples;
- in [23], select the SQL script [dbproduits.sql]. It will generate the [PRODUITS] table in the [dbproduits] database;
 |
 |
- in [30], the [products] table has been created;
 |
- in [33], the columns of the [products] table;
 |
- in [34], it is initially empty;
Now, using STS, import the following projects (follow the same procedure used for the projects in the <examples>/spring-core folder):
 |
- in [2], the [mysql-config-jdbc] project will be found in the [<examples>/spring-database-config/mysql/eclipse/mysql-config-jdbc] folder [1];
This project configures the JDBC layer of the architecture below:
 |
Then import the following three projects again:
 |
- In [2], the projects will be found in the folder [<examples>/spring-database-config/spring-jdbc] [1];
These three projects are Maven projects that use the [mysql-config-jdbc] Maven project. This project generates the following Maven artifact (see pom.xml):
<groupId>dvp.spring.database</groupId>
<artifactId>generic-config-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
The same artifact will be generated by the [oracle-config-jdbc, db2-config-jdbc, ...] projects. To ensure that the [spring-generic-jdbc-*] projects currently loaded in STS are indeed using the [mysql-config-jdbc] project:
- Make sure that no other [sgbd-config-jdbc] project is loaded at the same time. This could cause errors that are difficult to understand;
- Update the Maven configuration of the loaded projects as follows:
 |
 |
To verify your configuration, run the [spring-jdbc-generic-01.IntroJdbc01] build configuration [1-3]:
 |
You should see the following console output:
------------------------------ Dumping the [PRODUCTS] table
------------------------------ Populating the [PRODUCTS] table
------------------------------ Displaying the [PRODUCTS] table
List of products:
{"id":1,"name":"NAME1","category":1,"price":100.0,"description":"DESC1"}
{"id":2,"name":"NAME2","category":1,"price":101.0,"description":"DESC2"}
{"id":3,"name":"NAME3","category":1,"price":102.0,"description":"DESC3"}
{"id":4,"name":"NAME4","category":1,"price":103.0,"description":"DESC4"}
{"id":5,"name":"NAME5","category":2,"price":104.0,"description":"DESC5"}
{"id":6,"name":"NAME6","category":2,"price":105.0,"description":"DESC6"}
{"id":7,"name":"NAME7","category":2,"price":106.0,"description":"DESC7"}
{"id":8,"name":"NAME8","category":2,"price":107.0,"description":"DESC8"}
{"id":9,"name":"NAME9","category":2,"price":108.0,"description":"DESC9"}
{"id":10,"name":"NAME10","category":3,"price":109.00000000000001,"description":"DESC10"}
------------------------------ Update to the [PRODUCTS] table
------------------------------ Displaying the [PRODUCTS] table
List of products:
{"id":1,"name":"NAME1","category":1,"price":110.00000000000001,"description":"DESC1"}
{"id":2,"name":"NAME2","category":1,"price":111.10000000000001,"description":"DESC2"}
{"id":3,"name":"NAME3","category":1,"price":112.2,"description":"DESC3"}
{"id":4,"name":"NAME4","category":1,"price":113.30000000000001,"description":"DESC4"}
{"id":5,"name":"NAME5","category":2,"price":104.0,"description":"DESC5"}
{"id":6,"name":"NAME6","category":2,"price":105.0,"description":"DESC6"}
{"id":7,"name":"NAME7","category":2,"price":106.0,"description":"DESC7"}
{"id":8,"name":"NAME8","category":2,"price":107.0,"description":"DESC8"}
{"id":9,"name":"NAME9","category":2,"price":108.0,"description":"DESC9"}
{"id":10,"name":"NAME10","category":3,"price":109.00000000000001,"description":"DESC10"}
------------------------------ Emptying the [PRODUCTS] table
------------------------------ Displaying the [PRODUCTS] table
List of products:
------------------------------ Inserting two products with the same primary key into the [PRODUCTS] table
The following errors occurred when adding two products with the same primary key:
- Duplicate entry '100' for key 'PRIMARY'
------------------------------ Displaying the [PRODUCTS] table
List of products:
------------------------------ Work completed
In the following examples, the reader can:
3.2. Steps for operating a database
 |
In the architecture above, operating a database via the console program involves the following steps:
- loading the database’s JDBC driver;
- opening a connection to the database;
- executing an SQL query on the database and processing the results of the SQL query;
- closing the connection;
Step 1 is performed only once. Steps 2–4 are performed repeatedly. Note that connections are not left open; they are closed as soon as they are no longer needed.
3.2.1. Step 1 - Loading the JDBC driver into memory
The code
// Loading the JDBC driver
try {
Class.forName(JDBC driver class name);
} catch (ClassNotFoundException e1) {
// handle the exception
}
The purpose of the operation on line 3 is to load the database’s JDBC driver into memory. This operation only needs to be performed once. However, repeating it does not cause an error. The JDBC driver class is searched for in the project’s classpath. Therefore, in the Eclipse project, the [jar] file containing the JDBC driver class must have been included in the project’s classpath.
3.2.2. Step 2 - Opening a connection
Once the JDBC driver is in place, we ask it to open a connection to the database:
The code
package spring.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class IntroJdbc01 {
...
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// Open connection
connection = DriverManager.getConnection(url, user, passwd);
...
} catch (SQLException e1) {
// handle the exception
...
} finally {
// close the connection
if (connection != null) {
try {
connection.close();
} catch (SQLException e2) {
// handle the exception
...
}
}
}
- Lines 3–7: The classes that implement the JDBC interface are all in the [java.sql] package. Furthermore, in the event of an error, they all throw an [SQLException] (lines 19, 27). This exception derives from the [Exception] class and is a so-called checked exception: you must use a try/catch block to handle it, or alternatively, choose not to handle it and indicate that the method allows the exception to propagate by adding [throws SQLException] to the method signature;
- line 17, [DriverManager.getConnection] is a static method that expects three parameters:
- [url]: the database URL. This is a string that depends on the database being used. For MySQL, it is in the form [jdbc:mysql://localhost:3306/db_name];
- [user]: the connection owner;
- [passwd]: the user's password;
- lines 24–30: the connection must be closed in the [finally] clause so that it is closed regardless of whether an exception occurs or not.
3.2.3. Step 3 - Executing SQL [SELECT] Statements
Once a connection is established, SQL commands can be executed. The way read commands [SELECT] are handled differs from that used for update operations [UPDATE, INSERT, DELETE]. We’ll start with [SELECT] SQL commands:
The code
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// Open connection
connection = DriverManager.getConnection(url, user, passwd);
// Start transaction
connection.setAutoCommit(false);
// in read-only mode
connexion.setReadOnly(true);
// Read the [PRODUCTS] table
ps = connection.prepareStatement("SELECT ID, NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS");
rs = ps.executeQuery();
System.out.println("List of products: ");
while (rs.next()) {
System.out.println(new Product(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDouble(4), rs.getString(5)));
}
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException(connection, e1);
} finally {
// handle the finally block
doFinally(rs, ps, connection);
}
private void doFinally(ResultSet rs, PreparedStatement ps, Connection connection) {
....
}
- Lines 8, 10: opening a transaction (line 8) in read-only mode (line 10). A transaction is a sequence of SQL statements that either all succeed or all fail. Thus, in a transaction containing N SQL statements, if the (I+1)th statement fails, then the preceding I statements will be rolled back. For a read operation, a transaction is not necessary. However, creating a read-only transaction can allow certain DBMSs to perform certain optimizations;
- Line 12: use of a [PreparedStatement]. A [PreparedStatement] normally has parameters denoted by the character ?. Here, it has none. A [PreparedStatement] is a statement prepared by the DBMS. This preparation has a cost and is performed only once. This prepared statement is then executed by the DBMS with actual parameters that replace the placeholder parameters ?. Note that it is preferable to specify the desired columns rather than using the * notation to retrieve all columns. By specifying the column names, their values can then be retrieved based on their position in the SELECT statement;
- Line 13: execution of the [PreparedStatement]. A [ResultSet] object is retrieved;
A [ResultSet] object represents a table, i.e., a set of rows and columns. At any given time, we have access to only one row of the table, called the current row. When the [ResultSet] is initially created, there is no current row. We must perform a [ResultSet.next()] operation to obtain it. The signature of the next method is as follows:
This method attempts to move to the next row of the [ResultSet] and returns true if successful, false otherwise. If successful, the next row becomes the new current row. The previous row is lost and cannot be retrieved.
The [ResultSet] table has columns named labelCol1, labelCol2, ... as specified in the executed [SELECT] query. With the query:
SELECT ID as myId, NAME as myName, CATEGORY as myCategory, PRICE as myPrice, DESCRIPTION as myDescription FROM PRODUCTS
- the [ID] column will go into a column in the [ResultSet] named [myId];
- the [NAME] column will go into a column in the [ResultSet] named [myName];
- ...
In the example above, the identifiers [myCol] are called column labels. Without these labels, the names of the [ResultSet] columns depend on the DBMS. When the [SELECT] operates on a single table, the column labels will by default be the names of the columns requested by the SELECT. The problem arises when the [SELECT] operates on multiple tables and those tables contain identical column names, as in the following example:
SELECT PRODUCTS.NAME, CATEGORIES.NAME FROM PRODUCTS, CATEGORIES WHERE PRODUCTS.CATEGORY_ID=CATEGORIES.ID
assuming that the [PRODUCTS] table has a foreign key to the [CATEGORIES] table represented by the relationship [PRODUCTS].CATEGORY_ID --> [CATEGORIES].ID, and that both the [PRODUCTS] and [CATEGORIES] tables have a [NAME] field. In this case, the names given in the [ResultSet] to the [PRODUITS.NOM] and [CATEGORIES.NOM] columns are DBMS-dependent. For portability between DBMSs, column labels must therefore be used here, and we would write:
SELECT PRODUCTS.NAME as p_NAME, CATEGORIES.NAME as c_NAME FROM PRODUCTS, CATEGORIES WHERE PRODUCTS.CATEGORY_ID = CATEGORIES.ID
To access the various fields of the current row in the [ResultSet], the following methods are available:
to retrieve the column named "labelColi" from the current row, i.e., the column in the [SELECT] statement with that label. Type refers to the data type of the "labelColi" field. The following [getType] methods can be used: getInt, getLong, getString, getDouble, getFloat, getDate, ... Instead of using the column name, you can use its position in the executed [SELECT] query:
where i is the index of the desired column (i>=1).
- lines 15–17: retrieval of values read from the database;
- line 19: the transaction is validated (also known as committed). This terminates it and releases the resources that the DBMS had allocated for it;
- line 25: resources are released in the [finally] block. This calls the following [doFinally] method:
private void doFinally(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) {
}
}
if (connection != null) {
try {
// close the connection
connection.close();
} catch (SQLException e3) {
// handle the exception
}
}
}
- lines 3-9: close the [ResultSet];
- lines 11–17: close the [PreparedStatement];
- lines 18–27: closing the connection;
The closures in lines 3–17 seem redundant since the connection is closed in lines 18–25. In fact, in some cases they are not redundant, and it is recommended to leave them [http://stackoverflow.com/questions/4507440/must-jdbc-resultsets-and-statements-be-closed-separately-although-the-connection].
- Line 22: The exception is handled by the following [doCatchException] method:
private static void doCatchException(Connection connection, Throwable th) {
// roll back transaction
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e2) {
// handle the exception
}
}
- lines 4–6: the transaction is rolled back. This terminates it, and the DBMS can release the resources allocated to it;
3.2.4. Step 3 - Issuing SQL statements [INSERT, UPDATE, DELETE]
SQL statements [INSERT, UPDATE, DELETE] are update operations: they modify the database but do not return any rows. The only information returned is the number of rows affected by the update operation.
The code
Connection connection = null;
PreparedStatement ps = null;
try {
// open connection
connection = DriverManager.getConnection(url, user, passwd);
// start transaction
connection.setAutoCommit(false);
// in read/write mode
connection.setReadOnly(false);
// update the table
ps = connection.prepareStatement("UPDATE PRODUCTS SET PRICE=PRICE*1.1 WHERE CATEGORY=?");
// category 1
ps.setInt(1, 10);
// execute
int rows = ps.executeUpdate();
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException(connection, e1);
} finally {
// handle the finally block
doFinally(null, ps, connection);
}
}
- line 9: the connection is used for reading and writing;
- line 11: a [PreparedStatement] with 1 parameter (represented by ?). There can be multiple parameters. They are numbered starting from 1;
- line 13: its value is assigned to the single parameter. The first parameter of [setType] is the parameter’s position in the [PreparedStatement] (1, 2, ...) and the second is the value assigned to it. You can use the methods [setInt, setLong, setFloat, setDouble, setString, setDate, ...];
- line 15: the [executeUpdate] method is used, not [executeQuery], which is reserved for SELECT statements. The method returns the number of rows affected by the operation. May be 0.
- line 17: the transaction is committed;
3.2.5. Step 4 - Closing the connection
A connection must be closed as quickly as possible in a multi-user environment because a DBMS accepts a limited number of open connections. In the previous examples, it was closed in the [finally] clause of the SQL operations so that it would be closed regardless of whether an exception occurred or not.
3.3. Configuring the JDBC Layer for the MySQL5 Database Management System
We will examine the [mysql-config-jdbc] project, which configures the JDBC layer as follows:
 |
3.3.1. The Eclipse project
 |
3.3.2. Maven Configuration
The project's [pom.xml] file 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>dvp.spring.database</groupId>
<artifactId>generic-config-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>generic jdbc configuration</name>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.2.3.RELEASE</version>
</parent>
<dependencies>
<!-- variable dependencies ********************************************** -->
<!-- JDBC driver for the DBMS -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- constant dependencies ********************************************** -->
<!-- Tomcat JDBC -->
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
</dependency>
<!-- JSON library -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<!-- Google Guava -->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>16.0.1</version>
</dependency>
<!-- Spring Boot -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot</artifactId>
</dependency>
<!-- Spring Boot Test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<!-- logs -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</dependency>
</dependencies>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>1.7</java.version>
</properties>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.18.1</version>
</plugin>
</plugins>
</build>
</project>
This Maven configuration includes a number of archives that are required either by the [mysql-config-jdbc] project or by projects that will depend on it:
- lines 4–6: the Maven artifact generated by the project. As previously mentioned, all [*-config-jdbc] projects generate this same artifact. Therefore, two [*-config-jdbc] projects must not be loaded at the same time;
- lines 9–13: the parent Maven project for this one. It defines the versions of a large number of archives used by the Spring ecosystem. This avoids having to specify them in the projects that derive from it;
- lines 18–21: the JDBC driver archive for the MySQL5 DBMS. This is the only archive required by the [spring-jdbc-01] project;
- lines 24–27: the [tomcat-jdbc] artifact provides an archive required by the JDBC projects [spring-jdbc-02 to 04];
- lines 29–36: provide the libraries required for JSON handling. Used in nearly all projects in this document;
- lines 38–42: Google Guava is a collection management library. Used in almost all projects in this document;
- lines 43–52: libraries for writing tests that integrate Spring and JUnit. Used in almost all projects in the document;
- lines 54–57: logging libraries. Used in almost all projects in this document;
- lines 67–71: the plugin used to install the [mysql-config-jdbc] project artifact in the local Maven repository;
3.3.3. The [ConfigJdbc] configuration class
 |
The [ConfigJdbc] class is as follows:
package generic.jdbc.config;
import org.springframework.beans.factory.config.ConfigurableBeanFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Scope;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.ser.impl.SimpleBeanPropertyFilter;
import com.fasterxml.jackson.databind.ser.impl.SimpleFilterProvider;
public class ConfigJdbc {
// connection parameters
public final static String DRIVER_CLASSNAME = "com.mysql.jdbc.Driver";
public final static String URL_DBPRODUCTS = "jdbc:mysql://localhost:3306/dbproducts";
public final static String USER_DBPRODUCTS = "root";
public final static String PASSWD_DBPRODUITS = "root";
...
// SQL statements [jdbc-01, jdbc-02]
public final static String V1_INSERT_PRODUCTS_WITH_ID = "INSERT INTO PRODUCTS(ID, NAME, CATEGORY, PRICE, DESCRIPTION) VALUES (?, ?, ?, ?, ?)";
public final static String V1_DELETE_PRODUCTS = "DELETE FROM PRODUCTS";
//public final static String V1_DELETE_PRODUCTS = String.format("DELETE FROM %s", TAB_PRODUCTS);
public final static String V1_SELECT_PRODUCTS = "SELECT ID, NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS";
public final static String V1_UPDATE_PRODUCTS = "UPDATE PRODUCTS SET PRICE=PRICE*1.1 WHERE CATEGORY=?";
public final static String V1_INSERT_PRODUCTS_2 = "INSERT INTO PRODUCTS(ID, NAME, CATEGORY, PRICE, DESCRIPTION) VALUES (100,'X',1,1,'x')";
// SQL statements [jdbc-03]
public final static String V2_INSERT_PRODUCTS = "INSERT INTO PRODUCTS(NAME, CATEGORY, PRICE, DESCRIPTION) VALUES (?, ?, ?, ?)";
public final static String V2_DELETE_ALLPRODUCTS = "DELETE FROM PRODUCTS";
public final static String V2_DELETE_PRODUCTS = "DELETE FROM PRODUCTS WHERE ID=?";
public final static String V2_SELECT_ALLPRODUCTS = "SELECT ID, NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS";
public final static String V2_SELECT_PRODUCT_BYID = "SELECT NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS WHERE ID=?";
public final static String V2_SELECT_PRODUCT_BYNAME = "SELECT ID, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS WHERE NAME=?";
public final static String V2_UPDATE_PRODUCTS = "UPDATE PRODUCTS SET NAME=?, PRICE=?, CATEGORY=?, DESCRIPTION=? WHERE ID=?";
...
}
The [ConfigJdbc] class is used to configure the JDBC layer for the four projects [spring-jdbc-01 to 04]. Most of the configuration pertains to the [spring-jdbc-04] project. We will cover this section when we examine that project. Only the configuration for projects [spring-jdbc-01 to 03] is shown above.
- lines 14–17: connection parameters for the MySQL5 database [dbproduits];
- lines 20–25: the SQL statements used in projects [spring-jdbc-01 and 02];
- lines 28–34: the SQL statements used in the [spring-jdbc-03] project;
These SQL statements use the [PRODUCTS] table in the MySQL5 database [dbproducts], which has the following structure:
 |
- [ID]: primary key in AUTO_INCREMENT mode (if no primary key is specified, the DBMS generates it);
- [NAME]: name of a product—unique;
- [CATEGORY]: category number;
- [PRICE]: its price;
- [DESCRIPTION]: a description of the product;
3.3.4. The [Product] class
 |
The [Product] class represents a row in the [PRODUCTS] table:
package generic.jdbc.entities.dbproducts;
public class Product {
// fields
private int id;
private String name;
private int category;
private double price;
private String description;
// constructors
public Product() {
}
public Product(int id, String name, int category, double price, String description) {
this.id = id;
this.name = name;
this.category = category;
this.price = price;
this.description = description;
}
// getters and setters
...
}
Later on, we will need to compare two products to determine whether they are equal or not. We will say that two products are equal if all their fields are equal. To do this, we will override the [equals] method of the [Object] class, from which the [Product] class derives:
// equality method
@Override
public boolean equals(Object o) {
// simple cases
if (o == null || o.getClass() != this.getClass()) {
return false;
}
Product p = (Product) o;
return this == o
|| (this.id == p.id && this.name.equals(p.getName()) && this.category == p.category
&& Math.abs(this.price - p.price) < 1e-6 && this.description.equals(p.description));
}
- line 3: the [equals] method receives an object o that it must compare to the object this;
- lines 5–7: the simple cases where we can immediately tell that the two objects are not equal. [Object].getClass() returns an instance of type [Class], a type that represents the object’s actual class;
- line 8: the object o is converted to a product p;
- line 9: if the two references o and p to a product are equal, then they physically refer to the same product;
- line 9: if o and p are two different references to two products with the same fields, we will say they are equal. Because the price is of type [double] and there is no exact representation of real numbers in computer science, we will consider two prices to be identical if they are within 10⁻⁶ of each other;
Furthermore, we will redefine the [hashCode] method of the [Object] class:
// hashcode
@Override
public int hashCode() {
return id + 2 * name.hashCode() + 3 * category + 4 * description.hashCode();
}
The hashCode values of two products must be the same if the [equals] method has declared these two products to be equal. This hashCode value is used to sort objects into collections such as dictionaries. In the example above, if two products are identical, they will indeed have the same hashCode.
3.3.5. The [UncheckedException]
 |
Consider the following architecture:
 |
- the [JDBC] layer throws [SQLException] exceptions. This exception must propagate up through the layers until it reaches the highest layer, in this case the testing layer;
The [DAO] layer could simply let the [SQLException] propagate up to the testing layer. But since this exception is unchecked (it derives directly from [Exception]), this would imply that the [IDao] interface of the [DAO] layer would be as follows:
public interface IDao {
// add products
public List<Product> addProducts(List<Product> products) throws SQLException;
// list of all products
public List<Product> getAllProducts() throws SQLException;
// a specific product
public Product getProductById(int id) throws SQLException;
public Product getProductByName(String name) throws SQLException;
// update multiple products
public int updateProducts(List<Product> products) throws SQLException;
// Delete all products
public int deleteAllProducts() throws SQLException;
// Delete multiple products
public int deleteProducts(int[] ids) throws SQLException;
}
And that's very annoying because it prevents us from implementing the [IDao] interface with a class that would throw a different exception. To work around this issue, the [DAO] layer will throw an unhandled [DaoException] (derived from [RuntimeException]), which allows us to omit the [throws] clause in the interface method signatures. As a result, the interface can be implemented by any class that also throws an unchecked exception, which may be different from the [DaoException]. Our architecture now looks like this:
 |
To facilitate the creation of unchecked exceptions for different layers of an application, we create a parent class [UncheckedException] for them:
|
package generic.jdbc.infrastructure;
import java.util.ArrayList;
import java.util.List;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
// generic exception class
// the exception is unchecked
public class UncheckedException extends RuntimeException {
// generated serial ID
private static final long serialVersionUID = -2924871763340170310L;
// properties
private int code;
private String trace;
private List<ShortException> exceptions;
// constructors
public UncheckedException() {
super();
}
public UncheckedException(int code, Throwable e, String simpleClassName) {
super(e);
// local
this.code = code;
this.exceptions = getErrorsForException(e);
// log
String fileName = String.format("%s.java", simpleClassName);
StackTraceElement[] traces = e.getStackTrace();
boolean found = false;
for (int i = 0; !found && i < traces.length; i++) {
StackTraceElement trace = traces[i];
if (fileName.equals(trace.getFileName())) {
this.trace = String.format("[%s,%s,%s]", simpleClassName, trace.getMethodName(), trace.getLineNumber());
found = true;
}
}
}
@Override
public String getMessage() {
return this.toString();
}
@Override
public void printStackTrace() {
System.out.println(this);
}
// list of error messages for an exception
private List<ShortException> getErrorsForException(Throwable th) {
// retrieve the elements from the exception stack
Throwable cause = th;
List<ShortException> exceptions = new ArrayList<ShortException>();
while (cause != null) {
// retrieve the current exception
exceptions.add(new ShortException(cause.getClass().getName(), cause.getMessage()));
// next exception
cause = cause.getCause();
}
return exceptions;
}
@Override
public String toString() {
ObjectMapper jsonMapper = new ObjectMapper();
try {
return String.format("[code=%s, trace=%s, exceptions=%s", code, trace, jsonMapper.writeValueAsString(exceptions));
} catch (JsonProcessingException e) {
e.printStackTrace();
return null;
}
}
// getters and setters
...
}
- line 12: the class extends [RuntimeException] and is therefore an unchecked exception type. It will be used to wrap a checked exception (SQLException) in an unchecked exception type (UncheckedException);
- to differentiate between [UncheckedException] exceptions, we can assign them a code that will be stored in the private field on line 18. Java code intercepting an [UncheckedException] will have access to this error code via the [getCode] method (lines 80 and beyond);
- line 20: stores the error messages from the stack of the encapsulated exception;
- lines 23–43: the different ways to construct an object of type [UncheckedException];
- lines 56–67: a private method that allows constructing the error list from line 20 from a [Throwable] object or a derived type, specifically the [Exception] type;
- lines 69–78: the [toString] method returns a string representing the exception. To display the list of errors from line 20, it uses a JSON library. This library is included in the project’s Maven dependencies:
<!-- jSON library -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
- lines 45–48: redefine the [getMessage] method of the parent class [RuntimeException]. Here, it returns the [toString] signature of the class;
- lines 50–53: redefine the [printStackTrace] method of the parent class [RuntimeException]. The [toString] signature of the class will be displayed;
The [UncheckedException] class stores, in the field on line 20, a list of exceptions described by the following [ShortException] type:
package pam.dao.exceptions;
public class ShortException {
// properties
private String className;
private String errorMessage;
// constructors
public ShortException() {
}
public ShortException(String className, String errorMessage) {
this.className = className;
this.errorMessage = errorMessage;
}
// getters and setters
...
}
- line 6: the name of the class of the exception that occurred;
- line 7: the associated error message;
Let's examine the following constructor of the [UncheckedException] class:
public UncheckedException(int code, Throwable e, String simpleClassName) {
super(e);
// local
this.code = code;
this.exceptions = getErrorsForException(e);
// log
String fileName = String.format("%s.java", simpleClassName);
StackTraceElement[] traces = e.getStackTrace();
boolean found = false;
for (int i = 0; !found && i < traces.length; i++) {
StackTraceElement trace = traces[i];
if (fileName.equals(trace.getFileName())) {
this.trace = String.format("[%s,%s,%s]", simpleClassName, trace.getMethodName(), trace.getLineNumber());
found = true;
}
}
}
- line 1, the parameters are as follows:
- [code]: an error code;
- [e]: the exception being encapsulated. [Throwable] is the parent class of the [Exception] class and derives directly from the [Object] class. It is the parent class of all C classes with which one can write [throw c;] where c is an instance of C;
- [simpleClassName]: the simple name of the user code class where the exception e was detected;
- line 4: the error code is recorded;
- line 5: the list of [ShortException] is constructed from the [Throwable e] passed as a parameter;
- lines 7–16: the so-called exception traces are examined. An initial exception occurs at a specific point in the code and then propagates back to the method that called the one where the exception occurred, and so on until a try/catch block catches it. During this propagation, the initial exception leaves traces stored in the [e.stackTrace] array of the exception e. These are retrieved here on line 8 from the [Throwable e] passed as a parameter. Each element of type [StackTraceElement] is an object with the following fields:
- [fileName]: the name of the Java file where the exception occurred;
- [lineNumber]: the line number in this file where the exception occurred;
- [methodName]: the name of the method in this file where the exception occurred;
- Lines 10–16 search the array of traces for the exception passed as a parameter, looking for the first occurrence of the condition [trace.fileName == simpleClassName.java], where [simpleClassName] is the third parameter of the constructor. The idea is to record where the exception occurred in the user code. The user code will wrap an exception as follows:
- line 13: we create a string of type [fileName, methodName, lineNumber] describing the location in the user code where the exception e was caught;
Now, let’s examine the code that records the list of exceptions from the exception stack of the [Throwable th] exception encapsulated by the previous constructor:
// list of error messages for an exception
private List<ShortException> getErrorsForException(Throwable th) {
// retrieve the elements from the exception stack
Throwable cause = th;
List<ShortException> exceptions = new ArrayList<ShortException>();
while (cause != null) {
// retrieve the current exception
exceptions.add(new ShortException(cause.getClass().getName(), cause.getMessage()));
// next exception
cause = cause.getCause();
}
return exceptions;
}
As it propagates back to the method that caught it using a try/catch block, the initial exception e may have been encapsulated within another exception. It is then this latter exception that propagates back to the method that will finally catch it. It, too, can therefore be encapsulated. Ultimately, when a method decides to catch an exception th and handle it, it will find the initial exception e buried at the bottom of a stack of exceptions. Thus, in the example above, the parameter [Throwable th] is merely the tip of the exception iceberg. Its attribute [th.cause] reveals the exception that it itself encapsulates. And so on. When an exception e satisfies [e.getCause()==null], it means that e is the initial exception.
- Line 8: For each exception in the exception stack of [Throwable th], two pieces of information are stored:
- [getClass().getName()]: the full name of the exception;
- [getMessage()]: the associated error message;
3.4. Example-01
3.4.1. Project Architecture
 |
In this example, a console program uses the [JDBC] layer interface.
3.4.2. The Eclipse Project
We create a Spring/Maven project [spring-jdbc-01] by following the procedure in Section 2.5.2.1.
 |
The project is a Maven project defined 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-01</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-jdbc-generic-01</name>
<description>Demo project for the JDBC API</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>
<!-- JDBC configuration for the DBMS -->
<dependency>
<groupId>dvp.spring.database</groupId>
<artifactId>generic-config-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
</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 uses the [generic-config-jdbc] artifact from the [mysql-config-jdbc] project we just examined. The [spring-jdbc-01] project therefore has access to all the elements of the [mysql-config-jdbc] project;
We can see this last point in two ways by inspecting the project’s Maven dependencies:
 |
- In [2], we see that the [mysql-config-jdbc] project is listed in the project’s Maven dependencies. Since these dependencies are in the project’s Classpath, this means that the [mysql-config-jdbc] project is also in this Classpath, and therefore its classes and interfaces are visible in the [spring-jdbc-01] project;
The Maven project [mysql-config-jdbc] does not need to be present in the [Package Explorer] tab to be usable by other Maven projects. It simply needs to be present in the local Maven repository. Unlike an IDE such as NetBeans, this is not automatic with Eclipse. It must be forced:
 |
We reviewed the conditions that enable this generation in Section 2.3.5. Once it has been completed, you can then remove the [mysql-config-jdbc] project from the [Package Explorer] tab:
 |
- Do not check [3], which physically deletes the project from the disk, making it unrecoverable;
This operation re-calculates the Maven dependencies of the projects that depend on the project removed from the [Package Explorer]. This changes the [Maven Dependencies] branch of these projects. For example, for the [spring-jdbc-01] project, the [Maven Dependencies] branch becomes as follows:
 |
This time, the dependency is no longer on a project but on its Maven artifact, in this case the [generic-config-jdbc] artifact [1]. We can see that we do indeed have access to all the classes and interfaces of this artifact. As mentioned, this artifact will be generated by all [*-config-jdbc] projects. To avoid errors, we:
- always keep a single [*-config-jdbc] project in the [Package Explorer] tab;
- update the Maven configuration of all projects in the [Package Explorer] tab (Alt-F5) so that they include the [*-config-jdbc] project in their Maven dependencies;
3.4.3. The skeleton of the main class
 |
The skeleton of the main class [IntroJdbc01] is as follows:
package spring.jdbc;
import generic.jdbc.config.ConfigJdbc;
import generic.jdbc.entities.dbproduits.Product;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
public class IntroJdbc01 {
// constants
final static ObjectMapper jsonMapper = new ObjectMapper();
public static void main(String[] args) {
// Load the JDBC driver for the DBMS
try {
Class.forName(ConfigJdbc.DRIVER_CLASSNAME);
} catch (ClassNotFoundException e1) {
doCatchException("JDBC driver not found", null, e1);
return;
}
// Clear the [PRODUCTS] table
System.out.println(String.format("------------------------------ %s", "Clearing the [PRODUCTS] table"));
delete();
// populate it
System.out.println(String.format("------------------------------ %s", "Filling the [PRODUCTS] table"));
insert();
// read it
System.out.println(String.format("------------------------------ %s", "Displaying the [PRODUCTS] table"));
select();
// update
System.out.println(String.format("------------------------------ %s", "Updating the [PRODUCTS] table"));
update();
// display
System.out.println(String.format("------------------------------ %s", "Displaying the [PRODUCTS] table"));
select();
// clearing the [PRODUCTS] table
System.out.println(String.format("------------------------------ %s", "Clearing the [PRODUCTS] table"));
delete();
// Display it
System.out.println(String.format("------------------------------ %s", "Displaying the [PRODUCTS] table"));
select();
// INSERT two identical rows
// The INSERT should fail, and neither item is inserted due to the transaction
System.out.println(String.format("------------------------------ %s",
"Inserting two products with the same primary key into the [PRODUCTS] table"));
insert2();
// Checking
System.out.println(String.format("------------------------------ %s", "Displaying the [PRODUCTS] table"));
select();
// done
System.out.println(String.format("------------------------------ %s", "Job completed"));
}
// list of products
private static void select() {
...
}
// Display a JSON object
private static void display(Object object) {
...
}
// Delete products
public static void delete() {
...
}
// add products
public static void insert() {
...
}
// Add two products with the same primary keys
public static void insert2() {
...
}
// Update certain products
public static void update() {
...
}
private static void doFinally(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) {
}
}
if (connection != null) {
try {
// close the connection
connection.close();
} catch (SQLException e3) {
// display error messages
show("The following errors occurred while closing the connection",
getErrorsFromThrowable(e3));
}
}
}
private static void doCatchException(String title, Connection connection, Throwable th) {
// display error messages
show(title, getErrorsFromThrowable(th));
// roll back transaction
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e2) {
// display error messages
show("Error rolling back the transaction", getErrorsFromThrowable(e2));
}
}
private static List<String> getErrorsFromThrowable(Throwable th) {
// retrieve the list of error messages from the exception
List<String> errors = new ArrayList<String>();
while (th != null) {
// error message of the throwable
errors.add(th.getMessage());
// move on to the cause of the throwable
th = th.getCause();
}
// result
return errors;
}
private static void show(String title, List<String> messages) {
// title
System.out.println(String.format("%s : ", title));
// messages
for (String message : messages) {
System.out.println(String.format("- %s", message));
}
}
}
- lines 23–29: loading the JDBC driver for the DBMS. On line 25, the constant [ConfigJdbc.DRIVER_CLASSNAME] defined in the [mysql-config-jdbc] project is used;
- lines 136–147: the [getErrorsFromThrowable] method returns the list of error messages encapsulated in an object of type [Throwable], which is the parent class of the [Exception] class. An exception may contain another exception, which can be retrieved using the [Throwable].getCause() method. This allows us to iterate through all exceptions encapsulated in the [Throwable] object;
- lines 149–156: the [show(String title, List<String> messages)] method displays the messages preceded by the text [title];
- lines 122–134: the method [doCatchException(String title, Connection connection, Throwable th)] handles exceptions encountered by the class’s methods. The handled exception is represented by the parameter [Throwable th]. The purpose of the method is:
- to roll back the current transaction of the [Connection connection] object (lines 127–129);
- to write the error messages encapsulated in the exception [Throwable th] (lines 124, 132);
- lines 93–120: the method [doFinally(ResultSet rs, PreparedStatement ps, Connection connection)] handles the [finally] block of the DBMS access methods. Its purpose is to release the resources allocated by the connection;
3.4.4. Deleting the contents of the products table
The [delete] method deletes the contents of the table:
// delete products
public static void delete() {
Connection connection = null;
PreparedStatement ps = null;
try {
// Open connection
connection = DriverManager.getConnection(ConfigJdbc.URL_DBPRODUITS, ConfigJdbc.USER_DBPRODUITS, ConfigJdbc.PASSWD_DBPRODUITS);
// start transaction
connection.setAutoCommit(false);
// in read/write mode
connection.setReadOnly(false);
// empty the [PRODUCTS] table
ps = connection.prepareStatement(ConfigJdbc.V1_DELETE_PRODUCTS);
ps.executeUpdate();
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException("The following errors occurred while deleting the table contents", connection, e1);
} finally {
// handle the finally block
doFinally(null, ps, connection);
}
}
Line 7 uses the following constants from the [ConfigJdbc] class:
public final static String URL_DBPRODUITS = "jdbc:mysql://localhost:3306/dbproduits";
public final static String USER_DBPRODUITS = "root";
public final static String PASSWD_DBPRODUITS = "";
Line 13, the prepared SQL statement is as follows:
public final static String V1_DELETE_PRODUCTS = "DELETE FROM PRODUCTS";
The [delete] method uses transactions. A transaction allows you to group SQL statements that must all succeed or all be rolled back. There are four operations to be aware of:
- start of a transaction: [connection.setAutoCommit(false)];
- end of a successful transaction: [connection.commit()]. In this case, all operations performed on the database during the transaction are committed;
- end of a transaction with failure: [connection.rollback()]. In this case, all operations performed on the database during the transaction are rolled back;
In our examples, whenever an exception occurs, we roll back the transaction in the [doCatchException] method:
private static void doCatchException(String title, Connection connection, Throwable th) {
// display the error messages
Static.show(title, Static.getErrorsFromThrowable(th));
// roll back transaction
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e2) {
// display error messages
Static.show("Error rolling back the transaction", Static.getErrorsFromThrowable(e2));
}
}
3.4.5. Creating the contents of the products table
The [insert] method creates the table content:
public static void insert() {
Connection connection = null;
PreparedStatement ps = null;
try {
// open connection
connection = DriverManager.getConnection(ConfigJdbc.URL_DBPRODUITS, ConfigJdbc.USER_DBPRODUITS, ConfigJdbc.PASSWD_DBPRODUITS);
// start transaction
connection.setAutoCommit(false);
// in read/write mode
connection.setReadOnly(false);
// populate the table
ps = connection.prepareStatement(ConfigJdbc.V1_INSERT_PRODUCTS_WITH_ID);
for (int i = 0; i < 10; i++) {
// preparation
int n = i + 1;
ps.setInt(1, n);
ps.setString(2, String.format("NAME%s", n));
ps.setInt(3, n / 5 + 1);
ps.setDouble(4, 100 * (1 + (double) i / 100));
ps.setString(5, String.format("DESC%s", n));
// execute
ps.executeUpdate();
}
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException("The following errors occurred while creating the table content", connection, e1);
} finally {
// handle the finally block
doFinally(null, ps, connection);
}
}
Line 12, the prepared SQL statement is as follows:
public final static String V1_INSERT_PRODUCTS_WITH_ID = "INSERT INTO PRODUCTS(ID, NAME, CATEGORY, PRICE, DESCRIPTION) VALUES (?, ?, ?, ?, ?)";
3.4.6. Displaying the contents of the products table
The [select] method displays the table contents:
// list of products
private static void select() {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// Open connection
connection = DriverManager.getConnection(ConfigJdbc.URL_DBPRODUITS, ConfigJdbc.USER_DBPRODUITS, ConfigJdbc.PASSWD_DBPRODUITS);
// start transaction
connection.setAutoCommit(false);
// Set to read-only mode
connection.setReadOnly(true);
// read the [PRODUCTS] table
ps = connection.prepareStatement(ConfigJdbc.V1_SELECT_PRODUCTS);
rs = ps.executeQuery();
System.out.println("List of products: ");
while (rs.next()) {
display(new Product(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDouble(4), rs.getString(5)));
}
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException("The following errors occurred while reading the table", connection, e1);
} finally {
// handle the finally block
doFinally(rs, ps, connection);
}
}
Line 14, the prepared SQL statement is as follows:
public final static String V1_SELECT_PRODUCTS = "SELECT ID, NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS";
The [display] method (line 18) is as follows:
// Display a JSON object
private static void display(Object object) {
try {
System.out.println(jsonMapper.writeValueAsString(object));
} catch (JsonProcessingException e) {
e.printStackTrace();
}
}
It displays the JSON representation of the object passed as a parameter (see JSON in Section 23.12).
3.4.7. Updating the table contents
The [update] method updates certain products:
// updating certain products
public static void update() {
Connection connection = null;
PreparedStatement ps = null;
try {
// Open connection
connection = DriverManager.getConnection(ConfigJdbc.URL_DBPRODUITS, ConfigJdbc.USER_DBPRODUITS, ConfigJdbc.PASSWD_DBPRODUITS);
// start transaction
connection.setAutoCommit(false);
// in read/write mode
connexion.setReadOnly(false);
// update the table
ps = connection.prepareStatement(ConfigJdbc.V1_UPDATE_PRODUCTS);
// category 1
ps.setInt(1, 1);
// execute
ps.executeUpdate();
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException("The following errors occurred while updating the table contents", connection, e1);
} finally {
// handle the finally block
doFinally(null, ps, connection);
}
}
Line 13, the prepared SQL statement is as follows:
public final static String V1_UPDATE_PRODUCTS = "UPDATE PRODUCTS SET PRICE=PRICE*1.1 WHERE CATEGORY=?";
3.4.8. Role of the transaction
The [insert2] method inserts two products with the same primary key into the table, which is not possible. Since we are in a transaction, the first insertion will be rolled back.
// Adding 2 products with the same primary keys
public static void insert2() {
Connection connection = null;
PreparedStatement ps = null;
try {
// Open connection
connection = DriverManager.getConnection(ConfigJdbc.URL_DBPRODUITS, ConfigJdbc.USER_DBPRODUITS, ConfigJdbc.PASSWD_DBPRODUITS);
// start transaction
connection.setAutoCommit(false);
// in read/write mode
connection.setReadOnly(false);
// add 1 row
ps = connection.prepareStatement(ConfigJdbc.V1_INSERT_PRODUCTS_2);
// execute
ps.executeUpdate();
// add the same row a second time, so with the same primary key
// The INSERT should fail, and neither of the two elements should be inserted because of the transaction
ps.executeUpdate();
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException("The following errors occurred while adding two products with the same primary key",
connection, e1);
} finally {
// handle the finally block
doFinally(null, ps, connection);
}
}
Line 13, the prepared SQL statement is as follows:
public final static String V1_INSERT_PRODUITS_2 = "INSERT INTO PRODUCTS(ID, NAME, CATEGORY, PRICE, DESCRIPTION) VALUES (100,'X',1,1,'x')";
3.4.9. Results
We execute the execution configuration named [spring-jdbc-generic-01.IntroJdbc01]:
 |
The following console output is obtained:
------------------------------ Dumping the [PRODUCTS] table
------------------------------ Populating the [PRODUCTS] table
------------------------------ Displaying the [PRODUCTS] table
List of products:
{"id":1,"name":"NAME1","category":1,"price":100.0,"description":"DESC1"}
{"id":2,"name":"NAME2","category":1,"price":101.0,"description":"DESC2"}
{"id":3,"name":"NAME3","category":1,"price":102.0,"description":"DESC3"}
{"id":4,"name":"NAME4","category":1,"price":103.0,"description":"DESC4"}
{"id":5,"name":"NAME5","category":2,"price":104.0,"description":"DESC5"}
{"id":6,"name":"NAME6","category":2,"price":105.0,"description":"DESC6"}
{"id":7,"name":"NAME7","category":2,"price":106.0,"description":"DESC7"}
{"id":8,"name":"NAME8","category":2,"price":107.0,"description":"DESC8"}
{"id":9,"name":"NAME9","category":2,"price":108.0,"description":"DESC9"}
{"id":10,"name":"NAME10","category":3,"price":109.0,"description":"DESC10"}
------------------------------ Update to the [PRODUCTS] table
------------------------------ Displaying the [PRODUCTS] table
List of products:
{"id":1,"name":"NAME1","category":1,"price":110.0,"description":"DESC1"}
{"id":2,"name":"NAME2","category":1,"price":111.0,"description":"DESC2"}
{"id":3,"name":"NAME3","category":1,"price":112.0,"description":"DESC3"}
{"id":4,"name":"NAME4","category":1,"price":113.0,"description":"DESC4"}
{"id":5,"name":"NAME5","category":2,"price":104.0,"description":"DESC5"}
{"id":6,"name":"NAME6","category":2,"price":105.0,"description":"DESC6"}
{"id":7,"name":"NAME7","category":2,"price":106.0,"description":"DESC7"}
{"id":8,"name":"NAME8","category":2,"price":107.0,"description":"DESC8"}
{"id":9,"name":"NAME9","category":2,"price":108.0,"description":"DESC9"}
{"id":10,"name":"NAME10","category":3,"price":109.0,"description":"DESC10"}
------------------------------ Emptying the [PRODUCTS] table
------------------------------ Displaying the [PRODUCTS] table
List of products:
------------------------------ Inserting two products with the same primary key into the [PRODUCTS] table
The following errors occurred when adding two products with the same primary key:
- Duplicate entry '100' for key 'PRIMARY'
------------------------------ Displaying the [PRODUCTS] table
List of products:
------------------------------ Work completed
- line 30: before inserting the two products with the same primary key, the table is empty;
- line 35: after inserting the two products with the same primary key, the table is empty. This demonstrates the role of the transaction:
- the first insertion succeeds. There is no reason for it to fail;
- the second insertion fails (line 32). Consequently, because these two insertions are within the same transaction, all SQL statements in that transaction are rolled back, including the first insertion.
3.4.10. Conclusion
What is striking about the previous code snippets is the significant amount of space devoted to handling the [SQLException]. Since any JDBC operation can potentially throw this exception, there are numerous try/catch blocks in the code.
3.5. Example-02
We will revisit the previous application using a [javax.sql.DataSource] data source:
We will use a data source implemented by the [org.apache.tomcat.jdbc.pool.DataSource] class. This class uses a connection pool, i.e., a set of open connections:
- When the pool is instantiated, a certain number of connections are opened to the database. This number is configurable;
- when the Java code opens a connection, it is provided by the pool;
- when the Java code closes a connection, it is returned to the pool;
Ultimately, connections are opened only once, which improves database access performance. The data source will be defined in a Spring configuration class
3.5.1. Project Architecture
 |
In this example, a console program uses the [JDBC] layer interface.
3.5.2. The Eclipse project
The new Eclipse project can be obtained by copying the previous one [1-6]:
 |
 |
We then evolve the project from [6] to [7]:
3.5.3. Maven Configuration
Project [7] is a Maven project defined 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-02</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-jdbc-generic-02</name>
<description>Demo project for the JDBC API</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>
<!-- JDBC configuration for the DBMS -->
<dependency>
<groupId>dvp.spring.database</groupId>
<artifactId>generic-config-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.18.1</version>
</plugin>
</plugins>
</build>
</project>
- lines 28–33: the Maven dependency on the [mysql-config-jdbc] project;
It is the [mysql-config-jdbc] project that includes in its Maven dependencies the library providing an implementation of a [javax.sql.DataSource] data source (see section 3.3.2):
<!-- Tomcat JDBC -->
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
</dependency>
3.5.4. Spring Configuration
 |
The Spring configuration class [AppConfig] is as follows:
package spring.jdbc;
import generic.jdbc.config.ConfigJdbc;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
@Configuration
@Import({ generic.jdbc.config.ConfigJdbc.class })
public class AppConfig {
// data source
@Bean
public DataSource dataSource() {
// TomcatJdbc data source
DataSource dataSource = new DataSource();
// JDBC access configuration
dataSource.setDriverClassName(ConfigJdbc.DRIVER_CLASSNAME);
dataSource.setUsername(ConfigJdbc.USER_DBPRODUITS);
dataSource.setPassword(ConfigJdbc.PASSWD_DBPRODUITS);
dataSource.setUrl(ConfigJdbc.URL_DBPRODUITS);
// Initial open connections
dataSource.setInitialSize(5);
// result
return dataSource;
}
}
- line 10: [AppConfig] is a Spring configuration class;
- line 11: import of the configuration class [generic.jdbc.config.ConfigJdbc.class] defined in the [mysql-config-jdbc] project. This means that all beans defined by this configuration file are available;
- lines 14–27: the Spring bean defining the data source;
- line 17: creation of the data source, which is not yet configured;
- lines 19–22: the information that allows the data source to connect to the database;
- line 24: creates a pool of 5 connections. We only need one here. There are never multiple simultaneous connections;
3.5.5. The main class
The main class [IntroJdbc02] is as follows:
package spring.jdbc;
import generic.jdbc.config.ConfigJdbc;
import generic.jdbc.entities.dbproduits.Product;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
public class IntroJdbc02 {
// JSON mapper
final static ObjectMapper jsonMapper = new ObjectMapper();
// data source
private static DataSource dataSource;
public static void main(String[] args) {
AnnotationConfigApplicationContext ctx = null;
try {
// Retrieve the Spring context
ctx = new AnnotationConfigApplicationContext(AppConfig.class);
// Retrieve the data source
dataSource = ctx.getBean(DataSource.class);
// Clear the [PRODUCTS] table
System.out.println(String.format("------------------------------ %s", "Clearing the [PRODUCTS] table"));
delete();
...
// done
System.out.println(String.format("------------------------------ %s", "Job completed"));
}
// list of products
private static void select() {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// Open connection
connection = dataSource.getConnection();
// start transaction
connection.setAutoCommit(false);
// Set to read-only mode
connection.setReadOnly(true);
// read the [PRODUCTS] table
ps = connection.prepareStatement(ConfigJdbc.V1_SELECT_PRODUCTS);
rs = ps.executeQuery();
System.out.println("List of products: ");
while (rs.next()) {
display(new Product(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDouble(4), rs.getString(5)));
}
// commit transaction
connection.commit();
} catch (SQLException e1) {
// handle the exception
doCatchException("The following errors occurred while reading the table", connection, e1);
} finally {
// handle the finally block
doFinally(rs, ps, connection);
}
}
...
- line 25: the data source. Note that it is of type [javax.sql.DataSource] (line 13), which is an interface;
- line 31: instantiation of Spring objects;
- line 32: obtaining a reference to the data source. Note that the class actually used is never mentioned. Thus, here, there is nothing to suggest that a [TomcatJdbc] implementation is being used;
- line 49: obtaining an open connection. This is how the various methods in [IntroJdbc02] obtain a connection to the database. The rest of the code is identical to that of the [IntroJdbc01] class;
3.5.6. The tests
We run the execution configuration named [spring-jdbc-generic-02.IntroJdbc02]:
 |
We obtain the same results as before (section 3.4.9).
3.6. Example-03
3.6.1. Project architecture
 |
In this example, the data access methods are isolated in a [dao] layer. They will be tested using a JUnit test.
3.6.2. The Eclipse Project
The Eclipse project [spring-jdbc-03] is a Spring/Maven project built like the previous one and then supplemented as follows:
 |  |
The various packages have the following roles:
- [spring.jdbc.config]: Spring project configuration;
- [spring.jdbc.dao]: implementation of the [DAO] layer;
- [spring.jdbc.infrastructure]: implements the unhandled exception [DaoException];
3.6.3. Maven Configuration
The Maven project is 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-03</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-jdbc-generic-03</name>
<description>Demo project for the JDBC API</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>
<!-- JDBC configuration for the DBMS -->
<dependency>
<groupId>dvp.spring.database</groupId>
<artifactId>generic-config-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.18.1</version>
</plugin>
</plugins>
</build>
</project>
It is identical to that of the [spring-jdbc-02] project. In particular, it uses the Maven dependency of the [mysql-config-jdbc] project (lines 28–32).
3.6.4. [DAO] Layer Interface
 |
The [DAO] layer provides the following [IDao] interface:
package spring.jdbc.dao;
import java.util.List;
import spring.jdbc.entities.Product;
public interface IDao {
// add products
public List<Product> addProducts(List<Product> products);
// list of all products
public List<Product> getAllProducts();
// a specific product
public Product getProductById(int id);
public Product getProductByName(String name);
// update multiple products
public int updateProducts(List<Product> products);
// Delete all products
public int deleteAllProducts();
// Delete multiple products
public int deleteProducts(int[] ids);
}
3.6.5. The [DaoException] class
The [DaoException] class simply extends the [UncheckedException] class presented in section 3.3.5:
 |
package spring.jdbc.infrastructure;
public class DaoException extends UncheckedException {
private static final long serialVersionUID = 1L;
// constructors
public DaoException() {
super();
}
public DaoException(int code, Throwable e, String className) {
super(code, e, className);
}
}
3.6.6. Spring Project Configuration
 |
The [AppConfig] class that configures the Spring project is identical to the Spring configuration file in the [spring-jdbc-02] example, except for line 11:
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;
@Configuration
@ComponentScan(basePackages = { "spring.jdbc.dao" })
public class AppConfig {
// data source
@Bean
public DataSource dataSource() {
// TomcatJdbc data source
DataSource dataSource = new DataSource();
// JDBC access configuration
dataSource.setDriverClassName(ConfigJdbc.DRIVER_CLASSNAME);
dataSource.setUsername(ConfigJdbc.USER_DBPRODUITS);
dataSource.setPassword(ConfigJdbc.PASSWD_DBPRODUITS);
dataSource.setUrl(ConfigJdbc.URL_DBPRODUITS);
// Initial open connections
dataSource.setInitialSize(5);
// result
return dataSource;
}
}
- line 11: the [spring.jdbc.dao] package will be scanned to find other Spring components besides those defined in this configuration file;
3.6.7. Implementation of the [DAO] layer
 |
 |
Recall (Section 3.6.4) that the [DAO] layer implements the following [IDao] interface:
package spring.jdbc.dao;
import generic.jdbc.entities.dbproduits.Product;
import java.util.List;
public interface IDao {
// add products
public List<Product> addProducts(List<Product> products);
// list of all products
public List<Product> getAllProducts();
// a specific product
public Product getProductById(int id);
public Product getProductByName(String name);
// update multiple products
public int updateProducts(List<Product> products);
// Delete all products
public int deleteAllProducts();
// Delete multiple products
public int deleteProducts(int[] ids);
}
The [Dao1] and [Dao2] classes both implement this interface. The [Dao2] class is a variant of the [Dao1] class that introduces a new syntax feature. We will focus on the [Dao1] class. Its skeleton is as follows:
package spring.jdbc.dao;
import generic.jdbc.config.ConfigJdbc;
import generic.jdbc.entities.dbproduits.Product;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import spring.jdbc.infrastructure.DaoException;
@Component("dao1")
public class Dao1 implements IDao {
// class name
private String simpleClassName = getClass().getSimpleName();
// data source
@Autowired
protected DataSource dataSource;
// constructor
public Dao1() {
System.out.println("building Dao1...");
}
// ------------------------------- interface
@Override
public List<Product> getAllProducts() {
...
}
@Override
public Product getProductById(int id) {
...
}
@Override
public Product getProductByName(String name) {
...
}
@Override
public List<Product> addProducts(List<Product> products) {
....
}
@Override
public int updateProducts(List<Product> products) {
...
}
@Override
public int deleteAllProducts() {
...
}
@Override
public int deleteProducts(int[] ids) {
...
}
// ---------------------------------------- local methods
// finally handling
protected DaoException doFinally(ResultSet rs, PreparedStatement ps, Connection connection, int code,
DaoException daoException) {
...
}
// catch handling
protected DaoException doCatchException(Connection connection, Throwable th, int code, DaoException daoException) {
...
}
- line 20: the [Dao] class is a Spring component named [dao1]. This name is optional. When it is not present, the name used is the class name with the first letter lowercased;
- line 24: the class name. We avoid hard-coding [Dao] to allow the class to be renamed without having to redefine this field, which thus remains valid;
- lines 26–27: injection of the data source [tomcat-jdbc] defined in the configuration class [AppConfig];
- lines 36–68: implementation of the [IDao] interface;
- lines 78–80: centralized handling of the catch blocks for the various methods;
- lines 72–75: centralized handling of the `finally` blocks for the various methods;
The catch blocks of the various methods are handled as follows:
// catch handling
protected DaoException doCatchException(Connection connection, Throwable th, int code) {
// roll back transaction
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e2) {
e2.printStackTrace();
}
// daoException
return new DaoException(code, th, simpleClassName);
}
- Line 2: The method is declared [protected], which allows child classes to use it without it being public. It takes the following parameters:
- [Connection connection]: the connection to the DBMS—may be null;
- [Throwable th]: the exception that occurred and will be wrapped in a [DaoException] type;
- [int code]: an error code to be used if the method creates a new [DaoException];
- lines 4–7: the primary role of this method is to roll back the transaction associated with the connection passed as parameter 1;
- lines 8–10: if the transaction rollback fails, the exception trace is written to the console. There isn’t much else we can do since we’re going to throw an exception on line 12;
The *finally blocks* of the various methods are handled as follows:
// finally block
protected DaoException doFinally(ResultSet rs, PreparedStatement ps, Connection connection, int code,
DaoException daoException) {
// Close ResultSet
if (rs != null) {
try {
rs.close();
} catch (SQLException e1) {
}
}
// Close [PreparedStatement]
if (ps != null) {
try {
ps.close();
} catch (SQLException e2) {
}
}
// Close connection
if (connection != null) {
try {
connection.close();
} catch (SQLException e3) {
// log the error if possible
if (daoException == null) {
daoException = new DaoException(code, e3, simpleClassName);
}
}
}
// result
return daoException;
}
- line 2: this method is also declared [protected]. It takes the following parameters:
- [ResultSet rs]: the [ResultSet] if a [SELECT] operation was executed—may be null;
- [PreparedStatement ps]: the [PreparedStatement] that was executed—may be null;
- [Connection connection]: the connection to the DBMS—may be null;
- [int code]: an error code to use if the method throws a new [DaoException];
- [DaoException daoException]: the [DaoException] if one occurred before the finally block—may be null;
- lines 21–30: the primary purpose of this method is to close the connection (line 23);
- lines 24–29: if an exception occurs during this closure, then we check the status of the [DaoException daoException] parameter passed to us: if [daoException == null], then we create a new [DaoException] with the code passed as a parameter;
- line 32: the old or new [DaoException] is returned as the result;
We will not present all the methods of the [Dao] class, but only a few. They are all similar.
3.6.7.1. The [getProductById] method
The [getProductById] method returns the product whose primary key is equal to the [id] parameter, or null otherwise;
@Override
public Product getProductById(int id) {
// connection resources
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
// No exception initially
DaoException daoException = null;
// the product being searched for
Product product = null;
try {
// open connection
connection = dataSource.getConnection();
// start transaction
connection.setAutoCommit(false);
// Set to read-only mode
connection.setReadOnly(true);
// read the [PRODUCTS] table
ps = connection.prepareStatement(ConfigJdbc.V2_SELECT_PRODUCT_BYID);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
product = new Product(id, rs.getString(1), rs.getInt(2), rs.getDouble(3), rs.getString(4));
}
// commit transaction
connexion.commit();
// Return to default mode
connection.setAutoCommit(true);
} catch (SQLException e1) {
// handle the exception
daoException = doCatchException(connection, e1, 112);
} finally {
// handle the finally block
daoException = doFinally(rs, ps, connection, 113, daoException);
}
// exception?
if (daoException != null) {
throw daoException;
}
// result
return product;
}
- line 10: the product to be returned is set to null;
- line 19: the SQL statement [ConfigJdbc.V2_SELECT_PRODUCT_BYID] is as follows:
public final static String V2_SELECT_PRODUCT_BYID = "SELECT NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS WHERE ID=?";
- lines 22–24: if the [ResultSet] has a row, it is used to create the product to be returned; otherwise, the product to be returned remains null;
- line 41: the product is returned;
- line 8: the method's [DaoException] is initialized to null;
- line 31: the [doCatchException] method creates a [DaoException];
- line 34: the [daoException] parameter of the [doFinally] method is either null or the exception created by the [doCatchException] method. The [doFinally] method:
- leaves this parameter unchanged if it successfully closes the connection;
- leaves this parameter unchanged if it fails to close the connection and a [DaoException] has already occurred previously;
- creates a new [DaoException] if it fails to close the connection and no [DaoException] has occurred previously;
- lines 37–39: if the local [daoException] is not null, then it is thrown; otherwise, the requested result is returned (line 41);
3.6.7.2. The [deleteProducts] method
The [deleteProduits] method deletes the products whose primary keys are passed to it as parameters. It returns the number of products deleted.
@Override
public int deleteProducts(int[] ids) {
// connection resources
PreparedStatement ps = null;
Connection connection = null;
// No exception initially
DaoException daoException = null;
// number of products updated
int nbProducts = 0;
try {
// Open connection
connection = dataSource.getConnection();
// start transaction
connection.setAutoCommit(false);
// in read/write mode
connection.setReadOnly(false);
// delete products
ps = connection.prepareStatement(ConfigJdbc.V2_DELETE_PRODUCTS);
for (int id : ids) {
// parameters
ps.setInt(1, id);
// execute
nbProducts += ps.executeUpdate();
}
// commit transaction
connection.commit();
// return to default mode
connection.setAutoCommit(true);
} catch (SQLException e1) {
// handle the exception
daoException = doCatchException(connection, e1, 171);
} finally {
// handle the finally block
daoException = doFinally(null, ps, connection, 172, daoException);
}
// exception?
if (daoException != null) {
throw daoException;
}
// result
return nbProducts;
}
- Line 18, the SQL statement [ConfigJdbc.V2_DELETE_PRODUITS] is as follows:
public final static String V2_DELETE_PRODUCTS = "DELETE FROM PRODUCTS WHERE ID=?";
- lines 18–24: the code for deleting products. We can see that the SQL statement is prepared once (line 18) and executed n times (lines 19–24). This is the benefit of the [PreparedStatement] object;
- line 23: the [PreparedStatement].executeUpdate() method returns the number of rows affected by the update operation;
- line 41: returns the number of products updated;
3.6.7.3. The [updateProducts] method
The [updateProduits] method updates the products passed to it as parameters in the database. It returns the number of products updated.
@Override
public int updateProducts(List<Product> products) {
// connection resources
PreparedStatement ps = null;
Connection connection = null;
// No exception initially
DaoException daoException = null;
// number of products updated
int nbProducts = 0;
try {
// Open connection
connection = dataSource.getConnection();
// start transaction
connection.setAutoCommit(false);
// in read/write mode
connection.setReadOnly(false);
// update the [PRODUCTS] table
ps = connection.prepareStatement(ConfigJdbc.V2_UPDATE_PRODUCTS);
for (Product product : products) {
// parameters
ps.setString(1, product.getName());
ps.setDouble(2, product.getPrice());
ps.setInt(3, product.getCategory());
ps.setString(4, product.getDescription());
ps.setInt(5, product.getId());
// execute
nbProducts += ps.executeUpdate();
}
// commit transaction
connection.commit();
// return to default mode
connection.setAutoCommit(true);
} catch (SQLException e1) {
// handle the exception
daoException = doCatchException(connection, e1, 131);
} finally {
// handle the finally block
daoException = doFinally(null, ps, connection, 132, daoException);
}
// exception?
if (daoException != null) {
throw daoException;
}
// result
return nbProducts;
}
- line 18: the SQL statement [ConfigJdbc.V2_UPDATE_PRODUITS] is as follows:
public final static String V2_UPDATE_PRODUCTS = "UPDATE PRODUCTS SET NAME=?, PRICE=?, CATEGORY=?, DESCRIPTION=? WHERE ID=?";
- lines 19–28: the product update code;
3.6.7.4. The [addProducts] method
The [addProducts] method adds the products passed to it as parameters to the database. It returns these same products with their primary keys (before being added to the database, the products do not have a primary key).
@Override
public List<Product> addProducts(List<Product> products) {
// connection resources
PreparedStatement ps = null;
Connection connection = null;
// No exception initially
DaoException daoException = null;
try {
// open connection
connection = dataSource.getConnection();
// in read/write mode
connection.setReadOnly(false);
// Start transaction
connection.setAutoCommit(false);
// add rows to the [PRODUCTS] table
String generatedColumns[] = { ConfigJdbc.TAB_PRODUCTS_ID };
ps = connection.prepareStatement(ConfigJdbc.V2_INSERT_PRODUCTS, generatedColumns);
for (Product product : products) {
// parameters
ps.setString(1, product.getName());
ps.setLong(2, product.getCategory());
ps.setDouble(3, product.getPrice());
ps.setString(4, product.getDescription());
// Execute command
ps.executeUpdate();
// generated primary key
ResultSet generatedKeys = ps.getGeneratedKeys();
if (generatedKeys.next()) {
product.setId(generatedKeys.getInt(1));
} else {
throw new RuntimeException(String.format("The product named [%s] did not retrieve a primary key",
product.getName()));
}
}
// commit transaction
connection.commit();
// return to default mode
connection.setAutoCommit(true);
} catch (SQLException | RuntimeException e1) {
// handle the exception
daoException = doCatchException(connection, e1, 151);
} finally {
// handle the finally block
daoException = doFinally(null, ps, connection, 152, daoException);
}
// exception?
if (daoException != null) {
throw daoException;
}
// result
return products;
}
- Line 16, the SQL statement [ConfigJdbc.V2_INSERT_PRODUITS] is as follows:
public final static String V2_INSERT_PRODUCTS = "INSERT INTO PRODUCTS(NAME, CATEGORY, PRICE, DESCRIPTION) VALUES (?, ?, ?, ?)";
In the above, the product insertion command does not include the primary key [ID]. Since the primary key in the MySQL database has the [AUTOINCREMENT] attribute, the DBMS will generate a primary key for each insertion. The problem arises of retrieving this key. This is an important point because operations on products are performed via their primary keys. We therefore need to know these keys;
- lines 17–33: the product insertion loop;
- line 16: a specific form of the [prepareStatement] method. The second parameter [generatedColumns] is an array of column names whose values we want to retrieve after insertion. On line 16, we specified that we wanted to retrieve the value of the [id] column. Note here that although the names of a table’s columns are case-insensitive (upper/lowercase), the PostgreSQL DBMS requires that this name be in lowercase. This is typically the kind of problem encountered when porting code from one DBMS to another;
- line 24: inserting a row into the database;
- Line 26: Retrieves the list of values from the columns specified on line 16 into a [ResultSet]. Here, for a single insertion, the [ResultSet] will contain one row, and that row will have a single column containing the primary key;
- Line 28: Retrieves the primary key generated by the DBMS;
- Lines 29–32: If the generated primary key is not obtained, a [RuntimeException] is thrown, which will be wrapped in a [DaoException] (lines 38–40);
3.6.8. The [Dao2] class
 |
 |
The [Dao2] class is a variant of the [Dao1] class using a syntax called try-with-resource(resource):
- [resource] is a resource that implements the [java.lang.AutoCloseable] interface. All resources released using the [close] method fall into this category. This syntax ensures that on line 4, the [resource] will be closed. This avoids having to write a [finally] clause to perform this closing operation;
Let’s take the [getAllProducts] method of the [Dao2] class as an example:
@Override
public List<Product> getAllProducts() {
// potential exception
DaoException daoException = null;
// list of products
List<Product> products = new ArrayList<Product>();
try (Connection connection = dataSource.getConnection()) {
// start transaction
connection.setAutoCommit(false);
// in read-only mode
connection.setReadOnly(true);
// read the [PRODUCTS] table
try (PreparedStatement ps = connection.prepareStatement(ConfigJdbc.V2_SELECT_ALLPRODUCTS)) {
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
products.add(new Product(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDouble(4), rs.getString(5)));
}
}
// end transaction
connection.commit();
// return to default mode
connection.setAutoCommit(true);
} catch (SQLException e1) {
// roll back the transaction
daoException = doRollback(connection, e1, 111);
}
} catch (SQLException e2) {
// Handle the exception
if (daoException == null) {
daoException = new DaoException(112, e2, simpleClassName);
}
}
// exception?
if (daoException != null) {
throw daoException;
}
// result
return products;
}
- Line 7: try block with the [Connection] resource. Line 27 ensures that it is closed;
- line 13: try block with the [PreparedStatement] resource. Line 23 ensures that it is closed;
- line 14: try block with the [ResultSet] resource. Line 19 ensures that it is closed;
- Line 25: The transaction is rolled back as follows:
private DaoException doRollback(Connection connection, Throwable e1, int code) {
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
// Generate the exception
return new DaoException(code, e1, simpleClassName);
}
In the end, we have code that is easier to read.
3.6.9. Implementation of the test layer
3.6.9.1. The test classes
 |
 |
- the test [JUnitTestDao1] is a JUnit test for the class [Dao1];
- The [JUnitTestDao2] test is a JUnit test for the [Dao2] class;
- [AbstractJUnitTestDao] is the parent class of the two previous test classes;
- [MainTestDao1] is a console test class for the [Dao1] class;
- [MainTestDao2] is a console test class for the [Dao2] class;
- [AbstractMainTestDao] is the parent class of the two preceding classes. It reuses the code from the console classes [IntroJdbc01, IntroJdbc02] already presented, so we will not examine these console classes;
The [JUnitTestDao1] class is as follows:
package spring.jdbc.tests;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import spring.jdbc.config.AppConfig;
import spring.jdbc.dao.IDao;
@SpringApplicationConfiguration(classes = AppConfig.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class JUnitTestDao1 extends AbstractJUnitTestDao {
// [DAO] layer
@Autowired
@Qualifier("dao1")
private IDao dao;
@Override
IDao getDao() {
return dao;
}
}
- The annotations on lines 12–13 were discussed in Section 2.5.5. They allow a JUnit test to easily access the Spring context and its beans. This context is configured by the [AppConfig] class (line 12) discussed in Section 2.4.3;
- line 14: the class extends the [AbstractJUnitTestDao] class, which we will discuss shortly. The JUnit test methods are located within this class;
- lines 17–19: the bean named [dao1] (line 18) is injected (line 17). Thus, an instance of the [Dao1] class is injected here;
- lines 21–24: the [getDao] method overrides the method of the same name in the parent class;
Ultimately, the purpose of this class is to provide the parent class with a reference to the [DAO] layer that needs to be tested, in this case an instance of [Dao1]. Similarly, the [JUnitTestDao2] class provides the parent class [AbstractJUnitTestDao] with an instance of the [Dao2] class.
The [AbstractJUnitTestDao] class is a JUnit test class:
package spring.jdbc.tests;
import generic.jdbc.entities.dbproduits.Product;
import java.util.ArrayList;
import java.util.List;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.BeansException;
import spring.jdbc.dao.IDao;
import spring.jdbc.infrastructure.DaoException;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
public abstract class AbstractJUnitTestDao {
// [DAO] layer
abstract IDao getDao();
// JSON mapper
final static ObjectMapper jsonMapper = new ObjectMapper();
@Before
public void clean() {
// Clean the database before each test
log("Clearing the database", 1);
getDao().deleteAllProducts();
}
@Test
public void getProducts() throws JsonProcessingException {
...
}
@Test
public void getProductBy() {
...
}
@Test
public void doInsertsInTransaction() {
...
}
@Test
public void updateProducts() {
...
}
@Test
public void deleteProducts() {
....
}
@Test
public void perf1() {
...
}
@Test
public void perf2() {
...
}
@Test
public void perf3() {
....
}
// -------------- private methods
...
}
- line 19: the [AbstractJUnitTestDao] class is abstract;
- line 22: the abstract method [getDao], which provides a reference to the [DAO] layer to be tested. This method is implemented by the child classes;
- line 25: a JSON mapper that allows us to display the JSON value of products on the console;
- lines 27–32: before each test (line 27), the [PRODUCTS] table is cleared;
3.6.9.2. The private method [fill]
The private method [fill] is used to add products to the [PRODUCTS] table.
private List<Product> fill(int nbProducts) {
log("Filling the database", 1);
// create a list of products
List<Product> products = new ArrayList<Product>();
for (int i = 0; i < nbProducts; i++) {
int n = i + 1;
// int id, String name, int category, double price, String description
products.add(new Product(0, String.format("NAME%s", n), n / 5 + 1, 100 * (1 + (double) i / 100), String.format(
"DESC%s", n)));
}
// Store it in the database - retrieve products by their primary key
products = getDao().addProducts(products);
// create a dictionary of products to retrieve them more easily
// the dictionary key is the product's primary key in the database
for (Product product : products) {
mapProducts.put(product.getId(), product);
}
// Return the products
return products;
}
- line 1: the [fill] method inserts [nbProducts] into the [PRODUCTS] table, which is assumed to be empty;
- lines 3–10: creation of a list of products in the form:
new Product(0, String.format("NAME%s", n), n / 5 + 1, 100 * (1 + (double) i / 100), String.format("DESC%s", n)));
which uses the Product constructor(int id, String name, int category, double price, String description). The value of the first parameter [id] (primary key of the [PRODUCTS] table) is irrelevant since the [addProducts] method on line 10 does not insert it into the database and lets the DBMS generate its value;
- line 12: the list of products is persisted in the database. Each product in this list is assigned a new primary key [id]. The [addProduits] method returns its [produits] parameter. We could therefore have omitted retrieving the result;
- lines 15–17: the products are placed in a dictionary:
// product dictionary
private Map<Integer, Product> productMap = new HashMap<Integer, Product>();
The dictionary key is the product’s primary key, and the associated value is the product itself;
- line 19: we return the list of products;
3.6.9.3. The [getProducts] test
This is as follows:
@Test
public void getProducts() throws JsonProcessingException {
// initialization
fill(10);
// list of products
log("List of products", 2);
List<Product> products = getDao().getAllProducts();
display(products);
// Check that the retrieved list and the persisted list are the same
for (Product product : products) {
Product found = mapProducts.get(product.getId());
Assert.assertEquals(found, product);
mapProducts.remove(found.getId());
}
// All initial products should have been removed from the dictionary
Assert.assertEquals(0, mapProducts.size());
}
}
- line 4: 10 products are added to the database;
- line 7: once this is done, we request to view all products in the database;
- line 8: we display them. The goal is to verify that the products have been successfully saved and that they have a primary key;
- lines 10–13: we verify that the retrieved products are identical to those we persisted and that can be found in the dictionary [mapProduits];
- line 11: we retrieve from the dictionary the product with the same primary key as the one returned from the database. This shows that the persisted products have indeed been assigned a primary key;
- line 12: we ensure that the two products are identical. Recall that the [Product] class has defined an [equals] method (see section 3.3.4);
- line 13: the found element is removed from the dictionary;
- line 16: we verify that the dictionary of initial products is indeed empty, meaning that these initial products were all present in the list of products retrieved from the database;
The [display] method on line 8 is the following private method:
// display list of products
private <T> void display(List<T> elements) throws JsonProcessingException {
for (T element : elements) {
System.out.println(jsonMapper.writeValueAsString(element));
}
}
- line 2: The [display] method is a generic method. It is parameterized by a type T, denoted syntactically as <T>. If it were parameterized by two types T1 and T2, we would write <T1,T2>. The syntax of a method m parameterized by a type T is as follows:
scope <T> return_type m(... , T value1, ...){
...
T value2=...
}
In the code of method m, we will find data of type T. Method m of an instance c of class C can then be called as follows:
where T1 is the actual type that will replace the formal type T of method m. Most of the time, the compiler is able to infer the type T1 from the arguments of method m. Therefore, the previous statement will most often be simplified to:
Let’s return to the [display] method. It displays a list of elements of type T. This is possible because the JSON mapper used on line 4 is capable of rendering the JSON representation of any object type. In this specific example, the only type T used will be the [Product] type.
The [display] method could also have been written as follows:
// display list of products
private void display(Object o) throws JsonProcessingException {
System.out.println(jsonMapper.writeValueAsString(o));
}
Since the actual parameter is a list of products, line 3 would have printed the JSON representation of that list. This is not the same as printing the representation of each of its elements one by one.
The output produced by the [getProducts] test is as follows:
3.6.9.4. The [getProductBy] test
This is as follows:
@Test
public void getProductBy() {
// initialization
fill(10);
log("getProductBy", 1);
Product product = getDao().getProductByName("NAME3");
Product product2 = getDao().getProductById(product.getId());
Assert.assertNotNull(product2);
Assert.assertEquals(product2.getName(), product.getName());
Assert.assertEquals(product2.getId(), product.getId());
}
- line 6: the [getProductByName] method of the [IDao] interface is used to retrieve the product named [NAME3];
- line 7: the [getProductById] method of the [IDao] interface is then used to retrieve the same product, this time identified by its primary key;
- lines 8–10: we verify that [product2] and [product] have the same characteristics;
3.6.9.5. The [doInsertsInTransaction] test
This is as follows:
@Test
public void doInsertsInTransaction() {
log("Adding two products with the same name", 1);
// perform the insertion
List<Product> inserts = new ArrayList<Product>();
inserts.add(new Product(0, "x", 1, 1.0, ""));
inserts.add(new Product(0, "x", 1, 1.0, ""));
boolean error = false;
try {
getDao().addProducts(inserts);
} catch (DaoException daoException) {
error = true;
}
// checks
Assert.assertTrue(error);
List<Product> products = getDao().getAllProducts();
Assert.assertEquals(0, products.size());
}
- lines 5-7: we create a list of two products with the same name [x];
- line 10: these two products are inserted into the [PRODUCTS] table, which is empty (the [clean] method annotated with [@Before]). The first insertion will succeed, but not the second, because the [PRODUCTS] table has a unique constraint on product names. An exception must therefore occur. This is tested on line 15;
- because all methods of the [IDao] interface are executed within a transaction, the fact that the second insertion fails will roll back the entire transaction, including the first insertion. Ultimately, no insertions should be made to the [PRODUCTS] table;
- Lines 16–17: We verify this by retrieving the list of products in the [PRODUCTS] table and checking that this list is empty;
3.6.9.6. The [updateProducts] test
This is as follows:
@Test
public void updateProducts() {
// populate
fill(10);
log("Updating prices for products in category 1", 1);
// retrieve products
List<Product> products = getDao().getAllProducts();
// update those in category 1
List<Product> updated = new ArrayList<Product>();
int countUpdated = 0;
for (Product product : products) {
if (product.getCategory() == 1) {
// int id, String name, int category, double price, String description
updated
.add(new Product(product.getId(), product.getName(), 1, product.getPrice() * 1.1, product.getDescription()));
nbUpdated++;
}
}
int nbProducts = getDao().updateProducts(updated);
// Checks
// Assert.assertEquals(nbUpdated, nbProducts); -- fails with DB2
for (Product product : updated) {
Product product2 = getDao().getProductById(product.getId());
Assert.assertEquals(product2.getPrice(), product.getPrice(), 1e-6);
}
}
- Line 4: We insert 10 products into the database;
- line 7: retrieve them;
- lines 9–18: we increase the prices of products in category #1 by 10%;
- line 19: these changes are saved to the database;
- lines 22–25: we iterate through the list of products used for the update in memory. For each one, we look up the product with the same primary key in the database and verify that the price update was successful;
- line 19: retrieve the number of products updated by the [updateProducts] operation;
- line 21: we verify that this number is indeed the expected one. This test passes for all DBMSs except DB2. We have therefore commented it out;
3.6.9.7. The [deleteProducts] test
This test is as follows:
@Test
public void deleteProducts() {
// populate
fill(10);
log("deleteProducts", 1);
// list of products
List<Product> products = getDao().getAllProducts();
// delete two products
Product product0 = products.get(0);
Product product5 = products.get(5);
int nbDeleted = getDao().deleteProducts(new int[] { product0.getId(), product5.getId() });
// Checks
// Assert.assertEquals(2, nbDeleted); -- fails with DB2
Assert.assertNull(getDao().getProductById(product0.getId()));
Assert.assertNull(getDao().getProductById(product5.getId()));
Assert.assertEquals(products.size() - 2, getDao().getAllProducts().size());
}
- line 4: we insert 10 products into the database;
- lines 7–11: retrieve all products from the database and remove the products at positions 0 and 5;
- lines 14–16: we verify that the two products are no longer in the database and that the database now has two fewer products;
- The test in line 13 fails with the DB2 DBMS. It passes with the other DBMSs;
3.6.9.8. Performance Tests
We included three methods in the tests whose sole purpose is to evaluate the DBMS’s performance:
@Test
public void perf1() {
// fill
fill(10000);
}
@Test
public void perf2() {
// filling
fill(10000);
// modification
List<Product> products = getDao().getAllProducts();
// Update those in category 1
List<Product> updated = new ArrayList<Product>();
for (Product product : products) {
// int id, String name, int category, double price, String description
updated.add(new Product(product.getId(), product.getName(), 1, product.getPrice() * 1.1, product.getDescription()));
}
getDao().updateProducts(updated);
}
@Test
public void perf3() {
// populate
fill(10000);
// delete
List<Product> products = getDao().getAllProducts();
// primary keys
int[] keys = new int[products.size()];
for (int i = 0; i < keys.length; i++) {
keys[i] = products.get(i).getId();
}
getDao().deleteProducts(keys);
}
- lines 1–5: insertion of 10,000 products;
- lines 8–20: inserting 10,000 products and then modifying them using their primary keys;
- lines 23-34: inserting 10,000 products, then deleting them using their primary keys;
To run the [JUnitTestDao1] and [JUnitTestDao2] tests, the following test configurations can be used:
 |  |
The results of the [JUnitTestDao1] test are as follows:
 |
In [1] the results of [JUnitTestDao1] and in [2] those of [JUnitTestDao2]. There are no significant differences between them. In [1]:
- the test passes;
- inserting 10,000 products takes 3.15 seconds;
- inserting 10,000 products followed by their modification takes 4.80 seconds;
- inserting 10,000 products followed by their deletion takes 4.40 seconds;
- so the most expensive operation is insertion;