A User data CRUD based web application that takes care of data with Spring JDBCTemplate, Transaction and ContentNegotiatingViewResolver

All of us need a basic web application which has a set of CRUD operations against a database. This is considering the need for transactions. We also would like to present this data in multiple formats to the client in case of say, an API requesting client.

Here I go through a basic web app project with spring considering all the data needs.

I will add the necessary configurations here,

1. Pre-requisites

Maven: pom.xml
The pom.xml here has many dependencies but we just need the following;
spring-core, spring-context, spring-tx, sring-webmvc, jackson-mapper-asl, jackson-core-asl, common-pool, common-dbcp, mysql-connector-java, junit

2. Configuration for database connection

JDBCTemplate and transaction manager related configuration

<bean id="ms_rhl_dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
 <property name="driverClassName" value="${jdbc.rhl.mysql.driverClassName}" />
 <property name="url" value="${jdbc.rhl.mysql.url}" />
 <property name="username" value="${jdbc.rhl.mysql.username}" />
 <property name="password" value="${jdbc.rhl.mysql.password}" />
 <property name="removeAbandoned" value="true"/>
 <property name="initialSize" value="1" />
 <property name="maxActive" value="10" />
 <property name="validationQuery" value="SELECT 1"/>
 <property name="testOnBorrow" value="true"/> 
<!-- Use annotated transactions -->
 <tx:annotation-driven proxy-target-class="true" transaction-manager="rhlServiceTransactionManager" />
 <!-- JDBC Transaction Templates -->
 <bean id="rhlServiceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
 <property name="dataSource" ref="ms_rhl_dataSource"></property>
 <!-- JDBC Template Data source Mapping -->
 <bean id="rhlServiceJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
 <constructor-arg><ref bean="ms_rhl_dataSource" /></constructor-arg> 

3. DAO related code.

Here we cover most of the CRUD type of operations that may be required. This code is available in UserDAO.java class. I have used annotated transaction @Transactional at the method level so as to make sure the query is wrapped within a transaction block.

private JdbcTemplate rhlServiceJdbcTemplate;

A Query code to CREATE TABLE. A user data table and INSERT data. This will be used for going through our various cases

String sql = "CREATE TABLE IF NOT EXISTS `user` ("
           + " `user_id` INT NOT NULL AUTO_INCREMENT,"
           + " `user_name` VARCHAR(45) NULL,"
           + " `first_name` VARCHAR(45) NULL,"
           + " `last_name` VARCHAR(45) NULL,"
           + " `email` VARCHAR(45) NULL,"
           + " `password` VARCHAR(45) NULL,"
           + " `organization` VARCHAR(45) NULL, "
           + " `enabled` INT NULL DEFAULT 1 ,"
           + " `phone` VARCHAR(45) NULL,"
           + " `created_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,"
           + " PRIMARY KEY (`user_id`))"; 

 String insert = "INSERT INTO `user` (`user_id`, "
           + "`user_name`, `first_name`, "
           + "`last_name`, `email`, `password`, "
           + "`organization`, `enabled`, `phone`) "
           + " VALUES ('1', 'Vishwakarma', 'Rahul', 'Vishwakarma', "
           + "'rvk@hemail.com', 'wefbvseuf', 'RVK', '1', '9862541689')";

A query part code to INSERT data and fetch primary key,

