In this tutorial, we will learn to handle JDBC related models and transaction management in Spring framework. We will also learn about the Spring Web MVC framework.

As already mentioned in our previous articles of the series, Spring framework is an open source Java platform that provides MVC infrastructure support for developing robust Java applications very easily and very rapidly using recommended MVC pattern.

JDBC Framework

JDBC code can be cumbersome and hard to manage in any Java project due to repeated code to handle exceptions, managing database connections etc. But Spring JDBC Framework takes care of all the low-level details starting from opening the connection, preparing and executing the SQL statement, processing exceptions, handling transactions and finally closing the connection.

JdbcTemplate class

The JdbcTemplate class processes SQL queries, updates statements and stored procedure calls, performs iteration over ResultSets and extracts returned parameter values. It also handles JDBC exceptions and translates them to a generic, more informative exception hierarchy defined in the org.springframework.dao package.

Instances of the JdbcTemplate class are thread safe once configured. So we can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs.

A common practice when using the JdbcTemplate class is to configure a DataSource in our Spring configuration file, and then dependency-inject that shared DataSource bean into our DAO classes, and the JdbcTemplate is created in the setter for the DataSource.

Data Source Configuration

To start, we create a database table Student in our database TEST. We are using MySQL database.

CREATE TABLE Student(
  ID   INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  AGE  INT NOT NULL,
  PRIMARY KEY (ID)
);

Now we need to supply a DataSource to the JdbcTemplate so it can configure itself to get database access. You can configure the DataSource in the XML file with a bit of code as shown below:

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  <property name="url" value="jdbc:mysql://localhost:3306/TEST"/>
  <property name="username" value="root"/>
  <property name="password" value="password"/>
</bean>

Data Access Object (DAO)

DAO is commonly used to talk to a database. DAOs exist to provide a means to read and write data to the database and they should expose this functionality through an interface by which the rest of the application will access them. Using OOPs!

The Data Access Object (DAO) support in Spring makes it easy to work with data access technologies like JDBC, Hibernate, JPA or JDO in a consistent way.

Sample Queries

Let us perform sample CRUD applications.

• Let’s run a simple Count query,

String query = "select count(*) from Student";
int rowCount = jdbcTemplateObject.queryForInt(query);

• Now, let’s use a bind variable:

String SQL = "select age from Student where id = ?";
int age = jdbcTemplateObject.queryForInt(SQL, new Object[]{10});

• Let’s query for a Java Object now.

String SQL = "select * from Student where id = ?";
Student student = jdbcTemplateObject.queryForObject(SQL, 
                 new Object[]{10}, new StudentMapper());

public class StudentMapper implements RowMapper<Student> {
  public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
     Student student = new Student();
     student.setID(rs.getInt("id"));
     student.setName(rs.getString("name"));
     student.setAge(rs.getInt("age"));
     return student;
  }
}

• We can read multiple objects now.

String SQL = "select * from Student";
List<Student> students = jdbcTemplateObject.query(SQL,
                        new StudentMapper());

public class StudentMapper implements RowMapper<Student> {
  public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
     Student student = new Student();
     student.setID(rs.getInt("id"));
     student.setName(rs.getString("name"));
     student.setAge(rs.getInt("age"));
     return student;
  }
}

• Insert a row.

String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Liran", 21} );

• Updating a row into the table:

String SQL = "update Student set name = ? where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{"Liran", 20} );

• Deleting a row from table:

String SQL = "delete Student where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{20} );

Transaction Management

A transaction is defined as a sequence of steps that are treated as a single unit of work. This work should either be completed as a whole or nothing of it should be completed at all.

Transaction management is an important concept of RDBMS oriented enterprise applications to ensure data integrity and consistency. The properties of the transactions can be shown with following 4 key properties described as ACID:

• Atomicity: A transaction should be treated as a single unit of operation which means either the entire sequence of operations is successful or failed.
• Consistency: This indicates the consistency of the referential integrity of the database, unique primary keys in tables etc.
• Isolation: There may be many transactions processing with the same data set at the same instant, each transaction should be isolated from others to prevent data corruption.
• Durability: Once a transaction has finished, the results of this transaction are permanent and cannot be erased from the database due to system failure.

The TransactionDefinition is the core interface of the transaction support in Spring and it is defined below:

public interface TransactionDefinition {
  int getPropagationBehavior();
  int getIsolationLevel();
  String getName();
  int getTimeout();
  boolean isReadOnly();
}

Programmatic vs Declarative

Spring supports two types of transaction management:

• Programmatic transaction management: This means that you have to manage the transaction with the help of programming. That gives you extreme flexibility, but it is difficult to maintain.
• Declarative transaction management: This means you separate transaction management from the business code. You only use annotations or XML based configurations to manage the transactions.

Introducing Spring Web MVC

The Spring web MVC framework provides model-view-controller architecture and ready components that can be used to develop flexible and loosely coupled web applications. The MVC pattern results in separating the different aspects of the application (input logic, business logic, and UI logic), while providing a loose coupling between these elements.

• The Model encapsulates the application data and in general will consist of POJO.
• The View is responsible for rendering the model data and in general it generates HTML output that the client's browser can interpret.
• The Controller is responsible for processing user requests and building the appropriate model and then passing it to the view for rendering.

Request Response Management

Let’s understand how the request is handled in Spring by a DispatcherServlet and response is sent back to the client.

1. The first request will be received by DispatcherServlet.
2. DispatcherServlet will take the help of HandlerMapping and learn the @Controller class name associated with the given request.
3. So the request is transferred to the @Controller, which in turn processes the request by executing appropriate methods and returns a ModelAndView object (contains Model data and View name) back to the DispatcherServlet
4. Now DispatcherServlet sends the model object to the ViewResolver to get the actual view page.
5. Finally DispatcherServlet will pass the Model object to the View page to display the result.

Conclusion

In this quick tutorial, we went over the basics of dealing with JDBC and transactions in Spring by providing sample queries to perform basic create, read, update and delete operations in JDBC. Finally, we had a look at the Spring Web MVC framework and how a request travels across to get the response object back.