Exploring Spring JDBC

The Spring Data Access Object (DAO) support makes it easy to work with data access technologies such as JDBC, Hibernate, or JDO in a standardized way. Spring Framework provides APIs to reduce JDBC code duplication. Spring JDBC hides the low-level details and allows us to concentrate on business logic, which makes switching between databases easy and simple.

In a normal JDBC code, we catch a series of checked exceptions such as SQLException while acquiring a connection or executing a SQL statement; with Spring, we can code without worrying about catching exceptions, as Spring does the exception handling for us. Spring is not throwing away or eating the checked exceptions but is instead translating them to unchecked/runtime ones.

Spring provides a set of abstract DAO classes that one can extend; these abstract classes have methods to provide the data source and any other configuration settings that are specific to the technology one is currently using.

The following are the DAO support classes:

JdbcDaoSupport
HibernateDaoSupport
JdoDaoSupport
JpaDaoSupport

In normal JDBC code, we write the code in the following way to access the database:

Define the connection parameters.
Open the connection.
Specify the statement.
Prepare and execute the statement.
Set up the loop to iterate through the results (if any).
Do the work for each iteration.
Process any exception.
Handle transactions.
Close the connection.

Spring Framework relaxes the requirement to write numerous JDBC code lines. We need to write only the code to perform the following:

Specify the statement
Do the work for each iteration

Spring takes care of all the grungy, low-level details that can make JDBC such a tedious API to develop against.

The Spring-JDBC abstraction framework consists of four different packages:

org.springframework.jdbc.core
org.springframework.jdbc.datasource
org.springframework.jdbc.object
org.springframework.jdbc.support

The org.springframework.jdbc.core package contains the following:

The JdbcTemplate class
Various callback interfaces
A variety of related classes

The org.springframework.jdbc.datasource package contains the following classes:

A utility class for easy DataSource access
Various simple DataSource implementations that can be used to test and run unmodified JDBC code outside of a J2EE container
The utility class provides static methods to obtain connections from JNDI and to close connections if necessary
It has support for thread-bound connections, for example, to use with DataSourceTransactionManager

The org.springframework.jdbc.object package contains the following:

Classes that represent RDBMS queries, updates, and stored procedures as thread-safe, reusable objects
This approach is modeled by JDO, although of course, objects returned by queries are disconnected from the database
This higher level of JDBC abstraction depends on the lower-level abstraction in the org.springframework.jdbc.core package

The org.springframework.jdbc.support package contains the following:

The SQLException translation functionality and some utility classes
Exceptions thrown during JDBC processing are translated to exceptions defined in the org.springframework.dao package
The code using the Spring JDBC abstraction layer does not need to implement JDBC-or RDBMS-specific error handling
All translated exceptions are unchecked giving you the option of catching the exceptions that you can recover from while allowing other exceptions to be propagated to the caller

The JdbcTemplate class is the main class in the org.springframework.jdbc.core package. It simplifies the use of JDBC since it handles the creation and release of resources. This helps avoid common errors such as not closing the connection, and it executes the core JDBC workflow such as statement creation and execution leaving application code to provide SQL and extract results.

We'll build a phone book application and store phone numbers using Spring JDBC and normal JDBC and realize the simplicity and usability of Spring JDBC. We'll use the Apache Derby database for persistence. Derby can be downloaded from http://db.apache.org/derby/.

You can use better built-in databases such as H2. It has more features and less restriction than Derby. However, we're using Derby for simplicity.

The following are the steps to run Derby:

Download the binary media file and extract media to a location. We'll refer to it as DERBY_HOME in the next steps.
On a Windows machine, go to DERBY_HOME\bin and execute startNetworkServer.bat.
It will launch Command Prompt and print to the console that the database server is started, such as the following:
```
started and ready to accept connections on port 1527.
```

Download the latest version of the Spring JDBC JAR and its dependencies from http://maven.springframework.org/release/org/springframework/spring/.

Perform the following steps to implement Spring JDBC and simplify the code:

Launch Eclipse and create a Java project named DatabaseAccess.

Add a class PhoneEntry to store phone details. The following are the class details:

  package com.packt.database.model;

  public class PhoneEntry implements Serializable {

  private static final long serialVersionUID = 1L;

  private String phoneNumber;
  private String firstName;
  private String lastName;
  
  // getters and setters
  }

