In this tutorial, we will cover:

• JDBC data source configuration
• JDBC templates
• Integrating Hibernate with Spring
• Spring and JPA
• JNDI data sources
• Automatic JPA repositories with Spring data

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.

Spring in the Backend

With the core of Spring container now under your belt, it’s time to dive into some real world applications. You have probably dealt with database access in an application in the past. In practice, you’ll know that data access has many pitfalls. You have to initialize your data-access framework, open connections, handle various exceptions, and close connections. If you get any of this wrong, you could potentially corrupt or delete valuable company data.

Spring Data access philosophy

Spring ensures that the data-access layer in our project is not scattered and so, we can keep data-access logic in one component, commonly known as the Data Access objects (DAO) or repositories.

To avoid binding the application to a specific data-access strategy, properly written repositories should expose their functionality through interfaces. In this tutorial, we will go through setting up JDBC, followed by Hibernate and Data JPA in Spring to access data.

Configure a Data Source

Spring offers several options for configuring data-source beans in your Spring application, including these:  

• Data sources that are defined by a JDBC driver  
• Data sources that are looked up by JNDI  
• Data sources that pool connections

For production-ready applications, I recommend using a data source that draws its connections from a connection pool.

Using pooled data source

Let’s configure a pooled data source directly in Spring. Although Spring doesn’t provide a pooled data source, plenty of suitable ones are available, including the following open source options:  

