Cacher is the code snippet organizer for pro developers

CREATE TABLE rdsc_data_fetcher
(
    id                    NUMBER(18) PRIMARY KEY,
    sql                   VARCHAR2(4000) NOT NULL,
    description           VARCHAR2(500),
    securityExpression    VARCHAR2(500)
);

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

    <context:component-scan base-package="au.com.rds.rdsc.datafetcher"/>

    <jdbc:embedded-database id="dataSource" type="H2">
        <jdbc:script location="classpath:META-INF/test-schema.sql"/>
        <jdbc:script location="classpath:META-INF/test-data.sql"/>
    </jdbc:embedded-database>

    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <tx:annotation-driven transaction-manager="txManager"/>

</beans>

package au.com.rds.rdsc.datafetcher;

import org.junit.Assert;
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 org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Ke Lin
 * @version $Id$
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(
        locations = "classpath:META-INF/spring/applicationContext-test.xml"
)
@TransactionConfiguration(transactionManager = "txManager", defaultRollback = true)
public class DataFetcherRepositoryTest
{
  @Autowired
  private DataFetcherRepository repository;

  @Test
  @Transactional
  public void testFindOne()
  {
    DataFetcher fetcher = repository.findOne(1L);
    Assert.assertNotNull("Should be able to find the data fetcher row with id 1", fetcher);
    Assert.assertEquals(fetcher.getId(), Long.valueOf(1L));
  }

  @Test
  @Transactional
  public void testRunTheFirstFetcher()
  {
    DataFetcher fetcher = repository.findOne(1L);
    List<Map<String, Object>> resultList = repository.executeDataFetcher(fetcher);
    Assert.assertEquals("Should get a result list of size 1", resultList.size(), 1);

    Map<String, Object> result = resultList.get(0);
    Assert.assertEquals("Should have an ID property which equals to 1", BigDecimal.valueOf(1), result.get("ID"));
    Assert.assertEquals("Should have a DESCRIPTION property 'return a single row, id = 1'", "return a single row, id = 1", result.get("DESCRIPTION"));
  }

  @Test
  @Transactional
  public void testRunTheSecondOneWhichRequiresParameters()
  {
    DataFetcher fetcher = repository.findOne(2L);
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("id", 3);
    List<Map<String, Object>> resultList = repository.executeDataFetcher(fetcher, params);
    Assert.assertEquals("Should get a result list of size 1", resultList.size(), 1);

    Map<String, Object> result = resultList.get(0);
    Assert.assertEquals("Should have an ID property which equals to 3", BigDecimal.valueOf(3), result.get("ID"));
    Assert.assertEquals("Should have a DESCRIPTION property 'return multiple rows'", "return multiple rows", result.get("DESCRIPTION"));
  }

  @Test
  @Transactional
  public void testRunTheThirdOneWhichReturnsMultipleRows()
  {
    DataFetcher fetcher = repository.findOne(3L);
    List<Map<String, Object>> resultList = repository.executeDataFetcher(fetcher);
    Assert.assertEquals("Should get a result list of size 3", resultList.size(), 3);
  }

}

INSERT INTO rdsc_data_fetcher VALUES(1, 'select id, description from rdsc_data_fetcher where id = 1', 'test 1 desc', 'test security expression 1');
INSERT INTO rdsc_data_fetcher VALUES(2, 'select id, description from rdsc_data_fetcher where id = 2', 'test 2 desc', 'test security expression 2');

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.193</version>
</dependency>

import org.junit.runner.RunWith;
import org.junit.runners.Suite;

@RunWith(Suite.class)
@Suite.SuiteClasses({
        DataFetcherRepositoryTest.class, DataFetcherServiceTest.class
})
public class DataFetcherTestSuite
{
}