Chapter 1. Basics

Persistence of data is a challenging task for developers. There are many things that could go wrong. The introduction of JDBC has given the developer community a bit of joy by taking away painstakingly cumbersome database access in Java applications. However, there are a few wrinkles that come with JDBC, such as having to write boilerplate code, finding out a clue from the SQLExcetion stacktrace, resource management, and so on.

Spring has gone further in simplifying the data access by providing a simple and straightforward framework. This chapter discusses Spring’s take on JDBC, and how Spring simplified the JDBC programming model; it did so by employing simple yet powerful mechanisms, such as Dependency Injection, Templates, and other patterns.

Using Plain JDBC

With the advent of JDBC, accessing data from a Java application has become relatively easy. Not only do we have independence from database vendor lock-in, but we also have a standard API to access multitude of databases.

However, the steps involved in using a JDBC are always the same—obtain a connection, create a Statement, execute a query, run it through ResultSet, and release the resources.

The following code demonstrates a simple example of selecting the TRADES data using plain JDBC:

public class JdbcPlainTest {

  private String DB_URL="jdbc:mysql://localhost:3306/JSDATA";
  private final String USER_NAME = "XXXX";
  private final String PASSWORD = "XXXX";

  private Connection createConnection() {
    Connection conn = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = 
         DriverManager.getConnection(DB_URL, USER_NAME,PASSWORD);

    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return conn;
  }

  private void query() {
    ResultSet rs = null;
    Statement stmt = null;
    Connection conn = createConnection();

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT * FROM TRADES");

      while (rs.next())
        System.out.println(rs.getString(1));
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        try {
          rs.close();
          stmt.close();
          conn.close();
        } catch (SQLException ex) {
          e.printStackTrace();
        }
     }
  }

  public static void main(String args[]) {
    JdbcPlainTest t = new JdbcPlainTest();
    t.query();
  }
}

Phew! That’s a lot of code for a simple task! Did you notice the code around exceptions?

There are a few things that you could have noticed from the above example:

The resource management (creating and closing connections and statements) is a repetitious process.
The SQLException must be caught in both the creation and destruction processes.
The actual business logic is not more than a couple of lines; unfortunately, code is cluttered with lot of JDBC API statements and calls.

We can create a home-grown framework with callbacks and handlers to resolve these issues. Although it does work, creating your own framework leads to several issues—maintenance, extending to suit newer requirements, extensive testing, and others.

If there’s already a framework that does this work, why reinvent the wheel?

The Spring data access framework is specifically created to address these problems. It is a beautiful framework that promotes Dependency Injection principles and carries multiple features.

Spring Data Access

The Spring data access framework has made the developer’s job very easy!

It creates a rich framework in which, or from which to access databases by decoupling our code from the access mechanisms. As always, the framework heavily uses Dependency Injection patterns, so decoupling of our code really comes to life. The components using framework’s API are easily testable, too. Moreover, there’s no exceptions that we should have to catch when using the APIs!

The access logic revolves around Template patterns and Support classes. These patterns hide away all the boilerplate code and allows the developer to concentrate solely on business logic.

Templates

From the previous example, we can see that there is a lot of code that’s not central to business function. It would be ideal to wrap up the non-critical code away from our business code in a separate class. Spring’s JdbcTemplate class does exactly that.

This class wraps up all the access logic so users only need to concentrate on the heart of the application. If you understand the workings of JdbcTemplate, I would say you’ve conquered most of Spring’s data access workings.

In addition to the standard JdbcTemplate, there are two other variations of the Template class: SimpleJdbcTemplate and NamedParameterJdbcTemplate. These two varieties are nothing but wrappers around JdbcTemplate that are used for special cases. We will discuss all of these in the coming sections. Before we work out examples, we have to carry out some prerequisites such as creating a database schema and prepopulating test data.

If you already have a database in place, you can skip this section without any concern.

MySQL Database Scripts

I am using MySQL as the database for all of the examples provided in this book. Setting up the database is easy if you follow the instructions from the provider carefully.

