Spring JDBC征服数据库

Spring将数据访问过程中固定的和可变的部分明确划分为两个不同的类，模板(template)和回调(callback)。模板管理过程中固定的部分，回调处理自定义的数据访问代码。

Spring的模板类处理数据访问的固定部分--事务控制、管理资源以及处理异常。同时，应用程序相关的数据访问--语句、绑定参数以及整理结果集在回调的实现中处理。

以Spittr这个应用来演示JDBC。

我们先定义Spitter和Spittle两个实体类

package spittr.domain;
public class Spitter {

    private Long id;
    private String username;
    private String password;
    private String fullname;
    private String email;
    private boolean updateByEmail;

    public Spitter(Long id, String username, String password, String fullname, String email, boolean updateByEmail) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.fullname = fullname;
        this.email = email;
        this.updateByEmail = updateByEmail;
    }

    public Long getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public String getFullname() {
        return fullname;
    }

    public String getEmail() {
        return email;
    }

    public boolean isUpdateByEmail() {
        return updateByEmail;
    }


}

package spittr.domain;

import java.util.Date;

public class Spittle {

    private final Long id;
    private final Spitter spitter;
    private final String message;
    private final Date postedDate;

    public Spittle(Long id, Spitter spitter, String message, Date postedDate) {
        this.id = id;
        this.spitter = spitter;
        this.message = message;
        this.postedDate = postedDate;
    }

    public Long getId() {
        return id;
    }

    public Spitter getSpitter() {
        return spitter;
    }

    public String getMessage() {
        return message;
    }

    public Date getPostedDate() {
        return postedDate;
    }
}

然后定义SpittleRepository、SpitterRepository两个接口。

package spittr.db;
        import spittr.domain.Spittle;

        import java.util.List;

public interface SpittleRepository {
    long count();

    List<Spittle> findRecent();

    List<Spittle> findRecent(int count);

    Spittle findOne(long id);

    Spittle save(Spittle spittle);

    List<Spittle> findBySpitterId(long id);

    void delete(long id);

}

package spittr.db;

import spittr.domain.Spitter;

import java.util.List;

public interface SpitterRepository {

    long count();

    Spitter save(Spitter spitter);

    Spitter findOne(long id);

    Spitter findByUsername(String username);

    List<Spitter> findAll();
}

Spring的JDBC框架承担了资源管理和异常处理的工作，从而简化了JDBC代码，让我们只需编写从数据库读写数据的必要代码。

Spring为JDBC提供了三个模板供选择：

JdbcTemplate:最基本的Spring JDBC 模板，支持简单的JDBC数据库访问功能以及基于索引参数的查询。

NamedParameterJdbcTemplate：使用该模板类进行查询时可以将值以命名参数的形式绑定到SQL中，而不是简单的使用索引参数。

对于大多数JDBC任务来说，JdbcTemplate就是最好的方案。

我们定义JdbcSpitterRepository实现SpitterRepository接口，定义JdbcSpittleRepository实现SpittleRepository接口。

