标签： JdbcTemplate

1. 前言

Spring 除了 Mybatis 外同样支持 JPA 和 JdbcTemplate 等的数据映射框架。这里简单给一个关于 JdbcTemplate 的示例。

JdbcTemplate 的功能当然不如 MyBatis 来的强大，但是如果偏爱 Java 代码手动做对象映射的可以试一试，下面不多废话，直接上代码。

2. 实例配置

首先给出需要引入的依赖：

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

然后自定义数据源 dataSource 和 jdbcTemplate：

  package com.avaloninc.springjdbctemplateexample.conf;
  ​
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.context.annotation.Bean;
  import org.springframework.context.annotation.Configuration;
  import org.springframework.context.annotation.PropertySource;
  import org.springframework.core.env.Environment;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  import org.springframework.jdbc.datasource.DriverManagerDataSource;
  import org.springframework.transaction.PlatformTransactionManager;
  import org.springframework.transaction.annotation.EnableTransactionManagement;
  ​
  import javax.sql.DataSource;
  ​
  @Configuration
  @EnableTransactionManagement
  @PropertySource(value = "classpath:application.properties")
  public class AppConfig {
  ​
    @Autowired
    private Environment env;
  ​
    @Bean(name = "dataSource")
    public DataSource dataSource() {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      String                  url        = env.getProperty("jdbc.url");
      String                  userName   = env.getProperty("jdbc.username");
      String                  password   = env.getProperty("jdbc.password");
      dataSource.setUrl(url);
      dataSource.setUsername(userName);
      dataSource.setPassword(password);
      return dataSource;
    }
  ​
    @Bean
    public PlatformTransactionManager dataSourceTransactionManager() {
      return new DataSourceTransactionManager(dataSource());
    }
  ​
    @Bean
    public JdbcTemplate jdbcTemplate() {
      JdbcTemplate jdbcTemplate = new JdbcTemplate();
      jdbcTemplate.setDataSource(dataSource());
      return jdbcTemplate;
    }
  }

这段代码中，我们通过注入 Environment 对象来实现从配置文件中获取数据库的连接信息。

3. 实例代码

先给出模型类：

  package com.avaloninc.springjdbctemplateexample.domain;
  ​
  import lombok.Data;
  ​
  @Data
  public class Person {
    private int    id;
    private String name;
    private int    age;
    private Gender gender;
  ​
    public enum Gender {
      MALE,
      FEMALE;
    }
  }
  ​

然后给定两个简单的读写接口的实现：

  package com.avaloninc.springjdbctemplateexample.service;
  ​
  import com.avaloninc.springjdbctemplateexample.domain.Person;
  ​
  public interface PersonService {
    /**
     * Insert int.
     *
     * @param person the person
     * @return the int
     */
    int insert(Person person);
  ​
    /**
     * Gets person by id.
     *
     * @param id the id
     * @return the person by id
     */
    Person getPersonById(int id);
  }
  ​

  package com.avaloninc.springjdbctemplateexample.service;
  ​
  import com.avaloninc.springjdbctemplateexample.domain.Person;
  import com.avaloninc.springjdbctemplateexample.domain.Person.Gender;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.core.RowMapper;
  import org.springframework.jdbc.support.GeneratedKeyHolder;
  import org.springframework.stereotype.Service;
  ​
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  ​
  @Service
  public class PersonServiceImpl implements PersonService {
  ​
    @Autowired
    private JdbcTemplate jdbcTemplate;
  ​
    @Override
    public int insert(Person person) {
      GeneratedKeyHolder holder = new GeneratedKeyHolder();
      int rowCount = this.jdbcTemplate.update(
          connection -> {
            PreparedStatement statement = connection.prepareStatement(
                "insert into person (name, age, gender) values (?, ?,?)",
                Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, person.getName());
            statement.setInt(2, person.getAge());
            statement.setInt(3, person.getGender().ordinal());
            return statement;
          },
          holder);
      person.setId(holder.getKey().intValue());
      return rowCount;
    }
  ​
    @Override
    public Person getPersonById(int id) {
      return this.jdbcTemplate.queryForObject("select id, name, age, gender from person where id = ?",
                                              new Object[] {id}, new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
              Person person = new Person();
              person.setId(rs.getInt("id"));
              person.setName(rs.getString("name"));
              person.setAge(rs.getInt("age"));
              person.setGender(Gender.values()[rs.getInt("gender")]);
              return person;
            }
          });
    }
  }
  ​

完全手动实现了对象的映射，对于复杂类型的数据反序列化，不必借助 MyBatis 的 TypeHandler，直接代码实现即可。

4. 单元测试

单元测试走一发，确认有效：

  package com.avaloninc.springjdbctemplateexample.service;
  ​
  import com.avaloninc.springjdbctemplateexample.domain.Person;
  import com.avaloninc.springjdbctemplateexample.domain.Person.Gender;
  import junit.framework.TestCase;
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.boot.test.context.SpringBootTest;
  import org.springframework.test.context.junit4.SpringRunner;
  ​
  @SpringBootTest
  @RunWith(SpringRunner.class)
  public class PersonServiceImplTest extends TestCase {
  ​
    @Autowired
    private PersonService personService;
  ​
    @Test
    public void test() {
      Person person = new Person();
      person.setName("John");
      person.setAge(28);
      person.setGender(Gender.MALE);
  ​
      int count = personService.insert(person);
      assertTrue(count > 0);
      assertTrue(person.getId() > 0);
      Person personById = personService.getPersonById(person.getId());
      assertNotNull(personById);
      assertEquals(person, personById);
    }
  }