This is a demonstration of how Spring and Oracle are playing happily together via JdbcTemplate.

Prerequisites:

• Install in your local Maven repo ojdbc6.jar
• Install Oracle XE 11gR2 in VirtualBox (no matter the OS)

Let’s start from the end: this is how the JUnit 4 test case looks like for the DAO of our domain object for all its CRUD operations armored with rollback functionality so our database data are not being altered after our transactional operations. Notice the use of matchers and the spring-context-enabling annotation of the test class:

package com.dimitrisli.springJdbcOracle.dao.impl;

import com.dimitrisli.springJdbcOracle.dao.LocationDao;
import com.dimitrisli.springJdbcOracle.model.Location;
import org.junit.Test;
import org.junit.runner.RunWith;
import static org.junit.Assert.*;
import static org.hamcrest.CoreMatchers.*;
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 javax.inject.Inject;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "/spring/context/applicationContext.xml")
@TransactionConfiguration(transactionManager = "jdbcTransactionManager", defaultRollback = true)
@Transactional
public class LocationDaoTest {

    @Inject private LocationDao locationDao;

    @Test
    public void testSelectAllLocations(){
    List<Location> locations = locationDao.getLocations();
    assertThat(locations.size(), is(23));
    }

    @Test
    public void testSelectOneLocation(){
      Location location = locationDao.getLocation(1000L);
      assertNotNull("test entry not found", location);
    }

    @Test
    public void testDeleteLocation(){
        assertNotNull("entry for test should be there", locationDao.getLocation(1000L));
        locationDao.deleteLocation(1000L);
        assertNull("entry wasn't successfully deleted", locationDao.getLocation(1000L));
    }

    @Test
    public void testInsertLocation(){
        Location location = new Location(1000L,"test","11111","athens","athens","IT");
        int sizeBeforeInsert = locationDao.getLocations().size();
        locationDao.createLocation(location);
        assertThat(locationDao.getLocations().size(),is(sizeBeforeInsert + 1));
    }

    @Test
    public void testUpdateLocation(){
        Location newLocation = new Location(1000L,"test","11111","athens","athens","IT");
        locationDao.updateLocation(newLocation);
        Location changedLocation = locationDao.getLocation(1000L);
        assertThat(changedLocation.getStreetAddress(), is("test"));
    }

}

The POM looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>SpringJdbcOracle</groupId>
    <artifactId>SpringJdbcOracle</artifactId>
    <version>1.0</version>


    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                    <encoding>${project.build.sourceEncoding}</encoding>
                </configuration>
            </plugin>

            <!--Logging related plugin
                this plugin breaks the build if non-wanted logging frameworks are spotted in the classpath
            -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-enforcer-plugin</artifactId>
                <version>1.0.1</version>
                <executions>
                    <execution>
                        <id>enforce-versions</id>
                        <goals>
                            <goal>enforce</goal>
                        </goals>
                        <configuration>
                            <rules>
                                <bannedDependencies>
                                    <excludes>
                                        <exclude>commons-logging:commons-logging</exclude>
                                        <exclude>log4j:log4j</exclude>
                                    </excludes>
                                </bannedDependencies>
                            </rules>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!--Spring related dependencies -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>javax.inject</groupId>
            <artifactId>javax.inject</artifactId>
            <version>1</version>
        </dependency>


        <!--Oracle jdbc driver-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

        <!-- DB Connection Pool -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <!--Logging related dependencies
            Further info: http://www.slf4j.org/faq.html#excludingJCL and
                          http://blog.frankel.ch/configuring-maven-to-use-slf4j
        -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <!--scope should be runtime but applied at compile time
                to get autocompletion visibility at logback.xml-->
            <!--scope>runtime</scope-->
            <version>0.9.24</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.6.1</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>1.7.2</version>
        </dependency>

        <!-- JUnit 4 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>

        <!-- Misc -->
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2.2</version>
            <scope>runtime</scope>
        </dependency>


    </dependencies>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring.version>3.1.2.RELEASE</spring.version>
     </properties>


</project>

Notes:
– Note how we explicitly piping any commons logging or log4j logging through our logback slf4j wrapper
– Notice how we are declaring our Oracle driver dependency given that it’s already installed in our local Maven repo (see prerequisites section)

The applicationContext:

<?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"
       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">

    <import resource="classpath*:spring/database/database.xml"/>
    <context:component-scan base-package="com.dimitrisli.springJdbcOracle" />
</beans>

The imported database context above is:

<?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:p="http://www.springframework.org/schema/p"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:c="http://www.springframework.org/schema/c"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean  class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
           p:location="properties/database.properties" />

    <bean  id="dataSource"
           class="org.apache.commons.dbcp.BasicDataSource"
           destroy-method="close"
           p:driverClassName="${jdbc.driverClassName}"
           p:url="${jdbc.url}"
           p:username="${jdbc.username}"
           p:password="${jdbc.password}" />

    <bean  class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"
           c:dataSource-ref="dataSource"  />

    <bean  id="jdbcTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
            p:dataSource-ref="dataSource"/>
    <tx:annotation-driven transaction-manager="jdbcTransactionManager"/>

</beans>

Notes:

