Suggested Pages

Tuesday, June 19, 2012

Spring JdbcTemplate - Tutorial

In the following post i'll show an example of a way to use JdbcTemplate in Spring framework.
JdbcTemplate is a useful class that provides simple methods to retrieve, update and insert entities in the DB.
In the Javadoc it's written: JdbcTemplate semplifies the use of JDBC avoiding the common errors such as forgetting to always close the connection.
The method shown in this post has the following signature: List<T> query(String sql, RowMapper rowMapper). This method executes an SQL query and maps each row of the ResultSet to a Java object via a RowMapper.
RowMapper interface has a method: T mapRow(ResultSet resultSet, int arg1) throws SQLException that must be implemented. It has to map each row of data in the ResultSet into an Entity of the application domain.

As you can see in the following snippet, in the Spring context it's declared a bean called jdbcTemplate ( see ContactDAOTest-context.xml) and then i will use it in my test class (ContactDAOTest.java) via injection. You can notice that JdbcTemplate is a bean that only needs a Datasource to communicate with the database.

ContactDAOTest-context.xml


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
 xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jaxrs="http://cxf.apache.org/jaxrs"
 xmlns:cxf="http://cxf.apache.org/core"
 xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://cxf.apache.org/jaxrs http://cxf.apache.org/schemas/jaxrs.xsd http://cxf.apache.org/core http://cxf.apache.org/schemas/core.xsd   http://www.springframework.org/schema/beans  http://www.springframework.org/schema/beans/spring-beans-3.0.xsd    http://www.springframework.org/schema/context  http://www.springframework.org/schema/context/spring-context-3.0.xsd    http://www.springframework.org/schema/jee  http://www.springframework.org/schema/jee/spring-jee-3.0.xsd    http://www.springframework.org/schema/tx   http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

 <!-- annotation support -->
 <context:annotation-config />

 <!-- support for transaction -->
 <tx:annotation-driven />

 <!-- scan package for @Repository annotations -->
 <context:component-scan base-package="com.simonefolinojavablog.persistence.dao" />


 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="dataSource" />
 </bean>


 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
  <property name="driverClass" value="com.p6spy.engine.spy.P6SpyDriver" />
  <property name="jdbcUrl" value="jdbc:hsqldb:mem:SN" />
  <property name="user" value="sa" />
  <property name="password" value="" />
 </bean>

 <bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="persistenceUnitName" value="persistenceUnitTest" />
  <property name="dataSource" ref="dataSource" />
  <property name="jpaVendorAdapter">
   <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="showSql" value="true" />
    <property name="databasePlatform" value="org.hibernate.dialect.HSQLDialect" />
    <property name="generateDdl" value="true" />
   </bean>
  </property>
 </bean>

 <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
  <property name="entityManagerFactory" ref="entityManagerFactory" />
  <property name="dataSource" ref="dataSource" />
 </bean>

</beans>





The following class is a Test Class that makes use of JdbcTemplate.
I use query method of JdbcTemplate which has two parameters:
  • String: the sql statement to retrieve data from the DB;
  • RowMapper: an interface that we'll implement using an Anonymous Class.
In practice we'll make with RowMapper a sort of mapping between the ResultSet and the Contact entity.

ContactDAOTest.java

....

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

import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.simonefolinojavablog.persistence.dao.ContactDAO;
import com.simonefolinojavablog.persistence.entity.Contact;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(value = "ContactDAOTest-context.xml")
public class ContactDAOTest {

 @Autowired
 private JdbcTemplate jdbcTemplate;

 @Autowired
 private ContactDAO contactDAO;

 private static Contact contact1;
 
 private static Contact contact2;

 @BeforeClass
 public static void initContact() {
  System.out.println("------------initContact(): start -------");
  
  contact1 = new Contact();
  contact1.setNumber(4444);
  
  contact2 = new Contact();
  contact2.setNumber(5555);
  System.out.println("------------initContact(): end -------");
 }

 @Before
 public void insert(){
  
  contactDAO.save(contact1);
  contactDAO.save(contact2);
  
 }
 
 
 @Test
 public void testQuery() {
  
  List<Contact> contacts = jdbcTemplate.query("select * from Contacts", new RowMapper<Contact>() {
   
   public Contact mapRow(ResultSet resultSet, int arg1) throws SQLException {
    Contact contact=new Contact();
    contact.setNumber(resultSet.getInt("number"));
    return contact;
   }
  });
    
  System.out.println(contacts);

 }


}

No comments :

Post a Comment

Suggested Pages