Dynamic SQL queries, multi-criteria, paging

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" >< Mapper namespace= "Cn.bdqn.mybatis.mapper.CameraMapper" ><!--key is the where part, which is determined by the validity of the condition using the where tag to do the dynamic-->< Select Id= "Findcamera" resulttype= "Camera" >selectid,typeid,brandid,title,pixel,zoom,price,colorfrom camera<where><ifTest= "Typeid!=null" >typeid=#{typeid}</if><if test= "Brandid!=null" >and brandid=#{brandid}</if><!--because the label is made up of <>, In the XML file is a character with special meaning, error parsing XML, parser Xmlcdata block is ordinary character--><if test= "Pixel!=null" ><! [Cdata[and pixel>=#{pixel}]]></if><if test= "Zoom!=null" ><! [Cdata[and zoom>=#{zoom}]]></if><if test= "Price!=null" ><! [Cdata[and price<=#{price}]]></if><if test= "color!=null" >and color like #{color}</if><if Test= "Title!=null" >and title like #{title}</if></where>order by id</select></mapper>

Package Cn.bdqn.mybatis.mapper;import Java.util.list;import Org.apache.ibatis.annotations.param;import Cn.bdqn.mybatis.entity.camera;import Cn.bdqn.mybatis.entity.grade;import cn.bdqn.mybatis.entity.user;// Through the interface inside the abstract method to invoke the corresponding statement//interface Map file----The SQL statement namespace set to the full class name of the interface//equivalent to the original DAO layer, I only need to define the DAO interface, the specific implementation of the class now do not have to write their own, just get the Mapper interface class can be   save a lot of things, improve the efficiency of development public interface Cameramapper {//based on a conditional search, MyBatis does not automatically identify which property I want to correspond to, I need to add annotations to public list<camera> Findcamera (@Param ("TypeId") long TypeId, @Param ("Brandid") long Brandid, @Param ("pixel") Integer pixel, @Param ("Zoom") Double Zoom, @Param ("price") Double price, @Param ("color") string color, @Param ("title") string title);

MyBatis's own paging mechanism is deprecated: First, SQL statements are used to load all the data in the database back into memory, and then extract the data from that page in memory with the page number I specify and the number of rows per page. Poor efficiency.

Better paging, at the time of the query, I want to see the page statement only displayed. Write the underlying paging statement yourself. With the plug-in mechanism, you write a paging plugin (maybe the company has its own paging plugin)

First step: Add a paging plugin

MyBatis is characterized by a focus on the efficiency of executing statements.

In the configuration file, configure the plugin on the top of the environment

<plugins>    <plugin interceptor= "Cn.bdqn.mybatis.plugin.PaginationInterceptor" >    <property Name= "Dialectclass" value= "Cn.bdqn.mybatis.plugin.MySQLDialect"/>    //page plug-in supports two dialects class Oracle and MySQL  </ Plugin>  </plugins>

Public list<camera> Findcamera (
Cn.bdqn.mybatis.plugin the existence of Pageparam This class @Param ("Pageparam") Pageparam Param, the  name must be called " Pageparam "@Param (" TypeId ") long typeId, @Param (" Brandid ") long Brandid, @Param (" pixel ") Integer pixel, @Param ("Zoom") Double Zoom, @Param ("price") Double price, @Param ("color") string color, @Param ("title") string title);

Dynamic SQL queries, multi-criteria, paging