– We are using DBCP for our DB connection pool datasource
– We are using the parameter namespace to save on some attribute injection open-close xml characters
– We are explicitly stating NamedParameterJdbcTemplate as our jdbcTemplate and inject it with the needed datasource so we can have it available for injection conveniently in our DAOs.

The domain POJO object we are about to play with is the Location object that corresponds to the Locations table on the Oracle HR schema:

package com.dimitrisli.springJdbcOracle.model;

public class Location {

    private Long locationId;
    private String streetAddress;
    private String postalCode;
    private String city;
    private String stateProvince;
    private String countryId;

    public Location(Long locationId, String streetAddress, String postalCode, String city, String stateProvince, String countryId) {
        this.locationId = locationId;
        this.streetAddress = streetAddress;
        this.postalCode = postalCode;
        this.city = city;
        this.stateProvince = stateProvince;
        this.countryId = countryId;
    }

//getters, hashcode(), equals(), toString() ignored for brevity

The RowMapper that will provide Location objects having resultSets coming from the DB. It’s a factory method (although not static but stateless by our design) used internally from Spring per DB line result returned:

package com.dimitrisli.springJdbcOracle.orm;

import com.dimitrisli.springJdbcOracle.model.Location;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;

@Component
public class LocationRowMapper implements RowMapper<Location> {

    @Override
    public Location mapRow(ResultSet rs, int rowNum) throws SQLException {
        return  new Location(rs.getLong("LOCATION_ID"),
                             rs.getString("STREET_ADDRESS"),
                             rs.getString("POSTAL_CODE"),
                             rs.getString("CITY"),
                             rs.getString("STATE_PROVINCE"),
                             rs.getString("COUNTRY_ID"));
    }
}

Here’s the DAO interface responsible for the CRUD operations:

package com.dimitrisli.springJdbcOracle.dao;

import com.dimitrisli.springJdbcOracle.model.Location;

import java.util.List;

public interface LocationDao {

    public void createLocation(Location location);
    public List<Location> getLocations();
    public Location getLocation(Long locationId);
    public void updateLocation(Location location);
    public void deleteLocation(Long locationId);

}

and its implementation looks like this:

package com.dimitrisli.springJdbcOracle.dao.impl;

import com.dimitrisli.springJdbcOracle.dao.LocationDao;
import com.dimitrisli.springJdbcOracle.model.Location;
import com.dimitrisli.springJdbcOracle.orm.LocationRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.inject.Inject;
import java.util.HashMap;
import java.util.List;

@Repository("locationDao")
public class LocationDaoImpl implements LocationDao {

    private static final String CREATE_SQL = "INSERT INTO LOCATIONS( LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, " +
                                             "STATE_PROVINCE, COUNTRY_ID) " +
                                             "VALUES (LOCATIONS_SEQ.NEXTVAL, :streetAddress, :postalCode, :city, " +
                                             ":stateProvince, :countryId)";

    private static final String GET_ALL_SQL = "SELECT LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID " +
                                              "FROM LOCATIONS";

    private static final String GET_SQL = "SELECT LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID " +
                                          "FROM LOCATIONS WHERE LOCATION_ID = :locationId";

    private static final String DELETE_SQL = "DELETE LOCATIONS WHERE LOCATION_ID = :locationId";

    private static final String UPDATE_SQL = "UPDATE LOCATIONS SET STREET_ADDRESS = :streetAddress, POSTAL_CODE=:postalCode, " +
                                            "CITY = :city, STATE_PROVINCE = :stateProvince, COUNTRY_ID = :countryId " +
                                            "WHERE LOCATION_ID = :locationId";

    @Inject private NamedParameterJdbcOperations jdbcTemplate;
    @Inject private LocationRowMapper locationRowMapper;

    @Override
    public void createLocation(Location location) {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("streetAddress", location.getStreetAddress())
                .addValue("postalCode", location.getPostalCode())
                .addValue("city", location.getCity())
                .addValue("stateProvince", location.getStateProvince())
                .addValue("countryId", location.getCountryId());
        jdbcTemplate.update(CREATE_SQL, params);
    }

    @Override
    public List<Location> getLocations() {
        return jdbcTemplate.query(GET_ALL_SQL, new HashMap<String, Object>(), locationRowMapper);
    }

    @Override
    public Location getLocation(Long locationId) {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("locationId", locationId);
        List<Location> locations = jdbcTemplate.query(GET_SQL, params, locationRowMapper);
        return locations.isEmpty()?null:locations.get(0);
    }

    @Override
    public void updateLocation(Location location) {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("locationId", location.getLocationId())
                .addValue("streetAddress", location.getStreetAddress())
                .addValue("postalCode", location.getPostalCode())
                .addValue("city", location.getCity())
                .addValue("stateProvince", location.getStateProvince())
                .addValue("countryId", location.getCountryId());
        jdbcTemplate.update(UPDATE_SQL, params);
    }

    @Override
    public void deleteLocation(Long locationId) {
        jdbcTemplate.update(DELETE_SQL, new MapSqlParameterSource("locationId",locationId));
    }
}

Notes:
– Notice how we inject the JdbcTemplate and not fetching it from this class directly
– Notice how we inject the RowMapper and we don’t anonymous-class-it from this class directly
– The CRUD operations are setup parameterized in the top of the file as private static finals
– In all the CRUD operations we are using either jdbcTemplate.update() or jdbcTemplate.query() methods

Here’s the Github repo of the project