Mybatis常用技巧

最新推荐文章于 2024-06-18 15:55:13 发布

intx

最新推荐文章于 2024-06-18 15:55:13 发布

阅读量438

点赞数

分类专栏：工作 MyBatis

本文链接：https://blog.csdn.net/changudeng1992/article/details/80971692

版权

工作同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MyBatis

2 篇文章 0 订阅

订阅专栏

持久层代码：（ServiceImpl->Mapper.java->Mapper.xml）

taMarginLendingRecordExtendsMapper.selectByCreateTime(totalApplyParam);

List<TaMarginLendingRecord> selectByCreateTime(Map<String , Object> param);//参数以map传入的查询方式

<select id="selectByCreateTime" resultMap="io.fdt.trade.admin.mapper.TaMarginLendingRecordMapper.BaseResultMap" >

    select

    <include refid="io.fdt.trade.admin.mapper.TaMarginLendingRecordMapper.Base_Column_List" />

    from TA_MARGIN_LENDING_RECORD

    <where>

        <if test="symbol != null">

            and SYMBOL = #{param.symbol,jdbcType=VARCHAR}

        </if>

        <if test="route != null">

            and ROUTE = #{param.route,jdbcType=VARCHAR}

        </if>

        <if test="groupId != null">

            and GROUP_ID = #{param.groupId,jdbcType=VARCHAR}

        </if>

        <if test="beginDate != null and endDate != null">

            and CREATE_TIME BETWEEN #{param.beginDate,jdbcType=TIMESTAMP} and #{param.endDate,jdbcType=TIMESTAMP}

        </if>

    </where>

    order by CREATE_TIME asc

</select>

/****************************************************************/

accountPoolExtendsMapper.selectByAccountIds(accountIds);

List<AccountPool> selectByAccountIds(@Param("param") List<String> param);  //参数以list传入的查询方式

<select id="selectByAccountIds" resultMap="io.fdt.trade.admin.trade.mapper.AccountPoolMapper.BaseResultMap">

    select INSTRUMENT_POOL_ID , ACCOUNT

    from ACCOUNT_POOL

    where ACCOUNT in

    <foreach collection="list" index="index" item="accountId" open="(" separator="," close=")">

        #{accountId,jdbcType=VARCHAR}

    </foreach>

</select>

accountPoolExtendsMapper.select(param);

List<AccountPool> select(Map<String, Object> param);

<select id="select" resultMap="io.fdt.trade.admin.trade.mapper.AccountPoolMapper.BaseResultMap">

    select INSTRUMENT_POOL_ID, group_concat(ACCOUNT) as ACCOUNT

    from ACCOUNT_POOL

    <where>

        <if test="poolList != null and poolList.size() > 0">

            INSTRUMENT_POOL_ID in

            <foreach collection="poolList" index="index" item="poolId" open="(" separator="," close=")">

                #{poolId,jdbcType=VARCHAR}

            </foreach>

        </if>

    </where>

    group by INSTRUMENT_POOL_ID

</select>

/****************************************************************/

taCompanyStockPoolExtendsMapper.countRecordsByOrderRoute(param);

@Select("select count(*) from TA_COMPANY_STOCK_POOL where SYMBOL = #{param.symbol} and GROUP_ID = #{param.groupId} and ORDER_ROUTE = #{param.orderRoute}")

int countRecordsByOrderRoute(@Param("param") Map<String, Object> params);

taCompanyStockPoolExtendsMapper.selectSumQtyByRouteAndSymbol(param);

Double selectSumQtyByRouteAndSymbol(@Param("param") Map<String, Object> param);

taCompanyStockPoolExtendsMapper.insertSelective(tcsp);//实现传入对象的可变参数个数的插入

