MyBatis call MySQL stored procedure with output input parameters

http://lohasle.iteye.com/blog/1669879

Stored procedures are the same, but according to their preferences, you can use map or JavaBean to pass parameters.

SQL code

2. -- --------------------------------------------------------------------------------
4. --Routine DDL
6. --note:comments before and after the routine body is not being stored by the server
8. -- --------------------------------------------------------------------------------
10. DELIMITER $$
14. CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' SelectCount ' (
16. in Pcsid int,
18. in Drid int,
20. in Partnerid int,
22. in customerId int,
24. Out pcscount int,
26. Out drcount int
30. )
32. BEGIN
36. Select count (md.id) to @pcsC from mdm_device md
38. Left join Mdm_device_security mds on mds.device_id = md.id
40. where mds.device_rooted = Pcsid
42. and md.partner_id = Partnerid and md.customer_id = customerId;
44. Set pcscount = @pcsC;
50. Select count (md.id) to @drC from mdm_device MD
52. where md.managed_status = Drid and Date_sub (Curdate (), INTERVAL 7 day ) <= DATE (md.un_manage_date)
54. and md.partner_id = Partnerid and md.customer_id = customerId;
56. Set drcount = @drC;
64. END

1.java call incoming map. Get via map.

1.1 Mapper File notation

XML code

2. <parametermap type="map" id="Homevo">
4. <parameter property="Pcsid" jdbctype="INTEGER" mode="in"/>
6. <parameter property="Drid" jdbctype="INTEGER" mode="in"/>
8. <parameter property="Partnerid" jdbctype="INTEGER" mode="in"/>
10. <parameter property="customerId" jdbctype="INTEGER" mode="in"/>
12. <parameter property="Pcscount" jdbctype="INTEGER" mode="out"/>
14. <parameter property="Drcount" jdbctype="INTEGER" mode="out"/>
16. </parametermap>
18. <select id= "selectforhome" parametermap="Homevo "
20. statementtype="callable">
22. {Call SelectCount (
24. ?,?,?,?,?,?
26. )}
28. </Select>

1.2 Java call notation

Java code

2. @Override
4. Public Stringselecthomecount (Homevo Home) throws Exception {
6. map<string, object> map = new hashmap<string, object> ();
8. Map.put ("Pscid", 0);
10. Map.put ("Drid", 1);
12. Map.put ("Partnerid", 25);
14. Map.put ("CustomerId", 50);
16. Map.put ("Isolation", 1);
18. SelectOne ("Mapper.selectforhome", map);
20. System.out.println (Map.get ("Pcscount"));
24. return Map.get ("Drcount");
26. }

2.java calls incoming JavaBean. The return value is obtained through the JavaBean property

2.1 Passing Parameters mapper notation by JavaBean

XML code

2. <parametermap type="Com.polysaas.mdm.device.entity.HomeVO" id="Home">
4. <parameter property="Pcsid" jdbctype="INTEGER" mode="in"/>
6. <parameter property="Drid" jdbctype="INTEGER" mode="in"/>
8. <parameter property="Partnerid" jdbctype="INTEGER" mode="in"/>
10. <parameter property="customerId" jdbctype="INTEGER" mode="in"/>
12. <parameter property="Pcscount" jdbctype="INTEGER" mode="out"/>
14. <parameter property="Drcount" jdbctype="INTEGER" mode="out"/>
16. </parametermap>

JavaBean can be mapped in two ways

XML code

2. <resultmap type="Com.polysaas.mdm.device.entity.HomeVO" id="Home">
4. <result column="Partnerid" property="Partnerid" jdbctype="INTEGER"/ >
6. <result column="customerId" property="customerId" jdbctype="INTEGER" />
8. <result column="Pcsid" property="Pcsid" jdbctype="INTEGER" />
10. <result column="Drid" property="Drid" jdbctype="INTEGER" />
12. <result column="Pcscount" property="Pcscount" jdbctype="INTEGER" / >
14. <result column="Drcount" property="Drcount" jdbctype="INTEGER" />
16. </resultmap>
18. <Select id= "selectForHome2" parametertype=" Com.polysaas.mdm.device.entity.HomeVO "
20. statementtype="callable">
22. {Call SelectCount (
24. #{pcsid,jdbctype=INTEGER, mode= in},
26. #{drid, jdbctype=INTEGER,mode= in},
28. #{partnerid, jdbctype=INTEGER,mode= in},
30. #{customerid, jdbctype=INTEGER,mode= in},
32. #{pcscount, jdbctype=INTEGER,mode= out},
34. #{drcount, jdbctype=INTEGER,mode= out}
36. )}
38. </Select>

2.2 Using JavaBean to pass parameter Java call notation

Java code

2. @Override
4. Public Homevo Selecthomecount (Homevo Home) throws Exception {
6. Homevo vo = new Homevo ();
8. Vo.setpcsid (0);
10. Vo.setdrid (0);
12. Vo.setpartnerid (25);
14. Vo.setcustomerid (50);
16. Vo.setpcscount (0);
18. Vo.setdrcount (0);
20. SelectOne ("com.polysaas.mdm.device.mapper.MdmDeviceMapper.selectForHome2", VO);
22. return VO;
24. }

Encountered a lot of exceptions: Remember to write down

1. By the time of JavaBean, #{pcsid, mode=in, Javatype=integer},javatype this attribute is required.

2. Transfer using JavaBean. The output parameter does not require an initial value. Personal preferences and recommended use of JavaBean, because the definition is clearer, encapsulation.

3.map can be used as a placeholder question mark, JavaBean. There will be an exception No value specified for parameter 1

4. Say you don't know a parameter. There may be no stored procedure created, this occurs in multiple libraries

5. Read only This may be a transaction control read-only: I am because the stored procedure has a set statement, and the method is called SELECTAAAA ().

MyBatis call MySQL stored procedure with output input parameters