Working with Spring Embedded Database

Sometimes it is very useful to work with an in memory databases when you want to demonstrate certain database centric features of an application during development phase. Spring supports HSQL, H2, and Derby as default embedded databases but, you can also use an extensible API to plug in new embedded database types and “DataSource” implementations.

In this tutorial we’ll see how to configure and use embedded database (HSQL) using spring. Spring supports XML as well as Programmatic configuration of beans. For, simplicity I’ll use XML based configuration in this article. Here are the items that we’ll cover in this article

1.    Configuring embedded database using spring.
2.    Define a simple domain object (User) and creating a simple DAO layer for accessing underling embedded database.
3.    Create two Spring MVC controllers & views to create users and display users.

I’ll suggest you to download the example code for this article from SVN before you start, this will help you to follow the article and refer the actual code. You can download working code of this article from my SVN repository at Google: http://suryakand.googlecode.com/svn/trunk/boilerplate

I’ll be using Maven for this project. Before you start, add following dependencies to your project’s pom.xml file       

 org.springframework
 spring-jdbc
 ${spring.version}



 org.hsqldb
 hsqldb
 2.2.9

Above dependencies are specifically needed for HSQL DB and spring’s JDBC template. You’ll also need to add other dependencies, for detailed configuration about dependencies please refer the actual pom.xml (it is self explanatory) of project (http://suryakand.googlecode.com/svn/trunk/boilerplate/pom.xml)

Once you are done with setting up pom.xml file, you’ll need to perform following steps to get up and running with an embedded database:

1.    Configure embedded database using Spring XML
Spring has provided “jdbc” namespace for easy and quick configuration of embedded JDBC Data Sources. Here is an example configuration that you’ll need to add in application context file:

•    “id”: this is the bean ID for our “dataSource” that we’ll be referring from other bean definition (DAO) to get hold of database.
•    “type”: this is the type of database that we want use. In this example we’ll be using HSQL DB.
•    “location”: this the location where spring will look for SQL script files to create schema and insert sample data (if you want to). In this case we have stored SQL script files “schema.sql” (to create database schema) and “test-data.sql” (to insert sample data) in maven’s main/resource directory.

See "applicationContext.xml" file for more details.

2.    Define a simple domain object & DAO
We’ll create a simple domain object (POJO) called as User.java and a DAO class UserDaoImpl.java that will facilitate representation of database rows as java objects and database access.

public class User {
 private int userId;
 private int groupId;
 private String username;
 private String password; 
 private String firstName;
 private String middleName;
 private String lastName;
 private int phoneNumber;
 private String verificationCode;
 private String resetPaswordCode;
 private String passwordQuestion;
 private String passwordAnswer;
 
 // Omitted getters and setter 
}

UserDao Interface

public interface UserDao {
 public List getAllUsers();
 public User getUserByUserName(String userName);
 public void createUser(User user);
}

UserDao Implementation
We’ll use the “dataSource” that we have defined in step 1 and spring’s “JdbcTemplate” for accessing database. For simplicity, I have used “JdbcTemplate” and have manually populated our domain object “User.java” but, you can use any ORM framework like Hibernate, MyBatis etc. to do this task in more elegant way.

public class UserDaoImpl implements UserDao {
 private DataSource dataSource;
 private JdbcTemplate jdbcTemplate;

 public void createUser(User user) {
  jdbcTemplate.update("insert into users (group_id,username,password,first_name," +
    "middle_name,last_name,phone_number) " +
    "values (?,?,?,?,?,?,?)", new Object[] {new Integer(1), user.getUsername(), user.getPassword(), 
    user.getFirstName(), user.getMiddleName(), user.getLastName(), 
    new Integer(user.getPhoneNumber())});
 }

 public List getAllUsers() {
  return jdbcTemplate.query("SELECT * from users", new UserMapper());
 }

 public User getUserByUserName(String userName) {
  User user = null;

  if(StringUtils.isNotBlank(userName)) {
   List users = jdbcTemplate.query("SELECT * from users where username = ?", new UserMapper(), new Object[] {userName});

   if(users != null && users.size() > 0) {
    user = users.get(0);
   }
  }

  return user;
 }

 private static final class UserMapper implements RowMapper {
  public User mapRow(ResultSet rs, int rowNum) throws SQLException {
   User user = new User();
   user.setUserId(rs.getInt("user_id"));
   user.setGroupId(rs.getInt("group_id"));
   user.setUsername(rs.getString("username"));
   user.setPassword(rs.getString("password"));
   user.setFirstName(rs.getString("first_name"));
   user.setMiddleName(rs.getString("middle_name"));
   user.setLastName(rs.getString("last_name"));
   user.setPhoneNumber(rs.getInt("phone_number"));
   user.setVerificationCode(rs.getString("verification_code"));
   user.setResetPaswordCode(rs.getString("reset_pasword_code"));
   user.setPasswordQuestion(rs.getString("password_question"));
   user.setPasswordAnswer(rs.getString("password_answer"));
   return user;
  }
 }

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
  this.jdbcTemplate = new JdbcTemplate(this.dataSource);
 }

 public DataSource getDataSource() {
  return dataSource;
 }
}

Once the domain object and DAO is implemented, we’ll define a DAO in our bean definition file and will inject the “dataSource” dependency that we have defined in step 1.  Here is the DAO bean definition:

3.    Define controllers and views for creating and displaying users

We’ll create two controllers “CreateUserController” and “UserListController” to create and display users respectively from a web page/browser.

@Controller
public class CreateUserController {
 private String viewName;
 private String createSucessView;
 private UserDao userDao;

 @RequestMapping(value = {"/user/create"} , method = RequestMethod.POST)
 public ModelAndView createUser(@ModelAttribute("userModel") User userModel) {
  userDao.createUser(userModel);
  ModelAndView mv = new ModelAndView(createSucessView);
  return mv;
 }

 @RequestMapping(value = {"/user/create"} , method = RequestMethod.GET)
 public ModelAndView createUserForm(User userModel) {
  ModelAndView mv = new ModelAndView(viewName);
  mv.addObject("userModel", new User());
  return mv;
 }

 @RequestMapping(value = {"/user/isavailbale"} , method = RequestMethod.POST)
 public @ResponseBody Boolean createUser(@RequestParam("username") String username){
  User user = userDao.getUserByUserName(username);
  if(user != null) {
   return Boolean.FALSE;
  }
  return Boolean.TRUE;
 }
}

"Create User" Form

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>




  


Create New User
   














 
    
   

@Controller
public class UserListController {
 private String viewName;
 private UserDao userDao;

 @RequestMapping("/user/all")
 public ModelAndView showUserList() {
  List userList = userDao.getAllUsers();

  ModelAndView mv = new ModelAndView(viewName);
  mv.addObject("userList", userList);

  return mv;
 }
}

User List View

<%@ taglib uri='http://java.sun.com/jsp/jstl/core' prefix='c'%> 



   


  


   
User ID
   
First Name
   
User Name
   

  


    
${userRow.userId}
    
${userRow.firstName}
    
${userRow.username}
   

Fig. 1: Create User Form

Fig. 2: User List

I hope this article will help you to understand and quickly get started with spring embedded database feature. If you have any questions or suggestion, please feel free to post them.

Thank you very much for reading!