package spittr.db.jdbc;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import spittr.db.SpitterRepository;
import spittr.domain.Spitter;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcSpitterRepository implements SpitterRepository {

    private JdbcTemplate jdbcTemplate;

    private static final String INSERT_SPITTER = "insert into Spitter(username,password,fullname,email,updateByEmail) values(?,?,?,?,?) ";
    private static final String SELECT_SPITTER = "select id,username,password,fullname,email,updateByEmail from Spitter ";

    public JdbcSpitterRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }



    @Override
    public Spitter save(Spitter spitter) {
        Long id = spitter.getId();
        if(id == null){
            long spitterId = insertSpitterAndReturnId(spitter);
            return new Spitter(spitterId,spitter.getUsername(),spitter.getPassword(),spitter.getFullname(),spitter.getEmail(),spitter.isUpdateByEmail());
        }
        else{
         jdbcTemplate.update("update Spitter set username=?, password=?, fullname=?, email=?, updateByEmail=? where id =? ",
                 spitter.getUsername(),
                 spitter.getPassword(),
                 spitter.getFullname(),
                 spitter.getEmail(),
                 spitter.isUpdateByEmail(),
                 id);
        }

        return spitter;
    }

    private long insertSpitterAndReturnId(Spitter spitter){
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Spitter");
        jdbcInsert.setGeneratedKeyName("id");
        Map<String, Object>args = new HashMap<>();
        args.put("username",spitter.getUsername());
        args.put("password",spitter.getPassword());
        args.put("fullname",spitter.getFullname());
        args.put("email",spitter.getEmail());
        args.put("updateByEmail",spitter.isUpdateByEmail());
        long spitterId = jdbcInsert.executeAndReturnKey(args).longValue();
        return spitterId;
    }

    public void insertSpitter(Spitter spitter){
        jdbcTemplate.update(INSERT_SPITTER,
                spitter.getUsername(),
                spitter.getPassword(),
                spitter.getFullname(),
                spitter.getEmail(),
                spitter.isUpdateByEmail());
    }

    @Override
    public Spitter findOne(long id) {

        return jdbcTemplate.queryForObject(SELECT_SPITTER "where id = ?",new SpitterRowMapper(),id);
    }

    @Override
    public List<Spitter> findAll() {
        return jdbcTemplate.query(SELECT_SPITTER "order by id",new SpitterRowMapper());
    }

    @Override
    public long count() {
        return jdbcTemplate.queryForLong("select count(id) from spitter");
    }

    @Override
    public Spitter findByUsername(String username) {
        return jdbcTemplate.queryForObject(SELECT_SPITTER "where username = ?",new SpitterRowMapper(),username);
    }

    private static final class SpitterRowMapper implements RowMapper<Spitter>{
        @Override
        public Spitter mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Spitter(rs.getLong("id"),rs.getString("username"),rs.getString("password"),
                    rs.getString("fullname"),rs.getString("email"),rs.getBoolean("updateByEmail"));

        }
    }
}

package spittr.db.jdbc;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import spittr.db.SpittleRepository;
import spittr.domain.Spitter;
import spittr.domain.Spittle;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcSpittleRepository implements SpittleRepository {

    private static final String SELECT_SPITTLE = "select sp.id, s.id as spitterId, s.username, s.password, s.fullname, s.email, s.updateByEmail, sp.message, sp.postedTime from Spitter s, Spittle sp where s.id = sp.spitter";

    private static final String SELECT_SPITTLE_BY_ID = SELECT_SPITTLE " and sp.id=?";

    private static final String SELECT_SPITTLE_BY_SPITTER_ID = SELECT_SPITTLE " and s.id=? order by sp.postedTime desc";

    private static final String SELECT_RECENT_SPITTLES = SELECT_SPITTLE " order by sp.postedTime desc limit ?";

    private JdbcTemplate jdbcTemplate;

    public JdbcSpittleRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public long count() {
        return jdbcTemplate.queryForLong("select count(id) from Spittle");
    }

    @Override
    public Spittle findOne(long id) {

        try {
            return jdbcTemplate.queryForObject(SELECT_SPITTLE_BY_ID,new SpittleRowMapper(),id);
        } catch (DataAccessException e) {
            return null;
        }

    }

    @Override
    public Spittle save(Spittle spittle) {
        long spittleId = insertSpittleAndReturnId(spittle);
        return new Spittle(spittleId,spittle.getSpitter(),spittle.getMessage(),spittle.getPostedDate());
    }

    @Override
    public List<Spittle> findBySpitterId(long id) {
        return jdbcTemplate.query(SELECT_SPITTLE_BY_SPITTER_ID,new SpittleRowMapper(),id);
    }

    @Override
    public List<Spittle> findRecent() {
        return findRecent(10);
    }

    @Override
    public List<Spittle> findRecent(int count) {
        return jdbcTemplate.query(SELECT_RECENT_SPITTLES,new SpittleRowMapper(),count);
    }

    @Override
    public void delete(long id) {
        jdbcTemplate.update("delete from Spittle where id = ?",id);
    }

    private long insertSpittleAndReturnId(Spittle spittle){
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Spittle");
        jdbcInsert.setGeneratedKeyName("id");
        Map<String,Object> args = new HashMap<>();
        args.put("spitter",spittle.getSpitter().getId());
        args.put("message",spittle.getMessage());
        args.put("postedTime",spittle.getPostedDate());
        long spittleId = jdbcInsert.executeAndReturnKey(args).longValue();
        return spittleId;
    }

    private static final class SpittleRowMapper implements RowMapper<Spittle> {

        @Override
        public Spittle mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Spittle(rs.getLong("id"),
                    new Spitter(rs.getLong("spitterId"),rs.getString("username"),
                            rs.getString("password"),rs.getString("fullname"),
                            rs.getString("email"),rs.getBoolean("updateByEmail")),
                    rs.getString("message"),rs.getTimestamp("postedTime"));
        }
    }
}

