JdbcTemplate – 调用存储过程

た入场券 2023-09-26 10:39 50阅读 0赞

本指南演示了几个示例，说明如何在**弹簧**/**弹簧启动**应用程序中使用 **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 示例：**

1.  [春季 Jdbc 模板 – 批量更新][Jdbc _ _]
2.  [春季 Jdbc 模板 – 查询][Jdbc _ _ 1]
3.  [春季 Jdbc 模板 – 分页][Jdbc _ _ 2]
4.  [春季 Jdbc 模板 – IN 条款][Jdbc _ _ IN]
5.  [弹簧 Jdbc 模板 – 自动生成的密钥][Jdbc _ _ 3]
6.  [弹簧启动 – 加载初始数据][Link 1]

### 6. 参考资料 ###

1.  [春季文件][Link 2]
2.  [存储过程][Link 3]
3.  [弹簧启动初始化数据][Link 1]
4.  [J单元 5 教程][J_ 5]
5.  [弹簧靴 J 单元 5 测试][J _ 5]

[GitHub]: https://github.com/javabydeveloper/spring-boot/tree/master/spring-boot-jdbc-example/src/test/java/com/javabydeveloper/demo/template/sp
[Jdbc _ _]: https://javabydeveloper.com/spring-jdbctemplate-batch-update-with-maxperformance/
[Jdbc _ _ 1]: https://javabydeveloper.com/spring-jdbctemplate-query-examples/
[Jdbc _ _ 2]: https://javabydeveloper.com/spring-jdbctemplate-pagination-examples/
[Jdbc _ _ IN]: https://javabydeveloper.com/spring-jdbctemplate-in-clause-with-list-of-values/
[Jdbc _ _ 3]: https://javabydeveloper.com/spring-jdbctemplate-get-auto-generated-keys/
[Link 1]: https://javabydeveloper.com/spring-boot-loading-initial-data/
[Link 2]: https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#jdbc
[Link 3]: https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html
[J_ 5]: https://javabydeveloper.com/junit-5-tutorial/
[J _ 5]: https://javabydeveloper.com/spring-boot-junit-5-test-example/