Once you have MySQL set up, make sure you run the SQL scripts provided by the book’s source code. These scripts will create a database called JSDATA and create necessary tables such as ACCOUNTS, TRADES, PRICES, and others. If you are working with some other database, you should be able to run the scripts without any issues; personally, I have not tested them.

The next important thing is to create a DataSource. The DataSource encapsulates the database provider information and hence acts like a connection factory by fetching connections to talk to the database. It should be created by driver information such as URL, username, password, and other information. Make sure that you supply the necessary provider (driver) information to construct a DataSource if you are using any other databases.

The datasource-beans.xml file shown below creates a DataSource for MySQL database:

<bean id="mySqlDataSource" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close">
  <property name="driverClassName" value="com.mysql.jdbc.Driver" />
  <property name="url" value="jdbc:mysql://localhost:3306/JSDATA" />
   ....
</bean>

The class attribute points to an implementor of the DataSource interface; in the above snippet, it is a BasicDataSource class from Apache Common’s DBCP project. The driverClassName points to a class that will be specific to a database.

We will see the full definition in a minute.

Throughout the book, we will use DBCP datasource, which can be downloaded from the site: http://commons.apache.org/dbcp/. If you are using Maven, add the snippet to your pom.xml file (check out the full pom.xml provided with the book’s source code) to include DBCP and MySQL connector jars:

<!-- pom.xml -->
<dependency>
  <groupId>commons-dbcp</groupId>
  <artifactId>commons-dbcp</artifactId>
  <version>1.4</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.18</version>
</dependency>

Using JdbcTemplate

The JdbcTemplate is a framework class utilized for data access operations such as queries, inserts, and deletes. It is the fundamental class in the framework, so we dwell on it in detail here.

Note that the JdbcTemplate is a thread safe class—it can be easily shared across your threads. One of the biggest advantages in using the JdbcTemplate is its ability to clean up resources. Most developers forget to close the JDBC connections and other related resources, which leads to lots of issues. JdbcTemplate comes to our rescue in doing the house cleaning job for us!

Before we work with JdbcTemplate, we must set the DataSource first. This is a mandatory requirement that JdbcTemplate be configured with a DataSource object so the template will be able to create connections and statements behind the scenes.

Configuring a DataSource

As we have already seen, the javax.sql.DataSource is an interface that determines the connection details for a particular provider. Each provider will have their own implementation of the class, usually provided in a Jar file. The MySQL driver class is defined by the com.mysql.jdbc.Driver class, for example.

The following configuration shows how to set up a data source for MySQL:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-3.0.xsd">

  <!-- MySql DataSource -->

  <bean id="mySqlDataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/JSDATA"/>
        <property name="username" value="jsuser"/>
        <property name="password" value="jsuser"/>
  </bean>

</beans>

The above snippet will create a bean named mySqlDataSource that points to a MySQL database running on localhost, directed by the url property. If we are using other providers, we need to create another bean with the same properties, but with appropriate values relevant to our provider.

Configuring JdbcTemplate

Now that the data source has been configured, the next step is to create and work with the JdbcTemplate class.

There are couple of ways to create this class. One is to create an instance in your class and provide a preconfigured data source, and the other is to create and instantiate the bean in the configuration file and inject it into your Data Access Object (DAO) classes. The DAOs are the classes that talk to databases in order to fulfill the data access functions.

Let’s see an example of instantiating JdbcTemplate with a preconfigured DataSource.

public class JdbcTemplateTest {
  private ApplicationContext ctx = null;
  private JdbcTemplate template = null;
  private DataSource datasource = null;

  public JdbcTemplateTest() {
    // Create a container forming the beans from datasource XML file
    ctx = new ClassPathXmlApplicationContext("datasources-beans.xml");

    // DataSource bean has been fetched from the container
    datasource = ctx.getBean("mySqlDataSource",DataSource.class);

    // Instantiate the template with the datasource
    template = new JdbcTemplate(datasource);
  }

  public static void main(String[] args) {
    JdbcTemplateTest t = new JdbcTemplateTest();

    // execute the data access methods from here
    ....
  }
}