• Apache Commons DBCP (http://jakarta.apache.org/commons/dbcp)  
• c3p0 (http://sourceforge.net/projects/c3p0/)  
• BoneCP (http://jolbox.com/)

Most of these connection pools can be configured as a data source in Spring in a way that resembles Spring’s own DriverManagerDataSource or SingleConnectionDataSource (which we’ll talk about next). For example, here’s how you might configure DBCP’s BasicDataSource:

@Bean

public BasicDataSource dataSource() {

BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName("org.h2.Driver");   ds.setUrl("jdbc:h2:tcp://localhost/~/spitter");

ds.setUsername("sa");

ds.setPassword("");

ds.setInitialSize(5);

ds.setMaxActive(10);

return ds;

}

The properties listed above are self explanatory except for the setMaxActive which tells the maximum number of connections that can be allocated from the pool at the same time. If 0, there’s no limit.

Handling JDBC code

To get started straightaway, let’s look at the code, using JDBC, to insert a user into the table.

private static final String SQL_INSERT_USER =

      "insert into user (username, password, fullname) values (?, ?, ?)";

 

private DataSource dataSource;

 

public void addUser(User user) {

  Connection conn = null;

  PreparedStatement stmt = null;

  try {

      conn = dataSource.getConnection();

      stmt = conn.prepareStatement(SQL_INSERT_USER);

      stmt.setString(1, user.getUsername());

      stmt.setString(2, user.getPassword());

      stmt.setString(3, user.getFullName());

      stmt.execute();

  } catch (SQLException e) {

  // do something...not sure what, though

  } finally {

      try {

          if (stmt != null) {

              stmt.close();

          }

          if (conn != null) {

              conn.close();

          }

      } catch (SQLException e) {

      // I'm even less sure about what to do here

      }

  }

}

That is surprisingly weird actually. 20 lines of code just to insert a user into the database. Now, let’s look at code to update a row in the database:

private static final String SQL_UPDATE_USER =

      "update spitter set username = ?, password = ?, fullname = ?"

              + "where id = ?";

public void saveSpitter(User user) {

  Connection conn = null;

  PreparedStatement stmt = null;

  try {

      conn = dataSource.getConnection();

      stmt = conn.prepareStatement(SQL_UPDATE_USER)

      stmt.setString(1, user.getUsername());

      stmt.setString(2, user.getPassword());

      stmt.setString(3, user.getFullName());

      stmt.setLong(4, user.getId());

      stmt.execute();

  } catch (SQLException e) {

  // Still not sure what I'm supposed to do here

  } finally {

      try {

          if (stmt != null) {

              stmt.close();

          }

          if (conn != null) {

              conn.close();

          }

      } catch (SQLException e) {

      // or here

      }

  }

}

I think that’s enough to understand what a pain JDBC can be. Let’s jump straight to using JdbcTemplate.

Spring’s JDBC framework will remove most of the above boilerplate code. Let’s define a bean for the data source and get started:

@Bean

public JdbcTemplate jdbcTemplate(DataSource dataSource) {

  return new JdbcTemplate(dataSource);

}

Here, the DataSource is injected via constructor injection. Let’s define a Repository now to perform user related transactions:

@Repository

public class JdbcUserRepository implements UserRepository {

    private JdbcOperations jdbcOperations;

    @Inject

    public JdbcUserRepository(JdbcOperations jdbcOperations) {

        this.jdbcOperations = jdbcOperations;

    }

...

}

We can simply add a method inside this repository class to save a user like:

public void addUser(User user) {

  jdbcOperations.update(INSERT_USER,

          user.getUsername(),

          user.getPassword(),

          user.getFullName(),

          user.getEmail(),

          user.isUpdateByEmail());

}

I think this version of adding a user is much simpler. Clearly, absence of all the boilerplate code doesn’t indicate that it isn’t there but it is hidden inside JdbcTemplate.

Reading Data with JdbcTemplate

Reading data is also simplified with JdbcTemplate. Let’s see how to read user information from database:

public Spitter findOne(long id) {

      return jdbcOperations.queryForObject(

              SELECT_USER_BY_ID, new UserRowMapper(),

              id

      );

}

 

private static final class UserRowMapper

        implements RowMapper<User> {

    

     public User mapRow(ResultSet rs, int rowNum)

            throws SQLException {

        return new User(

                rs.getLong("id"),

                rs.getString("username"),

                rs.getString("password"),

                rs.getString("fullName"),

                rs.getString("email"),

                rs.getBoolean("updateByEmail"));

      }

}

Persisting Data with ORM

Apart from mapping database objects to POJOs, we need some other requirements in our ORM layer as well, like:

• Lazy loading: As object graphs become more complex, you sometimes don’t want to fetch entire relationships immediately. Lazy loading allows us to grab data only as it’s needed and ignore other data.
• Eager fetching: This is the opposite of lazy loading. Eager fetching allows us to grab an entire object graph in one query.
• Cascading: Sometimes changes to a database table should result in changes to other tables as well. For example, when an Order object is deleted, we also want to delete the associated LineItems from the database.

When we start to configure Hibernate, we need a sessionfactory bean. Let’s do that next.

@Bean

public LocalSessionFactoryBean sessionFactory(DataSource dataSource) {

  LocalSessionFactoryBean sfb = new LocalSessionFactoryBean();

  sfb.setDataSource(dataSource);

  sfb.setMappingResources(new String[] { "Spitter.hbm.xml" });

  Properties props = new Properties();

  props.setProperty("dialect", "org.hibernate.dialect.H2Dialect");

  sfb.setHibernateProperties(props);

  return sfb;

}

Three properties required with LocalSessionFactoryBean are:

1. Reference to a data source bean
2. One or more Hibernate mapping files that define the persistence strategy for the application
3. Properties of how Hibernate should operate

Now, with Hibernate, let’s again write the same code.

public HibernateSpitterRepository(SessionFactory sessionFactory) {

  this.sessionFactory = sessionFactory;

}

private Session currentSession() {

  return sessionFactory.getCurrentSession();

}

public long count() {

  return findAll().size();

}

public Spitter save(User user) {

  Serializable id = currentSession().save(user);

  return new Spitter((Long) id,

          user.getUsername(),

          user.getPassword(),

          user.getFullName(),

          user.getEmail(),

          user.isUpdateByEmail());

}

 

public User findOne(long id) {

  return (User) currentSession().get(User.class, id);

}

public User findByUsername(String username) {

  return (Spitter) currentSession()

          .createCriteria(User.class)

          .add(Restrictions.eq("username", username))

          .list().get(0);

}

public List<User> findAll() {

  return (List<User>) currentSession()

          .createCriteria(User.class).list();

}

Now the Hibernate version of the repository is complete. And we were able to develop it without directly depending on any Spring-specific classes (aside from the @Repository annotation). That same template-less approach can be applied when developing a pure JPA-based repository.

Conclusion

In this article, we learned a lot. From configuring JDBC data sources and then starting to integrate Hibernate into our application.

In the next article, we will continue our journey to setup NoSQL databases like Neo4J and MongoDB in Spring framework.

Visit our homepage to search, compare and review top development tools.