<insert id="insertSelective" parameterType="io.fdt.trade.admin.model.TaCompanyStockPool" >

  insert into TA_COMPANY_STOCK_POOL

  <trim prefix="(" suffix=")" suffixOverrides="," >

    <if test="route != null" >

      ROUTE,

    </if>

    <if test="poolName != null" >

      POOL_NAME,

    </if>

    <if test="symbol != null" >

      SYMBOL,

    </if>

    <if test="name != null" >

      NAME,

    </if>

    <if test="totalQty != null" >

      TOTAL_QTY,

    </if>

    <if test="origPool != null" >

      ORIG_POOL,

    </if>

    <if test="orderRoute != null" >

      ORDER_ROUTE,

    </if>

    <if test="createTime != null" >

      CREATE_TIME,

    </if>

    <if test="updateTime != null" >

      UPDATE_TIME,

    </if>

    <if test="operator != null" >

      OPERATOR,

    </if>

    <if test="groupId != null" >

      GROUP_ID,

    </if>

    <if test="outerId != null" >

      OUTER_ID,

    </if>

  </trim>

  <trim prefix="values (" suffix=")" suffixOverrides="," >

    <if test="route != null" >

      #{route,jdbcType=VARCHAR},

    </if>

    <if test="poolName != null" >

      #{poolName,jdbcType=VARCHAR},

    </if>

    <if test="symbol != null" >

      #{symbol,jdbcType=VARCHAR},

    </if>

    <if test="name != null" >

      #{name,jdbcType=VARCHAR},

    </if>

    <if test="totalQty != null" >

      #{totalQty,jdbcType=DOUBLE},

    </if>

    <if test="origPool != null" >

      #{origPool,jdbcType=VARCHAR},

    </if>

    <if test="orderRoute != null" >

      #{orderRoute,jdbcType=VARCHAR},

    </if>

    <if test="createTime != null" >

      #{createTime,jdbcType=TIMESTAMP},

    </if>

    <if test="updateTime != null" >

      #{updateTime,jdbcType=TIMESTAMP},

    </if>

    <if test="operator != null" >

      #{operator,jdbcType=VARCHAR},

    </if>

    <if test="groupId != null" >

      #{groupId,jdbcType=VARCHAR},

    </if>

    <if test="outerId != null" >

      #{outerId,jdbcType=BIGINT},

    </if>

  </trim>

</insert>

 taCompanyStockPoolExtendsMapper.selectRecordsByConditions(param);//注解编写sql语句的形式

@Select("select * from TA_COMPANY_STOCK_POOL where GROUP_ID = #{param.groupId} and ROUTE = #{param.route} and SYMBOL = #{param.symbol}")

@ResultMap("io.fdt.trade.admin.mapper.TaCompanyStockPoolMapper.BaseResultMap")

TaCompanyStockPool selectRecordsByConditions(@Param("param") Map<String, Object> params);

taCompanyStockPoolExtendsMapper.updateQtyByUniKey(param);

int updateQtyByUniKey(@Param("param") Map<String, Object> param);

<update id="updateQtyByUniKey" parameterType="java.util.Map">

    update TA_COMPANY_STOCK_POOL

    set TOTAL_QTY = #{param.qty},

    UPDATE_TIME = #{param.updateTime},

    OPERATOR = #{param.oper}

    where ROUTE = #{param.route}

    and SYMBOL = #{param.symbol}

    and GROUP_ID = #{param.groupId}

</update>

taCompanyStockPoolExtendsMapper.insertBatchSelective(taCompanyStockPoolAddList);

int insertBatchSelective(@Param("list") List<TaCompanyStockPool> list);