The steps are simple:

Load and fetch the context from a config file that consists of datasources (in our case, it’s the datasouces-beans.xml)
Create the JdbcTemplate using the new operator providing the datasource bean to its constructor

Once you have the JdbcTemplate fully configured and functional, you are ready to use it to access our databast tables. The JdbcTemplate has a lot of functionality that requires a bit of detail study.

Working with JdbcTemplate

The JdbcTemplate has more than 100 methods that give varied access to data sets!

For example, you may wish to execute straight queries such as inserting data or creating tables. You can use the execute() method exposed on the JdbcTemplate for such actions.

Likewise, if you wish to query for single or multiple data rows, you should be using queryForXXX methods. There are lots of other methods, some of them are self explanatory and others are easy to follow using JavaDoc. We will cover the most important of all of these methods in the coming sections.

Querying for Single and Multiple Rows

Let’s say our requirement is to find out the number of rows present in the TRADES table.

The following snippet shows the usage of JdbcTemplate in its simplest form—for fetching the number of TRADES in the table:

public int getTradesCount(){

  int numOfTrades = 
    template.queryForInt("select count(*) from TRADES");

  return numOfTrades;
}

The queryForInt() method returns the count(*) equivalent from the table. The return type is obviously an integer. There are few variants of queryForXXX methods such as queryForString, queryForLong, queryForMap, and queryForObject. Basically, these are facility methods that convert your column value to an appropriate data type.

You can also rewrite the above example by using the more generic queryForObject method. However, the method takes a second parameter, which basically describes the return value’s data type. In our example, because count(*) will return an integer, we pass the Integer class to the method call.

This is illustrated below:

public int getTradesCount(){
  int numOfTrades = 
    template.queryForObject("select count(*) from TRADES",Integer.class);
  return numOfTrades;
}

// Another example of get the max id of the 
// trade using queryForObject method

public int getTradeMaxId(){
  int maxId = 
    template.queryForObject("select max(id) from TRADES", Integer.class);
  return maxId;
}

The above snippet also provides another example of using the queryForObject method to query for a Trade that has a maximum id. The queryForLong and queryForString follow the same pattern, returning a Long and String value, respectively.

The queryForMap returns a single row in a Map<String,Object> format as shown below:

public Map<String,Object> getTradeAsMap(){

  // note that we have hardcoded ID here!
  Map<String,Object> tradeAsMap = 
    template.queryForMap("select * from TRADES where id=1");

  System.out.println("Trades Map:"+tradeAsMap);

  return tradeAsMap;
}

//The output to the console is:

Trades Map:{ID=1, ACCOUNT=1234AAA, SECURITY=MDMD, 
    QUANTITY=100000, STATUS=NEW, DIRECTION=BUY}

As you can see, each column name is the key represented by String while the value is represented by the Object in the Map<String,Object> declaration.

However, the queryForList is a bit different to others in that it can return multiple rows. The rows are returned as a List of Map<String,Object> format.

Let’s see this at work. The getAllTrades() method fetches all of the trades and prints out to the console:

public List<Map<String,Object>> getAllTrades(){

  List<Map<String,Object>> trades = 
    template.queryForList("select * from TRADES");
  
  System.out.println("All Trades:"+trades);
  
  return trades;
}

//Prints to console as:

All Trades:
[{ID=1, ACCOUNT=1234AAA, ... STATUS=NEW, DIRECTION=BUY}, 
 ..., 
{ID=5, ACCOUNT=452SEVE, ... STATUS=NEW, DIRECTION=SELL}]

The queries that we used in the above examples are fairly simple. We can also write complex queries that can be executed in the same fashion. We often use where clauses and other SQL constructs to execute complex queries. However, the where clause requires input variables to be set. How can we parameterize these bind variables?

Bind Variables

Bind variables help to create a dynamic SQL query. If our requirement is to fetch records based on various conditions, we usually use the where clause in our SQL script. Bind variables are the preferred option as opposed to using inline variables because they protect our application against SQL injection attacks.

