Location>code7788 >text

Anti-paradigm design, redundant user names, and after modifying user names, business tables are updated synchronously -- MySQL stored procedure

Popularity：482 ℃/2025-02-22 13:34:42

Anti-paradigm design, redundant user names, synchronous update of business tables through stored procedures.
All tables are created and modified fields are added. . When the user's name changes, all tables must be updated.

Create stored procedures

MySQL

CREATE PROCEDURE UpdateAllUserInfo(IN userId VARCHAR(255), IN newName VARCHAR(255))
 BEGIN
     DECLARE var_table_name VARCHAR(255);
     DECLARE done INT DEFAULT 0;
        
     -- Define cursors and find all tables that meet the criteria
     DECLARE cur CURSOR FOR
         -- The variable name should be different from the field name, otherwise when the variable is taken later, whether the value is taken or not
         SELECT TABLE_NAME as var_table_name
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = 'vipsoft' -- Note the library name
         AND COLUMN_NAME IN ('create_user_name','create_userId')
         GROUP BY TABLE_NAME
         HAVING COUNT(DISTINCT COLUMN_NAME) = 2;

     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

     -- Open cursor
     OPEN cur;

     -- Process each table in a loop
     read_loop: LOOP
         FETCH cur INTO var_table_name;
         IF done THEN
             LEAVE read_loop;
         END IF;

         -- Dynamically generate SQL statements
         SET @createUser = CONCAT('UPDATE ', var_table_name, ' SET create_user_name = "', newName, '" WHERE create_userId = "', userId, '";');

         -- Print SQL
         -- SELECT @createUser;
         -- Execution dynamic SQL
         PREPARE c_stmt FROM @createUser;
         EXECUTE c_stmt;
         DEALLOCATE PREPARE c_stmt;

        -- Dynamically generate SQL statements
         SET @updateUser = CONCAT('UPDATE ', var_table_name, ' SET update_user_name = "', newName, '" WHERE update_userId = "', userId, '";');
         -- SELECT @updateUser;
         -- Execution dynamic SQL
         PREPARE u_stmt FROM @updateUser;
         EXECUTE u_stmt;
         DEALLOCATE PREPARE u_stmt;

     END LOOP;
     -- Close cursor
     CLOSE cur;

     -- Update fixed table
     SET @proInfo = CONCAT('UPDATE project_info SET project_manager_name = "', newName, '" WHERE project_manager_id = "', userId, '";');

     -- Execution dynamic SQL
     PREPARE pro_stmt FROM @proInfo;
     EXECUTE pro_stmt;
     DEALLOCATE PREPARE pro_stmt;


     -- Normal SQL update
     UPDATE project_task SET user_name = proName WHERE project_id = proId;

 END

Code call

MyBatis-Plus call code

/**
  * Data layer
  */
 public interface SysUserMapper extends BaseMapper<SysUser> {

     @Select("CALL UpdateAllUserInfo(#{userId}, #{newName})")
     void callUpdateUserInfo(@Param("userId") String userId, @Param("newName") String newName);
 }

 @Autowired
 private UserMapper userMapper;
 //service layer If the name changes, update
 if (entity != null && !().equals(())) {
      ((), ());
 }

Call stored procedures in MyBatis-Plus (e.g.CALL UpdateUserInfo('Zhang San', 'zs');), which can be achieved through the following steps:

1. use@SelectAnnotation calls stored procedures

If your stored procedure does not have a return value, you can use it directly@SelectAnnotation calls stored procedures.

Sample code

import ;
import ;

@Repository
public interface UserMapper {

    @Select("CALL UpdateUserInfo(#{newName}, #{userId})")
    void callUpdateUserInfo(@Param("newName") String newName, @Param("userId") String userId);
}

Calling methods

import ;
import ;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public void updateUserInfo(String newName, String userId) {
        (newName, userId);
    }
}

2. use@OptionsAnnotation setting stored procedure call

If the stored procedure has output parameters or needs to set other options, you can use@Optionsannotation.

Sample code

import ;
import ;
import ;
import ;

@Repository
public interface UserMapper {

    @Select("CALL UpdateUserInfo(#{newName}, #{userId})")
    @Options(statementType = )
    void callUpdateUserInfo(@Param("newName") String newName, @Param("userId") String userId);
}

3. Call stored procedures using XML configuration

If you prefer to use XML configuration, you canA stored procedure call is defined in the file.

Sample code

existmiddle:

<select  statementType="CALLABLE">
    CALL UpdateUserInfo(#{newName}, #{userId})
</select>

existUserMapperIn the interface:

import ;
import ;

@Repository
public interface UserMapper {
    void callUpdateUserInfo(@Param("newName") String newName, @Param("userId") String userId);
}

4. Process the output parameters of stored procedures

If the stored procedure has output parameters, it can be passedMapOr custom object reception.

Sample code

import ;
import ;
import ;
import ;

import ;

@Repository
public interface UserMapper {

    @Select("CALL UpdateUserInfo(#{newName}, #{userId}, #{result, mode=OUT, jdbcType=INTEGER})")
    @Options(statementType = )
    void callUpdateUserInfo(
        @Param("newName") String newName,
        @Param("userId") String userId,
        @Param("result") Map<String, Object> resultMap
    );
}

Calling methods

import ;
 import ;

 import ;
 import ;

 @Service
 public class UserService {

     @Autowired
     private UserMapper userMapper;

     public void updateUserInfo(String newName, String userId) {
         Map<String, Object> resultMap = new HashMap<>();
         (newName, userId, resultMap);
         ("Stored procedure execution result: " + ("result"));
     }
 }

5. Things to note

  1. Database permissions：

    • Ensure that the user whose application connects to the database has permission to call stored procedures.

  2. Stored procedure parameters：

    • Ensure that the type and number of parameters passed in are consistent with the stored procedure definition.

  3. Transaction Management：

    • If a stored procedure contains multiple SQL operations, it is recommended to enable transaction management when calling the stored procedure.

  4. Error handling：

    • Catch and handle exceptions that may be thrown during the execution of stored procedures.

6. Complete example

Here is a complete example showing how to call stored procedures in MyBatis-Plus:

Stored procedure definition

CREATE PROCEDURE UpdateUserInfo(IN new_name VARCHAR(255), IN user_id VARCHAR(255))
 BEGIN
     -- Update logic
     UPDATE pe_test.user_table SET create_user_name = new_name WHERE create_user_id = user_id;
     UPDATE pe_test.demo SET member_name = new_name WHERE member_id = user_id;
 END;

MyBatis-Plus call code

import ;
import ;

@Repository
public interface UserMapper {

    @Select("CALL UpdateUserInfo(#{newName}, #{userId})")
    void callUpdateUserInfo(@Param("newName") String newName, @Param("userId") String userId);
}

import ;
import ;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public void updateUserInfo(String newName, String userId) {
        (newName, userId);
    }
}

import ;
 import ;

 @Component
 public class AppRunner implements CommandLineRunner {

     @Autowired
     private UserService userService;

     @Override
     public void run(String... args) throws Exception {
         ("Zhang San", "zs");
         ("Stored procedure call complete!");
     }
 }
<Previous Next>