MyBatis (iv) annotations and stored procedures

1. SQL Common Fragment

<sql id= "Publicfindemp" >
        select E.*,depname from Employee E, Department d
        where D.depid=e.depid
< /sql>

Using SQL Common fragments:

<include refid= "Publicfindemp"/>

<!--3.1 paged query and query all-
    <select id= "Findemloyee" parametertype= "Pageutil" resultmap= "Empmap" >
        <choose>
            <!--paging query-
            <when test= "startrow!=0 and pagesize>0" >
                <include refid= " Publicfindemp "/>
                limit #{startrow},#{pagesize}
            </when>
            <!--query All--
            < otherwise>
                <include refid= "publicfindemp"/>
            </otherwise>
        </choose>
    </select>

2. How the stored procedure works

Delimiter//
CREATE PROCEDURE pro_emp (in StartRow int,in pagesize int.)
begin
   Select E.*,d.depname from Employee E,department D
    where d.depid=e.depid limit startrow,pagesize;
End 
//
delimiter;

Delimiter//
CREATE PROCEDURE Proc_emptotalcount (out Empcount int)
begin
    Select COUNT (*) to Empcount from Employee E,department d
    where d.depid = E.depid;
End
//
delimiter;

In Mapper.xml, write this:
1) in

<select id= "Findbyprimarykey" 
parametermap= "Parammap" >>>> Note to write Parametermap
Statementtype= "Callable" >>>> represents a stored procedure
resulttype= "Com.pojo.Employee" >
        call Employeesys.proc_empbyid (?)
</select>
<parametermap id= "Parammap" type= "Employee" >
  <parameter property= "empId" mode= " In "jdbctype=" INTEGER "/>
 </parameterMap>

Type: Encapsulates the types of Java objects
Jdbctype: The data type of the parameter, must be a reference data type, must be uppercase;
Mode: The type of the parameter of the stored procedure, if the input parameter is in, the output parameter is out;
2) out

<select id= "Gettotalcount" 
 parametermap= "Totalcountmap" 
 statementtype= "callable" >
      call Employeesys.proc_emptotalcount (?)
  </select>

<parametermap id= "Totalcountmap" type= "Pageutil" >
     <parameter property= " TotalCount "mode=" Out "jdbctype=" INTEGER "/>
</parameterMap>

3) test on out

        SYSTEM.OUT.PRINTLN ("---gettotalcount (pageutil pageutil)---");
        Pageutil pageUtil1 = new Pageutil (n);
        Mapper.gettotalcount (PAGEUTIL1);
        System.out.println (Pageutil1.gettotalcount ());

Understanding: The stored procedure out is: Out property= "TotalCount", and is encapsulated in type= "Pageutil" inside, so with Pageutil1.gettotalcount () to pick up. Select COUNT (*) into Empcount, the method does not need to return the value, directly into the property inside the fishing. 3) Annotations

To write SQL statements directly inside the Mapper interface, you do not need to use an XML file:

@Select ("SELECT * From Project")
 list<workorder> findName ();
 Paged Query
 @Select ("Select W.*,p.projectname from WorkOrder w,project p where   p.id=w.projectid limit #{startrow},# {pagesize} ")
 list<workorder> findpage (Pageutil pageutil);