ref:Spring JDBC框架

ref:https://blog.csdn.net/u011054333/article/details/54772491

Spring JDBC简介

先来看看一个JDBC的例子。我们可以看到为了执行一条SQL语句，我们需要创建连接，创建语句对象，然后执行SQL，然后操纵结果集获取数据。

try(Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)){
    List<User> users = new ArrayList<>();
    try (Statement statement = connection.createStatement()) {
        try (ResultSet rs = statement.executeQuery("SELECT *FROM user")) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setNickname(rs.getString(4));
                user.setBirthday(rs.getDate(5));
                users.add(user);
            }
        }
    }
}

其实这些步骤中有很多步骤都是固定的，Spring JDBC框架将这些操作封装起来， 我们只需要关注业务逻辑点即可。在Spring JDBC框架中，我们要做的事情如下：

• 定义连接字符串参数。
• 指定SQL语句。
• 声明参数和参数类型。
• 每次迭代结果集的操作。

Spring会帮我们完成以下事情：

• 打开连接。
• 准备和执行SQL语句。
• 在需要的情况下迭代结果集。
• 处理异常。
• 操作事务。
• 关闭结果集、语句和数据库连接。

使用JdbcTemplate

JdbcTemplate是Jdbc框架最重要的类，提供了较为底层的Jdbc操作。其它几个类都是在JdbcTemplate基础上封装了相关功能。

添加依赖

要在Gradle项目中使用Spring JDBC框架，添加如下一段。由于Spring JDBC的主要类JdbcTemlate需要一个数据源用来初始化，所以还需要一个数据源的实现。

compile group: 'org.springframework', name: 'spring-jdbc', version: '4.3.5.RELEASE'
compile group: 'org.apache.commons', name: 'commons-dbcp2', version: '2.1.1'

如果要使用Spring框架的其他功能，可能还需要添加对应的依赖。

创建Jdbc Template Bean

首先需要创建一个数据源Bean。为了将配置分离，我们先新建一个jdbc.properties文件。

jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.username=root
jdbc.password=12345678

然后创建一个Spring配置文件jdbc.xml。这里用到了<context:property-placeholder>节点来导入其它配置文件。然后用这些属性创建一个数据源Bean，然后再利用数据源Bean来创建一个JdbcTemplate。

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource"/>
    </bean>
    <context:property-placeholder location="jdbc.properties"/>
</beans>

JdbcTemplate操作

注册了JdbcTemplate之后，就可以将它注入到任何地方来使用了。首先它可以使用execute方法，执行任何SQL语句。这里创建了一个简单的MySQL用户表，只有主键和用户名。

jdbcTemplate.execute("CREATE TABLE user(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) UNIQUE)");

它还可以使用update方法执行增加、更新和删除操作。

jdbcTemplate.update("INSERT INTO user(name) VALUES(?)", "yitian");
jdbcTemplate.update("INSERT INTO user(name) VALUES(?)", "zhang2");
jdbcTemplate.update("UPDATE user SET name=? WHERE name=?", "zhang3", "zhang2");
jdbcTemplate.update("DELETE FROM user WHERE name=?", "zhang3");

查询操作也很简单，使用queryForObject方法，传入SQL字符串和结果类型即可。

int count = jdbcTemplate.queryForObject("SELECT count(*) FROM user", Integer.class);
System.out.println("记录数目是:" + count);
String name = jdbcTemplate.queryForObject("SELECT name FROM user WHERE id=1", String.class);
System.out.println("姓名是:" + name);

如果要查询整条记录也可以。Spring提供了一个接口RowMapper，只需要实现该接口的mapRow方法，即可将结果集的一条记录转化为一个Java对象，该方法的第二个参数是当前行的行数。下面是一个RowMapper实现。

public class UserRowMapper implements RowMapper<User> {

    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new User(rs.getInt(1), rs.getString(2));
    }
}

User实体类对应于上面创建表时简单的用户表（其他方法已省略）。

public class User {
    private int id;
    private String name;
}

实现了RowMapper接口之后，我们就可以查询一条记录并转化为Java对象了。

User user = jdbcTemplate.queryForObject("SELECT id,name FROM user WHERE id=?", new UserRowMapper(), 1);
System.out.println(user);

查询多条记录也可以，这时候需要使用query方法。

List<User> users = jdbcTemplate.query("SELECT id,name FROM usr", new UserRowMapper());
System.out.println(users);

