Mybatis的初级使用小结

coder chen

于 2022-05-30 21:43:24 发布

阅读量268

点赞数

文章标签： java 开发语言

本文链接：https://blog.csdn.net/weixin_44226752/article/details/125054656

版权

mybatis的尚硅谷笔记

https://blog.csdn.net/u011863024/article/details/107854866

Mybatis的insert

MyBatis的批量insert

Mapper接口：

int insertBatch(List<TcxqBranchPool> records);

xml文件：

oracle写法对应的SQL：

INSERT ALL 
INTO my_table(field_1,field_2) VALUES (value_1,value_2) 
INTO my_table(field_1,field_2) VALUES (value_3,value_4) 
INTO my_table(field_1,field_2) VALUES (value_5,value_6)
SELECT 1 FROM DUAL;

mysql写法对应的SQL：

insert into my_table(field_1,field_2)
values
(value_1,value_2),
(value_1,value_2),
(value_1,value_2);

<insert id="insertBatch"  parameterType="cn.org.nafmii.bean.pool.TcxqBranchPool">
	insert all
    <foreach item="item" index="index" collection="list">
    	into t_cxq_branch_pool 
        	(DCM_ID,STATUS,CRE_USER_ID)
        values 
            (#{item.dcmId,jdbcType=VARCHAR},
            #{item.status,jdbcType=VARCHAR},
            #{item.creUserId,jdbcType=VARCHAR})
    </foreach>
    select 1 from dual
</insert>

MyBatis的动态insert

Mapper接口

int insertSelective(TCxqPool record);

xml文件

<insert id="insertSelective" keyColum="DCM_ID" keyProperty="DCM_ID" parameterType="cn.org.nafmii.bean.pool.TcxqBranchPool" useGeneratedKeys="true">
	insert into T_CXQ_POOL
    <trim prefix="(" suffix=")" suffixOverrides=",">
    	<if test="BASIC_STATUS!=null">
            BASIC_STATUS,
        </if>
        <if test="POOL_STATUS!=null">
            POOL_STATUS,
        </if>
        <if test="STATUS!=null">
            STATUS,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
    	<if test="BASIC_STATUS!=null">
            #{BASIC_STATUS,jdbcType=VARCHAR},
        </if>
        <if test="POOL_STATUS!=null">
            #{POOL_STATUS,jdbcType=VARCHAR},
        </if>
        <if test="STATUS!=null">
            #{STATUS,jdbcType=VARCHAR},
        </if>
    </trim>
</insert>

Mybatis的普通insert方法

Mapper接口

int insert(TCxqPool record);

xml文件

<insert id="insert" keyColum="DCM_ID" keyProperty="DCM_ID" parameterType="cn.org.nafmii.bean.pool.TcxqBranchPool" useGeneratedKeys="true">
    insert into T_CXQ_POOL 
    	(BASIC_STATUS,POOL_STATUS,STATUS)
    values
        (#{BASIC_STATUS,jdbcType=VARCHAR},
        #{POOL_STATUS,jdbcType=VARCHAR},
        #{STATUS,jdbcType=VARCHAR})
</insert>

Mybatis的select

MyBatis的select 动态条件

Mapper接口

List<TCxqBasicGreen> selectAllByWhere(TCxqBasicGreen tCxqBasicGreen);

xml文件

 <select id="findAllForExport" resultType="cn.org.nafmii.bean.basicgreen.TCxqBasicGreen">
    select
      BOND_CODE, BOND_NAME,ORGANIZATION_NAME, ISSUER_AMOUNT, REMAINDER, INTEREST_DAY
    from T_CXQ_BASIC_GREEN
    <where>
      <if test="bondCode != null and bondCode != ''">
        and BOND_CODE like '%' || #{bondCode,jdbcType=VARCHAR} || '%'
      </if>
      <if test="bondName != null and bondName != ''">
        and BOND_NAME like '%' ||  #{bondName,jdbcType=VARCHAR} || '%'
      </if>
    </where>
    order by CRE_TIME desc , BOND_CODE desc
  </select>

MyBatis的动态select service拼接条件

entity实体类

额外定义一个字段，不需要入库:

/**
 * 查询条件
 */
private String others;

service方法

@Override
    public List<TCxqBranchPool> selectByAdd(TCxqBranchPool vo) {
        User user = UserInfoUtil.get();
        if ("2".equals(user.getInstitution())  ) {
            //查询的时候按照省和单列市分类
            String others;
            if(org.springframework.util.StringUtils.isEmpty(user.getCity())) {
                others = " (b.PROVINCES = '" + user.getDistrict()
                        + "' and ( not EXISTS (SELECT DICT_CODE FROM T_CXQ_DATA_DICT WHERE DICT_ITEM='CITY' and DICT_CODE=b.CITY)))";
            } else { // 单列市
                others = " ( b.CITY='" + user.getCity() + "' )";
            }
            vo.setOthers(others);
        }
        return tCxqBranchPoolMapper.selectByAdd(vo);
    }

Mapper接口

 List<TCxqBranchPool> selectByAdd(TCxqBranchPool record);

xml文件

<select id="selectByAdd" parameterType="cn.org.nafmii.bean.pool.TCxqBranchPool" resultMap="BaseResultMap">
SELECT
    tci.ORGANIZATION ,
    tci.ORGANIZATION_NAME ,
    tci .DCM_ID,
    (SELECT tcdd.dict_name FROM T_CXQ_DATA_DICT tcdd WHERE tcdd.DICT_ITEM = 'NAFMII_FST_TRADE' AND tcdd.DICT_CODE = tci.NAFMII_FST_TRADE) as NAFMII_FST_TRADE,
    (SELECT tcdd.dict_name FROM T_CXQ_DATA_DICT tcdd WHERE tcdd.DICT_ITEM = 'NAFMII_SCD_TRADE' AND tcdd.DICT_CODE = tci.NAFMII_SCD_TRADE) as NAFMII_SCD_TRADE,
    (SELECT tcdd.dict_name FROM T_CXQ_DATA_DICT tcdd WHERE tcdd.DICT_ITEM = 'ENTERPRISE_NATURE' AND tcdd.DICT_CODE = tci.ENTERPRISE_NATURE) as ENTERPRISE_NATURE,
    (SELECT tcdd.dict_name FROM T_CXQ_DATA_DICT tcdd WHERE tcdd.DICT_ITEM = 'PROVINCE' AND tcdd.DICT_CODE = PROVINCES) as PROVINCES
    FROM
    T_CXQ_ISSUER tci 
    <where>
      <if test="organizationName != null and organizationName != ''">
        AND b.ORGANIZATION_NAME like '%'||#{organizationName,jdbcType=VARCHAR}||'%'
      </if>
      <if test="organization != null and organization != ''">
        AND b.ORGANIZATION = #{organization,jdbcType=VARCHAR}
      </if>
      <if test="others !=null and others !=''">
        and ${others}
      </if>
    </where>
  </select>

注意：

#{}和${}的区别：

#{}是预编译处理，mybatis在处理#{}，会将SQL中的#{}替换为？，然后使用PreparedStatement的set方法来赋值，可以有效避免SQL注入问题

${}是字符串替换，mybatis处理$ {}时，直接把${}替换成变量的值

Mybatis的update

Mybatis的普通update

mapper接口

int updateByPrimaryKey(TCxqBasicGreenFeedback record);

xml文件

<update id="updateByPrimaryKey" parameterType="cn.org.nafmii.bean.basicgreen.TCxqBasicGreenFeedback">
    update T_CXQ_BASIC_GREEN_FEEDBACK
    set ORGANIZATION = #{organization,jdbcType=VARCHAR},
      COMMUNICATE_DIRECTION = #{communicateDirection,jdbcType=VARCHAR},
      PROVINCES = #{provinces,jdbcType=VARCHAR},
      CITY = #{city,jdbcType=VARCHAR},
    where FEEDBACK_ID = #{feedbackId,jdbcType=VARCHAR}
  </update>

Mybatis的动态update

mapper接口

int updateByPrimaryKeySelective(TCxqBasicGreenFeedback record);

xml文件

<update id="updateByPrimaryKeySelective" parameterType="cn.org.nafmii.bean.basicgreen.TCxqBasicGreenFeedback">
    update T_CXQ_BASIC_GREEN_FEEDBACK
    <set>
      <if test="organization != null">
        ORGANIZATION = #{organization,jdbcType=VARCHAR},
      </if>
      <if test="organizationName != null">
        ORGANIZATION_NAME = #{organizationName,jdbcType=VARCHAR},
      </if>
      <if test="communicateDirection != null">
        COMMUNICATE_DIRECTION = #{communicateDirection,jdbcType=VARCHAR},
      </if>
    </set>
    where FEEDBACK_ID = #{feedbackId,jdbcType=VARCHAR}
  </update>

Mybatis的delete

Mybatis的单条删除

mapper接口

int deleteByPrimaryKey(String dcmId);

xml文件

  <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
    delete from T_CXQ_BRANCH_POOL
    where DCM_ID = #{dcmId,jdbcType=VARCHAR}
  </delete>

Mybatis的批量删除

对应的sql

delete from tbl_employee where id in (1,2,3);
delete from tbl_employee where id = 1 or id = 2 or id = 3

方式一通过id所组成的字符串实现批量删除

Mapper接口

//通过id所组成的字符串实现批量删除
public void deleteMoreEmp(String eids);

xml文件

<delete id="deleteMoreEmp">
         delete from tbl_employee where id in (${ids})
</delete>

注意：这里要用${},因为#{}默认会给字符串加上单引号

方式二使用 foreach 标签来进行删除

Mapper接口

//通过list集合实现批量删除
public void deleteMoreByList(List<Integer> ids);

xml文件

<delete id="deleteMoreByList">
    delete from tbl_employee where id in
    <foreach collection="list" item="id"  separator="," open="(" close=")">
        #{id}
    </foreach>
</delete>

方式三使用 foreach 标签来批量删除（使用 or）

Mapper接口

//通过list集合实现批量删除
public void deleteMoreByList(List<Integer> ids);

xml文件

 <delete id="deleteMoreByList">
     delete from tbl_employee where
     <foreach collection="list" item="id"  separator="or">
         id = #{id}
     </foreach>
</delete>

Mybatis的标签

＜sql> 和< include>

Mybatis中＜sql>标签和< include refid=“” >标签的用法

标签1

<sql id="Base_Column_List" >
    BOND_CODE, BOND_NAME,ORGANIZATION_NAME, ISSUER_AMOUNT, REMAINDER, INTEREST_DAY, DUE_DAY, TERM, COUPON_RATE,
    (SELECT tcdd.dict_name FROM T_CXQ_DATA_DICT tcdd WHERE tcdd.DICT_ITEM = 'PROVINCE' AND tcdd.DICT_CODE = PROVINCES) as PROVINCES_NAME,
    (SELECT tcdd.dict_name FROM T_CXQ_DATA_DICT tcdd WHERE tcdd.DICT_ITEM = 'PROVINCE' AND tcdd.DICT_CODE = CITY) as CITY_NAME
  </sql>

标签2

<sql id="Where_Clause">
    <where>
      <if test="bondCode != null and bondCode != ''">
        and BOND_CODE like '%' || #{bondCode,jdbcType=VARCHAR} || '%'
      </if>
      <if test="bondName != null and bondName != ''">
        and BOND_NAME like '%' ||  #{bondName,jdbcType=VARCHAR} || '%'
      </if>
      <if test="organizationName != null and organizationName != ''">
        and ORGANIZATION_NAME like '%' ||  #{organizationName,jdbcType=VARCHAR} || '%'
      </if>
    </where>
  </sql>

用法：

<select id="selectBySelective" 				    parameterType="cn.org.nafmii.bean.basicgreen.BasicGreenSearchVO"  resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from T_CXQ_BASIC_GREEN
    <include refid="Where_Clause"/>
    order by CRE_TIME desc , BOND_CODE desc
</select>

< foreach> 下面的标签

<!--  int insertBatch(List<TCxqPool> recordList); -->
<insert id="insertBatch" parameterType="java.util.List">
    insert ALL
    <foreach item="item" index="index" collection="list">
      into T_CXQ_BRANCH_POOL (DCM_ID,STATUS, CRE_USER_ID)
      values 
        (#{item.dcmId,jdbcType=VARCHAR},#{item.status,jdbcType=VARCHAR},
        #{item.creUserId,jdbcType=VARCHAR})
    </foreach>
    SELECT 1 FROM DUAL
  </insert>

foreach用来在SQL中迭代一个集合。

< item > 表示集合中每个元素进行迭代时的别名

< index> 用来记录每次迭代时的位置，第几次

< open>表示该语句以什么开始

< separator>表示在每次进行迭代之间以什么符号作为分隔符

< close> 表示以什么结束

< collection> 该属性是必须指定的，但是在不同情况下，该属性的值是不一样的
1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

< trim>标签

<insert id="insertSelective" keyColumn="FEEDBACK_ID" keyProperty="feedbackId" parameterType="cn.org.nafmii.bean.basicgreen.TCxqBasicGreenFeedback">
    insert into T_CXQ_BASIC_GREEN_FEEDBACK
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="feedbackId != null">
        FEEDBACK_ID,
      </if>
      <if test="organization != null">
        ORGANIZATION,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="feedbackId != null">
        #{feedbackId,jdbcType=VARCHAR},
      </if>
      <if test="organization != null">
        #{organization,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

< trim>标签用来拼凑sql

prefix:表示在trim包裹的SQL语句前面添加的指定内容

suffix：表示在trim包裹的SQL末尾添加指定内容

suffixOverrides：表示去掉（覆盖）trim包裹的SQL的指定首部内容

prefixOverrides：表示去掉（覆盖）trim包裹的SQL的指定尾部内容

Mybatis的多表查询实现

Mybatis的工作原理

根据配置文件（全局，sql映射）初始化出Configuration对象
创建一个DefaultSqlSession对象，它里面包含Configuration以及Executor（根据全局配置文件中的defaultExecutorType创建出对应的Executor）
DefaultSqlSession.getMapper（）：拿到Mapper接口对应的MapperProxy；
MapperProxy里面有（DefaultSqlSession）；
执行增删改查方法：
调用DefaultSqlSession的增删改查（Executor）；
会创建一个StatementHandler对象。同时也会创建出ParameterHandler和ResultSetHandler）
调用StatementHandler预编译参数以及设置参数值，使用ParameterHandler来给sql设置参数
调用StatementHandler的增删改查方法；
ResultSetHandler封装结果

注意：四大对象(Executor、ParameterHandler、ResultSetHandler)每个创建的时候都有一个interceptorChain.pluginAll(parameterHandler);

coder chen

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis的初级使用小结

mybatis的尚硅谷笔记Mybatis的insertMyBatis的批量insertMyBatis的动态insertMybatis的普通insert方法Mybatis的selectMyBatis的select 动态条件MyBatis的动态select service拼接条件Mybatis的updateMybatis的普通updateMybatis的动态updateMybatis的deleteMybatis的单条删除Mybatis的批量删除Mybatis的标签＜sql> 和< include&.
复制链接

扫一扫