Mybatis 实用操作记录

1. tkMapper通用baseMapper,实现通用CRUD，省时，省力，放心
2. xml常用的方法记录；eg：批量新增，条件查询，通用条件sql，返回数据

上代码：

Spring boot集成，tkMapper实现通用CRUD：

详细说明连接：常用方法说明

一：启动类，MapperScan，注意导包是 tk.mybatis

import tk.mybatis.spring.annotation.MapperScan;
@MapperScan("com.dao")

二：父接口,一般放在common里， 注意：不能被扫描

@Repository
public interface TkMapper<T> extends Mapper<T>, MySqlMapper<T> {
}

三：继承 TkMapper

@Repository
public interface SystemConfigureDao extends TkMapper<SystemConfigure> {
    /**
     * 获取系统参数列表
     * @param name 参数名称
     * @return list
     */
    Page<SystemConfigure> list(String name);

}

四：继承后就会有下方一系列方法，啥也不用写了。一些复杂的逻辑，多表操作的时候，就老实写吧，只方便CRUD。

 @Override
    public int add(SystemConfigure systemConfigure) { return systemConfigureDao.insertSelective(systemConfigure); }

    @Override
    public int edit(SystemConfigure systemConfigure) { return systemConfigureDao.updateByPrimaryKeySelective(systemConfigure); }

    @Override
    public int del(SystemConfigure systemConfigure) { return systemConfigureDao.deleteByPrimaryKey(systemConfigure); }

    @Override
    public SystemConfigure get(String uuid) { return systemConfigureDao.selectByPrimaryKey(uuid); }

    @Override
    public PageInfo<SystemConfigure> list(SystemConfigure systemConfigure,Integer currentPage, Integer pageSize) {
        PageHelper.startPage(currentPage, pageSize);
        List<SystemConfigure> list = systemConfigureDao.select(systemConfigure);
        PageInfo result = new PageInfo(list);
        return result;
    }

五：xml里实用操作

1. 批量新增

  <insert id="insert" parameterType="java.util.List">
        INSERT INTO flow ( uuid,ip, province,city,ad_code,create_date ) VALUES
        <foreach collection="list" item="flow" index="index" separator=",">
            (
            #{flow.uuid},
            #{flow.ip},
            #{flow.province},
            #{flow.city},
            #{flow.adCode},
            #{flow.createDate}
            )
        </foreach>
    </insert>

2. 通用 sql

  <sql id="common_and">
    and deleted <![CDATA[ <> ]]> 1 and disabled <![CDATA[ <> ]]> 1
  </sql>

<select id="checkOrgs" parameterType="Orgs" resultType="java.lang.Integer">
     select count(*) from orgs where caption=#{caption} and prov=#{prov} <include refid="common_and" /> and auditstatus=1
  </select>

3. 多条件查询

<!-- 机构列表查询 -->
  <select id="list" parameterType="java.util.Map" resultType="com.hgs.hmc.domain.doctormgdomain.response.OrgsResponse">
    <include refid="org_select" />
    from orgs o
    left join orgtypes t on o.typeid=t.gid
    left join orgrades r on r.gid=o.gradeid
    left join orgusers u on u.parentid=o.gid
    left join orginfos i on i.parentid=o.gid
    left join orgaudits a on o.gid=a.orgid
    <!-- 有if条件成立时，会给开头加上where 条件 ，避免了 where 1=1 的尴尬 - -！ -->
    <where>
    <if test="_parameter.containsKey('arch') and arch != null ">and o.arch = #{arch}</if>
    <if test="_parameter.containsKey('tCaption') and tCaption != null and tCaption != '' ">and t.caption = #{tCaption}</if>  <!-- 类型 -->
    <if test="_parameter.containsKey('rCaption') and rCaption != null and rCaption != '' ">and r.caption = #{rCaption}</if>  <!-- 等级 -->
    <if test="_parameter.containsKey('caption') and caption != null and caption != '' ">and o.caption like CONCAT('%', #{caption}, '%')</if>
    <if test="_parameter.containsKey('phone') and phone != null and phone != '' ">and o.phone like CONCAT('%', #{phone}, '%')</if>
    <if test="_parameter.containsKey('prov') and prov != null and prov != '' ">and o.prov = #{prov}</if>
    <if test="_parameter.containsKey('city') and city != null and city != '' ">and o.city = #{city}</if>
    <if test="_parameter.containsKey('area') and area != null and area != '' ">and o.area = #{area}</if>
    <if test="_parameter.containsKey('paid') and paid != null ">and o.paid = #{paid}</if>
    <if test="_parameter.containsKey('auditstatus') and auditstatus != null ">and o.auditstatus = #{auditstatus}</if>
    <if test="_parameter.containsKey('disabled') and disabled != null ">and o.disabled = #{disabled}</if>
    <if test="_parameter.containsKey('startTime') and startTime != null and startTime != ''">AND o.buildtime &gt;= #{startTime}</if>
    <if test="_parameter.containsKey('endTime') and endTime != null and endTime != ''">AND o.buildtime &lt;= #{endTime}</if>
    </where>
    order by o.seqno asc
  </select>

4. 多表查询时，数据量相对较少时考虑不要用mybatis的 <collection>、<assocation>等标签，可以不考虑延迟加载 , sql写夺表查询，用一下接收实体类来接就行！

resultType="com.hgs.hmc.domain.doctormgdomain.response.OrgsResponse"

<!-- 常用-查询日期处理 -->
			<if test=" !_parameter.containsKey('timeLeft') ">
                and to_days(e.create_date) = to_days(now())
            </if>
            <if test="_parameter.containsKey('timeLeft') and timeLeft == timeRight ">
                and date_format(e.create_date,'%Y-%m-%d') = #{timeLeft}
            </if>
            <if test="_parameter.containsKey('timeLeft') and timeLeft != null and timeLeft != '' and timeRight == null and timeRight == ''   ">
                and e.create_date &gt;= #{timeLeft}
            </if>
            <if test="_parameter.containsKey('timeLeft') and timeLeft != timeRight ">
                and e.create_date &gt;= #{timeLeft} and e.create_date &lt;= date_add(#{timeRight}, interval 1 day)
            </if>

-- 查询某数据库某表的，所有字段拼接  epay 数据库， menu 表 ， m. 多表查询时的 别名
SELECT GROUP_CONCAT('m.',column_name)from information_schema.columns where TABLE_SCHEMA = 'epay' and TABLE_NAME = 'menu'; 
-- 字段值拼接，值带单引号
SELECT GROUP_CONCAT('''',column_name,'''')