还有一个通用方法queryForList，返回一个List，每一个元素都是一个Map，在Map中存放着列名和值组成的键值对。

List<Map<String, Object>> results = jdbcTemplate.queryForList("SELECT id,name FROM user");
System.out.println(results);

使用NamedParameterJdbcTemplate

前面的JdbcTemplate提供了非常方便的JDBC操作封装，但是在绑定参数的时候只能采用通配符?方式以顺序方式绑定参数。如果SQL语句比较复杂，参数比较多，那么这种方式显得不太方便。因此Spring提供了一个更加方便的类NamedParameterJdbcTemplate，它可以以命名方式绑定SQL语句参数。NamedParameterJdbcTemplate在内部使用一个JdbcTemplate，你也可以调用getJdbcOperations方法获取底层的JdbcTemplate对象，然后用前面的方法进行基本操作。

创建NamedParameterJdbcTemplate和JdbcTemplate相同，只需要传入一个数据源即可。

<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="dataSource"/>
</bean>

NamedParameterJdbcTemplate和JdbcTemplate的大部分操作相同，这里仅介绍绑定命名参数的部分。首先，SQL语句必须使用:参数名称的形式作为参数。然后，我们创建一个MapSqlParameterSource对象，它的内部使用了一个Map保存的命名参数的名称和值。然后我们使用它的addValue方法传递需要的命名参数的名称和值，这个方法还可以接受第三个参数指定参数类型，这个类型以java.sql.Types的一些公共字段的形式给出。最后，将MapSqlParameterSource传递给相应的方法执行即可。

String sql = "SELECT id,name FROM user WHERE name=:name AND id<:user_id";
MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("name", "test");
namedParameters.addValue("user_id", 100, Types.INTEGER);
User user = namedParameterJdbcTemplate.queryForObject(sql, namedParameters, new UserRowMapper());
System.out.println(user);

如果不想创建MapSqlParameterSource对象，还可以直接使用一个Map传递命名参数的名称和值。

Map<String, Object> map = new HashMap<>();
map.put("user_id", 100);
map.put("name", "test");
List<User> users = namedParameterJdbcTemplate.query(sql, map, new UserRowMapper());
System.out.println(users);

上面讨论的MapSqlParameterSource实际上实现了SqlParameterSource接口，上面的几个方法签名也是接受SqlParameterSource接口。这个接口表示用来传递命名参数和值的集合。除了MapSqlParameterSource之外，还有另外一个常用的实现，BeanPropertySqlParameterSource，这个类接受一个Java Bean对象，然后使用Bean的属性名和值作为命名参数的名称和值。这一点需要注意。

User bean = new User(100, "test");
SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(bean);
users = namedParameterJdbcTemplate.query(sql, parameterSource, new UserRowMapper());
System.out.println(users);

使用SimpleJdbc类

前面所说的JdbcTemplate封装了一些功能，让我们方便的使用JDBC。Spring还提供了几个更高级、功能更具体的SimpleJdbc类。这些类会读取JDBC的元数据Metadata，使用起来更加方便。

SimpleJdbcInsert

SimpleJdbcInsert类用来插入数据。简单的使用方法如下。SimpleJdbcInsert需要一个数据源来创建，withTableName方法指定要插入的表名，usingGeneratedKeyColumns指定设置了主键自增的列名。其他使用方法和前面所说的类类似。executeAndReturnKey这个方法很特别，它会将数据插入数据库并返回该条记录对应的自增键。有时候我们可能希望使用自增主键来插入一条数据，由于主键是数据库自动生成的，我们必须再次查询数据库才能获得主键。这种情况下使用executeAndReturnKey非常方便。注意这个方法返回的是java.lang.Number类型，可以调用其XXXvalue方法转换成各种数值。

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
        .withTableName("user")
        .usingGeneratedKeyColumns("id");
User user = new User();
user.setName("test");
Map<String, Object> params = new HashMap<>();
params.put("names", user.getName());
int id = simpleJdbcInsert.executeAndReturnKey(params).intValue();
System.out.println("simpleJdbcInsert" + user);

SimpleJdbcCall

SimpleJdbcCall类用来调用存储过程的。使用方法类似。这里就直接给出Spring官方文档的示例代码了。

MySQL存储过程。

CREATE FUNCTION get_actor_name (in_id INTEGER)
RETURNS VARCHAR(200) READS SQL DATA
BEGIN
    DECLARE out_name VARCHAR(200);
    SELECT concat(first_name, ' ', last_name)
        INTO out_name
        FROM t_actor where id = in_id;
    RETURN out_name;
