How does mybatis call mysql stored procedure and get the return value
mybatis calls mysql stored procedure and gets the return value
1, mysql creates stored procedure
#结束符号默认;, delimiter $$语句表示结束符号变更为$$ delimiter $$ CREATE PROCEDURE `demo`(IN inStr VARCHAR(100), out ourStr VARCHAR(4000)) BEGIN SET ourStr = '01'; if (inStr == '02') then set ourStr = '02'; end if; END$$ #结束符号修改 delimiter ;
2, mybatis calls
(1) Annotation method
// key1和key2都是参数map中的key // key1是需要传入存储过程的值 // key2是用于接收存储过程返回的值,跟获取自增主键类似 @Select("call demo('${key1}', #{key2, mode=OUT, jdbcType=VARCHAR})") @Options(statementType = StatementType.CALLABLE) String getDemoValue(Map<String, Object> map);
(2)xml method
<select id="getDemoValue" resultType="java.lang.String" parameterType="java.util.Map" statementType="CALLABLE"> call demo('${key1}', #{key2, mode=OUT, jdbcType=VARCHAR}) </select>
Mybatis encounters the pitfall of returning a null value when calling a stored procedure
Mybatis returns a null value when calling a stored procedure
It’s a simple one Stored procedure
BEGIN #SELECT COUNT(*) scount FROM test_user t WHERE t.user_sex=sex; SELECT user_name userName FROM test_user t WHERE t.user_sex=sex; END
Then in the Mybatis Mapper configuration file
<!--存储过程 输入参数用Map --> <select id="queryCountBySexWithProcedure" statementType="CALLABLE" parameterType="java.util.Map" resultType="java.util.HashMap"> { CALL queryCountBySexWithProcedure( #{sex,jdbcType=VARCHAR,mode=IN}, #{userName,jdbcType=VARCHAR,mode=OUT} ) } </select>
then the userName value is null. Why is this? Mapping is performed in the mapper.xml file, and the attribute user_name marked in red below in the stored procedure is not automatically mapped to the userName value, so calling the userName value must return null.
BEGIN #SELECT COUNT(*) scount FROM test_user t WHERE t.user_sex=sex; SELECT user_name userName FROM test_user t WHERE t.user_sex=sex; END
So how to deal with this problem? That is to add the INTO keyword
BEGIN #SELECT COUNT(*) scount FROM test_user t WHERE t.user_sex=sex; SELECT user_name INTO userName FROM test_user t WHERE t.user_sex=sex; END
The above is the detailed content of How does mybatis call mysql stored procedure and get the return value. For more information, please follow other related articles on the PHP Chinese website!

MySQLstringtypesimpactstorageandperformanceasfollows:1)CHARisfixed-length,alwaysusingthesamestoragespace,whichcanbefasterbutlessspace-efficient.2)VARCHARisvariable-length,morespace-efficientbutpotentiallyslower.3)TEXTisforlargetext,storedoutsiderows,

MySQLstringtypesincludeVARCHAR,TEXT,CHAR,ENUM,andSET.1)VARCHARisversatileforvariable-lengthstringsuptoaspecifiedlimit.2)TEXTisidealforlargetextstoragewithoutadefinedlength.3)CHARisfixed-length,suitableforconsistentdatalikecodes.4)ENUMenforcesdatainte

MySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,2)VARCHARforvariable-lengthtext,3)BINARYandVARBINARYforbinarydata,4)BLOBandTEXTforlargedata,and5)ENUMandSETforcontrolledinput.Eachtypehasspecificusesandperformancecharacteristics,sochoose

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

ToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl

ToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'

The string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.

The best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version
Useful JavaScript development tools
