This is a demonstration of how Spring and Oracle are playing happily together via JdbcTemplate.
Prerequisites:
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
![.HR.EMPLOYEES - ScalaProj - [~_Dimitris_ScalaProjects_ScalaProj]](http://dimitrisli.files.wordpress.com/2012/08/hr-employees-scalaproj-_dimitris_scalaprojects_scalaproj.jpg?w=350)
![DATABASE_1f59cfcd-b0bc-4ab3-a0ca-fd6402de2165.schema_HR - ScalaProj - [~_Dimitris_ScalaProjects_ScalaProj]](http://dimitrisli.files.wordpress.com/2012/08/database_1f59cfcd-b0bc-4ab3-a0ca-fd6402de2165-schema_hr-scalaproj-_dimitris_scalaprojects_scalaproj.jpg?w=500)
![Oracle Developer Days [Running]](http://dimitrisli.files.wordpress.com/2012/08/oracle-developer-days-running1.jpg?w=500)
![Oracle Developer Days [Running]-1](http://dimitrisli.files.wordpress.com/2012/08/oracle-developer-days-running-1.jpg?w=500)
![Oracle Developer Days [Running]](http://dimitrisli.files.wordpress.com/2012/08/oracle-developer-days-running.jpg?w=500)
