How exactly work the Spring RowMapper interface?

问题:

I am studying for the Spring Core certification and I have some doubts about how Spring handle the JDBC queries:

So I know that I can obtain data from my DB tables in various ways depending on the type of data that I expect to obtain:

1) Query for simple type (as an int, a long or a String): I use the queryForObject() method of the jdbcTemplate class, something like it:

String sql = "SELECT count(*) FROM T_REWARD";
int rowsNumber = jdbcTemplate.queryForObject(sql, Integer.class);

So to obtain a simple object as an int value I use the queryForObject() method passing to it the sql stattment and the object type of I expect to receive in output from the method.

Ok, this is pretty simple and I think that it is ok.

2) Query for an entire table row putted into a Map object: so if I need no a single value (that could be into a single column of a specific row of a table or something like the preovious example) I can use the queryForMap(..) and the queryForList() methods, in these way:

2.1) queryForMap(): I use it if I expect a single row putted into a single Map object where each columns value is mapped into a of my Map, something like:

String sql = "select * from T_REWARD where CONFIRMATION_NUMBER = ?";
Map<String, Object> values = jdbcTemplate.queryForMap(sql,confirmation.getConfirmationNumber());

2.2) queryForList(): I use it if I expect more rows as output of my query. So I will obtain a list of Map objects where each Map object represent a specific row of the query output. Something like it:

String sql = “select * from PERSON”;
return jdbcTemplate.queryForList(sql);

I think that also this is pretty clear.

Then I can map a ResultSet into a domain object using JdbcTemplate and this is not so clear for me.

Reading the documentation is told that JdbcTemplate supports this using a callback approach. What exactly meand this callback approach ?

I know that Spring provides a RowMapper interface for mapping a single row of a ResultSet to an object:

public interface RowMapper<T> {
    T mapRow(ResultSet rs, int rowNum)
    throws SQLException;
}

and I have the following example compoese by this method that use a new RestaurandRowMapper object as returned object of a queryForObject() method:

public Restaurant findByMerchantNumber(String merchantNumber) {
    String sql = "select MERCHANT_NUMBER, NAME, BENEFIT_PERCENTAGE, BENEFIT_AVAILABILITY_POLICY from T_RESTAURANT where MERCHANT_NUMBER = ?";

    return jdbcTemplate.queryForObject(sql, new RestaurantRowMapper(), merchantNumber);

and this inner class:

class RestaurantRowMapper implements RowMapper<Restaurant> {
    public Restaurant mapRow(ResultSet rs, int i) throws SQLException {
        return mapRestaurant(rs);
    }
}

that use this private method to create the mapping:

private Restaurant mapRestaurant(ResultSet rs) throws SQLException {
    // get the row column data
    String name = rs.getString("NAME");
    String number = rs.getString("MERCHANT_NUMBER");
    Percentage benefitPercentage = Percentage.valueOf(rs.getString("BENEFIT_PERCENTAGE"));
    // map to the object
    Restaurant restaurant = new Restaurant(number, name);
    restaurant.setBenefitPercentage(benefitPercentage);
    restaurant.setBenefitAvailabilityPolicy(mapBenefitAvailabilityPolicy(rs));
    return restaurant;
}

So I have some difficulties to understand how exactly work all this stuff.

My main doubt is the following one: I know that using the queryForObject() method I pass to it, as input parameter, the type of the object that I expect as output (for example an Interger or a Long).

If I expect to obtain a domain object that represent an entire row of a table (for example a row of the Restaurant table mapped into a Restaurand object) I'm thinking that I should use this object (as the Restaurant object) but in the previous example I use the **row mapper object instead the domain object:

return jdbcTemplate.queryForObject(sql, new RestaurantRowMapper(), merchantNumber);

This inner class contain only the mapRow() method that return the expected domain object

class RestaurantRowMapper implements RowMapper<Restaurant> {
    public Restaurant mapRow(ResultSet rs, int i) throws SQLException {
        return mapRestaurant(rs);
    }
}

So I think that Spring automatically call the mapRow() method that return the Restaurand domain object that is automatically replaced into the queryForObject() method, or something like this. But I am not so sure about it exactly work.

What am I missing? Can you explain me what exactly happen in the backstage?

Tnx

回答1:

The queryForObject method looks like this:

public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
    List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
    return DataAccessUtils.requiredSingleResult(results);
}

The queryForObject-method internally invokes the method query on the JdbcTemplate object. The query-method is defined as:

public <T> T query(
        PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
        throws DataAccessException;

As you can see, a ResultSetExtractor<T> is passed to the query-method and Spring conveniently converts your RowMapper<T> to an object of type new RowMapperResultSetExtractor<T>(rowMapper, 1). The RowMapperResultSetExtractor is the object that holds the key to the magic. When the object is invoked it iterates all rows as per this snippet:

public List<T> extractData(ResultSet rs) throws SQLException {
    List<T> results = (this.rowsExpected > 0 ? new ArrayList<T>(this.rowsExpected) : new ArrayList<T>());
    int rowNum = 0;
    while (rs.next()) {
        results.add(this.rowMapper.mapRow(rs, rowNum++));
    }
    return results;
}

So, your original RowMapper is the callback that is called for each row. Furthermore, as you can see here your RowMapper is invoked for all matching results and the Restaurant-object that you created is added to the result list. However, since you query for only one object the following statement is finally used to return your single Restaurant object.

        return DataAccessUtils.requiredSingleResult(results);

So, to sum up: JdbcTempalte implementes the Strategy Pattern (which is similar to the Template method pattern). And by providing a Strategy interface (the RowMapper) you can let JdbcTemplate do the heavy lifting for you (handling exceptions, connections etc). The RowMapper maps each hit as a POJO (the Restaurant) and all hits are collected to a List. The method queryForObject then takes the first row from that List and returns it to the caller. The return value is based on the generic type of the RowMapper which in your case is Restaurant.

回答2:

Use jdbcTemplate.queryForObject it will resolve your problem like this

public YourPojo getDatabaseDetails(int masterId) {

    sql = "Select * FROM <table_name> where ID=?";
    YourPojo pojo = (YourPojo) jdbcTemplate.queryForObject(sql,
            new Object[] { masterId }, new RowMapper<YourPojo>() {
                @Override
                public <YourPojo> mapRow(ResultSet rs, int rowNum)
                        throws SQLException {
                    YourPojo pojo2 = new YourPojo();
                    pojo2.setAccountId(rs.getString("AccountId"));
                    pojo2.setAccountName(rs.getString("AccountName"));
                    pojo2.setAccountCRN(rs.getString("AccountCRN"));
                    pojo2.setAccountStatus(rs.getString("AccountStatus"));
                    return pojo2;
                }
            });
    return pojo;
}