本指南演示了几个示例，说明如何在弹簧/弹簧启动应用程序中使用 Jdbc 模板和简单 JdbcCall 调用存储过程/函数。
1. 使用 Jdbc 模板调用存储过程
1.1. 简单的Mysql存储过程，用于按名称获取用户的搜索结果
DROP procedure IF EXISTS `search_users_proc`$
CREATE PROCEDURE `search_users_proc` (IN name VARCHAR(50))
BEGIN
SELECT *
FROM USER
WHERE username LIKE CONCAT('%', name , '%');
END$1.2. 使用弹性 Jdbc 模板调用存储过程。
@Autowired
private JdbcTemplate jdbcTemplate;
/* Calling Stored Procedure using JdbcTemplate */
public Map<String, Object> searchUserByName(String searchTerm) {
List<SqlParameter> parameters = Arrays.asList(new SqlParameter(Types.NVARCHAR));
return jdbcTemplate.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall("{call search_users_proc(?)}");
cs.setString(1, searchTerm);
return cs;
}
}, parameters);
}1.3. 过程结果和测试
@Autowired
private UserJdbcTemplate jdbcTemplate;
@Test
@DisplayName("JdbcTemplate Stored Procedure Test ")
void searchUserTest() {
Map<String, Object> out = jdbcTemplate.searchUserByName("pet");
// #result-set-1
List<User> users = mapUsers(out, 1);
assertTrue(users.size() == 4);
} public List<User> mapUsers(Map<String, Object> out, int resultSetPosition) {
List<User> users = new ArrayList<User>();
List<Map<String, Object>> results = (List<Map<String, Object>>) out.get("#result-set-" + resultSetPosition);
results.forEach(u -> {
User user = new User();
user.setId((Long) u.get("Id"));
user.setUserName((String) u.get("USERNAME"));
user.setPassword((String) u.get("PASSWORD"));
user.setCreatedTime((Date) u.get("CREATEDTIME"));
if (u.get("UPDATEDTIME") != null)
user.setCreatedTime((Date) u.get("UPDATEDTIME"));
user.setUserType(UserType.valueOf((String) u.get("USERTYPE")));
user.setDateofBirth((Date) u.get("DATEOFBIRTH"));
users.add(user);
});
return users;
}2. 使用简单Jdbc调用存储过程
2.1. 如果您使用简单JdbcCall，则会自动为德比，MySQL，微软SQL服务器，甲骨文，DB2，系统库和后greSQL数据库检测“IN”和“OUT”参数。对于任何其他数据库，您需要显式声明所有参数。下面是在参数和搜索结果中获取搜索计数的简单存储过程。out
/* Stored procedure to get search count for paged users by name */
DROP procedure IF EXISTS `count_search_users_proc`$
CREATE PROCEDURE `count_search_users_proc` (
IN order_by VARCHAR(50),
IN direction VARCHAR(4),
IN limit_ INT,
IN offset_ INT,
OUT total INT
)
BEGIN
SELECT COUNT(id)
INTO total
FROM USER;
SELECT * from USER ORDER BY CONCAT(order_by,' ', direction) LIMIT limit_ OFFSET offset_;
END$2.2. 下面是一个使用弹簧简单Jdbc调用存储过程的简单示例。要初始化SimpleJdbcCall ，您需要提供JdbcTemplate或DataSource实例。由于IN和OUT参数SimpleJdbcCall自动检测，声明参数是可选的，因此在以下程序中注释掉。
private SimpleJdbcCall simpleJdbcCall;
/* Calling Stored Procedure using SimpleJdbcCall */
public Map<String, Object> searchUsersWithCount(String by, String direction, int size, int offset) {
simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("count_search_users_proc");
/*.declareParameters(
new SqlParameter("order_by", Types.NVARCHAR),
new SqlParameter("direction", Types.VARCHAR),
new SqlParameter("limit_", Types.INTEGER),
new SqlParameter("offset_", Types.INTEGER),
new SqlOutParameter("total", Types.INTEGER));*/
Map<String, Object> out = simpleJdbcCall.execute(
new MapSqlParameterSource("order_by", by)
.addValue("direction", direction)
.addValue("limit_", size)
.addValue("offset_", offset));
return out;
}2.3. 过程结果和测试
@Test
@DisplayName("SimpleJdbcCall - Stored Procedure Test ")
void countSearchUserTest() {
Map<String, Object> out = jdbcTemplate.searchUsersWithCount("USERNAME", "DESC", 5, 0);
// #result-set-1
List<User> users = mapUsers(out, 1);
// out param
int total = (Integer) out.get("total");
assertTrue(users.size() == 5);
assertTrue(total == 12);
}3. 具有多个结果集的存储过程
3.1. 一个包含多个结果的简单存储过程，演示如何获取最近更新的语句的行计数。
/* Stored procedure to get multple result sets */
DROP procedure IF EXISTS `search_weak_users_proc`$
CREATE PROCEDURE `search_weak_users_proc` (
IN user_type VARCHAR(50),
OUT type_total INT
)
BEGIN
SELECT COUNT(ID) INTO type_total from USER WHERE USERTYPE = user_type;
SELECT * from USER
WHERE USERTYPE = user_type AND LENGTH(USERNAME) < 7;
SELECT * from USER
WHERE USERTYPE = user_type AND LENGTH(PASSWORD) <= 8;
UPDATE USER SET UPDATEDTIME = NOW() WHERE USERTYPE = user_type;
END$3.2. 一个春季SimplJdbc调用示例，用于在存储过程调用中获取多个结果集。
/* Example to get Multiple Result sets on stored procedure call */
public Map<String, Object> searchWeakUsers(String userType) {
simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("search_weak_users_proc")
.declareParameters(
new SqlParameter("user_type", Types.VARCHAR),
new SqlOutParameter("type_total", Types.INTEGER));
Map<String, Object> out = simpleJdbcCall.execute(
new MapSqlParameterSource("user_type", userType));
return out;
}3.3. 过程结果和测试
@Test
@DisplayName("Stored Procedure Multiple Result Sets Test")
void searchWeakUserTest() {
Map<String, Object> out = jdbcTemplate.searchWeakUsers(UserType.EMPLOYEE.toString());
System.err.println(out);
// #result-set-1
List<User> usersHasWeakUsername = mapUsers(out, 1);
// #result-set-2
List<User> usersHasWeakPassword = mapUsers(out, 2);
// out param
int total = (Integer) out.get("type_total");
// Last updated statements count
int updatedCount = (Integer) out.get("#update-count-1");
System.out.println(total);
}4. 调用存储函数
4.1. 一个简单的 mysql 存储函数。
/* Stored Function Example */
DROP function IF EXISTS `user_has_strongpwd_fn`$
CREATE FUNCTION `user_has_strongpwd_fn` (u_id BIGINT) RETURNS tinyint(1)
BEGIN
DECLARE length_pwd INT;
SELECT LENGTH(PASSWORD) INTO length_pwd FROM USER WHERE ID = u_id;
IF (length_pwd >= 7 && length_pwd < 15) THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END$4.2. 使用弹簧简单调用存储函数示例。若要调用存储的函数，请使用.withFunctionName提供存储的函数名称。
/* Example to call Stored Function */
public boolean hasUserStrongPassword(Long id) {
simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("user_has_strongpwd_fn")
.declareParameters(new SqlParameter("u_id", Types.BIGINT));
boolean out = simpleJdbcCall.executeFunction(Boolean.class,
new MapSqlParameterSource("u_id", id));
return out;
}4.3. 过程结果和测试
@Test
@DisplayName("Stored Function Test")
void userHasWeakPasswordTest() {
boolean userHasWeakPass = jdbcTemplate.hasUserStrongPassword(Long.valueOf(1));
assertTrue(!userHasWeakPass);
}5. 结论
我们已经介绍了几个关于如何使用春季简单Jdbc调用存储过程/函数的示例，这些调用和Jdbc模板。建议使用SimpleJdbcCall来调用存储过程，它会自动检测IN和OUT参数，并提供更清晰的方法来注册过程/函数/架构/目录名称和参数绑定。
在 GitHub 上查看源代码。
其他春季 JDBC 示例：
2640
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