END;
SimpleJdbcCall调用存储过程。

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcCall funcGetActorName;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.funcGetActorName = new SimpleJdbcCall(jdbcTemplate)
                .withFunctionName("get_actor_name");
    }

    public String getActorName(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        String name = funcGetActorName.executeFunction(String.class, in);
        return name;
    }

    // ... additional methods

}
如果要从存储过程获取记录的话，可以这样。以下是一个MySQL存储过程。

CREATE PROCEDURE read_all_actors()
BEGIN
 SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
END;
相对应的Java代码。

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcCall procReadAllActors;

    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadAllActors = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_all_actors")
                .returningResultSet("actors",
                BeanPropertyRowMapper.newInstance(Actor.class));
    }

    public List getActorsList() {
        Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
        return (List) m.get("actors");
    }

    // ... additional methods

}

将JDBC操作转化为Java对象

org.springframework.jdbc.object包下提供了一组类，让我们用更加面向对象的方式来操作数据库。我们可以将SQL查询转化为一组业务对象，也可以方便的进行查询、更新和执行存储过程的操作。

MappingSqlQuery使用方式

MappingSqlQuery是一个抽象类，继承自SQLQuery。我们在使用这个类的时候需要创建一个自定义类，继承自MappingSqlQuery，然后在其构造方法中初始化一个查询字符串，并在这里设置查询参数；然后需要实现该类的mapRow方法，将结果集的行转化为实体类对象。下面是一个例子。构造方法中定义的查询字符串会被创建为PreparedStatement，因此可以在查询字符串中使用占位符?。对于每个出现的占位符，我们都必须调用declareParameter方法声明参数，该方法接受一个SqlParameter对象，该对象需要参数名和类型两个参数。最后需要调用compile方法编译和准备查询。该类是线程安全的，因此可以安全的在多个线程之间共享对象。

public class UserMappingSqlQuery extends MappingSqlQuery<User> {
    public UserMappingSqlQuery(DataSource ds) {
        super(ds, "SELECT id,name FROM user WHERE id=:id");
        super.declareParameter(new SqlParameter("id", Types.INTEGER));
        compile();
    }

    @Override
    protected User mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new User(rs.getInt(1), rs.getString(2));
    }
}

然后我们创建一个对象，调用findObject方法并传入查询参数，即可获得结果对象。

@Test
public void testMappingSqlQuery() {
    MappingSqlQuery<User> mappingSqlQuery = new UserMappingSqlQuery(dataSource);
    User user = mappingSqlQuery.findObject(1);
    logger.debug(user);
}

如果查询要返回一组记录并传递多个查询参数。需要调用相应的execute方法。一下是另一个MappingSqlQuery，以及其测试代码。

public class UsersMappingSqlQuery extends MappingSqlQuery<User> {
    public UsersMappingSqlQuery(DataSource ds) {
        super(ds, "SELECT id,name FROM user WHERE id<? AND name LIKE ?");
        super.declareParameter(new SqlParameter("id", Types.INTEGER));
        super.declareParameter(new SqlParameter("name", Types.VARCHAR));
        compile();
    }

    @Override
    protected User mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new User(rs.getInt(1), rs.getString(2));
    }
}

//获取多个对象
mappingSqlQuery = new UsersMappingSqlQuery(dataSource);
List<User> users = mappingSqlQuery.execute(100, "test");
logger.debug(users);

使用SqlUpdate

这个类的使用方法和SqlQuery类似，但是由于它是一个具体类，因此不需要定义子类即可使用。下面是它的简单使用方法。为了更新具体的数据（例如一个Java Bean对象），你也可以继承该类，并提供自己的更新方法，就和上面一样。

@Test
public void testSqlUpdate() {
    SqlUpdate sqlUpdate = new SqlUpdate(dataSource, "INSERT INTO user(name) VALUES(?)");
    sqlUpdate.declareParameter(new SqlParameter("name", Types.VARCHAR));
    sqlUpdate.compile();
    sqlUpdate.update("wang5");

    List<User> users = jdbcTemplate.query("SELECT id,name FROM user", new UserRowMapper());
    logger.debug(users);
}

使用StoredProcedure

