15.使用JDBC实现数据访问 - 15.5 利用SimpleJdbc类简化JDBC操作 - 《Spring Framework 5 中文文档》

15.5 利用SimpleJdbc类简化JDBC操作

15.5 利用SimpleJdbc类简化JDBC操作

SimpleJdbcInsert类和SimpleJdbcCall类主要利用了JDBC驱动所提供的数据库元数据的一些特性来简化数据库操作配置。这意味着可以在前端减少配置，当然你也可以覆盖或是关闭底层的元数据处理，在代码里面指定所有的细节。

15.5.1 利用SimpleJdbcInsert插入数据

让我们首先看SimpleJdbcInsert类可提供的最小配置选项。你需要在数据访问层初始化方法里面初始化SimpleJdbcInsert类。在这个例子中，初始化方法是setDataSource。你不需要继承SimpleJdbcInsert，只需要简单的创建其实例同时调用withTableName设置数据库名。

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("t_actor");
    }
    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(3);
        parameters.put("id", actor.getId());
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        insertActor.execute(parameters);
    }
    // ... additional methods
}

代码中的execute只传入java.utils.Map作为唯一参数。需要注意的是Map里面用到的Key必须和数据库中表对应的列名一一匹配。这是因为我们需要按顺序读取元数据来构造实际的插入语句。

15.5.2 使用SimpleJdbcInsert获取自增Key

接下来，我们对于同样的插入语句，我们并不传入id，而是通过数据库自动获取主键的方式来创建新的Actor对象并插入数据库。当我们创建SimpleJdbcInsert实例时, 我们不仅需要指定表名，同时我们通过usingGeneratedKeyColumns方法指定需要数据库自动生成主键的列名。

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    // ... additional methods
}

执行插入操作时第二种方式最大的区别是你不是在Map中指定ID，而是调用executeAndReturnKey方法。这个方法返回java.lang.Number对象，可以创建一个数值类型的实例用于我们的领域模型中。你不能仅仅依赖所有的数据库都返回一个指定的Java类；java.lang.Number是你可以依赖的基础类。如果你有多个自增列，或者自增的值是非数值型的，你可以使用executeAndReturnKeyHolder 方法返回的KeyHolder

15.5.3 使用SimpleJdbcInsert指定列

你可以在插入操作中使用usingColumns方法来指定特定的列名

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingColumns("first_name", "last_name")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    // ... additional methods
}

这里插入操作的执行和你依赖元数据决定更新哪个列的方式是一样的。

15.5.4 使用SqlParameterSource 提供参数值

使用Map来指定参数值没有问题，但不是最便捷的方法。Spring提供了一些SqlParameterSource接口的实现类来更方便的做这些操作。
第一个是BeanPropertySqlParameterSource，如果你有一个JavaBean兼容的类包含具体的值，使用这个类是很方便的。他会使用相关的Getter方法来获取参数值。下面是一个例子：

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(actor);
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    // ... additional methods
}

另外一个选择是使用MapSqlParameterSource，类似于Map、但是提供了一个更便捷的addValue方法可以用来做链式操作。

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        SqlParameterSource parameters = new MapSqlParameterSource()
                .addValue("first_name", actor.getFirstName())
                .addValue("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    // ... additional methods
}

上面这些例子可以看出、配置是一样的，区别只是切换了不同的提供参数的实现方式来执行调用。

15.5.5 利用SimpleJdbcCall调用存储过程

SimpleJdbcCall利用数据库元数据的特性来查找传入的参数和返回值，这样你就不需要显式去定义他们。如果你喜欢的话也以自己定义参数，尤其对于某些参数，你无法直接将他们映射到Java类上，例如ARRAY类型和STRUCT类型的参数。下面第一个例子展示了一个存储过程，从一个MySQL数据库返回Varchar和Date类型。这个存储过程例子从指定的actor记录中查询返回first_name,last_name,和birth_date列。

CREATE PROCEDURE read_actor (
    IN in_id INTEGER,
    OUT out_first_name VARCHAR(100),
    OUT out_last_name VARCHAR(100),
    OUT out_birth_date DATE)
BEGIN
    SELECT first_name, last_name, birth_date
    INTO out_first_name, out_last_name, out_birth_date
    FROM t_actor where id = in_id;
END;

in_id 参数包含你正在查找的actor记录的id.out参数返回从数据库表读取的数据

SimpleJdbcCall 和SimpleJdbcInsert定义的方式比较类似。你需要在数据访问层的初始化代码中初始化和配置该类。相比StoredProcedure类，你不需要创建一个子类并且不需要定义能够在数据库元数据中查找到的参数。下面是一个使用上面存储过程的SimpleJdbcCall配置例子。除了DataSource以外唯一的配置选项是存储过程的名字

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcCall procReadActor;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.procReadActor = new SimpleJdbcCall(dataSource)
                .withProcedureName("read_actor");
    }
    public Actor readActor(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        Map out = procReadActor.execute(in);
        Actor actor = new Actor();
        actor.setId(id);
        actor.setFirstName((String) out.get("out_first_name"));
        actor.setLastName((String) out.get("out_last_name"));
        actor.setBirthDate((Date) out.get("out_birth_date"));
        return actor;
    }
    // ... additional methods
}

调用代码包括创建包含传入参数的SqlParameterSource。这里需要重视的是传入参数值名字需要和存储过程中定义的参数名称相匹配。有一种场景不需要匹配、那就是你使用元数据去确定数据库对象如何与存储过程相关联。在存储过程源代码中指定的并不一定是数据库中存储的格式。有些数据库会把名字转成大写、而另外一些会使用小写或者特定的格式。

execute方法接受传入参数，同时返回一个Map包含任意的返回参数，Map的Key是存储过程中指定的名字。在这个例子中它们是out_first_name, out_last_name 和 out_birth_date

