使用SimpleJdbc
类简化JDBC操作
SimpleJdbcInsert
和SimpleJdbcCall
类通过利用可以通过JDBC驱动程序检索的数据库元数据提供了简化的配置。这意味着您在前期需要配置的内容更少,尽管如果您更喜欢在代码中提供所有细节,您也可以覆盖或关闭元数据处理。
使用SimpleJdbcInsert
插入数据
我们首先看一下具有最少配置选项的SimpleJdbcInsert
类。您应该在数据访问层的初始化方法中实例化SimpleJdbcInsert
。在本示例中,初始化方法是setDataSource
方法。您不需要对SimpleJdbcInsert
类进行子类化。相反,您可以创建一个新实例,并使用withTableName
方法设置表名。该类的配置方法采用流畅
风格,返回SimpleJdbcInsert
的实例,这样您可以链接所有配置方法。以下示例仅使用一个配置方法(我们稍后会展示多个方法的示例):
-
Java
-
Kotlin
public class JdbcActorDao implements ActorDao {
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("t_actor");
}
public void add(Actor actor) {
Map<String, Object> parameters = new HashMap<>(3);
parameters.put("id", actor.getId());
parameters.put("first_name", actor.getFirstName());
parameters.put("last_name", actor.getLastName());
insertActor.execute(parameters);
}
// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val insertActor = SimpleJdbcInsert(dataSource).withTableName("t_actor")
fun add(actor: Actor) {
val parameters = mutableMapOf<String, Any>()
parameters["id"] = actor.id
parameters["first_name"] = actor.firstName
parameters["last_name"] = actor.lastName
insertActor.execute(parameters)
}
// ... additional methods
}
这里使用的execute
方法接受一个普通的java.util.Map
作为唯一参数。这里需要注意的重要一点是,Map
中使用的键必须与数据库中表中定义的列名匹配。这是因为我们读取元数据来构造实际的插入语句。
使用SimpleJdbcInsert
检索自动生成的键
下一个示例与前一个示例使用相同的插入方式,但是不是传入id
,而是检索自动生成的键并将其设置在新的Actor
对象上。在创建SimpleJdbcInsert
时,除了指定表名外,还使用usingGeneratedKeyColumns
方法指定生成的键列的名称。以下清单显示了它的工作原理:
-
Java
-
Kotlin
public class JdbcActorDao implements ActorDao {
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.insertActor = new SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingGeneratedKeyColumns("id");
}
public void add(Actor actor) {
Map<String, Object> parameters = new HashMap<>(2);
parameters.put("first_name", actor.getFirstName());
parameters.put("last_name", actor.getLastName());
Number newId = insertActor.executeAndReturnKey(parameters);
actor.setId(newId.longValue());
}
// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val insertActor = SimpleJdbcInsert(dataSource)
.withTableName("t_actor").usingGeneratedKeyColumns("id")
fun add(actor: Actor): Actor {
val parameters = mapOf(
"first_name" to actor.firstName,
"last_name" to actor.lastName)
val newId = insertActor.executeAndReturnKey(parameters);
return actor.copy(id = newId.toLong())
}
// ... additional methods
}
当您使用这种第二种方法运行插入时的主要区别是,您不将id
添加到Map
中,并调用executeAndReturnKey
方法。这将返回一个java.lang.Number
对象,您可以使用它创建您的领域类中使用的数值类型的实例。您不能依赖所有数据库在此处返回特定的Java类。java.lang.Number
是您可以依赖的基类。如果您有多个自动生成的列或生成的值是非数字的,您可以使用从executeAndReturnKeyHolder
方法返回的KeyHolder
。
为SimpleJdbcInsert
指定列
您可以通过使用usingColumns
方法指定列名列表来限制插入的列,如下例所示:
-
Java
-
Kotlin
public class JdbcActorDao implements ActorDao {
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource 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<>(2);
parameters.put("first_name", actor.getFirstName());
parameters.put("last_name", actor.getLastName());
Number newId = insertActor.executeAndReturnKey(parameters);
actor.setId(newId.longValue());
}
// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val insertActor = SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingColumns("first_name", "last_name")
.usingGeneratedKeyColumns("id")
fun add(actor: Actor): Actor {
val parameters = mapOf(
"first_name" to actor.firstName,
"last_name" to actor.lastName)
val newId = insertActor.executeAndReturnKey(parameters);
return actor.copy(id = newId.toLong())
}
// ... additional methods
}
执行插入与依赖元数据确定要使用的列时相同。
使用SqlParameterSource
提供参数值
使用Map
提供参数值是有效的,但不是最方便的类使用方式。Spring提供了一些SqlParameterSource
接口的实现类,您可以使用这些类代替。第一个是BeanPropertySqlParameterSource
,如果您有一个符合JavaBean规范的类包含您的值,这是一个非常方便的类。它使用相应的getter方法来提取参数值。以下示例展示了如何使用BeanPropertySqlParameterSource
:
-
Java
-
Kotlin
public class JdbcActorDao implements ActorDao {
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource 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
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val insertActor = SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingGeneratedKeyColumns("id")
fun add(actor: Actor): Actor {
val parameters = BeanPropertySqlParameterSource(actor)
val newId = insertActor.executeAndReturnKey(parameters)
return actor.copy(id = newId.toLong())
}
// ... additional methods
}
另一个选项是MapSqlParameterSource
,它类似于Map
,但提供了一个更方便的addValue
方法,可以进行链式调用。以下示例展示了如何使用它:
-
Java
-
Kotlin
public class JdbcActorDao implements ActorDao {
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource 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
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val insertActor = SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingGeneratedKeyColumns("id")
fun add(actor: Actor): Actor {
val parameters = MapSqlParameterSource()
.addValue("first_name", actor.firstName)
.addValue("last_name", actor.lastName)
val newId = insertActor.executeAndReturnKey(parameters)
return actor.copy(id = newId.toLong())
}
// ... additional methods
}
如您所见,配置是相同的。只需更改执行代码以使用这些替代输入类。
使用SimpleJdbcCall
调用存储过程
SimpleJdbcCall
类使用数据库中的元数据查找in
和out
参数的名称,因此您不必显式声明它们。如果您愿意或者有一些参数(如ARRAY
或STRUCT
)没有自动映射到Java类,您可以声明参数。第一个示例展示了一个简单的过程,从MySQL数据库中以VARCHAR
和DATE
格式仅返回标量值。示例过程读取指定的演员条目,并以out
参数的形式返回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
参数包含您要查找的演员的id
。out
参数返回从表中读取的数据。
您可以类似于声明SimpleJdbcInsert
来声明SimpleJdbcCall
。您应该在数据访问层的初始化方法中实例化和配置该类。与StoredProcedure
类相比,您无需创建子类,也无需声明可以在数据库元数据中查找的参数。下面的SimpleJdbcCall
配置示例使用前面的存储过程(除了DataSource
之外的唯一配置选项是存储过程的名称):
-
Java
-
Kotlin
public class JdbcActorDao implements ActorDao {
private SimpleJdbcCall procReadActor;
public void setDataSource(DataSource 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
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val procReadActor = SimpleJdbcCall(dataSource)
.withProcedureName("read_actor")
fun readActor(id: Long): Actor {
val source = MapSqlParameterSource().addValue("in_id", id)
val output = procReadActor.execute(source)
return Actor(
id,
output["out_first_name"] as String,
output["out_last_name"] as String,
output["out_birth_date"] as Date)
}
// ... additional methods
}
您编写调用的代码涉及创建包含IN参数的SqlParameterSource
。您必须将提供的输入值的名称与存储过程中声明的参数名称匹配。大小写不必匹配,因为您使用元数据确定数据库对象在存储过程中应如何引用。在存储过程的源中指定的内容不一定是存储在数据库中的方式。一些数据库将名称转换为全大写,而其他数据库使用小写或使用指定的大小写。
execute
方法接受IN参数并返回一个包含任何out
参数的Map
,按照存储过程中指定的名称进行键控。在这种情况下,它们是out_first_name
、out_last_name
和out_birth_date
。
execute
方法的最后部分创建一个Actor
实例,用于返回检索到的数据。同样,重要的是使用存储过程中声明的out
参数的名称。此外,结果映射中存储的out
参数名称的大小写与数据库中的out
参数名称的大小写匹配,这可能在不同数据库之间有所不同。为了使您的代码更具可移植性,您应该进行不区分大小写的查找或指示Spring使用LinkedCaseInsensitiveMap
。要执行后者,您可以创建自己的JdbcTemplate
并将setResultsMapCaseInsensitive
属性设置为true
。然后,您可以将此自定义的JdbcTemplate
实例传递给SimpleJdbcCall
的构造函数。以下示例显示了这种配置:
-
Java
-
Kotlin
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
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private var procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
isResultsMapCaseInsensitive = true
}).withProcedureName("read_actor")
// ... additional methods
}
out
参数的名称中使用的大小写冲突。
显式声明用于SimpleJdbcCall
的参数
在本章前面,我们描述了如何从元数据中推断参数,但如果您愿意,也可以显式声明它们。您可以通过创建和配置带有declareParameters
方法的SimpleJdbcCall
来这样做,该方法将一系列SqlParameter
对象作为输入。有关如何定义SqlParameter
的详细信息,请参见下一节。
如果您使用的数据库不是Spring支持的数据库,则需要显式声明。目前,Spring支持以下数据库的存储过程调用的元数据查找:Apache Derby、DB2、MySQL、Microsoft SQL Server、Oracle和Sybase。我们还支持以下数据库的存储函数的元数据查找:MySQL、Microsoft SQL Server和Oracle。 |
您可以选择显式声明一个、一些或所有参数。在您不显式声明参数的地方仍然使用参数元数据。要绕过所有元数据查找潜在参数的处理,并仅使用已声明的参数,您可以在声明中调用withoutProcedureColumnMetaDataAccess
方法。假设您为数据库函数声明了两个或更多个不同的调用签名。在这种情况下,您可以调用useInParameterNames
来指定要包括在给定签名中的IN参数名称列表。
以下示例显示了一个完全声明的过程调用,并使用了前面示例中的信息:
-
Java
-
Kotlin
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
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
isResultsMapCaseInsensitive = true
}).withProcedureName("read_actor")
.withoutProcedureColumnMetaDataAccess()
.useInParameterNames("in_id")
.declareParameters(
SqlParameter("in_id", Types.NUMERIC),
SqlOutParameter("out_first_name", Types.VARCHAR),
SqlOutParameter("out_last_name", Types.VARCHAR),
SqlOutParameter("out_birth_date", Types.DATE)
)
// ... additional methods
}
这两个示例的执行和最终结果是相同的。第二个示例明确指定了所有细节,而不依赖于元数据。
如何定义SqlParameters
要为SimpleJdbc
类和RDBMS操作类(在将JDBC操作建模为Java对象中介绍)定义参数,您可以使用SqlParameter
或其子类之一。为此,通常在构造函数中指定参数名称和SQL类型。SQL类型是通过使用java.sql.Types
常量来指定的。在本章前面,我们看到了类似以下声明:
-
Java
-
Kotlin
new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),
SqlParameter("in_id", Types.NUMERIC),
SqlOutParameter("out_first_name", Types.VARCHAR),
带有SqlParameter
的第一行声明了一个IN参数。您可以使用IN参数来调用存储过程和查询,方法是使用SqlQuery
及其子类(在理解SqlQuery
中介绍)。
第二行(带有SqlOutParameter
)声明了一个用于存储过程调用的out
参数。还有一个SqlInOutParameter
用于InOut
参数(为过程提供IN值并返回值的参数)。
只有声明为SqlParameter 和SqlInOutParameter 的参数用于提供输入值。这与StoredProcedure 类不同,后者(出于向后兼容性原因)允许为声明为SqlOutParameter 的参数提供输入值。 |
对于IN参数,除了名称和SQL类型外,您还可以为数字数据指定比例或为自定义数据库类型指定类型名称。对于out
参数,您可以提供一个RowMapper
来处理从REF
游标返回的行的映射。另一个选项是指定一个SqlReturnType
,它提供了定义返回值的自定义处理机会。
使用SimpleJdbcCall
调用存储函数
您可以几乎以与调用存储过程相同的方式调用存储函数,只是您提供函数名称而不是过程名称。您使用withFunctionName
方法作为配置的一部分来指示您要调用函数,并生成相应的函数调用字符串。专门的调用(executeFunction
)用于运行函数,并将函数返回值作为指定类型的对象返回,这意味着您无需从结果映射中检索返回值。对于只有一个out
参数的存储过程,还提供了类似的便利方法(名为executeObject
)。以下示例(针对MySQL)基于一个名为get_actor_name
的存储函数,该函数返回演员的全名:
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
,如下例所示:
-
Java
-
Kotlin
public class JdbcActorDao implements ActorDao {
private SimpleJdbcCall funcGetActorName;
public void setDataSource(DataSource 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
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val jdbcTemplate = JdbcTemplate(dataSource).apply {
isResultsMapCaseInsensitive = true
}
private val funcGetActorName = SimpleJdbcCall(jdbcTemplate)
.withFunctionName("get_actor_name")
fun getActorName(id: Long): String {
val source = MapSqlParameterSource().addValue("in_id", id)
return funcGetActorName.executeFunction(String::class.java, source)
}
// ... additional methods
}
使用的executeFunction
方法返回一个包含函数调用返回值的String
。
从SimpleJdbcCall
返回ResultSet
或REF游标
调用返回结果集的存储过程或函数有点棘手。一些数据库在JDBC结果处理过程中返回结果集,而另一些则需要明确注册特定类型的out
参数。这两种方法都需要额外的处理来循环遍历结果集并处理返回的行。使用SimpleJdbcCall
,您可以使用returningResultSet
方法并声明要用于特定参数的RowMapper
实现。如果结果集在结果处理过程中返回,那么没有定义名称,因此返回的结果必须与您声明的RowMapper
实现的顺序匹配。指定的名称仍然用于将处理后的结果列表存储在从execute
语句返回的结果映射中。
下一个示例(针对MySQL)使用一个不带IN参数并返回t_actor
表中所有行的存储过程:
CREATE PROCEDURE read_all_actors()
BEGIN
SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
END;
要调用此存储过程,您可以声明RowMapper
。因为要映射的类遵循JavaBean规则,您可以使用通过在newInstance
方法中传入要映射到的所需类而创建的BeanPropertyRowMapper
。以下示例显示了如何执行此操作:
-
Java
-
Kotlin
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
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {
private val procReadAllActors = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
isResultsMapCaseInsensitive = true
}).withProcedureName("read_all_actors")
.returningResultSet("actors",
BeanPropertyRowMapper.newInstance(Actor::class.java))
fun getActorsList(): List<Actor> {
val m = procReadAllActors.execute(mapOf<String, Any>())
return m["actors"] as List<Actor>
}
// ... additional methods
}
execute
调用传入一个空的Map
,因为此调用不带任何参数。然后从结果映射中检索演员列表并将其返回给调用者。