MyBatis Use of Dynamic SQL

Mapper.xml tips:

Create a new file in the 1:mapper package: MYBATIS-3-MAPPER.DTD

2: In Web App libraries/mybatis.jar/org.apache.ibatis.builder.xml/mybatis-3-mapper.dtd, open, copy content, Post to yourself new MYBATIS-3-MAPPER.DTD

3: The path to the DTD on the Mapper.xml header is the current directory!

4: Close the Stumapper.xml file and reopen it, there is a hint!

#{} and ${} differences: ${} does not value, regardless of data type, directly into SQL, if it is a string, no single quotes, SQL exception!    statment, SQL injection problem may occur! #{} does not occur: Automatically considers data types, strings are automatically spliced ', preparedstatment

MyBatis: Dynamic SQL Tags

<if> <choose> <when> <otherwise> <where> <set> <foreach> must be able to explain the rest: the reverse generation of Mapper.xml: See for yourself! Extended

<select id= "Findbylike" resulttype= "Com.stu.bean.Stu" parametertype= "Com.stu.bean.Stu" >

Selec * from Stu where 1=1

<if test= "Uname!=null" >and uname=#{uname}</if>

<if test= "Uage!=null" >and uage=#{uage}</if>

<if test= "Usex!=null" >and usex=#{usex}</if>

</select>

<select id= "Findbylike" resulttype= "Com.stu.bean.Stu" parametertype= "Com.stu.bean.Stu" >

Selec * from Stu where 1=1

<if test= "Uname!=null" >or uname=#{uname}</if>

<if test= "Uage!=null" >or uage=#{uage}</if>

<if test= "Usex!=null" >or usex=#{usex}</if>

</select>

Question: If it's always the full table

<select id= "Findbylike" resulttype= "Com.stu.bean.Stu" parametertype= "Com.stu.bean.Stu" >

Selec * from Stu where 1!=1

<if test= "Uname!=null" >or uname=#{uname}</if>

<if test= "Uage!=null" >or uage=#{uage}</if>

<if test= "Usex!=null" >or usex=#{usex}</if> </select> solve the problem above

<select id= "xxx3" parametertype= "Com.stu.bean.Stu" resulttype= "Com.stu.bean.Stu" >

SELECT * from Stu where 1!=1

<choose> <!--if ElseIf ElseIf--

<when test= "Sid!=null" >

or SID = #{sid}

</when>

<when test= "Sname!=null" >

or sname like #{sname}

</when>

<when test= "Sage!=null" >

or sage = #{sage}

</when>

<otherwise>

or saddress like #{saddress}

</otherwise>

</choose> conditions can only be set up one!

</select>

<select id= "xxx4" parametertype= "Com.stu.bean.Stu" resulttype= "Com.stu.bean.Stu" >

SELECT * FROM Stu

<where> <!--automatically stitch where and and by conditions

<if test= "Sname!=null" >

and Sname=#{sname}

</if>

<if test= "Saddress!=null" >

and saddress=#{saddress}

</if>

</where>

</select>

<update id= "xxx5" parametertype= "Com.stu.bean.Stu" >

Update Stu <!--automatically stitch set and comma-to-

<set>

<if test= "Sname!=null" >

Sname=#{sname},

</if>

<if test= "Sage!=null" >

Sage=#{sage},

</if>

<if test= "Ssex!=null" >

Ssex=#{ssex},

</if>

<if test= "Saddress!=null" >

Saddress=#{saddress},

</if>

</set>

<where>

<if test= "Sid!=null" >

SID=#{SID}

</if>

</where>

</update>

<delete id= "AAA" parametertype= "Array" >

Delete from Stu where SID in

<foreach collection= "Array" index= "index" item= "Item" open= "(" separator= "," close= ")" > #{item}

</foreach>

</delete >

=========================================================================================

Reverse Generation:

1: tool requires plugin: Put Dropins, restart my10!

2: Test into the jar package and put MySQL. Jar Import Workspace

3: Check in the Generatorconfig.xml file, modify the path

4: Right button generatorconfig.xml, click on Butterfly xxxexample: Method Condition Example

Use:

Sqlsession session= mybatisutil.getsqlsessionfactory (). Opensession ();

Stumapper sm = session.getmapper (Stumapper.class);

The

  sm.selectbyprimarykey (Integer SID); //is the same as the original XML method that was originally written   sm.selectbyexample ( Stuexample se);    stuexample se = new stuexample ();   Criteria C = Se.createcriteria ();  C . Andxxxxx ()   sm.selectbyexample (null);  queries all   sm.countbyexample (Stuexample se); Number of rows   sm.deletebyexample (Stuexample e) for the criteria of the criteria;   sm.deletebyprimarykey (Integer SID); //and original write XML method like   sm.insert (Stu s);  // As you originally wrote the XML method, pay attention to the primary key!   sm.insertselective (Stu s);   which property is not null, which property is inserted   sm.updatebyexample (Stu s, Stuexample e);   sm.updatebyexampleselective (Stu s, stuexample e);   sm.updatebyprimarykey (Stu s);  //and originally wrote the XML method as   sm.updatebyprimarykeyselective ( Stu s);  which property is not null, modify which property       condition or:   se.or (criteria2);

Sort: se.setorderbyclause ("Sid Desc");

Paging: Oracle:select * FROM (select X1.*,rownum RR from (SELECT * to Stu ORDER by Sid Desc) X1 where rr<=5 *n) x2 where x2.rr>=5*n-4

Mysql:select * from Stu ORDER by Sid DESC Limit 5*n-5,5

MyBatis: Do not use mapper, use Session.selectlist ();     Use a class: Rowbounds sqlsession session= mybatisutil.getsqlsessionfactory (). Opensession ();     Rowbounds RB = new Rowbounds (pagesize*pagenow-pagesize,pagesize); list<stu> list = session.selectlist ("Com.aaa.mapper.StuMapper.selectByExample", NULL, RB);

Focus: Understanding Selective: All attributes added if Judgment Example: Object template criteria:sql conditional template stuexample se = new stuexample ();          Criteria C = Se.createcriteria (); Skilled use: Xxxexample class

============================================================================

Job: 2 table crud servlet-"biz->mapper (page blur query)

Sqlsessionfactroy role: The Role of sqlsession: Stumapper.xml Description: Must be clear, dynamic SQL tags, resulttype resultmap objects between the relationship XML description ${} and #{} difference! Stuexample: Provides conditional templates, provides sorting, and provides the role of de-redundancy criteria: sort Paging

=========================================================================

GROUP BY: Groups: As long as the use of grouping, only must query group information Group information: select group name, 5 aggregate functions from the table name GROUP by group name have: to the group to add conditions, only the group name, 5 aggregate functions

Fit Sort: SELECT * from Stu Order by Sage DESC,SID ASC compliant Group: SELECT Ssex,count (*) from Stu Group by ssex,saddress ... is to divide ssex and saddress into a group!

MyBatis Use of Dynamic SQL