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"));
}    
}