Mapping object to row in spring using jdbc template

See this tutorial for getting the feel of spring jdbc template.
The example below uses database and corresponding from this post.
Now we can have our object from result set. Either it can be 1 value returned from select query, or we can have multiple rows. So thats where we have to map the object. Spring provides various ways to map result sets to these objects,eg. Flight.

Querying for Single Row

1. Custom RowMapper

class FlightMapper implements RowMapper<Flight> {
       @Override
    public Flight mapRow(ResultSet rs, int index) throws SQLException {
          Flight flight = new Flight();
          flight.setFlightNo(rs.getString(1));
          flight.setCarrier(rs.getString(2));
          flight.setFrom(rs.getString(3));
          flight.setTo(rs.getString(4));
          return flight;
       }

Now this should be passed to queryForObject function.
Pass it to queryForObject() method, the returned result will call your custom mapRow() method to match the value into the properly.

public Flight getFlightInfo(String flightNo) {

String sql = "select * from flights_test where flightno=?"
        Flight flight = (Flight) jdbcTemplate.queryForObject(sql,new Object[]
                {flightNo}, new FlightMapper());   

        return flight;             
    }

2. BeanPropertyRowMapper

In Spring 2.5, comes with a handy RowMapper implementation called ‘BeanPropertyRowMapper’, which can maps a row’s column value to a property by matching their names. Just make sure both the property and column has the same name, e.g property ‘flightNo’ will match to column name ‘FLIGHTNO’ or with underscores ‘FLIGHT_NO’.

Again queryForObject method can be used.

//Similar to above but using BeanPropertyRowMapper

public Flight getFlightInfo(String flightNo) {

String sql = "select * from flights_test where flightno=?"
        Flight flight = (Flight) jdbcTemplate.queryForObject(sql,new Object[]
                {flightNo}, new BeanPropertyRowMapper(Flight.Class)
());   

        return flight;             
    }

Querying for Multiple Rows

Here’s two examples to show how to query or extract multiple rows from database, and convert it into a List.

1. Map it manually

In mutiple return rows, RowMapper is not supported in query() method, you need to map it manually. 

public List<Flight> getAvailableFlights(String carrier) {
        return jdbcTemplate.query("select * from flights_test where carrier = ?", new FlightMapper(), carrier);
    }

2. BeanPropertyRowMapper

The simplest solution is using the BeanPropertyRowMapper class.