For example, if we have to get the STATUS of a Trade whose id is 5, we need to write the SQL as follows:

public String getTradeStatus(int id){
  
  String status = 
    template.queryForObject("select STATUS from TRADES where id= ?", 
    new Object[]{id}, String.class);
  
  return status;
}

The ? will be an indication to the framework to substitute the value with the second parameter of the method, which in the above case is the id. The way to do this is to create an array of Object with your incoming id value. The third parameter is the type of value the method query is expected to return; in this case, the STATUS is a String type.

We can provide more than a one bind variable, no restriction on the number.

In the following snippet, the overloaded getTradeStatus() method has two conditions in the where clause and accordingly, we provide a second value via a second parameter, Object array:

public String getTradeStatus(int id, String security){

  String status = 
    template.queryForObject("select STATUS from TRADES where id = ? and security=?", 
    new Object[]{id,security}, String.class);

  return status;
}

Mapping Rows to Domain Objects

We know that each row in the TRADES table is represented by our Trade domain object. Although we have seen fetching the Trades from the table, we have not yet seen how we create a Trade object from each row of the record.

In order to do this, we need to use a RowMapper callback provided by the framework. The RowMapper interface has one method—mapRow—where you need to map the incoming row to the domain object. You can create the RowMapper as an anonymous class or you can have your own class implementing the RowMapper interface separately.

Let’s take a look at each one separately.

First, we create a TradeMapper class that implements the RowMapper interface and defines its single method:

private static final class TradeMapper implements RowMapper<Trade>{
  @Override
  public Trade mapRow(ResultSet rs, int rowNum) throws SQLException {
    Trade t = new Trade();
    // set the values by use ResultSet's getXXX methods
    t.setId(rs.getInt("ID"));
    ....
    
    return t;
  }
}

In the mapRow method, a ResultSet instance for the current row is given to us via this callback. What we are doing is extracting the column data from the ResultSet object and setting the values against our newly instantiated domain object Trade. The method then retuns the fully initialized Trade object.

As we now have our RowMapper implementation ready, we give it to the overloaded queryForObject method to retrieve all the trades from the table:

public Trade getMappedTrade(int id){

  Trade trade = template.queryForObject("select * from TRADES where id = ?",
    new Object[]{id} , 
    new TradeMapper());

  return trade;

}

Did you notice the third argument to the method? It’s taking our TradeMapper class, which creates the Trade with the column values extracted from the ResultSet. The good thing about this callback class is that we can use it anywhere that a method expects a RowMapper to convert the column data to Trade object.

There’s an alternative way of using RowMapper—we can also use an anonymous class to create a RowMapper instead of creating a separate instance as we have seen above. The way to do so is illustrated below:

public Trade getTrade(int id){
  Trade trade = template.queryForObject("select * from TRADES where id= ?", 
  new Object[]{id}, 
  new RowMapper<Trade>(){
    @Override
    public Trade mapRow(ResultSet rs, int row) throws SQLException {
        Trade t = new Trade();
        t.setId(rs.getInt("ID"));
        t.setAccount(rs.getString("ACCOUNT"));
        ....
        t.setDirection(rs.getString("DIRECTION"));
      return t;
      }
  });
  return trade;
}

The RowMapper that is instantiated inline as an anonymous class does exactly the same thing that we saw earlier.

Note that second argument in the mapRow corresponds to the row number of the record given to the callback. Also, keep in mind that the ResultSet given to your callback has only one record. Any use of ResultSet.next() will throw a SQLException.

Creating the RowMapper class anonymously has a limited scope—it can’t be used anywhere else in the application. Unless you have a strong case to use the anonymous class, go with a separate class like TradeMapper and reuse it. Reusability scores good marks!

Note that both the JdbcTemplate and RowMapper classes are thread safe. You can share them and use them across threads without having to worry about state corruption.

Fetching List of Trades

Now that we know how to fetch a single record and map to a domain object, let’s see how to get the list of all rows mapped to domain objects. Actually, it is quite straight forward now that you have a RowMapper class already designed.