KeyHolder keyHolder = new GeneratedKeyHolder();
rhlServiceJdbcTemplate.update(new PreparedStatementCreator() {
   public PreparedStatement createPreparedStatement(
   Connection connection) throws SQLException {
   PreparedStatement ps = connection.prepareStatement(sql,
   new String[] { "USER_ID" });
   ps.setString(1, user.getUsername());
   ps.setString(2, user.getFirstName());
   ps.setString(3, user.getLastName());
   ps.setString(4, user.getEmail());
   ps.setString(5, user.getPassword());
   ps.setString(6, user.getOrganization());
   ps.setInt(7, user.getEnabled());
   ps.setString(8, user.getPhone());
   return ps;
}, keyHolder);
userId = (int) keyHolder.getKey().intValue();

Another query for BATCH INSERT of data,

updatesCount = rhlServiceJdbcTemplate.batchUpdate(sql, 
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
   User user = users.get(i);
   ps.setString(1, user.getUsername());
   ps.setString(2, user.getFirstName());
   ps.setString(3, user.getLastName());
   ps.setString(4, user.getEmail());
   ps.setString(5, user.getPassword());
   ps.setString(6, user.getOrganization());
   ps.setInt(7, user.getEnabled());
   ps.setString(8, user.getPhone());
  public int getBatchSize() {
   return users.size();

Query to SELECT data using rowmapper

User u = null;
String sql = "SELECT * FROM data.user WHERE user_id="+userId;
RowMapper<User> rowMapper = new RowMapper<User>() {
 public User mapRow(ResultSet rs, int arg1) throws SQLException {
   User u = new User();
   return u;
 u = rhlServiceJdbcTemplate.queryForObject(sql,rowMapper);

Query to SELECT data using BeanPropertyRowMapper

String sql = "SELECT * FROM data.user WHERE user_id="+userId;
u = rhlServiceJdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class));

Query to UPDATE data

int key = rhlServiceJdbcTemplate.update(sql, 
new Object[] { user.getUsername(), user.getFirstName(), user.getLastName(), user.getEmail(), user.getPassword(), user.getOrganization(), user.getEnabled(), user.getPhone(), new Timestamp(System.currentTimeMillis()), user.getUserId()});

Query to DELETE data

String sql = "DELETE FROM USER WHERE USER_ID="+userId; 
 int rows = rhlServiceJdbcTemplate.update(sql);

Query to SELECT count()

String sql = "SELECT count(1) FROM USER"; 
int count = rhlServiceJdbcTemplate.queryForInt(sql);

This covers the part where I am getting data from the database.

4. Content Negotiation View Resolver

One other requirement was to be able to show the data format based on the request query. We should be able to present data in JSON, XML etc based on the request query. This is where content negotiation view resolver was used so that a query can be served by marshalling the right data format based on the request query,


 <context:component-scan base-package="com.rhl" />
For Multiple response types REST Call to be appended with .json/.xml/.rss or just the bare call for the default format.
Reference: http://www.mkyong.com/spring-mvc/spring-3-mvc-contentnegotiatingviewresolver-example/ 
<!-- XML Parser -->
<bean id="jaxbMarshaller" class="org.springframework.oxm.jaxb.Jaxb2Marshaller">
<property name="classesToBeBound">
<bean class="org.springframework.web.servlet.view.ContentNegotiatingViewResolver">
 <property name="order" value="1" />
 <property name="defaultContentType" value="application/json" />
 <property name="mediaTypes">
     <entry key="json" value="application/json" />
     <entry key="xml" value="application/xml" />
 <property name="defaultViews">
  <!-- JSON View -->
  <bean class="org.springframework.web.servlet.view.json.MappingJacksonJsonView">
  <!-- JAXB XML View -->
  <bean class="org.springframework.web.servlet.view.xml.MarshallingView">
  <constructor-arg ref="jaxbMarshaller"></constructor-arg>
 <property name="ignoreAcceptHeader" value="true" />

NOTE: We also will have to remove the @ResponseBody annotations which is responsible for marshalling and unmarshalling of the datastructures at the @Controller interface.

Now, in order to request formatted data

  • XML data we format URL as http://localhost:8080/RhlJDBCTemplate/users/1.xml
  • JSON data as http://localhost:8080/RhlJDBCTemplate/users/1.json

The project can be cloned from github. You can also start a mysql local instance and create a databasse name data as root and no password. You can also configure the database in the database.properties file. Use the project codebase below and also the hyperlinks available in this blog for configurations and java class.

GITHUB: https://github.com/vishwakarmarhl/SpringJDBCTemplate