1. Basic addition, deletion, modification and query

From reverse generation

Code example: only add and query are selected

increase

 <insert id="insert" parameterType="com.csf.model.Book" >
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>

Check

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
</select>

Tag signature is the method type. Each attribute:

id: method name

parameterType: return type

Then, the specific sql statement is in the label content, where you can implement dynamic sql through if and foreach.

For example:

Query using foreach

<select id="selectBooksIn" resultType="com.javaxl.model.Book" parameterType="java.util.List">
  select * from t_mvc_book where bid in
  <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    #{bid}
  </foreach>
</select>

Use foreach loop to get a data set, and then query according to the data set.

The if implementation is in the methods generated by default.

2. # vs. $

Are used to reference parameter values in sql statements

#: there will be single quotation marks in the statement, so there is no need to add single quotation marks manually, but it is lack of flexibility

$: the usage is similar to the above, but without single quotation marks, so it is more flexible. However, it also has the risk of sql injection.

3. Configuration of query result set

Five situations

1 use resultMap to return a collection of custom types

2 use resultType to return list < T >

3 use resultType to return a single object

4. Use resultType to return list < Map >, which is applicable to the result set returned by multi table query

5. Use resultType to return map < string, Object >, which is suitable for multi table queries to return a single result set

Corresponding to various application scenarios

resultMap is suitable for querying user-defined entity classes

Others are applicable when the return value is a data type provided by the jdk rather than a user-defined entity class

4. Paging query

In pom POM dependency of importing paging plug-in in XML

       <dependency>
         <groupId>com.github.pagehelper</groupId>
         <artifactId>pagehelper</artifactId>
         <version>5.1.2</version>
       </dependency>

Configure the pagehelper plug-in into mybatis in the main configuration file of mybatis

       <!-- Configuring paging plug-ins PageHelper, 4.0.0 Later versions support automatic identification of the database used -->
       <plugin interceptor="com.github.pagehelper.PageInterceptor">
       </plugin>

For specific use, you need to start paging before the query statement

if(pageBean != null && pageBean.isPagination()){
        PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
}

The parameters passed in here are the starting subscript and offset

After the query statement, the result set is processed

Here, with the help of pageBean, it is more convenient for us to view the results

 if(pageBean != null && pageBean.isPagination()){
        PageInfo pageInfo = new PageInfo(list);
        System.out.println("Page:"+pageInfo.getPageNum());
        System.out.println("Page size:"+pageInfo.getPageSize());
        System.out.println("Total record:"+pageInfo.getTotal());
        pageBean.setTotal(pageInfo.getTotal()+"");
 }

5. Processing of special characters

Because the xml file is used to write sql statements, the greater than and less than signs can no longer be used, because they will conflict with the xml file format.

1. Wrap with special label

<![CDATA[ ]]>

use:

<select id="userInfo" parameterType="java.util.HashMap" resultMap="user">   
     SELECT id,newTitle, newsDay FROM newsTable WHERE 1=1  
     AND  newsday <![CDATA[>=]]> #{startTime}
     AND newsday <![CDATA[<= ]]>#{endTime}  
  ]]>  
 </select>  

2. Escape

Special character escape sequence
    <           &lt;
    >           &gt;
    &           &amp;
    "           &quot;
    '           &apos;