Spring 与 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>

然后自定义数据源 dataSourcejdbcTemplate

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注