The MySQL stored procedure is called in Mybatics.

Speaking of the Mybatics framework, Java development engineers around them are largely familiar. Mybatics is an Apache open source project, formerly known as IBATICS,2010, the project was migrated by the Apache Software Foundation to Google Code,mybatics is indeed a very good open-source persistence layer framework, SQL code isolation encapsulation, The benefits of automatic pojo mapping, JDBC Dynamic SQL ———— Mybatics can be said, but Mybatics has a very good feature that is often overlooked----that is mybatics also supports the call of the stored procedure.

The people who are unfamiliar with the stored procedure feel that the stored procedure is complex and difficult to understand, that since there is DAO, all the Logic (CRUD) on the database operation is put on the DAO layer, and the stored procedure is not necessary. In any case, it's much easier to write Java code in DAO than to write stored procedures in a database, and why spend more time learning a new stored procedure language?

Indeed, for the following small and medium-sized projects, the basic additions and deletions to the SQL operation is sufficient to deal with (again a bit more complex point also on the advanced query, the DAO layer changed to make a little more complex on the line). But people who have done a larger project (especially the Internet) know that large projects, especially distributed systems, have high requirements for database security, performance, and stability. When the Web server and the database server are distributed across different machines, the DAO layer Tuning database server requires significant network overhead, and the SQL statement must be sent from the Web server to the database server for execution. However, the stored procedure is different, all the SQL logic of the stored procedure is stored locally on the database server and the execution is very efficient. And because the DAO layer code is put on the local, the stored procedure Code is in the remote server, therefore the security also is inferior to the stored procedure. As for stability, don't even talk about it.

Fortunately, Mybatics is a perfect support for stored procedure calls, which is very comforting and undoubtedly adds to my fondness for it.

For MySQL, a later version of MySQL 5.0 supports stored procedures.

Here I describe how to call the MySQL stored procedure in Mybatics, as for the call to Oracle stored procedures is also very similar, it is worth noting that when the program needs to return the list collection data, Oracle needs to return a cursor, and MySQL directly select out.

1. Call the MySQL stored procedure in mybatics to return the list data.

Query user Information list by name (Blur), Return to user list

Configure stored procedure calls in Usermapper.xml, note statementtype= "callable", the ressulttype of the Select element configuration is the user type directly.