execute 方法的最后一部分使用返回的数据创建Actor对象实例。再次需要强调的是Out参数的名字必须是存储过程中定义的。结果Map中存储的返回参数名必须和数据库中的返回参数名（不同的数据库可能会不一样）相匹配，为了提高你代码的可重用性，你需要在查找中区分大小写，或者使用Spring里面的LinkedCaseInsensitiveMap。如果使用LinkedCaseInsensitiveMap，你需要创建自己的JdbcTemplate并且将setResultsMapCaseInsensitive属性设置为True。然后你将自定义的JdbcTemplate 传入到SimpleJdbcCall的构造器中。下面是这种配置的一个例子：

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;
    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_actor");
    }
    // ... additional methods
}

通过这样的配置，你就可以无需担心返回参数值的大小写问题。

15.5.6 为SimpleJdbcCall显式定义参数

你已经了解如何通过元数据来简化参数配置，但如果你需要的话也可以显式指定参数。这样做的方法是在创建SimpleJdbcCall类同时通过declareParameters方法进行配置，这个方式可以传入一系列的SqlParameter。下面的章节会详细描述如何定义一个SqlParameter

备注：如果你使用的数据库不是Spring支持的数据库类型的话显式定义就很有必要了。当前Spring支持以下数据库的存储过程元数据查找能力：Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, 和 Sybase. 我们同时对某些数据库内置函数支持元数据特性：比如：MySQL、Microsoft SQL Server和Oracle。

你可以选择显式定义一个、多个，或者所有参数。当你没有显式定义参数时元数据参数仍然会被使用。当你不想用元数据查找参数功能、只想指定参数时，需要调用withoutProcedureColumnMetaDataAccess方法。假设你针对同一个数据函数定义了两个或多个不同的调用方法签名，在每一个给定的签名中你需要使用useInParameterNames来指定传入参数的名称列表。下面是一个完全自定义的存储过程调用例子

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;
    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_actor")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("in_id")
                .declareParameters(
                        new SqlParameter("in_id", Types.NUMERIC),
                        new SqlOutParameter("out_first_name", Types.VARCHAR),
                        new SqlOutParameter("out_last_name", Types.VARCHAR),
                        new SqlOutParameter("out_birth_date", Types.DATE)
                );
    }
    // ... additional methods
}

两个例子的执行结果是一样的，区别是这个例子显式指定了所有细节，而不是仅仅依赖于数据库元数据。

15.5.7 如何定义SqlParameters

如何定义SimpleJdbc类和RDBMS操作类的参数，详见15.6： “像Java对象那样操作JDBC”,
你需要使用SqlParameter或者是它的子类。通常需要在构造器中定义参数名和SQL类型。SQL类型使用java.sql.Types常量来定义。
我们已经看到过类似于如下的定义：

new SqlParameter("in_id", Types.NUMERIC),
    new SqlOutParameter("out_first_name", Types.VARCHAR),

上面第一行SqlParameter 定义了一个传入参数。IN参数可以同时在存储过程调用和SqlQuery查询中使用，它的子类在下面的章节也有覆盖。

上面第二行SqlOutParameter定义了在一次存储过程调用中使用的返回参数。还有一个SqlInOutParameter类，可以用于输入输出参数。也就是说，它既是一个传入参数，也是一个返回值。

备注：参数只有被定义成SqlParameter和SqlInOutParameter才可以提供输入值。不像StoredProcedure类为了考虑向后兼容允许定义为SqlOutParameter的参数可以提供输入值

对于输入参数，除了名字和SQL类型，你可以定义数值区间或是自定义数据类型名。针对输出参数，你可以使用RowMapper处理从REF游标返回的行映射。另外一种选择是定义SqlReturnType，可以针对返回值作自定义处理。

15.5.8 使用SimpleJdbcCall调用内置存储函数

调用存储函数几乎和调用存储过程的方式是一样的，唯一的区别你提供的是函数名而不是存储过程名。你可以使用withFunctionName方法作为配置的一部分表示我们想要调用一个函数，以及生成函数调用相关的字符串。一个特殊的execute调用，executeFunction，用来指定这个函数并且返回一个指定类型的函数值，这意味着你不需要从结果Map获取返回值。存储过程也有一个名字为executeObject的便捷方法，但是只要一个输出参数。下面的例子基于一个名字为get_actor_name的存储函数，返回actor的全名。下面是这个函数的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
}

execute方法返回一个包含函数调用返回值的字符串

15.5.9 从SimpleJdbcCall返回ResultSet/REF游标

调用存储过程或者函数返回结果集会相对棘手一点。一些数据库会在JDBC结果处理中返回结果集，而另外一些数据库则需要明确指定返回值的类型。两种方式都需要循环迭代结果集做额外处理。通过SimpleJdbcCall，你可以使用returningResultSet方法，并定义一个RowMapper的实现类来处理特定的返回值。当结果集在返回结果处理过程中没有被定义名称时，返回的结果集必须与定义的RowMapper的实现类指定的顺序保持一致。而指定的名字也会被用作返回结果集中的名称。

下面的例子使用了一个不包含输入参数的存储过程并且返回t_actor标的所有行。下面是这个存储过程的Mysql源代码：

CREATE PROCEDURE read_all_actors()
BEGIN
 SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
END;

调用这个存储过程你需要定义RowMapper。因为我们定义的Map类遵循JavaBean规范，所以我们可以使用BeanPropertyRowMapper作为实现类。通过将相应的class类作为参数传入到newInstance方法中，我们可以创建这个实现类。

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
}

execute调用传入一个空Map，因为这里不需要传入任何参数。从结果Map中提取Actors列表，并且返回给调用者。