Spring JdbcTemplate中关于RowMapper的使用实例

在spring boot 集成使用jdbctemplate，首先在pom文件中引入相应的依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.34</version>
            <scope>runtime</scope>
        </dependency>

<!--整合druid数据源-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>

yml中配置数据源：

#druid数据源
    druid:
      username: root
      password: 12345
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/corporationmanagement?useUnicode=true&characterEncoding=utf8
      initialSize: 1
      minIdle: 1
      maxActive: 5
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

 

本文就来先解释下什么是Row Mapper，我们该如何正确地使用。

    @Override
    public Student getStudentByName(String name) {
        String sql = "select name, gender from test_student where name = ?";
        Student student = this.jdbcTemplate.queryForObject(sql, new Object[]{name}, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int i) throws SQLException {
                Student s = new Student();
                s.setName(rs.getString("name"));
                s.setGender(rs.getString("gender"));
                return s;
            }
        });
        return student;
    }

    @Override
    public List<Student> getStudentsByName(String name) {
        String sql = "select name, gender from test_student where name = ?";
        List<Student> students = this.jdbcTemplate.query(sql, new Object[]{name}, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int i) throws SQLException {
                Student s = new Student();
                s.setName(rs.getString("name"));
                s.setGender(rs.getString("gender"));
                return s;
            }
        });
        return students;
    }

 

一、RowMapper的基本使用

使用过原生JDBC的朋友应该知道，从数据库查询出来的记录全都被保存在ResultSet结果集中，我们需要将结果集中的数据一条条地获取并设置到具体的实体类上，如此，该实体类才能在接下来的程序中使用。然而问题是，每次都要这么操作实在是太麻烦了，Spring就不应该提供什么功能来替我们做这些事情吗？答案当然是有的，那就是本文的主角——RowMapper。

Spring JDBC中目前有两个主要的RowMapper实现，使用它们应该能解决大部分的场景了。

1.1 SingleColumnRowMapper

通过名字我们就能大概了解，在查询返回单列数据的时候，就该使用这个RowMapper，下面我们来看看具体的代码：

    @Override
    public String getStudentNameById(String id) {
        String sql = "select name from test_student where id = ?";
        return this.jdbcTemplate.queryForObject(sql, new Object[]{id},
                new SingleColumnRowMapper<>(String.class));
    }

    @Override
    public List<String> getStudentNamesByGrade(Integer grade) {
        String sql = "select name from test_student where grade = ?";
        return this.jdbcTemplate.query(sql, new Object[]{grade},
                new SingleColumnRowMapper<>(String.class));
    }

    @Test
    public void getStudentNameById(){
        String name = studentService.getStudentNameById("3");
        assertEquals("李清照", name);
    }

    @Test
    public void getStudentNamesByGrade(){
        List<String> names = studentService.getStudentNamesByGrade(1);
        assertTrue(2 == names.size());
    }

 

1.2 BeanPropertyRowMapper

当查询数据库返回的是多列数据，且你需要将这些多列数据映射到某个具体的实体类上，那么就该使用这个Row Mapper，下面是具体的使用代码：

    @Override
    public Student getStudentByName2(String name) {
        String sql = "select name, gender from test_student where name = ?";
        return this.jdbcTemplate.queryForObject(sql, new Object[]{name},
                new BeanPropertyRowMapper<>(Student.class));
    }

    @Override
    public List<Student> getStudentsByName2(String name) {
        String sql = "select name, gender from test_student where name = ?";
        return this.jdbcTemplate.query(sql, new Object[]{name},
                new BeanPropertyRowMapper<>(Student.class));
    }

    @Test
    public void getStudentByName2(){
        Student student = studentService.getStudentByName2("杜甫");
        assertEquals("杜甫",student.getName());
        assertEquals("男",student.getGender());
    }

    @Test
    public void getStudentsByName2(){
        List<Student> studentList = studentService.getStudentsByName("Jack");
        assertTrue(2 == studentList.size());
    }

 

这种使用方式有一个前提，那就是数据库SQL查出来的数据其列名与实体类中的属性名是一致的，当然个数和顺序可以不一致。比如数据库SQL查出来的姓名列叫name，那么对应的实体类中的姓名也必须叫name，而不能叫studentName或者其它。

二、定义自己的RowMapper

当然，如果你SQL查询出来的数据列名就是和实体类的属性名不一样，或者想按照自己的规则来装配实体类，那么就可以定义并使用自己的Row Mapper。

public class StudentRowMapper implements RowMapper<Student> {

    @Override
    public Student mapRow(ResultSet rs, int i) throws SQLException {
        Student student = new Student();
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setEmail(rs.getString("email"));
        return student;
    }
}

    @Override
    public Student getStudentByName3(String name) {
        String sql = "select name, gender, email from test_student where name = ?";
        return this.jdbcTemplate.queryForObject(sql, new Object[]{name}, new StudentRowMapper());
    }

    @Override
    public List<Student> getStudentsByName3(String name) {
        String sql = "select name, gender, email from test_student where name = ?";
        return this.jdbcTemplate.query(sql, new Object[]{name}, new StudentRowMapper());
    }

    @Test
    public void getStudentByName3(){
        Student student = studentService.getStudentByName3("杜甫");
        assertEquals("杜甫",student.getName());
        assertEquals("男",student.getGender());
        assertEquals("dufu@tang", student.getEmail());
    }

    @Test
    public void getStudentsByName3(){
        List<Student> studentList = studentService.getStudentsByName3("李白");
        assertTrue(2 == studentList.size());
    }

 

作者：力羊
链接：https://www.jianshu.com/p/be60a81e2fe7
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。