How it works...

The DAO layer serves as the data persistency layer of the MVC application. All objects in this layer must have @Repository applied since these objects are injected into the container with a special translator and exception, such as DataAccessException:

After designing and constructing the database and tables schemas, Spring must ready its data model objects, which are normally POJO, to hold property values during CRUD transactions. To utilize these models, Spring has a built-in Spring JDBC module which is the ultimate provider of APIs for data access templates actions and callback handlers.

This recipe introduces the multithreaded SimpleJdbcInsert to simplify the configuration details needed in saving objects to the database. The rule is just to provide the table name and a map containing the column names and the column values, to be inserted without using too many placeholders (?) for mapping. The only problems is that you can only set its table name once before every execute(), otherwise, this exception will be thrown:

In the background, this API still uses the low-level JdbcTemplate for its SQL executions; thus, the performance of SimpleJdbcInsert must be almost the same as JdbcTemplate when it comes to single-table record transactions. SimpleJdbcInsert can easily be fetched from the container while JdbcTemplate can only be derived from SimpleJdbcInsert through its factory method, or as an injected @Bean to be @Autowired by the DAO classes.

JdbcTemplate and SimpleJdbcInsert will not work without the implementation of the java.sql.DataSource. There are five implementations featured in this recipe which are popularly used nowadays, but each has its own disadvantages and advantages. The DriverManagerDataSource has no database connection pooling, so every user access corresponds to one instance of the Connection object. Connection pooling is very important in an enterprise application because it gives a threshold whenever there is connection traffic. It provides a set of logical connection objects that can be used to virtualize the user connection instead of creating another set and separate instances of Connection objects. Deciding on the appropriate DataSource implementation can help the MVC application minimize the overhead caused by the DAO layer at runtime.

To successfully implement the JDBC connection to our MySQL 5.7 server, the MySQL connector class named com.mysql.jdbc.Driver needs to be part of the Maven dependencies of the project. This class will communicate with the DataSource and verifies the username, password, and URL details of the database instance in order to successfully establish a connection.

After implementing all the required DAO classes, inject them into SpringDbConfig through the @Repository annotation to be @Autowired by services.