The following snippet is used to fetch such a list. Note that the only change was using query() method rather than queryForXXX method:

public List<Trade> getAllMappedTrades(){
  
  List<Trade> trades = 
    template.query("select * from TRADES", new TradeMapper());

  return trades;
}

For each row fetched, a respective Trade object will be formed by the TradeMapper and then added to the list—simple!

Now that you’ve seen various query mechanisms, let’s look at the update and delete workings, too.

Inserting, Deleting, and Updating Rows

We also use the JdbcTemplate to do the updates. We use JdbcTemplate.update() variants to execute the appropriate statements. The following snippet shows inserting a Trade into TRADES table:

private void insertTrade() {
  
  int rowsUpdated = 
    template.update("insert into TRADES values(?,?,?,?,?,?)", 
     61,"JSDATA","REV",500000,"NEW","SELL");
  
  System.out.println("Rows Updated:"+rowsUpdated);
}

The return value indicates the rows affected. Note that we use bind variables in the above query.

Similarly, use the same update method to update the values of the rows. The following example shows how to update the status of an existing Trade:

private void updateTrade(String status, int id) {
  
  int rowsUpdated = 
    template.update("update TRADES set status='"+status+"' where id="+id+"");
  
  System.out.println("Rows Updated:"+rowsUpdated);
}

The above statement looks a little bit ugly with all the String concatenation. Is there any other way of doing this job?

There is a way. You can use the another variant of the update method that takes varargs:

private void updateTrade(String status, int id) {
  int rowsUpdated = 
    template.update("update TRADES set status=? where id=?",status, id);
  System.out.println("Rows Updated:"+rowsUpdated);
}

There’s another overloaded method that sets the bind variables using an Object array (which we have already seen in our query examples earlier) and java.sql.Types array. The types array will provide the necessary framework tools to typecast the variables.

In the following updateTradeUsingTypes method, we are using the types array to let the framework know the bind values type. However, as the status and id are already known types, perhaps using the types array might not be needed except for the compiler’s sake.

private void updateTradeUsingTypes(String status, int id) {
 
  int rowsUpdated = template.update(
   "update TRADES set status=? where id=?", 
    new Object[] { status, id }, 
    new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER });

  System.out.println("Rows Updated:" + rowsUpdated);
}

However, see the updated snippet below where using SQL types comes necessary—we pass in all the arguments as String objects.

private void updateTradeUsingTypes() {
  
  int rowsUpdated = template.update(
    "update TRADES set status=? where id=?", 
    new Object[] { "UNKNOWN","6" }, 
    new int[] { java.sql.Types.VARCHAR,java.sql.Types.INTEGER });
  
  System.out.println("Rows Updated:" + rowsUpdated);
}

You can also invoke a Stored Procedure using the update method, as shown here:

private void replayTradesUsingSP(List tradeIds) {
  
  template.update(
    "call JSDATA.REPLAY_TRADES_SP (?)", tradeIds);
}

The REPLAY_TRADES_SP stored procedure picks up all the trades identified by the tradeIds list and replay them.

Executing Statements

The JdbcTemplate exposes execute methods so you can run Data Definition Language (DDL) statements easily:

public void createAndDropPersonTable(){
  template.execute("create table PERSON 
   (FIRST_NAME varchar(50) not null, LAST_NAME varchar(50) not null)");
  
  // drop the table
  template.execute("drop table PERSON");
  
  System.out.println("Table dropped");
}

Summary

In this chapter, we began the problem statement by discussing the wrinkles around Java database programming using standard JDBC APIs. We identified the boiler plate code around the usage in relation to the resource management and jumped to see what Spring’s framework has done to address them. The Spring’s framework has wrapped up the unnecessary boilerplate code into templates. We have seen the fundamental class of the framework—JdbcTemplate—in action. We learned how to utilize the class using simple examples.

The next chapter will discuss the additional templates along with advanced Spring JDBC usage using Support classes and callbacks.

Get Just Spring Data Access now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Just Spring Data Access by Madhusudhan Konda