StoredProcedure是关系数据库中存储过程概念的抽象类，提供了一组方便的受保护方法。因此在使用该类的时候需要我们创建一个子类，继承该类。在使用这个类的时候我们需要使用setSql方法设置数据库中存储过程的名称。在传递参数的时候，使用SqlParameter传递IN参数，使用SqlOutParameter传递OUT参数，使用SqlInOutParameter传递INOUT参数。

以下是Spring官方文档的一个例子。

class GetSysdateProcedure extends StoredProcedure {

    private static final String SQL = "sysdate";

    public GetSysdateProcedure(DataSource dataSource) {
        setDataSource(dataSource);
        setFunction(true);
        setSql(SQL);
        declareParameter(new SqlOutParameter("date", Types.DATE));
        compile();
    }

    public Date execute() {
        // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
        Map<String, Object> results = execute(new HashMap<String, Object>());
        Date sysdate = (Date) results.get("date");
        return sysdate;
    }
}

其他知识

提供SQL参数信息

一般情况下Spring可以自行决定SQL参数的类型，但是有时候或者说最好由我们提供准确的SQL参数信息。

• JdbcTemplate的很多查询和更新方法包含一个额外的参数，一个int数组，该数组应该是java.sql.Types指定的一些常量，表明SQL参数的类型。
• 可以使用SqlParameterValue来设置参数的值，在创建该对象的时候提供参数的值和类型。
• 如果使用具有命名参数功能的类时，使用SqlParameterSource类（BeanPropertySqlParameterSource或MapSqlParameterSource）来指定命名参数和其类型。

数据源

我们在学习JDBC的时候，基本上都是从DriverManager类创建一个数据库连接。在实际环境中，我们应该使用数据源（DataSource）来创建数据库连接。数据源将创建数据库的职责和应用代码分离，数据源可以交给数据库管理员来设置，程序员只需要获取数据源对象，然后开发相关代码。

在上面的例子中我们使用的是Apache的commons-dbcp2数据源，Spring自己也实现了几个数据源方便我们开发和测试。

DriverManagerDataSource是一个简单的数据源，每次请求都会返回一个新的数据库连接。它使用数据库驱动来创建数据源，就像我们使用DriverManager那样。这是一个简单的测试类，可以帮助我们在不借助任何Java EE容器的情况下获取数据源。但是由于使用commons-dbcp2这样的成熟数据源也很容易，所以其实我们只需要使用commons-dbcp2即可。

SingleConnectionDataSource也是一个数据源，它包装了一个单独的数据库连接，在每次请求都会返回同一个数据库连接对象。和DriverManagerDataSource相比它更轻量，因为没有创建额外数据库连接的开销。

初始化数据源

在创建数据源的时候我们可以在Spring配置文件中设置数据源的初始化脚本。

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath:com/foo/sql/db-schema.sql"/>
    <jdbc:script location="classpath:com/foo/sql/db-test-data.sql"/>
</jdbc:initialize-database>

有时候我们希望在初始化数据的时候删除上一次的测试数据。但是如果数据库不支持类似DROP TABLE IF EXISTS这样的语法，那么我们就必须在初始化脚本中添加一些DROP语句。这些删除语句可能会失败（如果没有测试数据的情况下执行删除），这时候就可以忽略删除失败。当初始化脚本出现错误的时候就会抛出异常，但是如果设置了忽略删除失败，Spring就会直接忽略这些失败而不抛出异常。ignore-failures属性还可以取另外两个值NONE和ALL，分别表示不忽略失败和忽略所有失败。

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
    <jdbc:script location="..."/>
</jdbc:initialize-database>

我们还可以设置初始化脚本的间隔符。

<jdbc:initialize-database data-source="dataSource" separator="@@">
    <jdbc:script location="classpath:com/foo/sql/db-schema.sql" separator=";"/>
    <jdbc:script location="classpath:com/foo/sql/db-test-data-1.sql"/>
    <jdbc:script location="classpath:com/foo/sql/db-test-data-2.sql"/>
</jdbc:initialize-database>

工具类

org.springframework.jdbc.datasource.DataSourceUtils，这是一个方便的工具类，包含了一组和数据源相关的工具方法。

org.springframework.jdbc.support.JdbcUtils类提供了一些方法来操作JDBC，在Spring内部使用，也可以用于自己的JDBC操作。

还有几个工具类主要由Spring内部使用，这里就不列举了。

参考资料

http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle/#jdbc

项目代码在Csdn代码库，有兴趣的同学可以看看。