Java Mybatis Xml写法查询语句的动态设计总结

浅浅的花香味﹌ 2022-01-07 02:17 282阅读 0赞

太原工作 2019-6.25

**文章总结**

**主要Xml中  Page分页对象中  定义一个Object 类型的属性，后台Dao中接受的时候 参数是Page分页对象， **

**这时可以根据Page中的condition对象 是否被定义 去判断 查询数据库时是否在  where后面动态的添加判断语句**

在controller Page分页对象

>     public class Pager {
>     /*中间的变量就不写了  不是重点*/
>        private Object condition;}

Controller调用   实例化Page对象 把对象发给到Page的Condition字段中

>     Pager pagerMember = new Pager();
>     pagerMember.setPageSize(2);
>     BookContactMember bookContactMember = new BookContactMember();
>     bookContactMember.setName("");
>     bookContactMember.setTelphone("");
>     bookContactMember.setContactId(bookContact.getId().toString());
>     pagerMember.setCondition(bookContactMember);

Dao 中

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.test.service.module.bookContact.dao.mapper.BookContactMapper">
    <resultMap id="BaseResultMap" type="com.leimingtech.core.entity.base.BookContact">
    		<result column="id" property="id" jdbcType="INTEGER" />
    		<result column="user_id" property="userId" jdbcType="VARCHAR" />
    		<result column="contact_title" property="contactTitle" jdbcType="VARCHAR" />
    		<result column="contact_describe" property="contactDescribe" jdbcType="VARCHAR" />
    		<result column="contact_img" property="contactImg" jdbcType="VARCHAR" />
    		<result column="trade_type" property="tradeType" jdbcType="VARCHAR" />
    		<result column="contact_type" property="contactType" jdbcType="VARCHAR" />
    		<result column="phone_is_read" property="phoneIsRead" jdbcType="VARCHAR" />
    		<result column="card_change" property="cardChange" jdbcType="VARCHAR" />
    		<result column="in_must_item" property="inMustItem" jdbcType="VARCHAR" />
    		<result column="in_check" property="inCheck" jdbcType="VARCHAR" />
    		<result column="is_check" property="isCheck" jdbcType="VARCHAR" />
    		<result column="check_status" property="checkStatus" jdbcType="VARCHAR" />
    		<result column="check_remark" property="checkRemark" jdbcType="VARCHAR" />
    		<result column="sort_type" property="sortType" jdbcType="VARCHAR" />
    		<result column="read_num" property="readNum" jdbcType="INTEGER" />
    		<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    		<result column="is_del" property="isDel" jdbcType="VARCHAR" />
    		<result column="del_remark" property="delRemark" jdbcType="VARCHAR" />
    	</resultMap>
    
    <sql id="whereStatement">
    		<where>
    			1 = 1
    			<if test="condition !=null">
    			<if test="condition.contactTitle != null and condition.contactTitle != ''">
    				AND contact_title LIKE 
    					<if test="dbName == 'oracle'">'%'||#{condition.contactTitle}||'%'</if>
    					<if test="dbName == 'mssql'">'%'+#{condition.contactTitle}+'%'</if>
    					<if test="dbName == 'mysql'">concat('%',#{condition.contactTitle},'%')</if>
    			</if>
    			<if test="condition.contactType != null and condition.contactType != ''">
    				AND contact_type = #{condition.contactType}
    			</if>
    			<if test="condition.tradeType != null and condition.tradeType != ''">
    				AND trade_type = #{condition.tradeType}
    			</if>
    			<if test="condition.inCheck != null and condition.inCheck != ''">
    				AND in_check = #{condition.inCheck}
    			</if>
    			<if test="condition.isCheck != null and condition.isCheck != ''">
    				AND is_check = #{condition.isCheck}
    			</if>
    			<if test="condition.checkStatus != null and condition.checkStatus != ''">
    				AND check_status = #{condition.checkStatus}
    			</if>
    			<if test="condition.isDel != null and condition.isDel != ''">
    				AND is_del = #{condition.isDel}
    			</if>
    			</if>
    		</where>
    	</sql>
    	<select id="findBookContactPagerList" resultMap="BaseResultMap"
    		parameterType="com.leimingtech.service.utils.page.Pager">
    
    		select * from book_contact
    		<include refid="whereStatement" />
    
    		<if test="orderBy == 0">
    			order by id desc
    		</if>
    		<if test="orderBy == 1">
    			order by id asc
    		</if>
    
    	</select>
    </mapper>