Java架构师学习日记-Mybatis

mybatis能够自动生成sql语句，也能够添加自定义sql语句

使用mybatis generator生成XX.java,XXExample.java,XXmapper.java,XXmapper.xml四个数据库表映射文件，

一、自动生成的 mapper

1）Example实例解析

mybatis的逆向工程中会生成实例及实例对应的XXExample.java，XXExample.java包含了对象的常用查询方法

XXExample有三个内部类，GeneratedCriteria，Criteria，Criterion

创建内部类Criteria用于查询，代码示例如下看表达：

XXExample example = new  XXExample();//新建XX对象的example对象进行查询，XXExample继承XX
XXExample.Criteria  c = example.creatCriteria();//new一个当前example的内部类Criteria
c.andXXIsNull();//设置查询条件，某值为空
c.andXXIsNotNull();//设置查询条件，某值不空
//使用mapper进行查询
List<XX> data = new XXMapper.selectByExample(example);

方法	说明
example.or(XXExample.Criteria)；	Criteria用或连接
example.setOrderByClause(“字段名 ASC”);	添加升序排列条件，DESC为降序
example.setDistinct(false)	去除重复，boolean型，true为选择不重复的记录。
criteria.andXxxIsNull	添加字段xxx为null的条件
criteria.andXxxIsNotNull	添加字段xxx不为null的条件
criteria.andXxxEqualTo(value)	添加xxx字段等于value条件
criteria.andXxxNotEqualTo(value)	添加xxx字段不等于value条件
criteria.andXxxGreaterThan(value)	添加xxx字段大于value条件
criteria.andXxxGreaterThanOrEqualTo(value)	添加xxx字段大于等于value条件
criteria.andXxxLessThan(value)	添加xxx字段小于value条件
criteria.andXxxLessThanOrEqualTo(value)	添加xxx字段小于等于value条件
criteria.andXxxIn(List<？>)	添加xxx字段值在List<？>条件
criteria.andXxxNotIn(List<？>)	添加xxx字段值不在List<？>条件
criteria.andXxxLike(“%”+value+”%”)	添加xxx字段值为value的模糊查询条件
criteria.andXxxNotLike(“%”+value+”%”)	添加xxx字段值不为value的模糊查询条件
criteria.andXxxBetween(value1,value2)	添加xxx字段值在value1和value2之间条件
criteria.andXxxNotBetween(value1,value2)	添加xxx字段值不在value1和value2之间条件

2）mapper接口中的方法解析

mapper.java的函数及方法,按命名的字面意思理解用途。

countByExample，带example的一般都是使用example查询条目数。selectByPrimaryKey使用id查询结果集

mapper中的方法和xml中的sql语句是对应的，

如果在mapper中传入example对象，xml中会解析example的值，取值拼写sql语句进行查询

传入String类型的key，id等，xml直接取值拼写sql进行查询

方法	功能说明
int countByExample(UserExample example) thorws SQLException	按条件计数
int deleteByPrimaryKey(Integer id) thorws SQLException	按主键删除
int deleteByExample(UserExample example) thorws SQLException	按条件删除
String/Integer insert(User record) thorws SQLException	插入数据（返回值为ID）
String/Integer insertSelective(User record) thorws SQLException	就会只给有值的字段赋值（会对传进来的值做非空判断）
User selectByPrimaryKey(Integer id) thorws SQLException	按主键查询
List selectByExample(UserExample example) thorws SQLException	按条件查询
int updateByPrimaryKey(User record) thorws SQLException	按主键更新
int updateByPrimaryKeySelective(User record) thorws SQLException	按主键更新值不为null的字段
int updateByExample(User record, UserExample example) thorws SQLException	按条件更新
int updateByExampleSelective(User record, UserExample example) thorws SQLException	按条件更新值不为null的字段

二、自定义生成的 mapper

1）select

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM PERSON WHERE ID = #{id}
</select>

List<OmsOrder> getList(@Param("queryParam") OmsOrderQueryParam queryParam);

