Mybatis一些使用小技巧(随时更新)

1.1对1实体类映射可以不使用association

如图所示 User实体类中关联着部门表

而在数据库中是一个字段 office_id
我们需要使用resultMap来进行映射

<resultMap id="userResult" type="User">
		<id property="id" column="id" />
		<result property="loginName" column="loginName" />
		<result property="password" column="password" />
		<result property="loginFlag" column="login_flag"/>
		<result property="office.id" column="office.id" /> //同理插入与更新都可以直接使用.属性名
		<result property="office.name" column="office.name" />
		<result property="office.parentIds" column="office.parentIds" />
	</resultMap>

查询语句举例 查询出一个用户的所有信息(包括office信息)

<select id="get" resultMap="userResult">
		SELECT
			a.id,a.login_name AS "loginName",
			a.office_id AS "office.id",    //起别名直接与ResultMap的属性名映射
			o.name AS "office.name",
			o.parent_id AS "office.parent.id" 
		FROM sys_user a
		LEFT JOIN sys_office o ON o.id = a.office_id   //连接查询
		WHERE a.id = #{id}
	</select>

2.使用标签判断多数据源执行不同的语句

<select id="findListByOffice" resultMap="userResult">
		SELECT
			<include refid="userColumns"/>
		FROM sys_user a
		<include refid="userJoins"/>

		WHERE a.del_flag = #{DEL_FLAG_NORMAL}

		<if test="office != null and office.id != null and office.id != ''">
			AND o.id = #{office.id}
		</if>
		<if test="office == null">
			AND (o.id = ''  or o.id is null)
		</if>

		<if test="loginName != null and loginName != ''">
			AND a.login_name like
			//多数据源判断
					<if test="_databaseId == 'postgre'">'%'||#{loginName}||'%'</if>
					<if test="_databaseId == 'oracle'">'%'||#{loginName}||'%'</if>
					<if test="_databaseId == 'mysql'">CONCAT('%', #{loginName}, '%')</if>
					<if test="_databaseId == 'mssql'">'%'+#{loginName}+'%'</if>
		</if>
		<!-- 排序 -->
		ORDER BY  a.name
	</select>

3.collection标签的嵌套查询来映射自定义List

 返回给前端必须为List对象的时候 
 可以自己创建一个不存在于数据库的实体类属性赋值后返回给前端
 插入、更新时 正常使用String类型的一串id或信息即可 
 该情景常见于1张表里有个字段存放多个id

使用collection标签的嵌套查询
此方法可能要实体类中加入注解 @JsonIgnoreProperties(value = { “handler”})

<resultMap id="MonitorModelResultMap" type="com.jeeplus.modules.system.entity.MonitorModel">
		<id property="id" column="id"/>
		<result property="monitorFactorids" column="monitor_factor_ids"/>
		<collection property="monitorFactors" select="findInFactor"  column="monitor_factor_ids" ofType="MonitorFactor"> // 映射到这个属性时候 先执行select里的sql 参数为column 解决集放入这个属性
		</collection>
	</resultMap>
	//嵌套查询如下 查询出因子信息 会在映射时自动执行这个sql 此需要写入dao接口中
	
	<select id="findInFactor" parameterType="string"  resultType="com.jeeplus.modules.system.entity.MonitorFactor">
		select f.id,f.name,f.fcode from monitor_factor f
		where find_in_set(f.id,#{ids})!=0
	</select>

4.使用和来减少sql语句的冗余

  数据字段查询量很大+多个sql反复使用的时候
 可以将select字段放入<sql>标签里

<sql id="userColumns">
    	a.id,
    	a.office_id AS "office.id",
    	a.login_name AS "loginName",
    	a.password,
		a.name,
		a.email,
		a.phone,
		a.mobile,
		a.login_ip,
		a.login_date,
		a.remarks,
		a.login_flag,
		a.photo,
		a.qrcode,
		a.sign,
		a.create_by AS "createBy.id",
		a.create_date,
		a.update_by AS "updateBy.id",
		a.update_date,
		a.del_flag,
    	o.name AS "office.name",
    	o.parent_id AS "office.parent.id",
    	o.parent_ids AS "office.parentIds",
    	oa.id AS "office.area.id",
    	oa.name AS "office.area.name",
    	oa.parent_id AS "office.area.parent.id",
    	oa.parent_ids AS "office.area.parentIds"
    </sql>
    <sql id="userJoins">
		LEFT JOIN sys_office o ON o.id = a.office_id
		LEFT JOIN sys_area oa ON oa.id = o.area_id
    </sql>

 使用 <include>调用写好的<sql>

<select id="get" resultMap="userResult">
		SELECT
			<include refid="userColumns"/>,
		FROM sys_user a
		   <include refid="userJoins"/>
		WHERE a.id = #{id}
	</select>

5.在mybatis中 in与not in 条件必须为集合

    如题  记得传List然后遍历即可

<select id="siteForON" parameterType="map" resultType="com.jeeplus.modules.siteinfo.entity.SiteInfo">
		select s.id, o.name from site_info s
		left join sys_office o on o.id=s.office_id
		where o.type='6'  and
		s.id not in
		<foreach collection="strings" item="item" index="index" open="(" close=")" separator=",">
			#{item}
		</foreach>
	</select>

6.sql 语句加入根据条件变化别名

select
        sum(pulp_volume) as "pulpVolume",
        (case pulp_key     //条件别名
        when 'caoxian' then '曹县'
        when 'daming' then '大名'
        when 'feicheng' then '肥城'
        when 'juxian' then '莒县'
        when 'linqu' then '临朐'
        when 'ningyang' then '宁阳'
        when 'qihe' then '齐河'
        when 'shanting' then '山亭'
        when 'xiajin' then '夏津'
        when 'xinglong' then '兴隆'
        when 'yanggu' then '阳谷'
        when 'yishui' then '沂水'
        when 'yuncheng' then '郓城'
        when 'zhangqiu' then '章丘'
        when 'puding' then '普定'
        when 'huangping' then '黄平'
        when 'ziyun' then '紫云'
        when 'youyang'then '酉阳'
        when 'wenchang'then '文昌'
        end
        ) as "pulp"
        from tb_pulp_station
        where 1=1
        <if test="nowDate!=null and nowDate!=''">
            and date_format(create_date,"%Y") = #{nowDate}
        </if>
        <if test="nowDate==null or nowDate==''">
            and date_format(create_date,"%Y") = date_format(now(),"%Y")
        </if>
        <if test="plup!=null">
            and pulp_key in
            <foreach collection="plup" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY pulp_key   //分组 查询sum总数
        order by  CONVERT(pulp_key USING GBK) ASC   //排序 按照首字母A-Z