可以看到Spring中JDBC将获取连接，捕获异常都封装在JdbcTemplate中让我们更关注数据操作代码。对于增删改查四个方面，JdbcTemplate都为我们封装了一系列的函数。

SimpleJdbcInsert 可以简化我们的插入操作，指定一个数据库中的表名，然后传入一个Map，Key为数据表中的表项名，Value为插入的值，并可以指定一个返回的值。

通过update方法来实现删改。

查询基本类型例如Long等有对应的queryForLong方法等，若要将查询结果处理成为对象或对象集合，我们需要实现RowMapper接口实现将查询结果的一行映射为对象。

其实以上的代码我们并不能运行，因为我们还没有指定数据库数据源我们写一个JdbcConfig配置类。

package spittr.jdbc;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.transaction.PlatformTransactionManager;
import spittr.db.jdbc.JdbcSpitterRepository;
import spittr.db.jdbc.JdbcSpittleRepository;

import javax.sql.DataSource;

@Configuration
public class JdbcConfig {

    @Bean
    public DataSource dataSource(){
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScripts("classpath:spittr/db/jdbc/schema.sql","classpath:spittr/db/jdbc/test-data.sql")
                .build();
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public JdbcSpitterRepository spitterRepository(JdbcTemplate jdbcTemplate){
        return new JdbcSpitterRepository(jdbcTemplate);
    }

    @Bean
    public JdbcSpittleRepository spittleRepository(JdbcTemplate jdbcTemplate){
        return new JdbcSpittleRepository(jdbcTemplate);
    }

    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }
}

我们使用EmbeddedDatabaseBuilder来新建一个嵌入式数据库，这里我们选择H2，addScripts方法可以执行我们写好的SQL脚本。

将数据源注入到JdbcTemplate就可以使用它了，是不是很方便？？

我们还可以使用DriverManagerDataSource来配置基于JDBC驱动的数据源。

我们定义了dataSource、jdbcTemplate、spitterRepository、spittleRepository和transactionManager五个bean，dataSource bean 用来注入到jdbcTemplate bean中，jdbcTemplate bean 用来注入到用到它的repository实现中。spitterRepository和spittleRepository bean 用来注入到使用到它们的类。transactionManager bean 用来实现 事务管理，这里我们没有用到它。

下面是测试类的一部分。

package spittr.jdbc;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import spittr.db.jdbc.JdbcSpitterRepository;
import spittr.domain.Spitter;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes=JdbcConfig.class)
public class JdbcSpitterRepositoryTest {

  @Autowired
  JdbcSpitterRepository spitterRepository;

  @Test
  public void count() {
    assertEquals(4, spitterRepository.count());
  }

  @Test
  @Transactional
  public void findAll() {
    List<Spitter> spitters = spitterRepository.findAll();
    assertEquals(4, spitters.size());
    assertSpitter(0, spitters.get(0));
    assertSpitter(1, spitters.get(1));
    assertSpitter(2, spitters.get(2));
    assertSpitter(3, spitters.get(3));
  }
}

我们使用JdbcConfig类配置应用上下文，自动装配相应的repository实现。

这里需要注意的是@Transactional注解当它置于方法前时表示该方法使用事务处理，当它置于类前时表示该类的所有方法都使用事务处理。