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
classVarious callback interfaces
A variety of related classes
The
org.springframework.jdbc.datasource
package contains the following classes:
A utility class for easy
DataSource
accessVarious simple
DataSource
implementations that can be used to test and run unmodified JDBC code outside of a J2EE containerThe 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 classesExceptions thrown during JDBC processing are translated to exceptions defined in the
org.springframework.dao
packageThe 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 executestartNetworkServer.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 aderby
implementation of the DAO. It has configuration attributes such asdriver
,protocol
, anddbName
, and getters/setters. TheloadDriver()
method loads the database driver and gets invoked from thePhoneBookDerbyDao
constructor. ThegetConnection()
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 fromconnection
; it then populates the prepared statement with thePhoneEntry
values, executes the prepared statement, and then commits the connection. Thefinally
block closes the resources, which closes the prepared statement and the connection.Create a class named
PhoneBookDerbySpringDao
that implements thePhoneBookDao
interface. The following is the Spring implementation of thecreate
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 throughResultSet
, leaving the application code to provide SQL and extract results.PhoneBookDerbySpringDao
contains aJdbcTemplate
instance and delegates the database tasks tojdbcTemplate
.JdbcTemplate
uses data source definition from theapplicationContext
file.JdbcTemplate
has anupdate
method for the insert and update operations. It takes a SQL query and parameters. The new Spring version of thecreate()
method invokes theupdate()
method onjdbcTemplate
and passes thePhoneEntry
details. Now thecreate
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.