Object relational mappers allow you to map a lower level sql query to a java object. While spring's JdbcTemplate isn't considered a ORM, lets find out how to map a sql result set to a domain object.
Detailed Video Notes
JdbcTemplate is a core class within spring-jdbc that simplifies making data access calls that handles resource management such as closing and creating connections. It is a lighter weight alternative to mybatis or ibatis and hibernate especially if you are already working within spring context. Since it is easier to manage a list of business objects vs lower level resultset, lets find out how to map a jdbc row to a domain object.
Getting started
[0:24]
Following the spring boot tutorial we will set up and download a spring project from the spring initializr website. Selecting the data will add the spring-boot-starter-jdbc artifact. Next we will add the HyperSQL DataBase dependency in the pom.xml. As mentioned in how to test with a database in spring tutorial spring boot will recognize HSQLDB classes on the classpath and will autoconfigure an embedded database.
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<scope>runtime</scope>
</dependency>Set up
[0:50]
Creating database set up
Using spring @Sql and @SqlGroup annotations we will call a script to create a STATE table and preload the database table inserting three records.
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
@SqlGroup({
@Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD,
scripts = "classpath:beforeScripts.sql"),
@Sql(executionPhase = ExecutionPhase.AFTER_TEST_METHOD,
scripts = "classpath:afterScripts.sql") })
public class ApplicationTests {
private final static Logger LOGGER = Logger
.getLogger(ApplicationTests.class);
@Autowired JdbcTemplate jdbcTemplate;
}CREATE TABLE STATE (STATEID INT PRIMARY KEY, ABBREVIATION VARCHAR(10) NOT NULL, POSTALCODE VARCHAR(2) NOT NULL, STATE VARCHAR(20) NOT NULL);
INSERT INTO STATE VALUES(1, 'Ala', 'AL', 'Alabama');
INSERT INTO STATE VALUES(2, 'Wis', 'WI', 'Wisconsin');
INSERT INTO STATE VALUES(4, 'Hawaii', 'HI', 'Hawaii');
DROP TABLE STATE;Creating domain
[0:58]
In order to map a jdbc resultset we will need to create a State object with attributes of stateId as a primary key, abbreviation of the state, postalCode and a field for state itself. Overriding the object toString will give pretty output in our console.
public class State {
private int stateId;
private String abbreviation;
private String postalCode;
private String state;
//getters and setters omitted
} Creating StateRowMapper
[1:12]
The class that does the heavy lifting is StateRowMapper which implements spring's RowMapper. It's primary responsibility is to map each row of the result set object to the State POJO object. Since we know that we will reuse it in a couple of our examples we create an inner class but could of been substituted with an anonymous inner class when calling jdbcTemplate.
public class StateRowMapper implements RowMapper<State> {
@Override
public State mapRow(ResultSet rs, int rowNum) throws SQLException {
State state = new State();
state.setStateId(rs.getInt("STATEID"));
state.setAbbreviation(rs.getString("ABBREVIATION"));
state.setPostalCode(rs.getString("POSTALCODE"));
state.setState(rs.getString("STATE"));
return state;
}
}Map a single object
[1:31]
To map a single result row to our java object State we will call jdbcTemplate.queryForObject passing in the sql to execute and the StateRowMapper. Running the junit test the output shows that the sql is executed and the State was logged.
@Test
public void query_for_one_state() {
String sql = "SELECT * from STATE WHERE STATEID = " + 1;
State state = jdbcTemplate.queryForObject(sql, new StateRowMapper());
LOGGER.info(state);
assertEquals("AL", state.getPostalCode());
}Output
Executing SQL query [SELECT * from STATE WHERE STATEID = 1]
2015-02-08 07:11:01.510 DEBUG 44056 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Fetching JDBC Connection from DataSource
2015-02-08 07:11:01.517 DEBUG 44056 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Returning JDBC Connection to DataSource
2015-02-08 07:11:01.518 INFO 44056 --- [ main] com.levelup.ApplicationTests : State [stateId=1, abbreviation=Ala, postalCode=AL, state=Alabama]Map a list of objects
[1:46]
To map a list of records is very similar. Instead of calling jdbcTemplate.queryForObject using jdbcTemplate.query will execute a state query that will return all states in our table, then it will map each row to a State java object via a StateRowMapper. Again, running our unit test will show that the size of the states list is three.
@Test
public void query_for_list_states () {
String sql = "SELECT * from STATE";
List<State> states = jdbcTemplate.query(sql, new StateRowMapper());
LOGGER.info(states);
assertEquals(3, states.size());
}Output
2015-02-08 07:20:53.521 DEBUG 44303 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL query [SELECT * from STATE]
2015-02-08 07:20:53.522 DEBUG 44303 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Fetching JDBC Connection from DataSource
2015-02-08 07:20:53.528 DEBUG 44303 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Returning JDBC Connection to DataSource
2015-02-08 07:20:53.528 INFO 44303 --- [ main] com.levelup.ApplicationTests : [State [stateId=1, abbreviation=Ala, postalCode=AL, state=Alabama], State [stateId=2, abbreviation=Wis, postalCode=WI, state=Wisconsin], State [stateId=4, abbreviation=Hawaii, postalCode=HI, state=Hawaii]]
2015-02-08 07:20:53.528 DEBUG 44303 --- [ main] .s.t.c.j.SqlScriptsTestExecutionListener : Processing [MergedSqlConfig@6e35bc3d dataSource = '', transactionManager = '', transactionMode = INFERRED, encoding = '', separator = ';', commentPrefix = '--', blockCommentStartDelimiter = '/*', blockCommentEndDelimiter = '*/', errorMode = FAIL_ON_ERROR] for execution phase [AFTER_TEST_METHOD] and test context [DefaultTestContext@71423665 testClass = ApplicationTests, testInstance = com.levelup.ApplicationTests@20398b7c, testMethod = query_for_list_states@ApplicationTests, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@6fc6f14e testClass = ApplicationTests, locations = '{}', classes = '{class com.levelup.Application}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.boot.test.SpringApplicationContextLoader', parent = [null]]].While this tutorial shows the basics of mapping rows of a ResultSet to java object there is much, much more.
Thanks for joining in today's level up, have a great day!