<insert id="insertBatchSelective">

    insert into

    TA_COMPANY_STOCK_POOL

    (ROUTE,SYMBOL,GROUP_ID,NAME,TOTAL_QTY,ORDER_ROUTE,CREATE_TIME,UPDATE_TIME,OPERATOR)

    values

    <foreach collection="list" item="record" separator="," >

        (#{record.route,jdbcType=VARCHAR},#{record.symbol,jdbcType=VARCHAR},#{record.groupId,jdbcType=VARCHAR},

        #{record.name,jdbcType=VARCHAR},#{record.totalQty,jdbcType=DOUBLE},#{record.orderRoute,jdbcType=VARCHAR}, #{record.createTime,jdbcType=TIMESTAMP},#{record.updateTime,jdbcType=TIMESTAMP},#{record.operator,jdbcType=DOUBLE})

    </foreach>

</insert>

/****************************************************************/

taGroupAccountExtendsMapper.selectLtsAccountIdByGroupId(groupId);

@Select({"<script>select distinct LTS_ACCOUNT_ID from TA_GROUP_ACCOUNT ",

        "<if test=\"groupId != null and groupId != ''\">",

        " where GROUP_ID = #{groupId}",

        "</if>",

        " order by LTS_ACCOUNT_ID</script>"})

List<String> selectLtsAccountIdByGroupId(@Param("groupId") String groupId);

/****************************************************************/

<select id="selectUnStartBorrow" parameterType="java.lang.String"
        resultMap="io.fdt.trade.admin.mapper.TaAccountFundMapper.BaseResultMap">
    <![CDATA[
    select *
    from TA_ACCOUNT_FUND
    where LTS_ACCOUNT_ID = #{accountId} and TYPE = 'new' and START_DATE > DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')
    and DEADLINE >= START_DATE
    ]]>
</select>

/****************************************************************/

<select id="selerowRate" resultType="java.lang.Double" parameterType="java.lang.String">
    <![CDATA[
    select RATE
    from TA_ANT_FUND
    where LS_ACCT_ID = #{acutId} and TYPE = 'new'
    and RATE IS NOT NULL
    order by UPDATE_TIME desc
    limit 1
    ]]>
</select>

/****************************************************************/

IFNULL(expr1,expr2)的用法：

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1;
否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

<select id="sumInfedfff" resultType="java.lang.Double" parameterType="java.lang.String">
    select IFNULL(sum(FUND), 0)
    from TA_ANT_FUND
    <where>
        TYPE = 'deduct' and STATUS = 'done'
        <if test="acd != null">
            and LS_ACT_ID = #{accd}
        </if>
    </where>
</select>

/****************************************************************/

<select id="selectCountByParams" resultType="java.lang.Integer" parameterType="map" >
select count(distinct ${groupBy}) from TA_MARGIN_LENDIÅNG_RECORD
<where>
<if test="limit != null and length != null">
    limit #{limit}, #{length}
</if>
<if test="abandon != null" >
    and ABANDON = #{abandon,jdbcType=TINYINT}
</if>
<if test="startTime != null">
    and CREATE_TIME &gt; #{startTime}
</if>
<if test="endTime != null">
    and CREATE_TIME &lt;= #{endTime}
</if>
</where>

/****************************************************************/

<if test="type != null and type != '' ">
    and TYPE = #{type,jdbcType=VARCHAR}
</if>
<if test="currency != null and currency != '' ">
    and CURRENCY = #{currency,jdbcType=VARCHAR}
</if>
注意：当前端可能传过来type=这样的空数据时会组装参数为""，所以一定要添加！=‘’这样的空值校验排除。
/****************************************************************/

Mybatis 中$与#的区别

1 #是将传入的值当做字符串的形式，eg:select id,name,age from student where id =#{id},当前端把id值1，传入到后台的时候，就相当于 select id,name,age from student where id ='1'.

2 $是将传入的数据直接显示生成sql语句，eg:select id,name,age from student where id =${id},当前端把id值1，传入到后台的时候，就相当于 select id,name,age from student where id = 1.

3 使用#可以很大程度上防止sql注入。(语句的拼接)

4 但是如果使用在order by 中就需要使用 $.

5 在大多数情况下还是经常使用#，但在不同情况下必须使用$.

我觉得#与的区别最大在于：#{} 传入值时，sql解析时，参数是带引号的，而的区别最大在于：#{} 传入值时，sql解析时，参数是带引号的，而{}穿入值，sql解析时，参数是不带引号的。

一 : 理解mybatis中 $与#

在mybatis中的$与#都是在sql中动态的传入参数。

eg:select id,name,age from student where name=#{name} 这个name是动态的，可变的。当你传入什么样的值，就会根据你传入的值执行sql语句。

二:使用$与#

#{}: 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符，一个 #{ } 被解析为一个参数占位符。

${}: 仅仅为一个纯碎的 string 替换，在动态 SQL 解析阶段将会进行变量替换。

name-->cy

eg: select id,name,age from student where name=#{name} -- name='cy'

select id,name,age from student where name=${name} -- name=cy

实际开发中出现的：

<select id="selectCashFlowList" resultType="java.util.Map" >
    select
    ACCOUNT_ID as accountId,
    OPERATE_DATE as operateDate,
    TYPE as type,
    AMOUNT as amount,
    DESCRIPTION as description,
    PARENT_ORDER_ID as parentOrderId,
    MARKET as market,
    CURRENCY as currency,
    OPERATOR as operator,
    CREATE_TIME as createTime,
    UPDATE_TIME as updateTime
    from TA_CASH_FLOW
    <where>
        <if test="accountId != null">
            and ACCOUNT_ID = #{accountId,jdbcType=VARCHAR}
        </if>
        <if test="type != null and type != '' ">
            and TYPE = #{type,jdbcType=VARCHAR}
        </if>
        <if test="currency != null and currency != '' ">
            and CURRENCY = #{currency,jdbcType=VARCHAR}
        </if>
        <if test="startDate != null">
            and OPERATE_DATE &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            and OPERATE_DATE &lt;= #{endDate}
        </if>
    </where>
    <if test="orderBy!=null and orderBy!='' ">
        order by ${orderBy} ${ascending} limit ${startIndex},${limit}
    </if>
</select>

注意：红色的$必须用$。

》>>>>>>>>>>>>>>>>>>>>>

https://blog.csdn.net/w_wensan/article/details/76513085

DATE_FORMAT()函数。

 select * from OPEN_POSITIONS
         where TRADE_DATE = DATE_FORMAT(now(), '%Y-%m-%d')

DATE_FORMAT(COP_TIME, '%Y-%m-%d'):

 <if test="today != null">
                <![CDATA[
                and DATE_FORMAT(COP_TIME, '%Y-%m-%d') <= #{today})   
                ]]>
</if>

intx

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录