Spring JDBC征服数据库

时间:2021-09-23 01:23:22

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注解当它置于方法前时表示该方法使用事务处理,当它置于类前时表示该类的所有方法都使用事务处理。