<select id= "queryuserlistbylikename_sp" parametertype= "map" resulttype= "User" statementtype= "CALLABLE" >     {Call QUERYUSERLIST_NAMESP (            #{name,jdbctype=varchar,mode=in}          )         }</select>

Service Layer Java code, call Mapper interface into a map, the return value is the list<user> type

Public map<string, object> getuserlistnamelike (String name) {       try {         map<string,object>  Params=ajaxutil.getmap ();         Params.put ("name", name);         List<user> userlist =  usermapper.queryuserlistbylikename_sp (params);         if (userlist!=null) {         map<string,object> map= ajaxutil.messagemap (1, "Query succeeded");         Map.put ("UserList", userlist);        return map;         }} catch (Exception e) {logger.error (e); throw new RuntimeException (e);}    Return Ajaxutil.messagemap (-1, "Query Failed");}

Mapper interface code, an interface declaration (which produces its implementation class by means of a mybatics dynamic proxy)

Public list<user> queryuserlistbylikename_sp (map<string, object> params);

Finally, take a look at the code of the stored procedure. (also very simple for a select fuzzy query)

DELIMITER $ $USE ' easyuidemo ' $ $DROP PROCEDURE IF EXISTS ' queryuserlist_namesp ' $ $CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' Queryuserlist_namesp ' (in In_name VARCHAR) BEGIN    SELECT  * from T_user t WHERE t.name  like   CONCAT ('% ', in_name, '% ');    end$ $DELIMITER;

2. In Mybatics, call the MySQL stored procedure to add the user.

The page form Ajax uploads the user information through the stored procedure to complete the user addition. Required to add success, the stored procedure will return RC (reponsecode result code), MSG (Result message), UserId (newly added user ID)

Configuration in the Usermapper.xml.

It is important to note that the stored procedure configuration here can be configured either as a select node element or as a different insert, update, delete element without the need to configure Resulttype or RESULTMAP, if it is a SELECT element, Be sure to set usercache= "false". The entry I set here for the map. (The map here is actually java.util.Map, and there are many alias mappings in Java for type-to-JDBC types in mybatics, such as int in Java that corresponds to an integer in JDBC, and map is java.util.Map in the Mybatics alias), you may notice that I do not use the user this Bean object, the bean to pass the field properties is not more reasonable? I'll explain this to you later.

<select id= "adduser_sp" parametertype= "map" statementtype= "callable" usecache= "false" > {call adduser_sp (            # {name,jdbctype=varchar,mode=in},            #{age,jdbctype=integer,mode=in},            #{email,jdbctype=varchar,mode=in},            #{address,jdbctype=varchar,mode=in},            #{phone,jdbctype=varchar,mode=in},            #{rc,jdbctype=varchar, Mode=out},            #{msg,jdbctype=varchar,mode=out},            #{userid,jdbctype=varchar,mode=out}          )         }</ Select>

Service Layer Java code.

Everyone must be strange in this Code usermapper.adduser_sp (params); This front does not use the variable to receive the return value of the method, in fact, this method is not the return value, even if you define to return a value (such as map<string,object>), you will find that whatever you get is always null. So there is no need to receive the return value at all. So how do you call the out parameter returned by the stored procedure to receive it? Careful you may have found, yes, is in the Param!! After the call to the stored procedure succeeds, you will find that the stored RC, MSG, and UserID three out parameters are placed in your incoming parameter params ———— that is, the params will have three more field values RC, MSG, userid after the call storage succeeds.

Public map<string, object> adduser_sp (user user) {try {map<string,object>  params=ajaxutil.getmap (); Params.put ("Name", User.getname ());p arams.put ("Address", user.getaddress ());p arams.put ("Age", User.getage ()); Params.put ("Email", User.getemail ());p arams.put ("Phone", User.getphone ());   USERMAPPER.ADDUSER_SP (params);   Map<string, object> map=new hashmap<string,object> ();   Map.put ("RC", Params.get ("RC"));   Map.put ("msg", Params.get ("MSG"));   Map.put ("userid", Params.get ("userid"));   return map;} catch (Exception e) {logger.error (e); throw new RuntimeException (e);}    }

Look again at the definition of the Mapper interface method (nothing to say on an interface method definition)

public void adduser_sp (map<string, object> params);

Finally, look at the stored procedure Code:

DELIMITER $ $USE ' easyuidemo ' $ $DROP PROCEDURE IF EXISTS ' adduser_sp ' $ $CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' Adduser_sp ' (in  in_name varchar (#), in  in_age integers, in  in_email varchar (+), in  in_address varchar (+), in  in_phone varchar (+), out  RC INTEGER,  out msg varchar (+), out  userId varchar ()) BEGIN  DECLARE V_userid VARCHAR (+) DEFAULT ROUND (RAND () * 9000000+10000000);  DECLARE V_ucount INTEGER DEFAULT 0;  SELECT     COUNT (*) into V_ucount   from    t_user   WHERE t_user. ' id ' = V_userid;  IF v_ucount > 0 Then   SET rc =-1;  SET msg = ' Generate userid duplicate, insert failed ';  SET userid= ' -00000000 ';  ELSE   INSERT into T_user (ID, ' name ', age, email, address, phone)   VALUES    (      v_userid,      in_name,< C25/>in_age,      In_email,      in_address,      in_phone    );  SET userId = V_userid;  SET rc=1;  SET msg= ' add success '; #commit;  END IF; end$ $DELIMITER;

The stored procedure itself has nothing to say, and the only thing worth paying attention to is that the last commit and no commit is different in the MySQL stored procedure.

If a commit is not executed in the stored procedure, the operations performed by the stored procedure are rolled back once the spring container has a transaction rollback. If the stored procedure executes a commit, the transaction for the database itself is committed at this time, even if the transaction is managed in the spring container and automatically rolled back due to other reasons causing an exception in the service code, but this stored procedure is not rolled back. Because the transaction for the data itself has been committed before the stored procedure has been executed, this means that spring rollback is not valid for the operation of the stored procedure.

The MySQL stored procedure is called in Mybatics.