Create a data access interface for the phone book. The following are the API details:

  package com.packt.database.dao;

  import java.util.List;
  import com.packt.database.model.PhoneEntry;

  public interface PhoneBookDao {
    boolean create(PhoneEntry entry);

    boolean update(PhoneEntry entryToUpdate);

    List<PhoneEntry> searchByNumber(String number);

    List<PhoneEntry> searchByFirstName(String firstName);

    List<PhoneEntry> searchByLastName(String lastName);

    boolean delete(String number);
  }

Edit .classpath to add the following Spring dependencies:

Create a database access interface implementation to communicate with the database. The following are the data access object details:

  public class PhoneBookDerbyDao implements PhoneBookDao {

  private String driver = 
    "org.apache.derby.jdbc.EmbeddedDriver";
  private String protocol = "jdbc:derby:";
  private String userId = "dbo";
  private String dbName = "phoneBook";

  public PhoneBookDerbyDao() {
    loadDriver();
  }

  protected void loadDriver() {
    try {
      Class.forName(driver).newInstance();
    } catch (ClassNotFoundException cnfe) {
      cnfe.printStackTrace(System.err);
    } catch (InstantiationException ie) {
      ie.printStackTrace(System.err);
    } catch (IllegalAccessException iae) {
      iae.printStackTrace(System.err);
    }
  }

  protected Connection getConnection() throws SQLException {
    Connection conn = null;
    Properties props = new Properties();
    props.put("user", userId);
    conn = DriverManager.getConnection(protocol + dbName + 
      ";create=true",props);
    conn.setAutoCommit(false);
    return conn;
  }
}

Note that the PhoneBookDerbyDao class is a derby implementation of the DAO. It has configuration attributes such as driver, protocol, and dbName, and getters/setters. The loadDriver() method loads the database driver and gets invoked from the PhoneBookDerbyDao constructor. The getConnection() method connects to a Derby database and establishes a connection.

Implement the create behavior:

  @Override
  public boolean create(PhoneEntry entry) {
    PreparedStatement preparedStmt = null;
    Connection conn = null;
    try {
    conn = getConnection();
    preparedStmt = conn
     .prepareStatement("insert into PhoneBook values 
            (?,?,?)");

    preparedStmt.setString(1, entry.getPhoneNumber());
    preparedStmt.setString(2, entry.getFirstName());
    preparedStmt.setString(3, entry.getLastName());
    preparedStmt.executeUpdate();
    // Note that it can cause problems on some dbs if 
    //autocommit mode is on
    conn.commit();
      return true;
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {

      if (preparedStmt != null) {
        try {
          preparedStmt.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }

      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          }
      }
    }

    return false;
  }

The create method first acquires a database connection and creates a prepared statement from connection; it then populates the prepared statement with the PhoneEntry values, executes the prepared statement, and then commits the connection. The finally block closes the resources, which closes the prepared statement and the connection.

Create a class named PhoneBookDerbySpringDao that implements the PhoneBookDao interface. The following is the Spring implementation of the create method:
```
  public class PhoneBookDerbySpringDao  implements
     PhoneBookDao {

  private final JdbcTemplate jdbcTemplate;
  
    public PhoneBookDerbySpringDao(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public boolean create(PhoneEntry entry) {
      int rowCount = jdbcTemplate.update("insert into 
                  PhoneBook values (?,?,?)",
                  new Object[]{entry.getPhoneNumber(), 
                  entry.getFirstName(),
                  entry.getLastName()
        });
      return rowCount == 1;
    }
  }
```
The JdbcTemplate class simplifies the use of JDBC; it handles the resources and helps avoid common errors such as not closing the connection. It creates and populates the statement object, iterates through ResultSet, leaving the application code to provide SQL and extract results. PhoneBookDerbySpringDao contains a JdbcTemplate instance and delegates the database tasks to jdbcTemplate. JdbcTemplate uses data source definition from the applicationContext file.
JdbcTemplate has an update method for the insert and update operations. It takes a SQL query and parameters. The new Spring version of the create() method invokes the update() method on jdbcTemplate and passes the PhoneEntry details. Now the create method looks simple; it is just two lines of code. Spring Framework handles the resource life cycle.
Look at the Spring DAO class; it has only 54 lines. The class looks neat, simple, and readable. It doesn't handle resources; rather, it concentrates on data access.

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Mockito for Spring

By : Acharya

Mockito for Spring

By: Acharya

Overview of this book

Exploring Spring JDBC

Mockito for Spring

By : Acharya

Mockito for Spring

By: Acharya

Overview of this book

Exploring Spring JDBC

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access