<select id="getList" resultMap="com.macro.mall.mapper.OmsOrderMapper.BaseResultMap">
    SELECT *
    FROM
    oms_order
    WHERE
    delete_status = 0
    <if test="queryParam.orderSn!=null and queryParam.orderSn!=''">
        AND order_sn = #{queryParam.orderSn}
    </if>
    <if test="queryParam.status!=null">
        AND `status` = #{queryParam.status}
    </if>
    <if test="queryParam.sourceType!=null">
        AND source_type = #{queryParam.sourceType}
    </if>
    <if test="queryParam.orderType!=null">
        AND order_type = #{queryParam.orderType}
    </if>
    <if test="queryParam.createTime!=null and queryParam.createTime!=''">
        AND create_time LIKE concat(#{queryParam.createTime},"%")
    </if>
    <if test="queryParam.receiverKeyword!=null and queryParam.receiverKeyword!=''">
        AND (
        receiver_name LIKE concat("%",#{queryParam.receiverKeyword},"%")
        OR receiver_phone LIKE concat("%",#{queryParam.receiverKeyword},"%")
        )
    </if>
</select>

2）insert update delete

<insert id="insertAuthor">
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

<update id="updateAuthor">
  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>

<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>

int delivery(@Param("list") List<OmsOrderDeliveryParam> deliveryParamList);

<update id="delivery">
    UPDATE oms_order
    SET
    delivery_sn = CASE id
    <foreach collection="list" item="item">
        WHEN #{item.orderId} THEN #{item.deliverySn}
    </foreach>
    END,
    delivery_company = CASE id
    <foreach collection="list" item="item">
        WHEN #{item.orderId} THEN #{item.deliveryCompany}
    </foreach>
    END,
    delivery_time = CASE id
    <foreach collection="list" item="item">
        WHEN #{item.orderId} THEN now()
    </foreach>
    END,
    `status` = CASE id
    <foreach collection="list" item="item">
        WHEN #{item.orderId} THEN 2
    </foreach>
    END
    WHERE
    id IN
    <foreach collection="list" item="item" separator="," open="(" close=")">
        #{item.orderId}
    </foreach>
    AND `status` = 1
</update>

3）sql

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>,
    <include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

3）resultMap

public class OmsOrderReturnApplyResult extends OmsOrderReturnApply {
    @Getter
    @Setter
    @ApiModelProperty(value = "公司收货地址")
    private OmsCompanyAddress companyAddress;
}

OmsOrderReturnApplyResult getDetail(@Param("id")Long id);

<resultMap id="returnApplyDetailResultMap" type="com.macro.mall.dto.OmsOrderReturnApplyResult" extends="com.macro.mall.mapper.OmsOrderReturnApplyMapper.BaseResultMap">
    <association property="companyAddress" resultMap="com.macro.mall.mapper.OmsCompanyAddressMapper.BaseResultMap" columnPrefix="ca_"/>
</resultMap>

<select id="getDetail" resultMap="returnApplyDetailResultMap">
    SELECT
        ra.*, ca.id ca_id,
              ca.address_name ca_address_name,
              ca.`name` ca_name,
              ca.phone ca_phone,
              ca.province ca_province,
              ca.city ca_city,
              ca.region ca_region,
              ca.detail_address ca_detail_address
    FROM
        oms_order_return_apply ra
        LEFT JOIN oms_company_address ca ON ra.company_address_id = ca.id
    WHERE ra.id=#{id};
</select>

二、动态SQL

3）if

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

3）choose、when、otherwise

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

3）trim、where、set

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

3）foreach

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

int insertList(@Param("list") List<CmsPrefrenceAreaProductRelation> prefrenceAreaProductRelationList);

<insert id="insertList">

    insert into cms_prefrence_area_product_relation (prefrence_area_id, product_id) values
    <foreach collection="list" item="item" separator="," index="index">
        (#{item.prefrenceAreaId,jdbcType=BIGINT},
        #{item.productId,jdbcType=BIGINT})
    </foreach>
</insert>

posted @ 2020-12-17 17:06 荭枫阅读(159) 评论(0) 收藏举报

刷新页面返回顶部