Mybatis常用示例

mybatis 增删改查示例：

数据库和实体类映射  column：数据库字段值  property: 实体类对应值

<resultMap id="BaseResultMap" type="com.user.api.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="mobile" property="mobile"/>
        <result column="password" property="password"/>
        <result column="gender" property="gender"/>
        <result column="status" property="status"/>
    </resultMap>

 新增：新增之后返回新增数据ID

<insert id="insert" parameterType="com.user.api.entity.User" useGeneratedKeys="true" keyProperty="id">
    insert into user (name, mobile, password, gender,status)
    values (#{name}, #{mobile},#{password}, #{gender},#{status})
 </insert>

修改：

<update id="updateUser" parameterType="com.user.api.entity.User" flushCache="true">
        update user
        <set>
            <if test="mobile != null and mobile != ''">mobile = #{mobile},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="password != null and password != ''">password = #{password}</if>
        </set>
        where id = #{id}
    </update>

删除：

<delete id="delByChannelId">
    delete from user where id = #{id}
 </delete>

查询：

<sql id="where_sql">
        <where>
            <if test="id != null and id > 0">AND id = #{id}</if>
            <if test="name != null and name != ''">
                <bind name="pattern" value="'%' + name + '%'"/>
                AND name like #{pattern}
            </if>
            <if test="mobile != null and mobile != ''">AND mobile = #{mobile}</if>
        </where>
    </sql>
   <select id="listUsers" resultMap="BaseResultMap">
        SELECT * FROM user
        <include refid="where_sql"/>
        order by id desc limit #{limit},#{offset}
   </select>

批量查询：

<select id="listById" parameterType="java.util.List" resultMap="BaseResultMap">
        select * from user
        <where>
            <foreach collection="userIds" item="item" separator="OR">
                id = #{item}
            </foreach>
        </where>
 </select>

查询： choose when otherwise 一旦when条件成立 之后的when 则不会执行，条件都不满足则执行otherwise
//当搜索条件输入了名字按照姓名查找，输入了手机号按手机号查找，否则查询name为张三的用户

<select id="queryUser" resultType="com.user.api.entity.User">
    select * from tb_user WHERE sex=1
    <choose>
        <when test="name!=null and name.trim()!=''">
            and name like '%${name}%'
        </when>
        <when test="mobile!=null and mobile !=''">
            and mobile = #{mobile}
        </when>
        <otherwise>
            and name='张三'
        </otherwise>
    </choose>
  </select>

查询： where if 判断条件如果成立则会执行对应内容

<select id="getUser" resultType="com.user.api.entity.User">
    select * from user
    <where
    1=1
    <if test="name!=null and name.trim()!=''">
        and name like '%${name}%'
    </if>
    <if test="mobile!=null and mobile !=''">
        and mobile = #{mobile}
    </if>
    </where>
 </select>

查询： case when then end

select id,nick_name,
    case
        when `type` = 1 then '正常用户'
        when `type` = 2 then '马甲用户'
        else '无数据'
    end as 用户类型
from user ;

update goods set prize =
    case
        when level = '1' then prize * 1
        when level = '2' then prize * 1.5
        when level = '3' then prize * 2
        else prize * 3
    end

计算关键字匹配度：

SELECT temp.*, ROUND(temp.count / CHAR_LENGTH(temp.title) * 100) AS rate FROM

(SELECT tst.*,
<choose>
   <when test="keywordList != null and keywordList.size() > 0">
      <foreach collection="keywordList" item="item" separator="+">
         IF(CHAR_LENGTH(tst.title) - CHAR_LENGTH(REPLACE(tst.title, #{item}, '')) > 0,1,0)
      </foreach>as count
   </when>
   <otherwise>0 as count</otherwise>
</choose>
FROM
table_A tst
)
AS temp where temp.count > 0
ORDER BY temp.count DESC,rate DESC,temp.id DESC

 

 

查询：left join, right join, join
left join :左连接 会查询左表的所有数据 和满足on 条件的右表数据，没查询出的右表数据用空表示
right join:右连接 会查询右表的所有数据 和满足on 条件的左表数据，没查询出的左表数据用空表示
join: 即inner join 查询满足左右两表的交集数据

对右表过滤要在on 中进行，对左边表过滤要在where 中进行
on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

 

对于单个字符的查询在进行if 判断时候需要写到双引号或者toString()
<if test='gender!=null and gender == "1"'>
and name like '%${name}%'
</if>
或者
<if test="gender!=null and gender == '1'.toString()">
and name like '%${name}%'
</if>
if条件判断number类型，没必要判断''的情况，只需判断null的情况
<if test="status!=null">
and status = #{status}
</if>