    public List<Flight> getAvailableFlights(String carrier) {
        return jdbcTemplate.query("select * from flights_test where 
        carrier = ?", new BeanPropertyRowMapper(Flight.Class), carrier);
    }

Creating a DAO interface and implementation using JdbcTemplate

Prerequisites

1. Spring installation
   
2. To create a dao in spring we first need a database and corresponding class to represent entities in database. For this see - Creating a Database and corresponding pojo class to represent that entity

Now for data access we should use Dao pattern, so we create interface and implementation for this.

Creating a Dao Interface

package repository;

import java.util.List;
import java.util.Map;

import entity.Flight;

public interface FlightRepository {

public int getTotalFlights();

public int getTotalFlights(String carrier);

public Map getFlightInfo(String flightNo);

public List getFlights(String carrier);

public int getTotalFlights(String from, String to);

public List<Flight> getAvailableFlights(String
 carrier);

public void newFlight(Flight flight);

}

Creating corresponding implementation class:

• Creating a jdbc template:
  JdbcTemplate class requires a datasource to be supplied to successful creation. Provides full JDBC APIs.
  
  

  jdbcTemplate = new JdbcTemplate(dataSource);

  
  Following operations are supported i.e. CRUD(Create retrieve update and Delete) and some other operations:
  
  
  • Querying (SELECT operations).
    
    
  • Updating (INSERT, UPDATE, and DELETE operations).
    
    
  • Other SQL operations (all other SQL operations).
  SimpleJdbcTemplate class supports java5 features like generics and varargs. Provides method only for common CRUD operations:

  SimpleJdbcTemplate jdbcTemplate = new 
                         SimpleJdbcTemplate(dataSource);

  Use NamedParameterJdbcTemplate class which allows the usage of named parameters ':name' rather than traditional '?'.

  NamedJdbcTemplate namedTemplate = new
                  NamedParameterJdbcTemplate(dataSource);

  So we take both jdbc-template type as our fields in Repository implementation class and set their datasource:

  private SimpleJdbcTemplate jdbcTemplate;
  private NamedParameterJdbcTemplate namedTemplate;
  
  public void setDataSource(DataSource dataSource) {
       jdbcTemplate = new SimpleJdbcTemplate
                                     (dataSource);
       namedTemplate = new NamedParameterJdbcTemplate
  (dataSource);
  }
  

  Now we can perform various database operations using these templates.
  The JdbcTemplate query methods are used to send SELECT queries to the database. A variety of different query methods are supported, depending on how complicated the return values are.
• Quering for integer:
  Sometimes we simply require integer like count of something, or sometimes fields are integer as well eg. age . So queryOfInt method can be used.
  Now this can be done using 2 ways:
  Using select query :
  

  public int getTotalFlights() {
     return jdbcTemplate.queryForInt("select count(*)
                              from flights_test");
  }

  
  Using bind parameters:
  

  public int getTotalFlights(String carrier) { 

  //takes 2 parameters 

  return jdbcTemplate.queryForInt("select count(*) 
             from flights_test where carrier = ?",
             new Object[]{carrier});
  }

  
  Also note that SimpleJdbcTemplate class supports varargs. So Above bind parameters can be used with more than 1 binding parameters.
  Similarly there is queryForLong to fetch longs.
  
  Query For String
  What if you want String as 1 item in result-set. So in that case queryForObject can be used, and string can be returned. Example:
  
  

  public String getCarrierNameForFlightNo(String flightNo)  {

  String myString=(String) 
                 simpleJdbcTemplateTarget.queryForObject
                  ("select carrier from flights_test 
                      where flight_no= ?",
                    String.class,new Object[]{flightNo});
         
      return myString;
  }
  

  So here we are querying for object, but returning string. We can use queryForObject for more purposes as well.
  
• Query for single row:
  In this case we can use queryForMap to get this:
  

  public Map getFlightInfo(String flightNo) {
     return jdbcTemplate.queryForMap("select * from
        flights_test where flightno=?",  flightNo);                
  }

  
  
• Using name JdbcParameterTemplate
  Eg, We want a total no. of flights from 1 destination to other. So to have that we can use named parameter jdbc template:
  

  public int getTotalFlights(String from, String to) {
  Map<String, String> params = new HashMap<String, String>();
  params.put("from", from);
  params.put("to", to);
  String sql = "select count(*) from 
  flights_test where kahase=:from and
  kahatak=:to";
  return namedTemplate.queryForInt(sql, params);
   }

  
  
  
• Querying for list:
  We ca return a list where each element of the List contains a Map object holding column name, column value pair data using queryForList();
  

  public List getFlights(String carrier) {
  return jdbcTemplate.queryForList("select * 
  from flights_test where carrier = ?", carrier);
   } 

  
  
• Query for Domain objects
  Though in above case we returned list, but we have to still map manually. Therefore spring provides us with RowMapper interface:
  

  public List<Flight> getAvailableFlights(String carrier) {
  class FlightMapper implements RowMapper<Flight> {
  @Override
  public Flight mapRow(ResultSet rs, int index)  

                                   throws SQLException {
  Flight flight = new Flight();
  flight.setFlightNo(rs.getString(1));
  flight.setCarrier(rs.getString(2));
  flight.setFrom(rs.getString(3));
  flight.setTo(rs.getString(4));
  return flight;
  }
  }
  return jdbcTemplate.query("select * from flights_test

                 where carrier = ?", new FlightMapper()

                 , carrier);
  }
  

  
  
  
  
  So a flight object is created from every resultset and corresponding mapper object is passed to query() method, which finally returns a list. For more on mapping please refer to this post.
  
• For all other DML (data manipulation language) operations
  For this, update() function is used
  

  public void newFlight(Flight flight) {
  jdbcTemplate.update("insert into 
                flights_test values(?, ?, ?, ?)", 
    flight.getFlightNo(), flight.getCarrier(),
    flight.getFrom(), flight.getTo());
  }

  
  

Full code listing

package repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import entity.Flight;

public class JdbcFlightRepository implements FlightRepository {

private SimpleJdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedTemplate;

public void setDataSource(DataSource dataSource) {
jdbcTemplate = new SimpleJdbcTemplate(dataSource);
namedTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int getTotalFlights() {
return jdbcTemplate.queryForInt("select count(*)
from flights_test");
}

public int getTotalFlights(String carrier) {
return jdbcTemplate.queryForInt("select count(*)
from flights_test where carrier = ?",
new Object[]{carrier});
}

public Map getFlightInfo(String flightNo) {
return jdbcTemplate.queryForMap("select *
from flights_test where flightno=?",
flightNo);                
}

public List getFlights(String carrier) {
return jdbcTemplate.queryForList("select * 
from flights_test where carrier = ?", carrier);
}

public int getTotalFlights(String from, String to) {
Map<String, String> params = new HashMap<String, String>();
params.put("from", from);
params.put("to", to);
String sql = "select count(*) from flights_test where kahase=:from and kahatak=:to";
return namedTemplate.queryForInt(sql, params);
}

public List<Flight> getAvailableFlights(String carrier) {
class FlightMapper implements RowMapper<Flight> {
@Override
public Flight mapRow(ResultSet rs, int index) throws SQLException {
Flight flight = new Flight();
flight.setFlightNo(rs.getString(1));
flight.setCarrier(rs.getString(2));
flight.setFrom(rs.getString(3));
flight.setTo(rs.getString(4));
return flight;
}
}
return jdbcTemplate.query("select * from 
flights_test where carrier = ?", new FlightMapper(), carrier);
}

public void newFlight(Flight flight) {
jdbcTemplate.update("insert into 
flights_test values(?, ?, ?, ?)", 
flight.getFlightNo(), flight.getCarrier(),
 flight.getFrom(), flight.getTo());
}
}

Config files to set up the beans: Managing Datasource:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

<bean id="ds"
class="org.springframework.jdbc.datasource.
DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/test" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>    

</beans>

Managing the bean config file:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

<import resource="db-config.xml" />

<bean id="flightRepository" class="repository.JdbcFlightRepository">
<property name="dataSource" ref="ds" />
</bean>

</beans>

Tester of the program:

package test;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import repository.FlightRepository;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:ex-config.xml")
public class FlightRepositoryTest {

@Autowired private FlightRepository flightRepository;

@Test
public void testDifferentMethods() {
System.out.println(flightRepository.getTotalFlights());
//System.out.println(flightRepository.getTotalFlights("KINGFISHER"));
//System.out.println(flightRepository.getFlightInfo("JL-120"));
//System.out.println(flightRepository.getFlights("KINGFISHER"));
//System.out.println(flightRepository.getTotalFlights("MUMBAI", "JAIPUR"));
//System.out.println(flightRepository.getAvailableFlights("KINGFISHER"));
}    
}

Create a flight database , corresponding Pojo class for entity

Create a flight database:

create table flights_test(flightno varchar(10), carrier
 varchar(30), kahase varchar(30), kahatak varchar(30));

insert into flights_test values('JL-120','JET 
AIRWAYS', 'MUMBAI', 'JAIPUR');
insert into flights_test values('KL-
102','KINGFISHER', 'DELHI', 'MUMBAI');
insert into flights_test values('AI-
229','INDIAN', 'KOLKATA', 'DELHI');
insert into flights_test values('SP-
109','SPICEJET', 'CHENNAI', 'MUMBAI');
insert into flights_test values('GO-120','GO
 AIR', 'MUMBAI', 'BANGALORE');

kahase in hindi means - from Where,i.e. source of flight
kahatak in hindi means - till where i.e. destination of flight

Entity corresponding to database :

package entity;

public class Flight {

    private String flightNo;
    private String carrier;
    private String from;
    private String to;
    
    public String getFlightNo() {
        return flightNo;
    }
    public void setFlightNo(String flightNo) {
        this.flightNo = flightNo;
    }
    public String getCarrier() {
        return carrier;
    }
    public void setCarrier(String carrier) {
        this.carrier = carrier;
    }
    public String getFrom() {
        return from;
    }
    public void setFrom(String from) {
        this.from = from;
    }
    public String getTo() {
        return to;
    }
    public void setTo(String to
package entity;

public class Flight {

    private String flightNo;
    private String carrier;
    private String from;
    private String to;
    
    public String getFlightNo() {
        return flightNo;
    }
    public void setFlightNo(String flightNo) {
        this.flightNo = flightNo;
    }
    public String getCarrier() {
        return carrier;
    }
    public void setCarrier(String carrier) {
        this.carrier = carrier;
    }
    public String getFrom() {
        return from;
    }
    public void setFrom(String from) {
        this.from = from;
    }
    public String getTo() {
        return to;
    }
    public void setTo(String to) {
        this.to = to;
    }

    
}
) {        this.to = to;    }    }

This database and pojo is used by following posts, eg. -

1. Using jdbc-template to create data access layer 
2. Mapping the resultset to object or class 
3. The Spring Jdbc Template for database access - Tutorial

Spring : Create a class equivalent to entity

Entity corresponding to database shown in this post :

package entity;

public class Flight {

    private String flightNo;
    private String carrier;
    private String from;
    private String to;
    
    public String getFlightNo() {
        return flightNo;
    }
    public void setFlightNo(String flightNo) {
        this.flightNo = flightNo;
    }
    public String getCarrier() {
        return carrier;
    }
    public void setCarrier(String carrier) {
        this.carrier = carrier;
    }
    public String getFrom() {
        return from;
    }
    public void setFrom(String from) {
        this.from = from;
    }
    public String getTo() {
        return to;
    }
    public void setTo(String to
package entity;

public class Flight {

    private String flightNo;
    private String carrier;
    private String from;
    private String to;
    
    public String getFlightNo() {
        return flightNo;
    }
    public void setFlightNo(String flightNo) {
        this.flightNo = flightNo;
    }
    public String getCarrier() {
        return carrier;
    }
    public void setCarrier(String carrier) {
        this.carrier = carrier;
    }
    public String getFrom() {
        return from;
    }
    public void setFrom(String from) {
        this.from = from;
    }
    public String getTo() {
        return to;
    }
    public void setTo(String to) {
        this.to = to;
    }

    
}
) {        this.to = to;    }    }

Create a flight database

Create a flight database:

create table flights_test(flightno varchar(10), carrier varchar(30), kahase varchar(30), kahatak varchar(30));

insert into flights_test values('JL-120','JET AIRWAYS', 'MUMBAI', 'JAIPUR');
insert into flights_test values('KL-102','KINGFISHER', 'DELHI', 'MUMBAI');
insert into flights_test values('AI-229','INDIAN', 'KOLKATA', 'DELHI');
insert into flights_test values('SP-109','SPICEJET', 'CHENNAI', 'MUMBAI');
insert into flights_test values('GO-120','GO AIR', 'MUMBAI', 'BANGALORE');

kahase in hindi means - from Where,i.e. source of flight
kahatak in hindi means - till where i.e. destination of flight

Spring JDBC introduction

Spring provides a simplification in handling database access with the Spring JDBC Template.

The Spring JDBC Template has the following advantages compared with standard JDBC.

• The Spring JDBC template allows to clean-up the resources automatically, e.g. release the database connections.
• The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages.

The Spring JDBC template offers several ways to query the database. queryForList() returns a list of HashMaps. The name of the column is the key in the hashmap for the values in the table.
More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates the SQL result direct into an object (ResultSetExtractor) or a list of objects (RowMapper). Both these methods will be demonstrated in the coding.

Core Spring Interview Questions

What is Spring?

Spring is an open source enterprise application development framework, which is primarily based on IOC (inversion of control) or DI (dependency injection) design pattern. It provides ready container to create and manage objects and also provides enterprise services to those objects. It provides ready components for different tiers of application e.g. web, middle/business and data access.

What is inversion of control (IOC) or Dependency Injection?

Inversion of control (IOC) or dependency injection (DI) is a design pattern used to give control to the assembler of classes. Generally, if a class wants to use another class, it instantiates desired class. But using this design pattern, the instantiation control is provided to the assembler. Assembler instantiates the required class and injects it in using class.

What are different types of DI?

- Constructor Injection

- Setter Injection

- Interface Injection

What are different modules in Spring?

Following six modules are there in Spring.

- Core: Springs IoC container and core services

- Web: Spring MVC and ability to integrate Spring with other web frameworks like Strusts, Tapestry, JSF etc.

- JEE: Java enterprise services like EJB support, JMX, JMS, JCA etc.

- ORM: Support to integrate with object relation mapping frameworks like hibernate, iBatis, Toplink etc.

- DAO: Helps in implementing Data Access Object design pattern. Provides support for Spring JDBC transaction management.

- AOP: Implementation of cross cutting concerns through Spring AOP and AspectJ.

What is new in Spring 2.5 as compared to 2.0?

Following changes are introduced in Spring 2.5.

- IOC container: New bean scopes, easier xml configuration, extensible xml authoring, annotations

- AOP: Easier xml configuration, support for @AspectJ aspects, support for bean name pointcut element, support for AspectJ load-time waving

- Middle tier: Declarative transactions in xml, full Websphere transaction management support, JPA, Asynchronous JMS, JDBC improvements

- Web tier: Changes in Spring MVC, Portlet framework, Tiles, JSF, JAX-WS support, etc.
What is IoC container of Spring?

Spring IoC container take care of instantiation of objects, injection of objects in each other and providing enterprise services (e.g. AOP, transaction management) to these objects.

What is BeanFactory interface?

BeanFactory provides configuration framework to Spring object creation and basic functionality around object management.

What is ApplicationContext?

ApplicationContext is built around Spring’s BeanFactory and it provides enterprise centric features e.g. AOP features, message resourcing, event propagating, application-layer-specific contexts to applications.

What is difference between BeanFactory and ApplicationContext?

BeanFactory is core configuration and basic functionality centric while ApplicationContext is enterprise-centric functionality support.

What is your preference BeanFactory or ApplicationContext? Why?

ApplicationContext. It provides all features provided by BeanFactory and enterprise centric more features, which may be required by application in future.

How to instantiate IoC container?

ApplicationContext context = new ClassPathXmlApplicationContext(new String[] {“services.xml”, “daos.xml”});

How does a web application use Spring’s configuration xmls?

Spring container/configuration xmls can be integrated with web application through web.xml. Following entries in web.xml can integrate Spring container with web container.

	<context-param>
		<description>
		Context parameter to integrate Spring and Web containers
		</description>
		<param-name>contextConfigLocation</param-name>
		<param-value>
		classpath: services.xml,
		classpath: daos.xml
		</param-value>
	</context-param>
	<listener>
<listener-class>
org.springframework.web.context.ContextLoaderListener
</listener-class>
	</listener>

How to integrate multiple bean configuration xmls?

Multiple bean configuration xmls are created to separate configurations according to layers so that it becomes easy to manage and maintain them. These configuration xmls can be imported in single xml to combine all of them.

<beans>
    <import resource="services.xml"/>
    <import resource="daos.xml"/>

    <bean id="bean1" class="..."/>
    <bean id="bean2" class="..."/>
</beans>

What are Lazily-instantiated beans?

In default behavior Spring instantiates singleton beans at the time of startup, which is called eagerly instantiation. This is good behavior as it exposes any problems in instantiation of beans at start up only. But sometimes this behavior is not expected hence by addition lazy-init=”true” to the bean definition the instantiation can be postponed to first request. Also following configuration will not allow any bean to get instantiated eagerly.

<beans default-lazy-init="true">
    <!-- no beans will be pre-instantiated... -->
</beans>

What is autowiring?
By Autowiring, Spring injects dependencies without having to specify those explicitly. Spring inspects bean factory contents and establishes relationships amongst collaborating beans. To implement it, just add autowire property in xml configuration.

What are different modes of autowiring?

Autowiring has following five modes

- no: No autowiring.

- byName: Autowiring by property name, means bean matching property name is autowired.

- byType: Bean having type as that of property type is autowired.

- constructor: Similar to byType just that the property is in constructor.

- autodetect: Spring is allowed to select autowiring from byType and constructor.

What are different bean scopes available to configure?

Following scopes can be assigned to different beans.

- singleton: One bean instance per IoC container

- prototype: Any number of instances of bean

- request: Within HTTPRequest object scope

- session: As long as HttpSession is alive

- globalsession: Within life-cycle of global HttpSession. Applicable in portlet context usually.

What is default scope in Spring?

Singleton.

How can you control bean instantiation process?

Bean instantiation by Spring can be controlled using initialization call backs. There are two ways of doing it. First is having a initialization method (say init()) and specifying it in bean configuration as ‘init-method’ property. Second is implementing InitializingBean interface and implementing afterPropertiesSet() method in it.

How can you control bean destruction process?

There are two ways of doing it. First is add a destroy() method and specify it in bean configuration as ‘destroy-method’ property. Second is implement DisposableBean interface and implement destroy() method of it.

How do you implement inheritance in bean definition?

Bean definition inheritance can be implemented by specifying ‘parent’ property of the bean equal to its parent bean definition id. This bean class must have extended itself from the parent bean class.

What are advantages of Spring usage?

- Spring provides commonly required enterprise services without a need of expensive application server.

- It reduces coupling in code and improves maintainability.

- Readily available component improve productivity and subsequently reduce development cost.

- Pojo based programming enables reuse.

- Dependency Injection can be used to improve testability.

What all you have to do to start using Spring?

- Download Spring (and dependent Jars) from Spring’s site.

- Create application context xml to define beans and dependencies.

- Integrate this xml with web.xml

- Deploy and Run the application