sql语句总结2018-02-07

最新推荐文章于 2020-07-20 01:06:11 发布

一朵风中摇曳的水仙花

最新推荐文章于 2020-07-20 01:06:11 发布

阅读量282

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： sql 总结

本文链接：https://blog.csdn.net/u012045045/article/details/79315867

mysql 专栏收录该内容

97 篇文章

订阅专栏

本文提供了一组使用 MyBatis 进行 CRUD 操作的示例代码，包括增删改查等基本操作，并展示了如何进行条件查询、批量查询及分页查询等功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql语句总结2018-02-07
（1）增【含校验】

<insert id="insertSelective" parameterType="com.mmmm.template.db.basic.domain.SysDemo" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    insert into SYS_DEMO
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="demoId != null" >
        DEMO_ID,
      </if>
      <if test="demoName != null" >
        DEMO_NAME,
      </if>
      <if test="demoMobile != null" >
        DEMO_MOBILE,
      </if>
      <if test="demoEmail != null" >
        DEMO_EMAIL,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="demoId != null" >
        #{demoId,jdbcType=VARCHAR},
      </if>
      <if test="demoName != null" >
        #{demoName,jdbcType=VARCHAR},
      </if>
      <if test="demoMobile != null" >
        #{demoMobile,jdbcType=VARCHAR},
      </if>
      <if test="demoEmail != null" >
        #{demoEmail,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

【不含校验】

  <insert id="insert" parameterType="com.mmmm.template.db.basic.domain.SysDemo" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    insert into SYS_DEMO (DEMO_ID, DEMO_NAME, DEMO_MOBILE, 
      DEMO_EMAIL)
    values (#{demoId,jdbcType=VARCHAR}, #{demoName,jdbcType=VARCHAR}, #{demoMobile,jdbcType=VARCHAR}, 
      #{demoEmail,jdbcType=VARCHAR})
  </insert>

（2）删

  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    delete from SYS_DEMO
    where DEMO_ID = #{demoId,jdbcType=VARCHAR}
  </delete>

（3）改【含校验】

  <update id="updateByPrimaryKeySelective" parameterType="com.mmmm.template.db.basic.domain.SysDemo" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update SYS_DEMO
    <set >
      <if test="demoName != null" >
        DEMO_NAME = #{demoName,jdbcType=VARCHAR},
      </if>
      <if test="demoMobile != null" >
        DEMO_MOBILE = #{demoMobile,jdbcType=VARCHAR},
      </if>
      <if test="demoEmail != null" >
        DEMO_EMAIL = #{demoEmail,jdbcType=VARCHAR},
      </if>
    </set>
    where DEMO_ID = #{demoId,jdbcType=VARCHAR}
  </update>

【不含校验】

<update id="updateByPrimaryKey" parameterType="com.mmmm.template.db.basic.domain.SysDemo" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update SYS_DEMO
    set DEMO_NAME = #{demoName,jdbcType=VARCHAR},
      DEMO_MOBILE = #{demoMobile,jdbcType=VARCHAR},
      DEMO_EMAIL = #{demoEmail,jdbcType=VARCHAR}
    where DEMO_ID = #{demoId,jdbcType=VARCHAR}
  </update>

（4）查

 <sql id="Base_Column_List" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    DEMO_ID, DEMO_NAME, DEMO_MOBILE, DEMO_EMAIL
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    select 
    <include refid="Base_Column_List" />
    from SYS_DEMO
    where DEMO_ID = #{demoId,jdbcType=VARCHAR}
  </select>

（5）sql返回值封装成对象类

  <resultMap id="BaseResultMap" type="com.mmmm.template.db.basic.domain.SysDemo" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <id column="DEMO_ID" property="demoId" jdbcType="VARCHAR" />
    <result column="DEMO_NAME" property="demoName" jdbcType="VARCHAR" />
    <result column="DEMO_MOBILE" property="demoMobile" jdbcType="VARCHAR" />
    <result column="DEMO_EMAIL" property="demoEmail" jdbcType="VARCHAR" />
  </resultMap>

（6）模糊查询


<if test="org.orgCode != null and org.orgCode != ''">
and ORG_CODE like CONCAT(CONCAT('%','${org.orgCode}'),'%')
</if>
（7）多层条件查询
<sql id="Base_Column_List" >
ORG_ID, PARENT_ORG_ID, REAL_ORG_ID, REAL_LEVEL, NAME, SHORT_NAME, ADDRESS, CREATOR_ID,
CREATE_TIME, UPDATE_TIME, STATUS, SYS_SYNC_TIME
</sql>
<select id="selectByExample" resultMap="BaseResultMap" parameterType="com.tesla.common.domain.SysOrgExample" >
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
from SYS_ORG
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
</select>
（8）批量查询

<select id="selectuserId" parameterType="java.lang.String"
resultType="java.util.Map">
select
ID,IDTYPE,IDNUM,USERNAME,PHONENUM
from (
<foreach collection="tables" item="item" separator="union all">
select
ID,ID_TYPE IDTYPE,ID_NUM IDNUM,USER_NAME USERNAME,PHONE_NUM PHONENUM
from ${item}
where 1=1

<if test="idNum != null and idNum != ''">
and ID_NUM like CONCAT(CONCAT('%','${idNum}'),'%')
</if>
<if test="userName != null and userName != ''">
and USER_NAME like CONCAT(CONCAT('%','${userName}'),'%')
</if>
<if test="phoneNum != null and phoneNum != ''">
and PHONE_NUM like CONCAT(CONCAT('%','${phoneNum}'),'%')
</if>
</foreach>
)
where 1=1
</select>
（9）多变量的批量查询
<select id="batchSelectUserChannelId" parameterType="java.lang.String"
resultMap="userChannels">
select
USER_ID,
CHANNEL_USER_ID
from
(
<foreach collection="userIds" item="item" separator="union all">
select
USER_ID,
CHANNEL_USER_ID
from ${item.tableName}
where 1=1
<if test="#{item.userId} != null and #{item.userId} != ''">
and USER_ID=#{item.userId}
</if>
</foreach>
)
where 1=1
</select>
（10）查询sequence
<select id="selectNextVal" resultType="java.lang.Integer">
select
USERSSEQUENCEGENERATOR.nextval
from
dual
</select>
（11）批量分页查询【带条件】
<select id="selectSubBraInfoByIdsDoPagination" parameterType="java.lang.String" resultMap="braOffRecSubBra">

select

ID,
SUB_BRANCH_ORG_CODE,
SUB_BRANCH_NAME,
BRANCH_ID,
BRANCH_NAME

from (
<foreach collection="idList" item="item" separator="union all">

select
a.ID,
SUB_BRANCH_ORG_CODE,
SUB_BRANCH_NAME,
BRANCH_ID,
BRANCH_NAME
from ONLINE_BRA_OFF_REC_SUB_BRA a
left join ONLINE_BRA_OFF_REC_BRA b
on a.branch_id=b.id

where a.ID=#{item}

</foreach>
)

where 1=1
<if test="subBranchNameParam != null and subBranchNameParam != ''">
and SUB_BRANCH_NAME like CONCAT(CONCAT('%','${subBranchNameParam}'),'%')
</if>
order by ID asc
</select>
（12）批量删除

<delete id="deleteLogicGroupBatch">
<foreach collection="list" item="item" index="index" open="begin"
close=";end;" separator=";">
delete from T_LOGIC_GROUP t
where t.PLAN_CODE = #{item.key1}
</foreach>
</delete>