Java使用RowMapper将jdbc结果集Map到java对象

x33g5p2x  于4个月前 转载在 Java  
字(4.8k)|赞(0)|评价(0)|浏览(159)

对象关系Map器允许您将较低级别的sql查询Map到java对象。虽然spring的JdbcTemplate不被视为ORM,但让我们看看如何将sql结果集Map到域对象。

介绍

JdbcTemplate是spring-jdbc中的一个核心类,它简化了处理资源管理(如关闭和创建连接)的数据访问调用。它是mybatis or ibatishibernate的轻量级替代品,特别是如果您已经在spring环境中工作的话。由于与较低级别的结果集相比,管理业务对象列表更容易,让我们了解如何将jdbc行Map到域对象。

入门

根据spring引导教程,我们将从spring initializr website设置并下载一个spring项目。选择数据将添加spring-boot-starter-jdbc工件。接下来,我们将在pom.xml中添加HyperSQL DataBase依赖项。正如在如何在spring教程中测试数据库中提到的那样,spring boot将识别类路径上的HSQLDB类,并自动配置嵌入式数据库。

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>runtime</scope>
</dependency>

正在创建数据库设置

使用spring @Sql@SqlGroup注解,我们将调用一个脚本来创建STATE表,并预加载数据库表,插入三条记录。

@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;

正在创建域

为了Mapjdbc resultset,我们需要创建一个State对象,其中stateId属性是主键,abbreviation是状态,m1n是o1p,而state本身是一个字段。覆盖对象toString将在控制台中提供很好的输出。

public class State {

    private int stateId;
    private String abbreviation;
    private String postalCode;
    private String state;

    //getters and setters omitted 

}

创建StateRowMapper

执行繁重工作的类是StateRowMapper,它实现了spring的RowMapper。它的主要职责是将结果集对象的每一行Map到State POJO对象。因为我们知道将在几个示例中重用它,所以我们创建了一个内部类,但在调用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单个对象

要将单个结果行Map到java对象State,我们将调用传入sql以执行的jdbcTemplate.queryForObjectStateRowMapper。运行junit测试,输出显示sql已执行,State已记录。

@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());
}

输出

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对象列表

Map记录列表非常类似。不是使用jdbcTemplate.query调用jdbcTemplate.queryForObject,而是执行一个状态查询,该查询将返回表中的所有状态,然后它将通过StateRowMapper将每一行Map到State java对象。同样,运行单元测试将显示状态列表的大小为三。

@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());

}

输出

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 [[email protected] dataSource = '', transactionManager = '', transactionMode = INFERRED, encoding = '', separator = ';', commentPrefix = '--', blockCommentStartDelimiter = '/*', blockCommentEndDelimiter = '*/', errorMode = FAIL_ON_ERROR] for execution phase [AFTER_TEST_METHOD] and test context [[email protected] testClass = ApplicationTests, testInstance = [email protected], testMethod = [email protected], testException = [null], mergedContextConfiguration = [[email protected] testClass = ApplicationTests, locations = '{}', classes = '{class com.levelup.Application}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.boot.test.SpringApplicationContextLoader', parent = [null]]].

虽然本教程介绍了将ResultSet的行Map到java对象的基本知识,但还有很多很多。
感谢您加入今天的升级,祝您度过美好的一天!

github:View the source上提供了此页面上的所有代码

相关文章

微信公众号

最新文章

更多